Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IRR Guess Problem
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,$V150),(-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,$V150),(-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$1 0:$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! |
#2
|
|||
|
|||
IRR Guess Problem
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). |
#3
|
|||
|
|||
IRR Guess Problem
It works. But why does it work?
"Harlan Grove" wrote in message oups.com... 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). |
#4
|
|||
|
|||
IRR Guess Problem
"Dkline" wrote...
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. |
#5
|
|||
|
|||
IRR Guess Problem
I remember Lotus 123. Use to use it every day until Excel became a better
product. Thanks for your help. "Harlan Grove" wrote in message ... "Dkline" wrote... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |