Page 1 of 2 12 LastLast
Results 1 to 10 of 13

IRR - Annual IRR calc based on montlhy cash flow

This is a discussion on IRR - Annual IRR calc based on montlhy cash flow within the Excel Questions forums, part of the Question Forums category; Does anyone know how to calculate the annual IRR on a monthly stream of cash flow? I don't think multiplying ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    25

    Default

    Does anyone know how to calculate the annual IRR on a monthly stream of cash flow? I don't think multiplying the calculated IRR by 12 produces the correct result.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    Explain your problem a little more.

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    25

    Default

    OK, say I have the following stream of monthly cash flows:

    months 1-12 -125,000 each month
    months 13-24 +75,000 each month
    months 25-36 +75,000 each month
    months 37-48 +75,000 each month
    months 49-60 +75,000 each month

    The annual IRR should be somewhere around 50%. Excel calculates a monthly IRR of around 3.3%. If I multiply that by 12 to annualize it, the result would be 39.6%, which is too low. Is there a formula that will take monthly cash flow and calculate an annual rate of return?

    Thanks.


  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Why don't you do the IRR calc. against annual figures: I.e., year 1 is $1.5 mil, etc...50% IRR? That's pretty large, almost too large....Good luck.

    Formula, =irr(a1:a10,b1) helps where each cell in a1 through a10 contains annualized cash flows and b1 is your guess (close to where the IRR should fall) a reasonable guess is your hurdle rate. If you're well above your hurdle rate, you may want to double-check your assumptions.

    Didn't notice your cash outflows, money for nothing?
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-15 14:38 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    25

    Default

    That is specifically what I am trying to avoid. There must be a formula out there that will do this without adding the second step of annualizing cash flow into yearly buckets before doing the calculation.

    Any ideas?

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Well, one could try

    0=(125000/(1+irr)^(1/12))+(125000/(1+irr)^(2/12))......

    And so forth, matching the cf for the month with the exponential numerator.

    Set this up in your spreadsheet so that the numerator of the exponentional and cash flows are references on a timeline and irr is an absolute reference. Do a goal seek to solve for the irr value where the sum equals 0.

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-15 14:55 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    25

    Default

    Thank! Will give that a shot.

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Also, the IRR is a hit and miss iteration calculation for Excel. Your goal seek will be most accurate if you set Excel to perform it's maximum amount of iterations.

    Click tools->options->calculation. Check iteration and enter 32767 for the number of iterations. Probably helps to reduce your maximimum change amount as well.

    Hope this helps.

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi Corey,

    B1:B60 houses your cashflows
    in A1 put 1/1/02 (US date convention)
    in A2 put =DATE(YEAR(A1),MONTH(A1)+1,1)
    copy down to A60

    Wherever you want your result, place the following formula:
    =XIRR(B1:B60,A1:A60,SIGN(SUM(B1:B60))*0.1)
    =47.1899%

    XIRR returns an annualized result, whereas IRR returns the period result and you must annualize the values yourself.

    Slight differences you'll notice here is that I chose the dates to fall at the beginning of each month, so there are 28, 29, 30, or 31 days between cashflows. XIRR is sensitive to that, so be aware.

    Suggestions for use...
    1. Always give a guess. I chose to use +/- 10% depending on the siummation of the total cashflows.

    2. If you have non-normal cashflows, where the sign changes more than once, you have multiple IRRs and the initial guess becomes exceedingly important. Even more important is that the result is almost meaningless in that case. It is a probelm with the concept more than the Excel function.

    HTH,
    Jay


    HTH,
    Jay

    On 2002-03-15 14:22, Corey wrote:
    OK, say I have the following stream of monthly cash flows:

    months 1-12 -125,000 each month
    months 13-24 +75,000 each month
    months 25-36 +75,000 each month
    months 37-48 +75,000 each month
    months 49-60 +75,000 each month

    The annual IRR should be somewhere around 50%. Excel calculates a monthly IRR of around 3.3%. If I multiply that by 12 to annualize it, the result would be 39.6%, which is too low. Is there a formula that will take monthly cash flow and calculate an annual rate of return?

    Thanks.


  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17

    Default

    Notwithstanding the above problems with odd dates and multiple results, if your cashflows are definately monthly and you're looking to annualise then the following will convert it
    =((1+0.033)^12)-1
    Where 0.033 was your IRR result.

    i.e. it's not just x 12. This gave me a result of 47.64% which was what I think you were expecting

    XIRR is a very handy alternative though.

    [ This Message was edited by: RaoulF on 2002-03-21 03:59 ]

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com