Loan - Extra payment benefit formula

  • Thread starter Thread starter D2
  • Start date Start date
D

D2

Hi,

I have the following criteria:
Outstanding principal: 616, 000/=
Interest Rate: 13.25%
Remaining tenure: 22 months
Monthly Payment: 31, 672/=

Lets say I increase my monthly payment by 2,000/= then what would the
benefit (savings in interest and decrease in payment cycle). I know
there are some web based tools that will allow me to calculate this.

I'm looking for a formula to perform the above calculation. Any help
would be appreciated.

Thanks!
 
I have the following criteria:
Outstanding principal: 616, 000
Interest Rate: 13.25%
Remaining tenure: 22 months
Monthly Payment: 31, 672

Lets say I increase my monthly payment by 2,000
then what would the benefit (savings in interest
and decrease in payment cycle).
Click to expand...

The Analysis ToolPak has a function, CUMIPMT, which might be helpful
in making the computation. But I would not trust it because there is
sufficient room for interpretation. (Also, CUMIPMT does not permit
you to control the payment amount.) I would prefer to do the
computations myself as follows.

Suppose B1 contains the loan amount (616000), B2 contains the annual
interest rate (13.25%), C2 contains the monthly interest rate
(=B2/12), and B3 contains the monthly payment (31672). Then we can
compute the following:

B4: Number of payments
=ROUNDUP(NPER($C$2,B3,-$B$1),0)

B5: Remaining balance after B4-1 payments
=FV($C$2,B4-1,B3,-$B$1)

B6: Last payment
=ROUND(B5*(1+$C$2),2)

B7: Total Interest
=ROUND(B3*(B4-1)+B6-$B$1,2)

Set C3 to =B3+2000 and copy B4:B7 to C4:C7. The difference in
interest can then be computed by =C7-B7.

Note: You might notice that B4 is computed to be 23, not 22. You
will also notice that B5 is very small. Presumably the lender made a
subjective decision to increase the last payment by B5. You can
accommodate this by subtracting 1 in B4. But do that only after you
copy B4 to C4, since C5 is sufficiently large to warrant another
payment, IMHO. Alternatively, leave things as is. The difference is
very small. (You should be able to use the paradigm above to compute
that difference yourself.)
 
then what would the benefit (savings in interest ...

Hi. Looks like you save about $5,324.65 in interest.

? InterestSavings(616000, 0.1325 / 12, 22, 2000)
5324.65005894714

The basic math is a real mess, so I was surprised it reduces to the
following...

Function InterestSavings(s, r, yr, ep)
'/= = = = = = = = = = = = = = = = = = =
'/ s = Loan Amount, r = Interest Rate
'/ Yr = Loan Period, ep = Extra Payment
'/= = = = = = = = = = = = = = = = = = =

Dim k, x, t

k = 1 + r

x = Log((k ^ yr * (ep + r * s) - ep) / _
(ep * (k ^ yr - 1) + r * s)) / Log(k)

x = Int(x)

t = (ep * (k ^ yr - 1) * (k ^ (x + 1) - r * x - r - 1) + _
r * s * (k ^ (x + 1) - k ^ yr * (r * (x - yr + 1) + 1)))

InterestSavings = t / (r * (k ^ yr - 1))

End Function


I assume your Payment of 31672 is a typo instead of the expected 31692.
= = =
HTH :>)
Dana DeLouis
 
Back
Top