Simon Hurst describes a useful function for building cashflow projections in Excel.
During a recent IT Faculty Excel roadshow event one of the delegates asked how best to automatically allocate quarterly payments to the correct months for a 12-month forecast. The MOD() function sprang to mind. MOD() is a very simple function that just takes two arguments a number and a 'divisor'. MOD() then returns the remainder. For example MOD(5,2) would return 1. Assuming a payment first occurs in March, and that it will be repeated every three months, we could divide the month number by 3. There would be no remainder for months 3, 6, 9 and 12. We could use MOD() to check the remainder and, for each month where it is zero, include our payment.