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]

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
XIRR
0
0	0

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]6/30/2006[/TD]
[TD="align: right"]9/30/2006[/TD]
[TD="align: right"]12/31/2006[/TD]
[TD="align: right"]3/31/2007[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]-200[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]16%[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]-300[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]39%[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]-100[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]22%[/TD]

</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
XIRR
0
0	0

[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]6/30/2006[/TD]
[TD="align: right"]9/30/2006[/TD]
[TD="align: right"]12/31/2006[/TD]
[TD="align: right"]3/31/2007[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]-200[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]16%[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]-300[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]39%[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]-100[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]22%[/TD]

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
XIRR

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]6/30/2006[/TD]
[TD="align: right"]9/30/2006[/TD]
[TD="align: right"]12/31/2006[/TD]
[TD="align: right"]3/31/2007[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]16%[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]-300[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]39%[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]22%[/TD]

</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?