Use Excel's MOD() function to allocate quarterly payments

Kashflow logo
Share this content

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.

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk 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.

About AccountingWEB

Replies

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.

Related content