Help with IRR vs. XIRR

[mcelhinney]

Hi, I'm trying to figure out why XIRR and IRR are showing different returns in this case (simple bond math). These are the cash flows:

4/30/2018: -1,000
5/1/2018: 100
5/1/2019: 100
5/1/2020: 100
5/1/2021: 100
5/1/2022: 100+1,000

IRR returns 10%, which makes sense. XIRR returns 13%. Could someone help me create an XIRR function for this that will return 10%, the correct percent for this yield?

Thanks in advance!

 

[iggy_]

The reason you are getting different result is because IRR is indifferent to the time periods, so in your example it assumes year 0 as -1000, year 1-4 +100, and year 5 +1,100. XIRR takes actual dates into consideration. The reason you are getting ~13% on XIRR is because your first payment is 1 day after the initial investment, but it should rather be a year after initial investment. If you adjust your years 1-5 by 1 year with the first payment on 5/1/19 your XIRR will be ~10%.

 

[mcelhinney]

Thank you. That works on an annual basis. Having the same problem when I try this on a monthly basis, see below:

IRR is 0.44%, or 5.25% when multiplied by 12. The XIRR is 5.36%. Any idea how to make the XIRR return 5.25? Thakns!

[TABLE="width: 1233"]
<colgroup><col span="2"><col span="13"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/1/2019[/TD]
[TD="align: right"]6/1/2019[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]9/1/2019[/TD]
[TD="align: right"]10/1/2019[/TD]
[TD="align: right"]11/1/2019[/TD]
[TD="align: right"]12/1/2019[/TD]
[TD="align: right"]1/1/2020[/TD]
[TD="align: right"]2/1/2020[/TD]
[TD="align: right"]3/1/2020[/TD]
[TD="align: right"]4/1/2020[/TD]
[TD="align: right"]5/1/2020[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Payment[/TD]
[TD="align: right"]-$2,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$2,000,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Yield[/TD]
[TD][/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]-$2,000,000[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$8,750[/TD]
[TD="align: right"]$2,008,750[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IRR[/TD]
[TD="align: right"]5.25%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XIRR[/TD]
[TD="align: right"]5.36%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[iggy_]

Thats just the difference in compounding, if you compound the IRR ((1+period IRR)^t)-1 instead of just multiplying you will have much closer answers. The remaining difference will be due to how XIRR and IRR calculate interest (30/360 vs actual/365).