# What does XIRR tell me exactly?

#### hardgrafting

##### New Member
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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:
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?

(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.

Replies
6
Views
253
Replies
0
Views
214
Replies
1
Views
461
Replies
2
Views
246
Replies
0
Views
77

1,218,888
Messages
6,145,017
Members
450,586
Latest member
hehehihi2007

### 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.

### Which adblocker are you using?

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

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