XIRR Formula to start at first value

yuhonk

New Member
Joined
Jul 25, 2014
Messages
10
Hello all,

I would like to use the XIRR formula however not all my data start on the same date
2006-06-302006-09-302006-12-312007-03-31XIRR
-2001020516%
-300151535039%
-10010522%

<tbody>
</tbody>

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

Thanks,
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Copy E2 down.
Excel Workbook
ABCDE
16/30/20069/30/200612/31/20063/31/2007XIRR
2-2001020516%
3-300151535039%
4-10010522%
Sheet2
 
Upvote 0
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?
ABCDE
16/30/20069/30/200612/31/20063/31/2007XIRR
2 0-2001020516%
3-300151535039%
4 0 0-10010522%

<tbody>
</tbody>
thanks,
 
Upvote 0
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?
ABCDE
16/30/20069/30/200612/31/20063/31/2007XIRR
2 0-2001020516%
3-300151535039%
4 0 0-10010522%
thanks,
Excel Workbook
ABCDE
16/30/20069/30/200612/31/20063/31/2007XIRR
20-2001020516%
3-300151535039%
400-10010522%
Sheet5
 
Upvote 0
Sheet5

*ABCDE
16/30/20069/30/200612/31/20063/31/2007XIRR
20-200020516%
3-3001515039%
400-10010522%

<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
CellFormula
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.
 
Upvote 0
as joshmcameron mentionned, is there a way from the formula above to include 0 value after the first sart date?
 
Upvote 0

Forum statistics

Threads
1,216,099
Messages
6,128,813
Members
449,469
Latest member
Kingwi11y

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top