Inventory Wash-Out sheet
 

(You may have to maximize your message window for my 'worksheet' to display correctly.)

I trying to build a spreadsheet for inventory(investment) wash-out purposes. I started with this formula in Column E: =INTRATE(An,Bn,Cn,Dn) which worked fine on Rows 1 thru 3 but on the rows of item that have not sold, I get a #NUM! error as in Cell E5 below, and it carries the #NUM! error message to the Running Totals in cell E9.

I then entered =IF(Bn=0,"",(INTRATE(An,Bn,Cn,Dn))) which leaves a blank like in Cell E6 below and results in the correct average of Column E being displayed in cell E9, but this in incorrect when you calculate total money spent on inventory versus money received (and when the money was spent/received).

I then tried =IF(Bn=0,TODAY(),(INTRATE(An,Bn,Cn,Dn))) but that gives me a return of 3,841,000% on each unsold item. Sure this is a great rate of return on one's money but it ain't correct.

My questions is: How can I account for the effect of the money spent on unsold inventory when calculating my Return?

A B C D E
1 Purchase Date Sale Date Cost Sold Return
2 01/01/2005 01/01/2006 $1 $2 100.00%
3 01/01/2005 01/01/2007 $1 $2 50.00%
4 01/01/2005 01/01/2008 $1 $2 33.33%
5 01/01/2005 $1 #NUM!
6 01/01/2005 $1
7
8
9 Running Totals $5 $6 61.11%


Thanks in advance!!