What does XIRR tell me exactly?

[hardgrafting]

Hello all,

I am trying to figure out what XIRR tells me exactly, I understand that it is a method used to calculate returns for a project, but in terms of what it actually means I am finding conflicting information from research online. Specifically I want to understand the difference between XIRR and a simple interest rate calculation.

Thanks

 

[pgc01]

Specifically I want to understand the difference between XIRR and a simple interest rate calculation.

Hi

The difference between IRR() and XIRR() is that
- for the IRR() you have the cash flows dates evenly spaced, like each month or annually
- for the XIRR() you may have cash flows at any date you want

 

[hardgrafting]

Thanks for this, perhaps I was not clear in my query.

What does XIRR actually tell me? For example, say I have an 8% XIRR in 18 months, does this mean I make 8% on my money?

So for example, say I invested $100 on January 2018, does this mean I will have $108 as at end June 2019?

 

[joeu2004]

(Did you ask a similar question in answers.microsoft.com? I ask because it would save me some time if I do not have answer the same question twice.)

What does XIRR actually tell me? For example, say I have an 8% XIRR in 18 months, does this mean I make 8% on my money?
So for example, say I invested $100 on January 2018, does this mean I will have $108 as at end June 2019?

No.

First, Excel XIRR always returns an annual rate. So after 18 months (1.5 years), you might think that you would have about 100*(1+8%)^1.5 = $112.24 after 18 months.

More precisely, 100*(1+8%)^((DATE(2019,6,30)-DATE(2018,1,1))/365) = $112.18, because Excel XIRR assumes daily compounding.

But Excel XIRR is the same as a compounded annual interest rate only in special cases, specifically for investments that compound interest daily based on the exact difference between dates. In contrast, Excel IRR assumes equal time between cash flows; for example, "monthly" instead of 28 to 31 days.

And even then, note that an annual interest rate is often stated as a simple rate, not compounded. For example, the daily rate might be annualRate / 365.

More generally, the IRR is a mathematical statistic that (some believe) can be used to compare alternative projects or investments. Specially, it is the discount rate that causes the net present value (NPV), i.e. the sum of the present value of the cash flows, to be zero. An investor might choose the investment with the highest IRR. The actual IRR value is not meaningful; it is not a predictor of future value.

(For some cash flow models, there might be multiple IRRs or no computable IRR.)

To demonstrate the difference, consider a sequence of CDs that return simple interest on maturity at 1.5% per month. If we invest in 9, 5 and 4 month CDs sequentially, starting with $50,000, the final account balance would be $64,666.63. This is demonstrated as follows:


[TABLE="class: grid, width: 600"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#mo[/TD]
[TD="align: right"]int[/TD]
[TD="align: right"]bal[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$50,000.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$6,750.00[/TD]
[TD="align: right"]$56,750.00[/TD]
[TD]C3: =D2*$B$7*B3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$4,256.25[/TD]
[TD="align: right"]$61,006.25[/TD]
[TD]D3: =D2+C3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$3,660.38[/TD]
[TD="align: right"]$64,666.63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]%Int/mo[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]%Int/yr[/TD]
[TD="align: right"]18.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]B8: =B7*12[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]AER[/TD]
[TD="align: right"]19.56%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]B9: =EFFECT(B8,12)[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]cf[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]-$50,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]$64,666.63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]XIRR[/TD]
[TD="align: right"]18.76%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]B15: =XIRR(B12:B13,A12:A13)[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]CAGR1[/TD]
[TD="align: right"]18.71%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]B16: =(D5/D2)^(12/SUM(B3:B5))-1[/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD]CAGR2[/TD]
[TD="align: right"]18.76%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]B17: =(D5/D2)^(365/(A5-A2))-1
[/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD]IRR/mo
[/TD]
[TD="align: right"]1.44%
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]B18: =(1+B15)^(1/12)-1
[/TD]
[/TR]
</tbody>[/TABLE]


Note that monthly IRR in B18, based on the annual (X)IRR in B15), is significantly different from the actual monthly interest rate, 1.50% in B7.