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