Excel: Internal Rate of Return Function (IRR)? 2

[drmichaelw]

I am using the IRR function and I am getting #DIV/0! returned. When I check my data there is no zero value.
Here is my formula. Can you tell me why I am getting #DIV/0!

=IRR(B6:T6)*12

Here are my values:

($24,000.00) ($128,448.45) ($30,000.00) ($100,000.00) ($27,000.00) ($50,000.00) ($25,000.00) ($37,211.98) $25,000.00 ($52,211.97) ($25,103.25) ($50,000.00) ($50,000.00) ($27,211.97) ($25,000.00) ($55,000.00) ($7,500.00) ($30,000.00) $80,000.00

The above values are in one row across on the spreadsheet.

 

[Zathras]

Probably because the rate of return is so tiny that an intial guess of 0.1 is inadequate.

Your total investment is $743,687.62 and the return is only $105,000.00 after 18 months.

If you supply a value of -0.5 for the guess, IRR returns a value of -27.410% loss per month, or -328.919% loss for the year and a half.

 

[bandit600]

On reading the help file for IRR, it also says that you must have at least 1 negative value. I don't see one amongst your data.

bandit600

 

[Zathras]

bandit600, you're not trying very hard. There are 17 negative numbers and 2 positive numbers (25,000.00 and 80,000.00) -- Scroll to the right.

 

[drmichaelw]

Thanks for the info.
The formula works, can you explain what this formula is doing?

Why doesn't a positive # work for the guess and why doesn't the value change when I use guess of "-.5" or "-.9"?

 

[Zathras]

The formula calculates the net present value of each of the positive and negative cash flows using an assumed rate of return. Then depending on whether the sum is negative or positive, adjusts the assumed rate of return up or down and calculates again. The process stops when the rate is accurate to within 0.00001 percent or 20 tries whichever comes first. You can check this all out for yourself in the Excel help file (look for IRR worksheet function)

Positive doesn't work because it too far away from the correct IRR to achieve balance within 20 loops.

The value doesn't change with different guesses because the value is what it is. The guess just gets the process off on the right foot to be able to converge within 20 loops.

 

[bandit600]

Zathras,

I've being using Excel for quite a while and I never knew that enclosing in brackets is equivalent to a negative. So I've definitely learnt something today. Have a star from me as well.

bandit600

 

[drmichaelw]

I did read the help and found it a bit confusing.
So if my results for the 19 months is -26.0%. That means what?

 

[Zathras]

The interpretation really depends on what the cash flows represent. But one way to understand a monthly return of negative 27.41% would be to consider a stock portfolio that starts out worth $100,000 and after 12 months is worth $2,141 and after 18 months is worth a mere $313. I say 18 months (not 19) because with 19 data points, the first represents time 0, the second time 1, etc.

Or another way, if you simply add all 19 cash flow numbers you get $638,688. Now if you start with a stock portfolio worth $638,688 and experience a monthly return of negative 27.41% it would be worth $2,000 after 18 months. The difference between that $2,000 and zero (which is what the IRR converges on) is the fact that the cash flows are spread out over the 18 months and not all present at the start.

The result from the IRR formula is the periodic rate for the number of periods provided. (Initial investment, followed by periodic +/- cash flows) Since your formula included multiplication by 12, I inferred that these were monthly cash flows and so the result is a monthly rate of return (or loss as it turns out).

Accountants use this for proper comparison of investment alternatives. E.g., which is the better 3-year investment of $100,000 -- One that will return nothing for the first year and then return $1,000 a month after that (A), or one that returns $650 the first month and every month after that (B)?

(A) will pay out a total of $24,000 over three years, while (B) will pay out a total of $23,400 over three years. So is A better? No. If you anticipate that the value of the investment (in either case) will be $80,000 after three years, Investment A has a monthly IRR of .0917% while investment B has a monthly IRR of .1045%, so investment B is better.

Whew! I wasn't planning on being so long-winded. Well, maybe it's because I'm starting on an 8-day vacation tomorow morning. If you have a reply, I'll see it next week.