Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: IRR - Annual IRR calc based on montlhy cash flow

  1. #11
    New Member
    Join Date
    Mar 2002
    Location
    Seattle, WA
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jay and RaoulF for the good information. I'll try both.

    Jay - are you able to explaing what =((1+IRR)^12)-1 does. I know it accurately annualizes a monthly IRR, but need to clearly explain how to my boss. Any help you could provide would be appreciated.

    Thanks,

    Corey

  2. #12
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Corey,

    Returns are geometrically linked -- think compound interest -- so you cannot just multiply the monthly returns by 12. That is an arithmetically linking and should not be used here.

    IRR produces a rate of return based on a series of regular cashflows. It makes no distinction between cashflows that happen 7 days apart or 7 years apart. It just assumes that the intervals are equally spaced.

    Sample cashflows
    -100
    50
    10
    10
    65

    IRR = 12.48%

    Let's introduce two date ranges just for example.
    First series:
    Date / Cashflow
    1/1/02 -100
    1/1/03 50
    1/1/04 10
    1/1/05 10
    1/1/06 65

    XIRR returns 12.47%

    Change the dates to 30 day intervals (1/1/02, 1/31/02, 3/2/02, 4/1/02, 5/1/02)

    XIRR returns 318.05%

    Same cashflows, only the timing of them plays a critical role.

    OK, now let's see where the IRR function matches when you annualize it.

    If we assume monthly intervals, then we should expect that the period return annualized would be the monthly return raised to the 12th power.

    More precisely, the (1+IRR)^12 - 1 is calculated on a 360 day-basis year and 30 day intervals.

    =(1+IRR)^(360/30)-1
    =309.94%

    To more closely match the XIRR use a 365 day-basis year
    =(1+IRR)^(365/30) - 1
    =318.05%

    Sorry for the long-winded reply. In short, remember that intra-period returns are geometrically linked period to period. The calculation should be
    =(1+IRR)^(365/day interval between each cashflow) - 1

    Please note that this works with cashflows greater than a year apart. Suppose two years apart, then the power is 365/730.

    Daily, Quarterly, Annually all calculated with teh same convention.

    Hope this helps,
    Jay

    On 2002-03-21 07:34, Corey wrote:
    Thanks Jay and RaoulF for the good information. I'll try both.

    Jay - are you able to explaing what =((1+IRR)^12)-1 does. I know it accurately annualizes a monthly IRR, but need to clearly explain how to my boss. Any help you could provide would be appreciated.

    Thanks,

    Corey

  3. #13
    New Member
    Join Date
    Jul 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IRR - Annual IRR calc based on montlhy cash flow

    Quote Originally Posted by Corey View Post
    Thanks Jay and RaoulF for the good information. I'll try both.

    Jay - are you able to explaing what =((1+IRR)^12)-1 does. I know it accurately annualizes a monthly IRR, but need to clearly explain how to my boss. Any help you could provide would be appreciated.

    Thanks,

    Corey
    Actually, that formula does not tell the whole story that XIRR does. XIRR takes into account the number of days in the month (or more accurately, the number of days from the present or first cash flow date). If you have a monthly cash flow on the first of the month, annualizing it using that formula assumes that all months are the same length (~30.4 days). The differences are slight in most cases, but there is a difference, nonetheless.

Some videos you may like

User Tag List

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
  •