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

2 Answers 2

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .