Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IRR vs XIRR
I have an out flow and then monthly inflows for a month. They are the same time every month the first of the month. When I use IRR I get 18%, when I use XIRR I get 640%. If i multiply 18% by 12 I get 217% which seems right ,but doesn;t take into account compounding. If use this =(iRR(C4:C16)+1)^12-1) I get 633% which is very close to 640% but seems way to high. I haven't even tripled my investment, which was -130,000 while making 440,00 total over the year. Can someone tell me which number is correct and why?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR vs XIRR
Would you please list the 12 monthly cash flows
I understand that your initial cash outlay is $-130,000 It also seems you have a typo in total benefits which I assume equals $440,000 rather that what you have posted as $440,00 I assume that the cash flows are not in equal amounts as when I divided 440,000 by 12 the IRR values are much different than you quoted So unless you post your monthly cash flows, there is no way for me to verify the result of IRR function With that out of the way, I would like to add a few comments You stated that the monthly benefits (+ve cash flows) occur at start of month. That leads me to think that your XIRR results are the most accurate here unless my assumption is incorrect that you did not add the initial cash outlay and first monthly benefit to arrive at the cash flow at time period 0 Thus please post the cash flows for each of the months so I can have a look On May 21, 9:10*pm, Csmithers wrote: I have an out flow and then monthly inflows for a month. *They are the same time every month the first of the month. *When I use IRR I get 18%, when I use XIRR I get 640%. *If i multiply 18% by 12 I get 217% which seems right ,but doesn;t take into account compounding. *If use this =(iRR(C4:C16)+1)^12-1) I get 633% which is very close to 640% but seems way to high. *I haven't even tripled my investment, which was -130,000 while making 440,00 total over the year. *Can someone tell me which number is correct and why? -- Csmithers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR vs XIRR
"Csmithers" wrote:
I have an out flow and then monthly inflows for a month. They are the same time every month the first of the month. When I use IRR I get 18%, when I use XIRR I get 640%. If i multiply 18% by 12 I get 217% which seems right , but doesn;t take into account compounding. If use this =(iRR(C4:C16)+1)^12-1) I get 633% which is very close to 640% All are right and wrong! First, IRR returns the periodic rate. If you have monthly cash flows, IRR returns a monthly rate. In contrast, XIRR always returns an annual rate. That explains why XIRR is so much larger than the (monthly) IRR. Second, there is no general agreement on how to annualize a periodic rate. To some degree, it depends on what financial securities you are modelling, the industry conventions applied to those securities, and even applicable regional laws. Sometimes it is correct to multiply a monthly IRR by 12; sometimes it is correct to compound it over 12 periods; sometimes we multiply by other factors. In contrast, XIRR always compounds daily. That partly explains why even when you compound the monthly IRR over 12 periods, it is not exactly the same as daily-compounded XIRR. Third, by definition, IRR treats each month as the same length when using monthly cash flows. But XIRR uses the actual number of days. That further explains why the monthly compounded IRR over 12 periods does not agree exactly with the XIRR. Finally, note that neither the IRR nor the XIRR can be computed algebraically (unless all cash flows are the same, and they occur regularly). Instead, each function uses some algorithm to "home in on" the rate that causes the (X)NPV to be close to zero. Microsoft documentation indicates that starting with Excel 2003, IRR and XIRR use the Newton-Raphson method. But each algorithm is implemented differently, which gives rise to differences in the results due to arithmetic anomalies (due to binary floating-point) as well as diffences in the tolerances and the underlying differential formulas. That is another reason why the monthly compounded IRR over 12 period does not agree with the XIRR. In actual practice, it is best not to read too much into the detailed numerical results. They are all only estimates anyway. Choose whichever function is more appropriate for the data that you have. PS: I might note that the Excel IRR and XIRR functions are simply two ways to calculate the "internal rate of return" (IRR). In other words, when I speak of "IRR", you need to decide by context whether I am speaking of the Excel function or the financial concept. "Csmithers" wrote: I get 633% which is very close to 640% but seems way to high. I haven't even tripled my investment, which was -130,000 while making 440,00 total over the year. Can someone tell me which number is correct and why? Yes, that is the fallacy of annualizing periodic rates, IMHO. For example, if the value of a security changes 1% in a day, surely you do not believe we can expect its value has grown (will grow) at a annual rate of 3678%. That is indeed what (1+1%)^365-1 is. But that is not a realistic assessment of the change in value. So even though it is common practice to annualize periodic rates, I try to avoid it -- although there are applications of the rate of return where we must annualize. Instead, I prefer to specify appropriate periodic rates of return. So instead of annualizing a periodic IRR, I prefer to "de-compound" the annual(ized) XIRR rate. For example, (1+XIRR(...))^(1/12)-1. Nevertheless, that monthly XIRR will not be the same as the periodic IRR based on monthly cash flows for all of the reasons given above. |
#4
|
|||
|
|||
Quote:
Thanks for the help. Here are the monetary inputs. 1-Jan-12 (130,000) 31-Jan-12 6,685 29-Feb-12 13,133 31-Mar-12 19,262 30-Apr-12 24,986 31-May-12 30,386 30-Jun-12 35,549 31-Jul-12 40,478 31-Aug-12 45,168 30-Sep-12 49,682 31-Oct-12 54,075 30-Nov-12 58,357 31-Dec-12 62,535 I guess my follow up question now is how do I explain in laymans terms to superiors that it is an annual 640%? When we haven't even increased the original outlay by 640%. They seem to think that it should mean we should make 6.4 * 130,000. for it to be a 640% return. They think that the 217% makes the most sense. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR vs XIRR
Whilst you're at it:
Can you let everyone here know where we can invest our 130K, based on your figures we can get $171.00 a day return which is approx 48% in just 12 months....:) Just let me who to make the check out to.... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR vs XIRR
"Csmithers" wrote:
I guess my follow up question now is how do I explain in laymans terms to superiors that it is an annual 640%? When we haven't even increased the original outlay by 640%. They seem to think that it should mean we should make 6.4 * 130,000. for it to be a 640% return. They think that the 217% makes the most sense. Well, a return of 640% should mean that their return was 7.4*130000; that is, 130000*(1+640%). And they are correct: 640% is the wrong answer, based on how "they" appear to be thinking. Based on your 1-year data, the actual annual return is about 239%. If your data are in B1:B13, your actual annual return is SUM(B2:B13)/(-B1)-1 formatted as Percentage. That is a simple average monthly rate of about 19.9%, computed by (SUM(B2:B13)/(-B1)-1)/12. Alternatively, it is a compounded average monthly rate of about 10.7%, computed by (SUM(B2:B13)/(-B1))^(1/12)-1. The difference between these figures and the number returned by IRR (a monthly rate of about 18.1%) is "time-value of money" -- the assumption that it is better to have more returns earlier. Note that if the cash flows in B2:B13 were reversed, IRR returns a different result. Since that does not seem to be the way "they" are thinking, I would not use a compounded average monthly rate (10.7% or 18.1%). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR vs XIRR
On May 22, 6:06*pm, Csmithers
wrote: 'joeu2004[_2_ Wrote: ;1601997']"Csmithers" wrote:- I have an out flow and then monthly inflows for a month. They are the same time every month the first of the month. When I use IRR I get 18%, when I use XIRR I get 640%. If i multiply 18% by 12 I get 217% which seems right , but doesn;t take into account compounding. *If use this =(iRR(C4:C16)+1)^12-1) I get 633% which is very close to 640%- All are right and wrong! First, IRR returns the periodic rate. *If you have monthly cash flows, IRR returns a monthly rate. In contrast, XIRR always returns an annual rate. That explains why XIRR is so much larger than the (monthly) IRR. Second, there is no general agreement on how to annualize a periodic rate. To some degree, it depends on what financial securities you are modelling, the industry conventions applied to those securities, and even applicable regional laws. *Sometimes it is correct to multiply a monthly IRR by 12; sometimes it is correct to compound it over 12 periods; sometimes we multiply by other factors. In contrast, XIRR always compounds daily. That partly explains why even when you compound the monthly IRR over 12 periods, it is not exactly the same as daily-compounded XIRR. Third, by definition, IRR treats each month as the same length when using monthly cash flows. *But XIRR uses the actual number of days. That further explains why the monthly compounded IRR over 12 periods does not agree exactly with the XIRR. Finally, note that neither the IRR nor the XIRR can be computed algebraically (unless all cash flows are the same, and they occur regularly). *Instead, each function uses some algorithm to "home in on" the rate that causes the (X)NPV to be close to zero. *Microsoft documentation indicates that starting with Excel 2003, IRR and XIRR use the Newton-Raphson method. But each algorithm is implemented differently, which gives rise to differences in the results due to arithmetic anomalies (due to binary floating-point) as well as diffences in the tolerances and the underlying differential formulas. That is another reason why the monthly compounded IRR over 12 period does not agree with the XIRR. In actual practice, it is best not to read too much into the detailed numerical results. *They are all only estimates anyway. *Choose whichever function is more appropriate for the data that you have. PS: *I might note that the Excel IRR and XIRR functions are simply two ways to calculate the "internal rate of return" (IRR). *In other words, when I speak of "IRR", you need to decide by context whether I am speaking of the Excel function or the financial concept. "Csmithers" wrote:- I get 633% which is very close to 640% but seems way to high. *I haven't even tripled my investment, which was -130,000 while making 440,00 total over the year. *Can someone tell me which number is correct and why?- Yes, that is the fallacy of annualizing periodic rates, IMHO. For example, if the value of a security changes 1% in a day, surely you do not believe we can expect its value has grown (will grow) at a annual rate of 3678%. *That is indeed what (1+1%)^365-1 is. *But that is not a realistic assessment of the change in value. So even though it is common practice to annualize periodic rates, I try to avoid it -- although there are applications of the rate of return where we must annualize. Instead, I prefer to specify appropriate periodic rates of return. *So instead of annualizing a periodic IRR, I prefer to "de-compound" the annual(ized) XIRR rate. *For example, (1+XIRR(...))^(1/12)-1. Nevertheless, that monthly XIRR will not be the same as the periodic IRR based on monthly cash flows for all of the reasons given above. Thanks for the help. *Here are the monetary inputs. 1-Jan-12 * * * * (130,000) 31-Jan-12 * * * *6,685 29-Feb-12 * * * *13,133 31-Mar-12 * * * *19,262 30-Apr-12 * * * *24,986 31-May-12 * * * *30,386 30-Jun-12 * * * *35,549 31-Jul-12 * * * *40,478 31-Aug-12 * * * *45,168 30-Sep-12 * * * *49,682 31-Oct-12 * * * *54,075 30-Nov-12 * * * *58,357 31-Dec-12 * * * *62,535 I guess my follow up question now is how do I explain in laymans terms to superiors that it is an annual 640%? *When we haven't even increased the original outlay by 640%. *They seem to think that it should mean we should make 6.4 * 130,000. for it to be a 640% return. *They think that the 217% makes the most sense. -- Csmithers Well you made a common error in selection of the date for cash flow at time period t=0 thus your IRR and XIRR numbers mismatched The date for time period t = 0 would be 12/31/2011 rather than 1/1/2012 Once you make this correction the XIRR would equal 633.07% which is the same as the annualized IRR you calculated And the monthly IRR would equal the IRR you calculated by using Excel IRR function (1 + i)^12 - 1 = 633.07% (1 + i)^12 = 6.3307 + 1 (1 + i)^12 = 7.3307 (1 + i) = (7.3307)^1/12 1 + i = 1.1805800877156739686170250080055 i = 1.18058 - 1 i = 0.18058 i = 0.1806 i = 18.06% |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR vs XIRR
Errata.... I wrote:
"Csmithers" wrote: They seem to think that it should mean we should make 6.4 * 130,000. for it to be a 640% return. Well, a return of 640% should mean that their return was 7.4*130000; that is, 130000*(1+640%). Scratch that. It depends on the definition of "return". It was an irrelevant comment in the first place. It has no bearing the rest of my comments. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IRR vs XIRR
"PJ Hooker" wrote:
Csmithers Here are the monetary inputs. 1-Jan-12 (130,000) 31-Jan-12 6,685 29-Feb-12 13,133 31-Mar-12 19,262 30-Apr-12 24,986 31-May-12 30,386 30-Jun-12 35,549 31-Jul-12 40,478 31-Aug-12 45,168 30-Sep-12 49,682 31-Oct-12 54,075 30-Nov-12 58,357 31-Dec-12 62,535 [....] Well you made a common error in selection of the date for cash flow at time period t=0 thus your IRR and XIRR numbers mismatched The date for time period t = 0 would be 12/31/2011 rather than 1/1/2012 There is no "common error" if those are the actual dates of the cash flows. But perhaps the point you intended to make is: in using the IRR with those cash flows, we must assume the first cash flow occurs on 31-Dec-2011 (i.e. all transactions occur at regular intervals). And that will contribute to differences between the results of the Excel XIRR and IRR functions. So for an apples-to-apples comparison, we should fudge the date of the first cash flow in the XIRR parameters. "PJ Hooker" wrote: Once you make this correction the XIRR would equal 633.07% which is the same as the annualized IRR you calculated Excel XIRR returns about 632.84%, whereas Excel IRR returns about 18.0617%, which is about 633.35% when annualized by compounding. (I wonder if the difference between PJHooker's 633.07% and Excel XIRR's 632.84% is due to the fact that Excel XIRR always uses 365 days for a year, whereas 2012 actually has 366 days. But when I make that substitution in my own NPV formulation, I get about 636.85%, not 633.07%; and using 633.07% results in a large error in the NPV.) In any case, they still are not "the same" for the other reasons that I provided, primarily differences in cash flow frequencies (the fact that each monthly cash flows is not the same number of days apart). There is no way to overcome that primary difference. But I must admit: I am surprised by the difference that 1 day makes. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XIRR | Excel Discussion (Misc queries) | |||
XIRR | Excel Discussion (Misc queries) | |||
XIRR | Excel Discussion (Misc queries) | |||
XIRR | Excel Worksheet Functions | |||
Xirr? | Excel Discussion (Misc queries) |