Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
I have calculated the IRR & XIRR for the following cashflows and would expect
the results to be the same, as the cashflows are for constant periods. As the formulas give different answers I checked the results using Solver NPV=0 which gives a rate which agrees to the IRR result. I would expect the XIRR to be more accurate as this includes the dates of the cashflows unlike IRR, but am unsure if that is the case. 31/05/2005 -9,375,000 30/06/2005 - 31/07/2005 -750,000 31/08/2005 - 30/09/2005 -2,438,881 31/10/2005 -9,349,619 30/11/2005 106,250 31/12/2005 -8,147,500 31/01/2006 -5,893,750 28/02/2006 3,106,250 31/03/2006 181,128 30/04/2006 3,256,200 31/05/2006 -9,011,402 30/06/2006 2,386,293 31/07/2006 2,760,688 31/08/2006 -1,846,470 30/09/2006 -6,268,750 31/10/2006 -7,280,390 30/11/2006 106,250 31/12/2006 3,673,533 IRR -42.308% XIRR -99.860% NPV £0.20 rate -42.308% Formulas: =IRR(B4:B23,-.4) =XIRR(B4:B23,A4:A23,-.4) =NPV(B29,B4:B23) where B4:B23 = cashflow movements where A4:A23 = dates where B29 = rate Any help to explain why these differences occur would be much appreciated. Many Thanks Alex |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
This is a pretty good analysis of the differences between XIRR and IRR:
http://exceltips.vitalnews.com/Pages..._Function.html Essentially, XIRR should be used when income and payments are made at different periods. See also: http://office.microsoft.com/en-us/ex...136321033.aspx Dave -- Brevity is the soul of wit. "Alex Morgan" wrote: I have calculated the IRR & XIRR for the following cashflows and would expect the results to be the same, as the cashflows are for constant periods. As the formulas give different answers I checked the results using Solver NPV=0 which gives a rate which agrees to the IRR result. I would expect the XIRR to be more accurate as this includes the dates of the cashflows unlike IRR, but am unsure if that is the case. 31/05/2005 -9,375,000 30/06/2005 - 31/07/2005 -750,000 31/08/2005 - 30/09/2005 -2,438,881 31/10/2005 -9,349,619 30/11/2005 106,250 31/12/2005 -8,147,500 31/01/2006 -5,893,750 28/02/2006 3,106,250 31/03/2006 181,128 30/04/2006 3,256,200 31/05/2006 -9,011,402 30/06/2006 2,386,293 31/07/2006 2,760,688 31/08/2006 -1,846,470 30/09/2006 -6,268,750 31/10/2006 -7,280,390 30/11/2006 106,250 31/12/2006 3,673,533 IRR -42.308% XIRR -99.860% NPV £0.20 rate -42.308% Formulas: =IRR(B4:B23,-.4) =XIRR(B4:B23,A4:A23,-.4) =NPV(B29,B4:B23) where B4:B23 = cashflow movements where A4:A23 = dates where B29 = rate Any help to explain why these differences occur would be much appreciated. Many Thanks Alex |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
Alex Morgan <Alex wrote...
I have calculated the IRR & XIRR for the following cashflows and would expect the results to be the same, as the cashflows are for constant periods. .... I would expect the XIRR to be more accurate as this includes the dates of the cashflows unlike IRR, but am unsure if that is the case. 31/05/2005 -9,375,000 30/06/2005 - .... IRR -42.308% XIRR -99.860% .... Any help to explain why these differences occur would be much appreciated. In a nutshell, IRR returns a periodic rate, whatever the period is, while XIRR always returns an effective annual rate. Since your cashflows are monthly, IRR returns an effective monthly rate. If you want it on the same basis as XIRR, put IRR's result on an annual basis using =(1+IRR(...))^12-1 which also returns -99.86%. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
On Jan 31, 6:54 am, Alex Morgan <Alex
wrote: I have calculated the IRR & XIRR for the following cashflows and would expect the results to be the same, as the cashflows are for constant periods. [....] I would expect the XIRR to be more accurate as this includes the dates of the cashflows unlike IRR, but am unsure if that is the case. [....] Any help to explain why these differences occur would be much appreciated. As others have noted, the reason for the big difference is because Excel's IRR() always gives a period rate (monthly, in your case), whereas Excel's XIRR() always gives an annualized rate. You can annualize a monthly IRR in one of two ways: simply 12*IRR(...); or (1 + IRR(...))^12 - 1. The latter will be closer to the XIRR result; and that is the method that I prefer. But many people -- academicians and practitioners alike -- simply do the former (multiply by 12). But even if the regular periods were annual, IRR and XIRR would have slightly different results because, as you say, XIRR takes the actual number of days into account. Whether or not XIRR is "more accurate" depends on your point of view. In my view, an answer is "accurate" if it agrees with "everyone else's", no matter how IRR is computed (with a calculator, for example). Most people rely on an IRR-like computation for "regular" intervals. For example, Jan-to-Jan periods are usually considered to be "regular", whether or not they include leap years. (Note that if you use XIRR, there is no such thing as a "regular" interval, unless it is daily.) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
PS....
On Jan 31, 10:09 am, I wrote: Whether or not XIRR is "more accurate" depends on your point of view. [....] Most people rely on an IRR-like computation for "regular" intervals. But of course, always use XIRR for truly irregular intervals. And if you use XIRR, the corresponding net present value function is XNPV(), not NPV(). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
"joeu2004" wrote...
.... You can annualize a monthly IRR in one of two ways: simply 12*IRR(...); or (1 + IRR(...))^12 - 1. The latter will be closer to the XIRR result; and that is the method that I prefer. But many people -- academicians and practitioners alike -- simply do the former (multiply by 12). Misleading and wrong. XIRR's result is always an annual EFFECTIVE rate. The only way to convert a monthly effective IRR rate to an annual EFFECTIVE rate is by using the (1+IRR(...))^12-1 formula. Simply multiplying by 12 gets you the nominal annual rate compounded monthly. Maybe there are practitioners and academics who can't tell an effective rate from a nominal rate (or a hole in the ground or their backside), but they won't match XIRR's result by multiplying IRR's result by 12. But even if the regular periods were annual, IRR and XIRR would have slightly different results because, as you say, XIRR takes the actual number of days into account. . . . .... The difference should be well less than a single basis point when there's more than 12 months, especially when the first and last Februaries are 3 or more months from the beginning and end of the cashflow. Negligible compared to confusing nominal and effective rates. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
On Jan 31, 11:16 am, "Harlan Grove" wrote:
"joeu2004" wrote... You can annualize a monthly IRR in one of two ways: simply 12*IRR(...); or (1 + IRR(...))^12 - 1. The latter will be closer to the XIRR result; and that is the method that I prefer. But many people -- academicians and practitioners alike -- simply do the former (multiply by 12). Misleading and wrong. I do not consider any part of my response wrong or misleading. It is a fact that many, if not most, academicians simply multiply by 12. They make no distinction between "nominal" and "effective" IRR. They also simply divide an annual IRR by 12 (to get a monthly rate) without qualification (e.g. "only if that is the nominal rate"). Don't shoot the messenger! Frankly, I am shocked and dismayed. You and I are in "violent agreement" that that is wrong. I was trying to be polite by stating my "preference". In fact, I believe I posted a very long article in these newsgroups about why an "effective" IRR is the only reasonable number to use for IRR. An IRR's only purpose in life is for use in compound calculations. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
On Feb 1, 7:55 am, "joeu2004" wrote:
It is a fact that many, if not most, academicians simply multiply by 12. They make no distinction between "nominal" and "effective" IRR. They also simply divide an annual IRR by 12 (to get a monthly rate) without qualification (e.g. "only if that is the nominal rate"). Which goes a long way toward explaining why they are in academia and not business :) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
On Jan 31, 7:15 pm, wrote:
On Feb 1, 7:55 am, "joeu2004" wrote: It is a fact that many, if not most, academicians simply multiply by 12. They make no distinction between "nominal" and "effective" IRR. They also simply divide an annual IRR by 12 (to get a monthly rate) without qualification (e.g. "only if that is the nominal rate"). Which goes a long way toward explaining why they are in academia and not business :) Point well taken. But what disappoints me is that I see this "12*IRR" formula in texts that are intended to teach MBAs how to make these computations and do the analysis. In other words, this is what CFAs learn :-(. Having said that, I should say that most CFAs simply rely on professional software to do the computation, and I really have no idea how such software annualizes IRRs. But if they use a financial calculator, they are on their own to perform the annualization. They will probably do it the way they were taught. Enough said! The point is: "12*IRR" is not uncommon, and I wanted to bring it to the OP's attention so that he/she was aware of the potential for differences in methodology. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
CFAs....ughhh.
In the middle of that program right now. Dave On Feb 1, 3:22 am, "joeu2004" wrote: On Jan 31, 7:15 pm, wrote: On Feb 1, 7:55 am, "joeu2004" wrote: It is a fact that many, if not most, academicians simply multiply by 12. They make no distinction between "nominal" and "effective" IRR. They also simply divide an annual IRR by 12 (to get a monthly rate) without qualification (e.g. "only if that is the nominal rate"). Which goes a long way toward explaining why they are in academia and not business :) Point well taken. But what disappoints me is that I see this "12*IRR" formula in texts that are intended to teach MBAs how to make these computations and do the analysis. In other words, this is what CFAs learn :-(. Having said that, I should say that most CFAs simply rely on professional software to do the computation, and I really have no idea how such software annualizes IRRs. But if they use a financial calculator, they are on their own to perform the annualization. They will probably do it the way they were taught. Enough said! The point is: "12*IRR" is not uncommon, and I wanted to bring it to the OP's attention so that he/she was aware of the potential for differences in methodology. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR & XIRR - Different Results
Many thanks for all your comments, think you've all answered my questions.
My intention was not to cause arguments!! "Dave F" wrote: CFAs....ughhh. In the middle of that program right now. Dave On Feb 1, 3:22 am, "joeu2004" wrote: On Jan 31, 7:15 pm, wrote: On Feb 1, 7:55 am, "joeu2004" wrote: It is a fact that many, if not most, academicians simply multiply by 12. They make no distinction between "nominal" and "effective" IRR. They also simply divide an annual IRR by 12 (to get a monthly rate) without qualification (e.g. "only if that is the nominal rate"). Which goes a long way toward explaining why they are in academia and not business :) Point well taken. But what disappoints me is that I see this "12*IRR" formula in texts that are intended to teach MBAs how to make these computations and do the analysis. In other words, this is what CFAs learn :-(. Having said that, I should say that most CFAs simply rely on professional software to do the computation, and I really have no idea how such software annualizes IRRs. But if they use a financial calculator, they are on their own to perform the annualization. They will probably do it the way they were taught. Enough said! The point is: "12*IRR" is not uncommon, and I wanted to bring it to the OP's attention so that he/she was aware of the potential for differences in methodology. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup on pivot table results = #N/A | Excel Worksheet Functions | |||
XIRR and Non Contiguous Cells | Excel Discussion (Misc queries) | |||
XIRR non contiguous references | Excel Discussion (Misc queries) | |||
abdualmohsn | Excel Discussion (Misc queries) | |||
XIRR and IRR | Excel Worksheet Functions |