Determine cumulative interest payable on an annuity with varying time periods


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.

The DATEDIF() bug


Assume a beginning date in cell A7 and ending date in cell B7.  In range C7:E7, the task is to compute the years (in cell C7), remaining months (in cell D7) and remaining days (in cell E7).  As an example, if one types in 12-March-2013 in cell A7 and 24-March-2014 in cell B7, then the result in range C7:E7 should be 1,12 and 12 respectively.  The formulas for getting these results are:

1.  In cell C7 - =DATEDIF($A7,$B7,"y")
2.  In cell D7 - =DATEDIF($A7,$B7,"ym")
3.  In cell E7 - =DATEDIF($A7,$B7,"md")

So far so good.  All the results are correct.  However, there is a problem with the "md" syntax of the DATEDIF() function.  Try this

1. In cell A7, enter 31-July-2007
2. In cell B7, enter 02-March-2009
3. In cell C7, enter =DATEDIF($A7,$B7,"y")
4. In cell D7, enter =DATEDIF($A7,$B7,"ym")
5. In cell E7, enter =DATEDIF($A7,$B7,"md")

The result in cell E7 will be -1 which is clearly incorrect.  To get the correct result of 2 in cell E7, try this formula instead


Hope this helps.