formula accounting for cash flows.

I need help figuring out how to account for an expense that say ends on 4/17/2010 on a monthly cash flow with payments due at the first of the month, I could do this the easy way and input values but I would prefer to write a code to account for this.

I have attached a spreadsheet outlining where I am right now.

Thanks in advance for any help!


Andrew


=IF(AND(year>=$D$9,year<=$E$9),$B$9*((1+$C$9)^IF(year=$D$9,0,IF(year<=$E$9,DATEDIF($D$9,G6,code)))),"")

This may work for you:

Monthly Formula: =IF(AND(F2>=start,F2<=end),SUM(IF(TEXT(start,"dd")=LEFT(TEXT(ROW(INDIRECT(E2&":"&F2)),"dd/mm/yyyy"),2),1,0))*amount,IF(end<F2,SUM(IF(TEXT(start,"dd")=LEFT(TEXT(ROW(INDIRECT(E2&":"&end)),"dd/mm/yyyy"),2),1,0))*amount))

Annual Formula: =IF(AND(F2>=start,F2<=end),SUM(IF(TEXT(start,"mm/dd")=LEFT(TEXT(ROW(INDIRECT(E2&":"&F2)),"mm/dd/yyyy"),5),1,0))*amount,IF(end<F2,SUM(IF(TEXT(start,"mm/dd")=LEFT(TEXT(ROW(INDIRECT(E2&":"&end)),"mm/dd/yyyy"),5),1,0))*amount))

These are array formulas so you will have to not just click "enter" but click "enter", "control", and "shift" at the same time when inputting the formula.

Basically, if a date is within the correct parameters the formulas above will tell you how many times a certain day of the month is between two dates. The annual formula will tell you how many times a certain day of a certain month is between two dates.

There are certainly some errors that will occur. For instance if the certain day of the month is also the day of the month in cash flow periods then it will count 1 too many periods. For instance if you are searching for a payment on the 1st of the month and the two periods you are looking between are 1/1/2000 and 4/1/2000 then the formula will count 5 periods. You could add an if statement to deduct 1 from the sum if the actual date is the same as the period interval in the cash flow period row.

See attached spreadsheet.