IRR Problem

[Dkline]

I have an IRR that seemingly cannot be solved by Excel.

I have a cash flow of $20,000 for two years. The value at the end of the 2nd
year is $43. On my HP 12C it calculates the IRR as -99.79. So I know there is
a answer.

I cannot get Excel to give my any answer other than an error such as #Value.

=IF(ISERROR(IRR((R3C20:RC20,RC38),-0.9)),IRR((R3C20:RC20,RC38),R[-1]C),IRR((R3C20:RC20,RC38),-0.9))

Basically the first portion says if you can't get an answer with the IRR
Guess of -0.9, then try using the IRR from the previous year, else use the
-0.9.

The context is this is a life insurance illustration. I am calculating the
IRR on the Cash Value each year.

In the first year I have a cash flow of $20,000 with a return of zero. I do
not use the IRR function but take the first year CV / Premium and subract.
Result is -100%.

Next year another $20,000 is paid as premium and the CV is $43.

-20,000
-20,000 +43
Excel returns #Value using my formula.

If I strip down my formula by removing the IF condition and only use the
-0.9 guess, Excel returns "#Num".

The only way I can get Excel to calculate the IRR is to manually type in the
-0.9979. BUT Excel returns -99.68%

How can I persuade Excel to return -99.79% instead of an error message?

 

[Niek Otten]

From your formula I cannot see if you address the right number of cells; it should be OK if the formula itself is on Row 4.

If I use your data and a guess of -0.99999 I get 99.7854602729448%


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have an IRR that seemingly cannot be solved by Excel.
|
| I have a cash flow of $20,000 for two years. The value at the end of the 2nd
| year is $43. On my HP 12C it calculates the IRR as -99.79. So I know there is
| a answer.
|
| I cannot get Excel to give my any answer other than an error such as #Value.
|
| =IF(ISERROR(IRR((R3C20:RC20,RC38),-0.9)),IRR((R3C20:RC20,RC38),R[-1]C),IRR((R3C20:RC20,RC38),-0.9))
|
| Basically the first portion says if you can't get an answer with the IRR
| Guess of -0.9, then try using the IRR from the previous year, else use the
| -0.9.
|
| The context is this is a life insurance illustration. I am calculating the
| IRR on the Cash Value each year.
|
| In the first year I have a cash flow of $20,000 with a return of zero. I do
| not use the IRR function but take the first year CV / Premium and subract.
| Result is -100%.
|
| Next year another $20,000 is paid as premium and the CV is $43.
|
| -20,000
| -20,000 +43
| Excel returns #Value using my formula.
|
| If I strip down my formula by removing the IF condition and only use the
| -0.9 guess, Excel returns "#Num".
|
| The only way I can get Excel to calculate the IRR is to manually type in the
| -0.9979. BUT Excel returns -99.68%
|
| How can I persuade Excel to return -99.79% instead of an error message?

 

[Dkline]

Year Cell Value
1 R3C20 20,000
2 R4C20 20,000
2 R4C38 -43

The R3C20:RC20 is locking in the starting row and letting the end row be the
current row as you go down the column.

My HP 12C agrees with your number. I'm trying to avoid hard coding a number
in there as eventually the IRR will reach almost 9% in the 121 years I
potentially have to calculate the value.

With that many durations, I frequently have a problem in later years where a
flat guess of -0.9 fails but can be solved by using the prior year result.

What is odd to me in this example is that it fails in year 2. This
particular case has 80 years of IRR to solve.