Projecting Cashflows for Various Payment Terms

[ikjohnson]

I'm looking to project cashflows for each month for various client payment terms. For example, if the terms are Monthly, then the client is paying 12 equal amounts commencing from the date when first due. If the terms are 30/30/30/10, the client is paying 30% in Month1, 30% in Month3, 30% in Month6 and the final 10% in Month12. The sample below provides the correct numbers (calculated manually for demonstration), but how could this be achieved with formulas?

Cashflow3.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Client	InvoiceAmount	1stPaymentdue	Method	Apr-04	May-04	Jun-04	Jul-04	Aug-04	Sep-04	Oct-04
2	FGLCorp	$12,542	1-May-04	LumpSum	$0	$30,000	$0	$0	$0	$0	$0
3	ARB	$5,624	3-Jun-04	30/30/30/10	$0	$0	$1,687	$0	$1,687	$0	$0
4	Efco	$8,016	3-May-04	Monthly	$0	$668	$668	$668	$668	$668	$668
5	ABC	$19,500	4-May-04	30/30/30/10	$0	$5,850	$0	$5,850	$0	$0	$5,850
Sheet1

 

[ikjohnson]

After a little modification, your suggested formula works beautifully. I'm still without the EOMONTH function, but I achieved a workaround by changing the dates in the headings to the last day of the month. Projecting out past 12 months created the #NA error, which was fixed by including an extra nested IF statement.