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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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

Banned user
Joined
Mar 2, 2014
Messages
3,080
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,439
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top