IRR formula

[CraveExcel]

I encountered a problem at work. I use XIRR formula to calculate annulized internal rate of return for the investment deals. I run a pivot table to organize the investment cash flows. The cash flows is organized by this way: column A lists all the dates, and column B is the first investment deal, say "Alta", column C is second investment deal "Bancorp", and etc. until column Z. In the XIRR formula, the first argument is the cash flow amounts , second argument is range of dates. The problem is XIRR will work only if the first cash flow amount selected is a negative number. While since the investment started at different dates, I have to manually adjust the XIRR formula to make it work, say, I have to manullay select the range of the cash flows starting with negative amount. I wish someone can come up with a solution that I can find the locaton of the first negative number for each investment, so I can drag the XIRR formula to apply to all investment. It has been a headache to me for a long time.

Hope someone can help me solve this puzzle.

Thanks in advance,

CraveExcel

 

[Joe Schmeaux]

You have two problems. I will try to help with the first, and will try not to make your life too miserable by pointing out the second without helping much.

The easy way to attack the first problem is to build column AA (outside the pivot table range), with the following formula in cell AA1

=if(b1<0,xirr(b1:b$100,$a1:$a$100,0.1),"")

then copy this formula to cells aa2 through aa100 (or whatever), and to columns ab through az. Each column from aa to az will then contain one number (the IRR) and a lot of blanks. You can then use something like
=max(aa1:aa100) to return the IRR, regardless which row it turns up in.

The second problem is that IRR is not a terribly good way of evaluating alternative investments. Unfortunately it is the favoured approach of Dilbert-style pointy-haired bosses who understand that a big IRR is better than a small one, but don't really understand discounted cash flow analysis or risk evaluation. Much better to first decide on a cost of capital and then look at things on a present-value basis.

One limitation of using rate-of-return is that it only works if you have a negative cash flow followed by a number of positive ones. If the cash flow stream starts with a positive number (maybe an upfront payment) then goes negative and positive again, then XIRR won't work, and neither will my suggestion above. If the initial cash flow is negative, followed by a mixture of positive and negative cash flows (a "nonconventional investment"), you can get multiple rates of return, and Excel will only tell you about the one it happens to find first. This situation is common where you have a base case investment A, and are trying to decide if you should do an add-on such as an acceleration-type investment. Subtracting the base case from the accelerated case will often give a nonconventional series of cash flows, and two rates of return will satisfy the condition PV=0. Often the two rates of return will be quite different (say -10% and +15%), but if you look at the PV's of the two investment alternatives, the difference might be quite small.

The second shortcoming of the rate-of-return approach is that it does not distinguish between magnitudes of investments. Would you prefer a $1 investment where you get $10 at the end of the year, or a $10,000 investment where you get $20,000 at the end of the year? The first choice has a much higher IRR ...

There are other problems with IRR, but I think I've used up enough board space with my rant!

Hope this helped a bit ...

 

[Antonio Toledo]

Do an NPV analysis and simply use a chart to visualize at what discount rate this investment becomes negative. Something like the below

http://internalinvestment.com/Dokument/ExcelFiler/Investering.xlsx

 

[CraveExcel]

O.M.G. Joe, you are a genius. How did you figure out such a brilliant formula? It is unbelievalbe. It is my first time post my excel question here, and I received such a warm welcome. Your second point is very valuable too. I want to say Thank you to you before I read your answer one more time.

Want to say thank yout to Antonio too. I will open the link and read it.

 

[Joe Schmeaux]

You're quite welcome. Actually, I use the contruction
=if(condition,result,"")
a lot.

Just wait til you ask a supertough question that only the Mr. Excel VPs can answer - they're the real geniuses around here !!

Joe

 

[CraveExcel]

Joe,

I tried your formula, worked very well. Except.... There will be several IRRs if the investments have several negative amounts at the beginning of the investment period. Obviously, the first IRR amount is the IRR number I need. Can you help me solve this puzzle? i.e. how to return the first positive number for each column.

Is it problem tougher? I did want to try to solve it myself, no luck so far.

Thanks in advance.

CraveExcel

 

[Joe Schmeaux]

try this (starting in the second row of your IRR data):

=if(and(b2<0,sum(aa$1:aa2)=0),xirr(b2:b$100,$a2:$a$100,0.1),"")

This should return a blank for all irr's other than the first one.

 

[CraveExcel]

Thanks So Much, Joe.

It is so perfect. I changed the formula a bit, so it can also calculate the IRR even if the b1 is negative. I just changed the b2 to b1 and sum(aa1:aa2) to sum(aa0:aa1) (if there I add an empty row, aa0 will become aa1, and it will work), and so the rest of the formua changed accordingly.

After all, your solution opened a window for me. I have been trying to think about a way to automate the IRR process, but no luck. I even tried to get an answer from my colleagues and friends, but no one can help.

Thanks again,

CraveExcel