N
Norman Harker
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.