Imagine a fixed monthly amount due to an Organisation for services rendered to various customers. While an invoice is raised every month by this Organisation, not all pay up the dues on time. For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum. The objective is to determine cumulative interest payable by various customers to Organisation X.
The base data looks like this
| Client | Monthly revenue | Int. calculation start date | Int. calculation end date | Interest rate |
| Client A | 33,967 | 01-Aug-16 | 25-Jul-17 | 9.00% |
| Client B | 123 | 12-Sep-16 | 30-Nov-17 | 4.00% |
Given the dataset above, the total interest payable by Client A is Rs. 16,237.20. The calculation is shown below:
| From | To | Days for which interest should be paid | Principal | Interest |
| 02-Aug-16 | 31-Aug-16 | 328.00 | 33,967.00 | 2,745.26 |
| 01-Sep-16 | 30-Sep-16 | 298.00 | 33,967.00 | 2,494.17 |
| 01-Oct-16 | 31-Oct-16 | 267.00 | 33,967.00 | 2,234.71 |
| 01-Nov-16 | 30-Nov-16 | 237.00 | 33,967.00 | 1,983.62 |
| 01-Dec-16 | 31-Dec-16 | 206.00 | 33,967.00 | 1,724.16 |
| 01-Jan-17 | 31-Jan-17 | 175.00 | 33,967.00 | 1,464.70 |
| 01-Feb-17 | 28-Feb-17 | 147.00 | 33,967.00 | 1,230.34 |
| 01-Mar-17 | 31-Mar-17 | 116.00 | 33,967.00 | 970.88 |
| 01-Apr-17 | 30-Apr-17 | 86.00 | 33,967.00 | 719.79 |
| 01-May-17 | 31-May-17 | 55.00 | 33,967.00 | 460.33 |
| 01-Jun-17 | 30-Jun-17 | 25.00 | 33,967.00 | 209.24 |
| 01-Jul-17 | 25-Jul-17 | – | 33,967.00 | – |
| Total | 16,237.20 |
You may download my solution workbook with from here. I have solved this problem using normal Excel formulas and the PowerPivot.
Determine cumulative interest payable on an annuity with varying time periods
{ 0 Comments }