1

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) = ?
1

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[0] = s is

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

enter image description here

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.

enter image description here

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

enter image description here

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.