IRR Guess Problem

[Dkline]

I need help in putting in a conditional formula as the guess for an IRR. I
have to caclculate an IRR every year for as much as 120 years. Under some
circumstances, the out of pocket can be zero for the first few years before
some amount of out of pocket takes place.

Below I've pasted a section of the worksheet. Each duration is one year. The
investor has zero out-of-pocket for the first five years by capitalizing the
loan interest, then pays the interest out-of-pocket for the next five years,
then pays it off in year eleven.

I am using the IRR function. In the first 5 years as there is no outlay the
IRR is incalcuable. I am trying to get a formula for the guess. The formula
below is for the row where I get my first IRR - 1906.13%. The first row
below is row 10 in the spreadhsheet and the first column below is in column
"V".

=IF(SUM($V$10:$V15)<=0,NA(),IF(AND(SUM($V$10:$V14)<=0,$V15>0),(-W15/V15)-1,I
F(ISERROR(IRR(($V$10:$V15,$W15),$Y14)),IRR(($V$10:$V15,$W15),0.001),IRR(($V$
10:$V15,$W15),$Y14))))

The "IF(SUM($V$10:$V15)<=0,NA()" says if you don't have any net outlay then
show an NA as it can't be calculated.

The "IF(AND(SUM($V$10:$V14)<=0,$V15>0),(-W15/V15)-1" says once you found
your first outlay, divide the "Financed DB" by the "Net Outlay" then
subtract 1 to get the IRR.

The
"IF(ISERROR(IRR(($V$10:$V15,$W15),$Y14)),IRR(($V$10:$V15,$W15),0.001),IRR(($
V$10:$V15,$W15),$Y14))))" says if there is an error when calculating the IRR
using the prior year's calculated IRR, then use the guess of 0.001, else use
the prior year's calculated IRR as the guess.

The problem is the #NUM! in the last row in the below snapshot. Using an HP
12c I know this value is -26.70%. So my using the prior year's IRR or the
0.001 guess is too far off the mark for Excel to come up with the answer.

How can I make the guess more intelligent? Should I look at the relative
change as a percentage and adjust the guess accordingly. Should I run
Solver?



Use IRR
Check IRR

Net
Financed
Financed
Num

Outlay
DB
DB
Financed DB






-
(3,000,000)
#N/A
#N/A

-
(3,000,000)
#N/A
#N/A

-
(3,000,000)
#N/A
#N/A

-
(3,000,000)
#N/A
#N/A

-
(3,000,000)
#N/A
#N/A

149,616
(3,000,000)
1905.13%
1905.13%

170,451
(3,000,000)
294.43%
294.43%

191,286
(3,000,000)
125.08%
125.08%

212,121
(3,000,000)
69.99%
69.99%

232,956
(3,000,000)
43.92%
43.92%

3,651,958
(3,000,000)
0.00%
#NUM!

 

[Harlan Grove]

Without getting into the question of whether ignoring capitalized
interest payments in the first 5 years invalidates already tenuous IRR
'analysis', it's almost always safe to use an initial guess interest
rate of -0.9 (yes, a negative value).

 

[Dkline]

It works. But why does it work?

 

[Harlan Grove]

It works. But why does it work?

It works because it forces Excel to search through a much wider initial
range of interest rates. As for why they didn't use this as the default
initial value, I'd guess it was for compatibility with Lotus 123 back a
couple of decades ago.

 

[Dkline]

I remember Lotus 123. Use to use it every day until Excel became a better
product. Thanks for your help.