+ Reply to Thread
Results 1 to 5 of 5

IRR Guess Problem

  1. #1
    Dkline
    Guest

    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,$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!




  2. #2
    Harlan Grove
    Guest

    Re: 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. #3
    Dkline
    Guest

    Re: IRR Guess Problem

    It works. But why does it work?

    "Harlan Grove" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    Harlan Grove
    Guest

    Re: IRR Guess Problem

    "Dkline" <[email protected]> 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. #5
    Dkline
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > "Dkline" <[email protected]> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1