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!

		5/1/2019	6/1/2019	7/1/2019	8/1/2019	9/1/2019	10/1/2019	11/1/2019	12/1/2019	1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020
	Payment	-$2,000,000												$2,000,000
	Yield		$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750
	Total	-$2,000,000	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$8,750	$2,008,750
IRR	5.25%
XIRR	5.36%

<colgroup><col span="2"><col span="13"></colgroup><tbody>
</tbody>

 

[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).