Use Excel's MOD() function to allocate quarterly payments
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.
Continued...
The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.
Registration is FREE and allows you to view all content, ask questions, comment and much more.
Or if you are already registered, login here
