IRR calc with different ending values

Hello all,

I'm working on a project to analyze several different investments' impacts on a portfolio's total IRR. I have three tables:

1 - Cash flows. This just shows the cash flows for the entire portfolio. The final cash flow (9-1-16) represents cash flows during the month plus the portfolio's ending value.
2 - Quarterly Gain. Each investment has had either a gain (positive) or a loss (negative) during the quarter.
3 - IRR Impact (this is what I need help on). This table shows how much each investment's gain/loss has impacted the portfolio's overall IRR.

The way I'm doing it now (as can be seen in the Example - Apple tab) is to subtract the 9-1-16 cash flow by each deal's quarterly gain individually and then manually pasting that value in to the IRR Impact table. I know there is a formula that can do all this legwork for me. Any idea on the best way to automate this?

I can be as flexible as needed on the format / structure, so if helper columns are the best solution then I'm happy to throw them in.

Thanks in advance for your help! Let me know if you have any questions.

You're on the right track. Thank you for taking a look.

Yes - I do want the 0.74% to show up in cell I4, but then I need to recalculate it for "pear" by subtracting $1,375 from the $100,000 in C43. Then I have to do it again for Orange, and again for Grapefruit, etc. So I'm trying to find a formula that will subtract the quarterly gain for each deal from the Sep 1, 16 cash flow and return the result to the IRR Impact table.

Hopefully that makes a little sense... Let me know if you have any follow-up questions.

Originally Posted by IronCladRooster

The way I'm doing it now (as can be seen in the Example - Apple tab) is to subtract the 9-1-16 cash flow by each deal's quarterly gain individually and then manually pasting that value in to the IRR Impact table. I know there is a formula that can do all this legwork for me. Any idea on the best way to automate this?

Array-enter (press ctrl+shift+Enter, not just Enter) the following formula into L4, then copy and paste into L5:L10:

Please Login or Register  to view this content.

You might not need the -10%. Or you might need some "guess" value (not necessarily -10%) for some of the formulas. Moreover, you might need different "guess" values for each formula. See below.

Also note that the IRR might not be computable at all for some cash flow series. Mathematically, the IRR is unstable when there are so many signs changes in the cash flows. See the IRR wikipage.

-----

Originally Posted by MrShorty

I don't know why, but the existing IRR() function gives me a #NUM error.

Me, too. Because in our Excel versions (mine is XL2007), IRR needs a "guess" value. Presumably, "IronClad" is using a later version of Excel where IRR has been improved, directly or indirectly.

If we look at the NPV curve for varying IRRs, we see that the original monthly IRR is between 0% and 5%. So ordinarily, I would start with a "guess" of 2.5%.

(In fact, we can calculate the required "guess" value from the annualized IRR that "IronClad" calculated. It is about 0.91%. But we usually do not have that information.)

However, that guess might not work for all of the IRRs of the modified cash flows, which we calculate in column L.

In the past, I have found that a "guess" of -10% works more consistently, for no good reason. No guarantee, however.

Of course, given the NPV curve, any negative guess is counter-intuitive. On the other hand, sometimes even a close guess does not work.

I have long-suspected that the Excel IRR and XIRR implementations approximate the derivative using a difference quotient. In that case, the step size is critical when the derivative is steep, which we can see in the NPV curve for the example that "IronClad" posted.

In contrast, my own IRR implementation calculates an exact derivative. It rarely needs a "guess" value. And it rarely fails to derive an IRR, if there is only one and it is computable.

Originally Posted by MrShorty

I don't know why, but the existing IRR() function gives me a #NUM error.

I would use a separate column for each "variation" of the IRR() calculation you want. I might
1) put something like "Apple" in Q2 (to identify which variation this column represents, and to use as a lookup value in a later lookup function).
2) Q4 =$C4 (note the absolute reference, so this column will be easy to copy across). copy down
3) In Q43 enter =$C43-VLOOKUP(Q2,$E$4:$F$10,2,FALSE) again, note the mix of relative and absolute references.
4) Copy the C45 over to Q45 and add any other additional calculations you want.
5) Make copies of column Q into R, S, T, ...
6) Then enter the desired formula/references in your impact calculation tables to see the summaries of the IRR impact.

This would do the trick. The table could get a little long, so maybe I'll do all of this legwork on a separate tab and reference everything over to the master tab.

Originally Posted by joeu2004

Array-enter (press ctrl+shift+Enter, not just Enter) the following formula into L4, then copy and paste into L5:L10:

Please Login or Register  to view this content.

Hmm I couldn't get this formula to work for me either. Thanks for trying though.

And yes - I'm using Excel 2010, but I don't know how much has changed on the IRR calc between 07 and 10.

Originally Posted by joeu2004

Array-enter (press ctrl+shift+Enter, not just Enter) the following formula into L4, then copy and paste into L5:L10:

Please Login or Register  to view this content.

Originally Posted by IronCladRooster

Hmm I couldn't get this formula to work for me

Works just fine for me, at least for the examples that you provided originally. See the column L of Sheet1 in the attachment [1].

IRR Array Calc.xlsx (click here)

(Could not use the Attachment feature in this forum.)

Unfortunately, you do not say in what way the formula did not work for you. Perhaps I can help, if you attach an example with the errant formula.

Some guesses....

Perhaps you did not array-enter the formulas (press ctrl+shift+Enter, not just Enter). When you do, the formula will appear surrounded by curly-braces in the Formula Bar. We cannot type the curly-braces ourselves. That is just how Excel displays an array-entered formula.

Or perhaps you selected more than just L4 when you array-entered the formula. We must array-enter into just L4, then copy and paste in other cells.

Or perhaps my guess of -10% does not work well in your version of Excel, as I said it might not.

Or perhaps you are trying the same guess (or no guess) with more data than you presented in your examples, which I forewarned might not work.

Unfortunately, in the latter case, you are right: the formula is not reliable because Excel IRR is not reliable, again as I forewarned.

However, I think you would encounter the same problem using MrShorty's method.


-----
[1] https://app.box.com/s/9n73dt1jpbm5t73oaro927ndu0883vtw

Originally Posted by joeu2004

Works just fine for me, at least for the examples that you provided originally. See the column L of Sheet1 in the attachment [1].

Some guesses....

Thanks for the follow up. I tried your formula again (and looked at your attachment) and it's working great now! When I tried it last time I got a #VALUE! error, so I assumed that the IRR guesses were throwing things off, but I must have entered something in incorrectly (I did use Ctrl Shift Enter, so that wasn't the culprit).

Either way - I'm all set now. Thanks a million! I'll mark this as solved.