# Charitable Contributions and IRR

I need some help from the math majors out there, maybe if Biticodes Elon Musk is ever available, haha. The question of the week a couple of weeks ago concerned making charitable contributions and taking the tax deductions. For those not familiar with the US tax code, money paid to certain charities is deductible for income tax purposes, generally within the year paid.

The situation: I want to make a \$1,000 charitable contribution in the first part of 2005 and I decide that I want to do this the last week of 2004. There exists some date in 2005 before which it makes sense for me to make the contribution on December 31, 2004 thereby getting the deduction earlier than if I had made it when planned. Positive and negative cash flows sounds like a problem for IRR (Internal Rate of Return) to me.

Some assumptions: I make 10% on my money and I pay a total tax rate of 33%. Also, I get a refund three months after year end.

I set up the cash flows and calced the IRR but didn’t get the expected results. I wanted to see if I could figure it out manually to help me find my error. Manually, I come up with April 30th, i.e. contributions I plan on making before April 30th should be made before the end of the year, and those after should be made when planned. My question is: Why don’t the IRRs equal when the cash flows from the manual method equal?

Column B – I make the payment on Dec 31st and get the refund on Mar 31st. Thereafter, I earn 10% on my money.

Column D – I earn money on my \$1,000 until I pay it out, then get my refund three months after I file my 2005 return.

Columns F and H just show the cash out because IRR is supposed to take the time value of money into consideration.

You can download CharityIRR.zip to see how it works. And if you know where I have erred, please educate me.

## 21 thoughts on “Charitable Contributions and IRR”

1. Lyle Green says:

Hi Dick

Lyle Green

2. Michael Markov says:

Lyle,

Are you wiiling to share your add-in? I find myself doing more NPV type analyses all the time, and the data entry can be tricky!

3. fxp says:

As an aside, the number of roots according to Decartes rule of signs for a polynomial is equal to the number number of sign changes. In the second example, two sign changes in cash flow equals only two roots. Since, multiple roots are mathematically feasible, they could still be economically absurd and therefore, potentially ignored. The conclusion, I agree with number (1) keep it simple, use NPV for ranking purposes.

4. Lyle Green says:

Michael

The polynomial stuff is beyond my level. I’m a finance guy, not a math guy. in re your question, I’ll share the add-in with anyone who wants it, provided they don’t laugh at the spaghetti code and bad programming practices. I would need to clean it up a bit first.

5. Seconding Lyle’s comments

Although as your cashflows occur by months and you have dates its probably easiest (and slightly more accurate) to use XNPV (enable via Tools -Addins and check Analysis Toolpak)

Lyle used
=NPV(10%/12,F11:F25)+F10
an alternative is
=XNPV(10%,F10:F37,A10:A37)

ALthough in this case it is a very minor difference, XNPV is more useful when converting full period cashflows to mid period valuations.

And think your discounted cashflows should just represent your investment as they do in columns F & H, the cashflows in columns B (B14 to B25)and D (D10 to D13) that represent getting a 10% return on your funds elswewhere are double dipping on the discount rate

Finally a litte diatribe on IRR

IRR is very much a secondary investment measure to NPV as

– it has no scale
– it assumes that project cashflows are re-invested at the projects rate of return. This may sound like gibberish but if a project returns 30% on invested cashflows and you can only reinvest the released cashflows at 10% somewhere else, the true return isn’t 30% as indicated by the IRR. The MIRR formula mentioned by Lyle handles this by allowing you to enter a re-investment rate
– the assumption re the IRR reinvestment return can lead to manipulation of IRR results by playing with the cashflows in the first few years, I’ve seen it done :)
– the Excel IRR formula assumes that cashflows are evenly spaced, if this isn’t the case you can use XIRR instead which accepts a date input

David Hager has given us a UDF combining MIRR and XIRR functionality, see http://www.j-walk.com/ss/excel/eee/eee017.txt

Lyle, I’d like to look at that addin, sounds interesting

Cheers

Dave

6. Lyle Green says:

Hi Dave

As I mentioned to Michael, I’ll just clean-up the add-in a bit. I created it mostly for my own use and for my staff. So I didn’t take the same care with respect to proper annotation, error handling etc as I would have for a general release add-in. Also in re: the NPV calculation actually I didn’t use the Excel formula at all. I don’t generally use the built in formulas because they assume that the data is organized a certain way, plus (in the case of IRR) they don’t annualize the Rate. I generally either use my add-in,which is userform based and then put the results into a worksheet or I use UDFs. Regards. Lyle

7. Hi Lyle,

I assumed you had used that NPV equation as it matched my formula test – my apologies.

I dont use Excel’s NPV equation either, I do it by calculating period by period discount factors and then I cross check this with an XNPV calc.

Can you please add me to your addin mail list at brettdj@vbaexpress.com when you have cleaned it up.

Cheers

Dave

8. Carl Betterton says:

Dick,

