Re: XIRR versus IRR

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Norman Harker (njharker_at_optusnet.com.au)
Date: 04/12/04 

Date: Tue, 13 Apr 2004 07:56:01 +1000

Hi rrobelen!

First, to get an almost identical rate you must ensure that you are
converting the IRR to the annual effective rate that is the rate
returned by the XIRR or convert the XIRR to the quarterly effective
rate returned by the IRR from quarterly cash flows.

Assuming quarterly cash flows:

Annual effective = (1+IRR)^4-1
Quarterly effective = (1+XIRR)^(1/4)-1

Second, there will still be a small difference caused by the fact that
the rate calculated by the IRR from quarterly flows assumes the same
number of days in each quarter. With XIRR the calculations use the
correct number of days between the quarter days used in the cash flow.
>From this it will be seen that the XIRR is the more accurate although
there won't be a large difference. It will be for you to determine
significance of the error.

Incidentally, although XIRR uses the exact day count between dates,
the daily equivalent used in the (internal) process is found using:

=(1+XIRR)^(1/365)-1.

XIRR does not adjust the calculation of this rate in Leap Years. But
I'm not so sure that any institution varies the rate it uses in
calculations; something that will only be found by studying the small
print of documentation. 

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes.
"rrobelen" <rrobelen(somewhere)@innocal.com> wrote in message 
news:B1D6C525-FA0E-496C-9811-4C4167AAB84B@microsoft.com...
> The IRR function in Excel returns the internal rate of return for a 
> series of cash flows represented by the numbers in values. These 
> cash flows do not have to be even, as they would be for an annuity. 
> However, the cash flows must occur at regular intervals, such as 
> monthly or annually.  The XIRR function returns the internal rate of 
> return for a schedule of cash flows that is not necessarily 
> periodic. To calculate the internal rate of return for a series of 
> periodic cash flows, use the IRR function.
> Why when I use the XIRR function for a series of quarterly cash 
> flows do I get a different result than that produced by the IRR 
> function?  Which is more accurate?
>

Relevant Pages

  • Re: XIRR Problem
    ... then IRR will compute a daily rate of return. ... IRR and XIRR deal with cash flows, ... If the XIRR result does not closely match the annualized IRR, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: XIRR versus IRR help!
    ... it is because XIRR computes the annualized rate based on the exact days between the initial cash flow and the i-th cash flow. ... IRR and RATE compute the periodic rate based on the assumption that cash flows are equally spaced. ... Now the XIRR differs from the IRR and RATE results the cash flows are not really equally spaced; one cash flow is 366 days apart. ... this because XIRR presumes daily compounding based on the exact dates. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: XIRR versus IRR help!
    ... you can annualize the IRR or RATE result. ... we need to know the frequency of the cash flows per year. ... you might see a significant difference with the XIRR result. ... this because XIRR presumes daily compounding based on the exact dates. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: XIRR versus IRR help!
    ... No one can say for sure because you do not show how you used XIRR and IRR, and you do not tell us exactly what the results of XIRR and IRR were. ... To convert the XIRR result to a periodic rate, we need to know the frequency of the cash flows per year. ... this because XIRR presumes daily compounding based on the exact dates. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: IRR
    ... I get an error message. ... Excel really shouldn't return an error. ... This is why a solution to IRR in this case is not really meaningful. ... it is not one of those simple cases where the initial investment is negative and all subsequent cash flows are positive. ...
    (microsoft.public.excel.worksheet.functions)