What does XIRR tell me exactly?

hardgrafting

New Member
Joined
Feb 6, 2017
Messages
25
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,838
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
 
Last edited:

hardgrafting

New Member
Joined
Feb 6, 2017
Messages
25
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,811
Office Version
  1. 2010
Platform
  1. Windows
(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:


ABCDE
1#mointbal
21/1/2018$50,000.00
310/1/20189$6,750.00$56,750.00C3: =D2*$B$7*B3
43/1/20195$4,256.25$61,006.25D3: =D2+C3
57/1/20194$3,660.38$64,666.63
6
7%Int/mo1.50%
8%Int/yr18.00%B8: =B7*12
9AER19.56%B9: =EFFECT(B8,12)
10
11cf
121/1/2018-$50,000.00
137/1/2019$64,666.63
14
15XIRR18.76%B15: =XIRR(B12:B13,A12:A13)
16CAGR118.71%B16: =(D5/D2)^(12/SUM(B3:B5))-1
17
CAGR218.76%B17: =(D5/D2)^(365/(A5-A2))-1
18
IRR/mo
1.44%
B18: =(1+B15)^(1/12)-1

<tbody>
</tbody>


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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,335
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top