IRR and XIRR results miles apart.

[outtahere]

I have been trying to obtain IRR results to evaluate returns of money invested. This comprises different monthly investments at different times of the month and then at the end of the year or any period, calculating the rate of return. There may be withdrawals too.

I tried IRR which gave one figure and I was told I had to use the US date format for that to work. I tried the XIRR to compare, thinking that it may change the results somewhat and I also compared it with a crude calculate and money weighted return and all the figures were way out.

Here is the spreadsheet. I have not yet worked out how to import microsoft for excel 2011 so bear with me.

In summary, initial value could be lump sum at start of period, periodic is whatever I invest, regularly,lump sum means any ad hoc investments, liquidation means withdrawals and final value is current valuation.

What can you suggest?

[TABLE="width: 427"]
<colgroup><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]Initial[/TD]
[TD]Periodic[/TD]
[TD]Lump[/TD]
[TD]Liquid-[/TD]
[TD]Final [/TD]
[TD]Sequence[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Value[/TD]
[TD]Invest-[/TD]
[TD]Sum[/TD]
[TD]ation[/TD]
[TD]Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ment[/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]
[/TR]
[TR]
[TD="align: right"]01/01/14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0[/TD]
[/TR]
[TR]
[TD="align: right"]02/02/14[/TD]
[TD][/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0[/TD]
[/TR]
[TR]
[TD="align: right"]03/15/14[/TD]
[TD][/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0[/TD]
[/TR]
[TR]
[TD="align: right"]04/08/14[/TD]
[TD][/TD]
[TD="align: right"]500.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500.0[/TD]
[/TR]
[TR]
[TD="align: right"]05/22/14[/TD]
[TD][/TD]
[TD="align: right"]1,000.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,000.0[/TD]
[/TR]
[TR]
[TD="align: right"]06/13/14[/TD]
[TD][/TD]
[TD="align: right"]2,000.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2,000.0[/TD]
[/TR]
[TR]
[TD="align: right"]07/12/14[/TD]
[TD][/TD]
[TD="align: right"]2,500.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2,500.0[/TD]
[/TR]
[TR]
[TD="align: right"]08/28/14[/TD]
[TD][/TD]
[TD="align: right"]4,000.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4,000.0[/TD]
[/TR]
[TR]
[TD="align: right"]09/22/14[/TD]
[TD][/TD]
[TD="align: right"]6,900.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6,900.0[/TD]
[/TR]
[TR]
[TD="align: right"]10/11/14[/TD]
[TD][/TD]
[TD="align: right"]3,750.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3,750.0[/TD]
[/TR]
[TR]
[TD="align: right"]11/15/14[/TD]
[TD][/TD]
[TD="align: right"]4,500.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4,500.0[/TD]
[/TR]
[TR]
[TD="align: right"]12/23/14[/TD]
[TD][/TD]
[TD="align: right"]12,500.0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40999[/TD]
[TD="align: right"]-28,499.0[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD="align: right"]37,650[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Monthly IRR [/TD]
[TD][/TD]
[TD="align: right"]3.74%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]12 month IRR[/TD]
[TD][/TD]
[TD="align: right"]55.33%[/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Money Weighted Return[/TD]
[TD="align: right"]3,349.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10729.16667[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XIRR[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Crude Estimate[/TD]
[TD][/TD]
[TD="align: right"]30%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

You should use XIRR for irregular cash flows. Set out your data like this:

Excel 2010
	A	B
1	Date	Amount
2	08/04/2014	500.00
3	22/05/2014	1,000.00
4	13/06/2014	2,000.00
5	12/07/2014	2,500.00
6	28/08/2014	4,000.00
7	22/09/2014	6,900.00
8	11/10/2014	3,750.00
9	15/11/2014	4,500.00
10	23/12/2014	12,500.00
11	23/12/2014	-40,999.00
12
13	XIRR	52%
Sheet1

Cell Formulas
Range	Formula
B13	=XIRR(B2:B11,A2:A11)

 

[outtahere]

Thank you, that works with the spreadsheet and looks fantastic on paper but before I get excited I am left with the following questions. I believe XIRR calculates a project rate of return over any period and so 52% is a 9 month return. For me to convert that to annual equivalent I guess I would have to multiply by 1.33

The other question this raises is how does it get that high. If I simply take the profit (end value less amount invested/amount invested over time e.g April X 9/12, May X 8/12 etc. Then I get a figure of around 31%. Even that seems high.

thanks

 

[Andrew Poulsom]

XIRR returns the rate that gives an XNPV of zero. Here's the proof:

Excel 2010
	A	B	C	D	E	F
1	Date	Amount		Days	Disc Rate
2	08/04/2014	500.00		0	152%	500.00
3	22/05/2014	1,000.00		44	152%	950.61
4	13/06/2014	2,000.00		66	152%	1,853.68
5	12/07/2014	2,500.00		95	152%	2,241.02
6	28/08/2014	4,000.00		142	152%	3,396.80
7	22/09/2014	6,900.00		167	152%	5,693.25
8	11/10/2014	3,750.00		186	152%	3,027.22
9	15/11/2014	4,500.00		221	152%	3,489.21
10	23/12/2014	12,500.00		259	152%	9,277.43
11	23/12/2014	-40,999.00		259	152%	-30,429.23
12					NPV	0.00
13	XIRR	52%
14	XNPV	0.00
Sheet1

Cell Formulas
Range	Formula
B13	=XIRR(B2:B11,A2:A11)
B14	=XNPV(B13,B2:B11,A2:A11)
D2	=A2-A$2
E2	=1+B$13
F2	=B2/E2^(D2/365)
F12	=SUM(F2:F11)

 

[joeu2004]

I believe XIRR calculates a project rate of return over any period and so 52% is a 9 month return. For me to convert that to annual equivalent I guess I would have to multiply by 1.33

No. XIRR always returns an annualized rate.

In contrast, IRR returns a periodic rate, assuming each cash flow is a different period, and assuming each period is the same length of time. The result of IRR must be annualized one way or another. The method of annualization varies with your purpose.

FYI....

I was told I had to use the US date format for that to work.

That is incorrect.

The format of the date does not matter.

However, you must enter the date in a form that is compatible with your settings in the Regional and Language Options control panel (WinXP and Win7; I don't know about Win8). Use ISNUMBER to verify that Excel interprets the data entry as a date.

What you might have been told is that the source of data uses the US date format. If that differs from your system date format, you might need to convert the source data. You can use Text To Columns to accomplish that.