Charitable Contributions and IRR

Posted on by Dick Kusleika

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?

CharityIRR

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. Hi Dick

    Let me first say that I’m a big fan of your blog and I’ve learned a lot from it. With respect to your problem there are a few things at work here. First off, note that because your cashflows are monthly your IRR calculations are also monthly i.e. your -30.9% IRR is actually -370.8% on an annualized basis. The crux of the problem is the timing of the $1000 payout. you take t=0 to be December 31, 2004 in both cases. This works in example one because your initial cashflow is negative and everything else is positive. The sign changes only once. For example 2 though your initial cashflow is not -$1000 it is 0. Subsequently the sign of your cashflow changes twice (positive to negative to positive). IRR calculations assume that the sign changes only once. This means that there may be one true IRR or many. With multiple sign changes you must use a Modified Internal Rate of Return (MIRR Function). The IRR will not return a true result. For your analysis I would suggest that NPV is a better way to compare the two alternatives. Under Scenario 1 NPV=($678.11) under Scenario 2 it is ($675.97) using your 10% Cost of Capital. I hope this helps. Feel free to contact me if I can help in any way or if I’ve confused you. I’m almost embarassed to offer this given your level of VBA expertise compared to mine but I do have an Excel add-in that I developed a few years ago. It does several types of financial analysis and it’s much more powerful than the built in tools (which I never) use. Take care

    Lyle Green

  2. 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. 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. 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. 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. 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.

  10. 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

  13. 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.

  14. 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)

  16. 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?

  18. 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

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

  20. 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?

    Your answer would be appreciated. Please leave at jrham@att.net.

    Thank You
    Joe

  21. Good Website! I wondered if I would be able quote a portion of your website and use a handful of things for a term paper. Please email me whether that would be fine. Thanks

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.