Your blog is a new find for me, and is very well done. I enjoy it and learn something each time I tune in.

I’m no expert but agree with the comments on negatives of the IRR approach, and those about “double dipping.” Using Net Present Value for the two cases (your columns F and G), and a 10% rate, I get (\$752.07) for the first case (your column F) and (\$604.01) for the second case (your column G). If you hold on to the \$1,000 longer you lose less money. This makes sense. If one misses the end of year for contributions, I think it will always be better to wait and make contributions on the next Dec 31st, from a purely financial stance. This brings us back to question the original premise, “There exists some date in 2005 before which it ….”

Best regards,

Carl

PS: Lyle, can you please add me to your addin mail list at carlb@uga.edu if you decide to send it out? Thanks.

9. Joel says:

Hi,

I had decided to use the XNPV function to calculate the NPV but I wanted to use it in VBA code. What I did not realise that the argument when calling the function through VBA should be an array of longs rather than an array of dates. I have put the code on my blog. Feel free to use it.

http://packetguy.blogspot.com/2005/06/excel-xirr-and-xnpv-functions.html

10. Cliff Crittenden says:

Lyle, can you please add me to your addin mail list at “math AT cliffcrittenden DOT com” if you decide to send it out?

Thanks.

Cliff

11. Found your site via google and I’m hoping someone here can help.

I’m trying to calculate an Investor’s Quarterly to Date IRR (By Bucket) and I keep hitting a stone wall. Here is simple example of what I am trying to do.

Investor invests \$1000.00
Month #1 => Investment losses 90.00 and pays 10.00 in Mgmt Fees
Month #2 => Investment losses 80.00 and pays 10.00 in Mgmt Fees

So ….
Month #1
A:Month #1 PNL Percentage (90/1000) = .09
B:Month #1 Mgmt Fee Percentage (10/1000) = .01

Month #2
C:Month #2 PNL Percentage ( A + 80/(1000-90-10) ) = .17889
D:Month #2 Mgmt Fee Percentage ( B + 10/(1000-90-10) ) = .021111

My Problem is that when I calculate the TOTAL IRR (PNL and Mgmt Fee) – my Total percentage
DOES NOT EQUAL THE SUM OF THE BUCKET %. IT ONLY WORKS FOR THE FIRST MONTH.

Month #1: (PNL + MgmtFee)/Investment => 100/1000 = .01
Month #2: (ALL PNL + ALL MgmtFee)/Investment => .19

My Impression is that I’m comparing Apples to Oranges – my numbers will never tie. BUT there has to be a way to report Month To Date, Quarter To Date AND Year To Date Compound PNL Percentages by Bucket.

I hope this all makes sense and someone can help.

Thanks,
T

12. Carmen says:

I came upon this site in google when looking for information to unravel the following mystery. I hope you can help me. I am calculating IRR for a project based on monthly cash flows. Therefore I add *12 to the Excel IRR formula to convert the rate to an annual rate. Since my company typically uses quarterly cash flows for feasibility analyses, I converted the monthly cash flows to quarterly cash flows (by adding the three months that make up each quarter) and calculated using *4 at the end of the formula, again to make the IRR rate an annual rate. I come up with a 26% for the monthly version and 39% for the quarterly version. I expected a difference, because of the change in frequency, but not of this magnitude. It seems to me that I may be doing something wrong. Can you provide some insight into what is happening? I have an Excel worksheet that I can transmit to you to illustrate.

13. Carmen,

What do your cashflows look like – can you please give an example?

suggestion:
Enable the Analysis ToolPak.
Then use XIRR to give you the correct rate of return

And you can’t use the method your method above when dealing with compounding cashflows. For example say your bank charged you 12% interest on your home loan. Monthly payments would result in an annual rate of 12.67% – not 12%

Cheers

Dave (brettdj)

14. In calculating the IRR% using MONTHLY cash streams does one multiply the formula by 12 irrespective of whether the cashflow is say 12 months or 48 months ie. you’re using the figure 12 as a common factor to convert years to months?

15. Joff: Yes, if you want the annual rate of return. If you want the monthly rate of return, you’d leave it as is. Annual is very common.

16. Haad Bajwa says:

Hi Guys,
I am trying to Design a calculator for a customer:
INPUTS ARE:
1.Cash flow with Variable Dates.
2.XNPV

OUPUT?
Rate of Gain or The rate through which XNPV is calculated
The solution I came up with is Gool seek is EXCEL but the problem is it slows down my program.
I was wondering if any one knows a method how to calculate the rate PLEASE HELP ME.
I am not looking for code just an algorithm or concept. I am not looking for XIRR I know how to calculate that. Do You think goal seek is the best method.
thanks in Advance for you Suggestions

17. Haad Bajwa says:

East Asia direct – Pakistan
I guess this Message Board is Closed well thanks for your Help.

18. Joe Hamel says:

I have a question;

Why do you have to use a negative sign for a positive cash flow in Excel when using the PV function to arrive at a postive number?