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

2006-06-30	2006-09-30	2006-12-31	2007-03-31	XIRR
	-200	10	205	16%
-300	15	15	350	39%
		-100	105	22%

<tbody>
</tbody>

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

Thanks,

 

[JoeMo]

Copy E2 down.

Excel Workbook
	A	B	C	D	E
1	6/30/2006	9/30/2006	12/31/2006	3/31/2007	XIRR
2		-200	10	205	16%
3	-300	15	15	350	39%
4			-100	105	22%
Sheet2

 

[yuhonk]

It worked perfectly. Many thanks!

 

[JoeMo]

It worked perfectly. Many thanks!

You are welcome - thanks for the reply.

 

[yuhonk]

Hi,

instead of having blank cells, what if the cells had 0 as value, how can i modify the formula to start only if the cell is different from 0?

	A	B	C	D	E
1	6/30/2006	9/30/2006	12/31/2006	3/31/2007	XIRR
2	0	-200	10	205	16%
3	-300	15	15	350	39%
4	0	0	-100	105	22%

<tbody>
</tbody>

thanks,

 

[JoeMo]

Hi,

instead of having blank cells, what if the cells had 0 as value, how can i modify the formula to start only if the cell is different from 0?

	A	B	C	D	E
1	6/30/2006	9/30/2006	12/31/2006	3/31/2007	XIRR
2	0	-200	10	205	16%
3	-300	15	15	350	39%
4	0	0	-100	105	22%

thanks,

Excel Workbook
	A	B	C	D	E
1	6/30/2006	9/30/2006	12/31/2006	3/31/2007	XIRR
2	0	-200	10	205	16%
3	-300	15	15	350	39%
4	0	0	-100	105	22%
Sheet5

 

[yuhonk]

again thanks for your help, much appreciated

 

[JoeMo]

again thanks for your help, much appreciated

You are welcome - thanks for the reply.

 

[joshmcameron]

Sheet5

*	A	B	C	D	E
1	6/30/2006	9/30/2006	12/31/2006	3/31/2007	XIRR
2	0	-200	0	205	16%
3	-300	15	15	0	39%
4	0	0	-100	105	22%

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:68px;"><col style="width:75px;"><col style="width:68px;"><col style="width:50px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =XIRR(OFFSET(A2,0,COUNT($A$1:$D$1)-COUNTIF(A2:D2,"<>0"),1,COUNTIF(A2:D2,"<>0")),OFFSET($A$1,0,COUNT($A$1:$D$1)-COUNTIF(A2:D2,"<>0"),1,COUNTIF(A2:D2,"<>0"))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

What if there are zeros that represent cash flows that didn't exist? How would you handle this? I would still want the countif to count those, just not any zeros left of the first initial cashflow.

 

[yuhonk]

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