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.