1

I have create a calculator for loan repayment its working properly.When I create for the extra repayment didn't give a result as I expect.

Question : Saved Interest & Saved Time is wrong while other calculation is right

Referance calculator : http://www.homeloans.com.au/calculators/extra-repayments-calculator/

Reference Calculator

My calculator

Result not match with 1619.72

firstpartlog=Math.pow((1 + r),-m)
interestpostive=Math.pow((1 + r),m)

secondpartlog=(-d + d2 + interestpostive *d - (r*s))

n = -(Math.log(firstpartlog*secondpartlog)/d2)/bottomlog))
  • Seems like there are two possibilities: either your calculator actually is not working right, or your idea of the right answer is wrong. – jamesqf Sep 5 '17 at 17:17
  • @jamesqf it works now. – Vasim Shaikh Sep 6 '17 at 4:13
3
+50

The mathematics for this calculation is explained here:

What is the formula for calculating the total cost of a loan with extra payments towards the principal?

First calculating for loan without extra repayments

n is the number of periods
s is the principal
r is the periodic interest rate
d is the periodic repayment

n = 40*52 = 2080
s = 123500
r = 9/100/52

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

Check

n = -(Log[1 - (r s)/d]/Log[1 + r]) = 2080

interest = n d - s =  333629.405

Now adding m and d2

m is the number of periods after which extra repayments are made
d2 is the repayment including the extra amount

m = 29*52 = 1508
d2 = d + 566 = 785.774

The formula for a loan with extra repayment towards the end is

enter image description here

∴ n = -(Log[((1 + r)^-m (-d + d2 + (1 + r)^m (d - r s)))/d2]/Log[1 + r]) = 1619.72

With the extra repayment the loan is repaid in 1619.72/51 = 31.15 years. Treatment of the incomplete week is up to the lender, who may require a larger payment on week 1619 to complete repayment or take a smaller repayment in week 1620. (This can slightly affect the total interest.)

interest = m d + (n - m) d2 - s =  295703.813

interest saving = 333629.405 - 295703.813 = 37925.59

Rounded to the dollar the saving calculate with a partial week is 37926.

Comparison with website results

8 year 10 months is (8*12) + 10 = 106 months

40 year loan less 106 months is 40*12 - 106 = 374 months

Calculated term is 1619.72*12/52 = 373.782 months

Website interest saved is 37925.

Amortization amortization table

Using an amortization table calculates the interest saving minutely differently due to the treatment of the incomplete week. In the table the final week's balance is held for the full week whereas the formula uses a fraction of a week for a linear solution.

Saving by formula: 2080 d - (m d + (n - m) d2) = 37925.59

Amortization table: 2080 d - s - 295703.950225 = 37925.46

Amortization table showing figures and formulas

enter image description here

Whole final week calculation without amortisation table

The following calculates the interest saving assuming the final week carries a whole week's interest and matches the website, (rounded to the dollar).

n = 40*52 = 2080
s = 123500
r = 9/100/52
d = r (1 + 1/(-1 + (1 + r)^n)) s = 219.774

interest for loan with no extra payments

i1 = n d - s = 333629.405

increased payment

d2 = d + 566

number of week at initial payment d

m = 1508

time needed to repay (as linear calculation)

n = -(Log[((1 + r)^-m (-d + d2 + (1 + r)^m (d - r s)))/d2]/Log[1 + r]) = 1619.72

number of whole weeks

n2 = 1619

present value of amount repaid up to and including week 1508

s2 = (d - d (1 + r)^-m)/r = 117621.856

present value remaining

s3 = s - s2 = 5878.144

future value of remainder at week 1508

s4 = s3 (1 + r)^m =  79757.043

number of weeks from 1508 to 1619

n3 = 1619 - 1508 = 111

amount paid by d2 in 111 weeks

s5 = (d2 - d2 (1 + r)^-n3)/r = 79292.149

future value remaining in week 1508

s6 = s4 - s5 = 464.894

future value remaining in week 1619 (note this matches the amortisation table)

s7 = s6 (1 + r)^n3 = 563.2699

interest from week 1619 to week 1620

i3 = s7 r = 0.97489

interest paid with extra payments

i2 = m d + n3 d2 + s7 + i3 - s =  295703.950

interest saving

i1 - i2 = 37925.455

Rounded to the dollar, 37925.

Addendum re. zero interest rate

When the interest rate is zero the standard loan equation becomes

enter image description here

∴ d = s/n

and the equation for a loan with extra payments becomes

enter image description here

∴ n = (s + d2 m - d m)/d2

Rerunning the calculation as described above

n = 40*52
s = 123500
r = 0
d = s/n = 59.375

i1 = n d - s = 0
d2 = d + 566
m = 1508
n = (s + d2 m - d m)/d2 = 1562.307

n2 = 1562
s2 = d m = 89537.5
s3 = s - s2 = 33962.5
s4 = s3 (1 + r)^m = 33962.5
n3 = 1562 - 1508 = 54
s5 = d2 n3 = 33770.25
s6 = s4 - s5 = 192.25
s7 = s6 (1 + r)^n3 = 192.25
i3 = s7 r = 0
i2 = m d + n3 d2 + s7 + i3 - s = 0
i1 - i2 = 0
  • In your amortization table the balance remaining for week 1508 should be balance for week 1507 * (1 + r) - d = 79838.63 (1 + r) - d = 79757.04 but the balance for the next week, 1509 should be balance for week 1508 * (1 + r) - d2 = 79757.04 (1 + r) - d2 = 79109.31. The balance for week 0 is 123500 and the balance for week 1619 is 563.27. – Chris Degnen Sep 5 '17 at 11:49
  • week 0 balance = 123500, week 1 balance = week 0 balance * (1 + r) - d, week 2 balance = week 1 balance * (1 + r) - d and so on up to week 1508 where d changes to d2. – Chris Degnen Sep 5 '17 at 13:24
  • When I use this it goes in minus – Vasim Shaikh Sep 7 '17 at 17:19
  • @VasimVanzara It is all shown in the amortisation table. The balance remaining is positive until week 1619, then it is paid down to zero with a reduced final repayment of 564.24. – Chris Degnen Sep 7 '17 at 19:20
  • I have accept your answer as well as bounty also added after 1 day to your account – Vasim Shaikh Sep 7 '17 at 22:58

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.