XIRR Formula to start at first value

[yuhonk]

Hello all,

I would like to use the XIRR formula however not all my data start on the same date[TABLE="width: 500"]
<tbody>[TR]
[TD]2006-06-30[/TD]
[TD]2006-09-30[/TD]
[TD]2006-12-31[/TD]
[TD]2007-03-31[/TD]
[TD]XIRR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-200[/TD]
[TD]10[/TD]
[TD]205[/TD]
[TD]16%[/TD]
[/TR]
[TR]
[TD]-300[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]350[/TD]
[TD]39%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]-100[/TD]
[TD]105[/TD]
[TD]22%[/TD]
[/TR]
</tbody>[/TABLE]

To sum up, I would like to be able to start a formula (XIRR) on th first cell <> 0.

Thanks,

 

[JoeMo]

as joshmcameron mentionned, is there a way from the formula above to include 0 value after the first sart date?

A zero-value cash flow is a non-event and should have no effect on the XIRR value.

 

[yuhonk]

Hi JoeMo,

You are correct a zero-value cashflow does not affect the XIRR value, however the formula above will result to 0% there be a zero in the middle.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]2006-06-30[/TD]
[TD]2006-09-30[/TD]
[TD]2006-12-31[/TD]
[TD]2007-03-31[/TD]
[TD]XIRR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-200[/TD]
[TD]0[/TD]
[TD]205[/TD]
[TD]16%[/TD]
[/TR]
[TR]
[TD]-300[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]350[/TD]
[TD]39%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]-100[/TD]
[TD]105[/TD]
[TD]22%[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Hi JoeMo,

You are correct a zero-value cashflow does not affect the XIRR value, however the formula above will result to 0% there be a zero in the middle.

[TABLE="class: cms_table, width: 500"]
[TR]
[TD]2006-06-30[/TD]
[TD]2006-09-30[/TD]
[TD]2006-12-31[/TD]
[TD]2007-03-31[/TD]
[TD]XIRR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-200[/TD]
[TD]0[/TD]
[TD]205[/TD]
[TD]16%
[/TD]
[/TR]
[TR]
[TD]-300[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]350[/TD]
[TD]39%
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]-100[/TD]
[TD]105[/TD]
[TD]22%[/TD]
[/TR]
[/TABLE]

Use the formula I posted in post #2. BTW - I think your first value (in red above) is wrong.

Excel Workbook
	A	B	C	D	E
1	6/30/2006	9/30/2006	12/31/2006	3/31/2007	XIRR
2		-200	0	205	5.08%
3	-300	15	0	350	30.88%
4			-100	105	21.88%
Sheet1

 

[butterexcel]

Hello,
The above was very helpful, however is there a way to calculate XIRR if there are zeros in front and throughout the dataset? thanks

6/30/2006	9/30/2006	12/31/2006	1/31/2007	3/31/2007
0	-200	10	0	205
-300	15	15	0	350
0	0	-100	0	105

 

[JoeMo]

XIRR requires the first numeric value to be negative, and that there be at least one positive and one negative value in the series. Otherwise, there can be zero values throughout.