# How to get interest earned from start of repayment to a specific date on a declining balance?

I need a formula in getting the interest earned from start of payment of my loan to a specific date. I know the formula for the current due interest, which my company have given me in excel:

``````days = DateDiff(LastPaymentDate, CurrentPaymentDate, "D")
Due Interest = Principal Remaining * Int. Rate * days / 360
``````

but these formula only gives me my current due interest. I would like to get my interest from the start to a specific point in time as if I have not paid any interest from it. I have googled for formulas but only found formulas on monthly or the whole life of the loan. By the way this is the details of my loan:

``````Principal        = 70000
Annual Interest  = 8%
Terms            = 3 years
Payment Freq.    = Monthly
``````

Sample scenario

``````Payment Start    = Jan - 2016
Payment Date     = March 15 2016
Interes Earned (Jan 01 to March 15) = ?
``````

The formula you require is

`interest = (d+d q-r s-(1+q) (1+r)^x (d-r s)+d r x)/r`

where

``````d is the monthly payment
r is the monthly rate = 0.08/12
s is the principal = 70000
x is the number of complete months = 2
q is the interest factor for the incomplete month = Int. Rate * days / 360
``````

Obtaining the monthly payment amount `d` using the loan payment formula

`d = r s/(1 - (1 + r)^-n)` where `n = 36 months`

``````∴ d = 70000 r/(1 - (1 + r)^-36) = 2193.55
``````

Taking the OP's specification for interest due

``````q = Int. Rate * days / 360 = 0.08 * 15/360
``````

and applying the formula

``````interest = (d + d q - r s - (1 + q) (1 + r)^x (d - r s) + d r x)/r = 1143.6
``````

The interest accrued from January to March 15th is 1143.6

To explain and demonstrate the method, first obtain the interest for just the first two months

``````x = 2
q = 0
interest = (d + d q - r s - (1 + q) (1 + r)^x (d - r s) + d r x)/r = 921.821
``````

Check: the first two months interest = `70000 r + (70000 (1 + r) - d) r = 921.821`

The balance after two months = `(70000 (1 + r) - d) (1 + r) - d = 66534.7`

15 days interest on the balance = `66534.7 * 0.08 * 15/360 = 221.782`

`interest = 921.821 + 221.782 = 1143.6` which matches the formula's result.

Another example: interest paid after 20 months and 10 days.

``````x = 20
q = 0.08 * 10/360
interest = (d + d q - r s - (1 + q) (1 + r)^x (d - r s) + d r x)/r = 7129.67
``````

Derivation of the formula

Here is the calculation in longhand leading to the resulting formula.

r is the monthly rate, s is the principal, n is the number of months and d is the monthly payment

``````r = 0.08/12
s = 70000
n = 36

d = (r (1+r)^n s)/(-1+(1+r)^n) = 2193.55
``````

Calculating interest for each month and the end-of-month balance.

``````int[jan2016] = 70000 r
``````

466.667

``````end[jan2016] = 70000 (1 + r) - d
``````

68273.1

``````int[feb2016] = end[jan2016] r
``````

455.154

``````end[feb2016] = end[jan2016] (1 + r) - d
``````

66534.7

Calculating the interest for 15 days on the balance. One may use an approximate method such as `Int. Rate * days / 360`. Here instead I have taken the daily rate as the 365th root of the annual effective rate: `(1 + Int. Rate/12)^(days 12/365) - 1`) which is how I would usually obtain a daily rate.

``````int[mar15th2016] = end[feb2016] ((1 + r)^(15*12/365) - 1)
``````

218.376

Interest from Jan to March 15th `= 466.667 + 455.154 + 218.376 = 1140.2`

Solving the recurrence equation

The solution for `end[x + 1] = end[x] (1 + r) - d` where `end = s` is

``````pn = (d-d (1+r)^x+r (1+r)^x s)/r
`````` This can be used to find the balance after any `x` number of complete months.

``````with x = 1
p1 = pn = 68273.1
int1 = p1 + d - s = 466.667

with x = 2
p2 = pn = 66534.7
int2 = p2 + d - p1 = 455.154

int3 = p2 ((1 + r)^(15*12/365) - 1) = 218.376
``````

Interest from Jan to March 15th `= int1 + int2 + int3 = 1140.2`

Extending the formula

The recurrence solution can be used in a summation for the interest after `x` complete months. The closed-form for the summation can be found by induction

``````bx = (d (1+x r-(1+r)^x)+r (-1+(1+r)^x) s)/r
`````` ``````with x = 2
b2 = bx = 921.821

total = b2 + p2 ((1 + r)^(15*12/365) - 1) = 1140.2
``````

Final formula

Putting the formulas `bx` & `pn` together and simplifying, with `q` as the interest factor for the odd 15 days.

``````q = (1 + r)^(15*12/365) - 1

x = 2

bx = (d (1 + x r - (1 + r)^x) + r (-1 + (1 + r)^x) s)/r
pn = (d - d (1 + r)^x + r (1 + r)^x s)/r

total = bx + pn q = (d (1 + x r - (1 + r)^x) + r (-1 + (1 + r)^x) s)/r +
q (d - d (1 + r)^x + r (1 + r)^x s)/r

∴ total = (d+d q-r s-(1+q) (1+r)^x (d-r s)+d r x)/r = 1140.2
``````

And alternatively, with the OP's method for calculating the daily accrual.

``````q = 0.08 * 15/360

total = (d+d q-r s-(1+q) (1+r)^x (d-r s)+d r x)/r = 1143.6
``````

If you know:

``````- S:  the original balance of the loan at the start. 70000)
- C:  the current balance of the loan. (69000)
- P:  the total amount of payments made. (3000)
``````

To know how much in interest:

``````I = P- (S - C) = 3000 - (70000 - 69000) = 3000 - 1000 = 2000
``````