RyanH said:
I am trying to calculate the interest I would save if I were to pay an
additional amount toward the prinicple of a loan, a certain amount of times a
year, starting at a particular month in the loan term. For example,
Loan Amount = $100,000
Interest Rate = 6.375%
Loan Start Date = 12/1/03
Additional Payment = $100
Number of Times Per Year = 12
Add. Payment Start Date = 12/1/08
First, let i denote the effective monthly interest rate 0.6375/12, or
0.0053125.
The interest paid in any regular loan payment is always equal to
effective periodic interest rate times the remaining balance. If your
loan above were originally to be repaid over 30 years, then the
regular loan payment is given by
PMT(i,360,-100000)
or 623.87. The interest portion of the first payment is given by
IPMT(i,1,360,-100000) [this requires the Analysis ToolPak be
installed]
or 531.25, which also equals 100000*i. When you make additional
principal payments, you decrease the principal balance remaining after
the payment, so the interest portion of the next regular payment is
less.
Anyway, the starting point would be to determine the principal balance
after the 11/1/2008 lone payment, which is the 60th payment, which is
given by
=FV(i,60,PMT(i,360,-100000),-100000)
or 93475.24. If you were to pay 100 more every month after that until
the loan were fully paid off, the number of subsequent payments is
given by
NPER(i,PMT(i,360,-100000)+100,-FV(i,60,PMT(i,360,-100000),-100000))
or 218.633703018029, which should be interpreted to mean 218 payments
of 723.87 and one final payment of less than that. The principal
balance after the first 60 payments of 623.87 and the next 218
payments of 723.87 is given by
FV(i,218,PMT(0.06375/12,360,-100000)+100,-FV(i,60,PMT
(0.06375/12,360,-100000),-100000))
or 456.74. That needs to be grossed up for interest to give the final
payment of 456.74*(1+i) = 459.16.
If you had stuck to the original loan payments of 360 monthly payments
of 623.87, your payments would total to 360*623.87 = 224593.16. 100000
of that would be principal, so the total interest paid would be
124593.16. The alternative of 60 payments of 623.87, 218 payments of
723.87 and a final payment of 459.16 would total to 195695.00. Again,
100000 of that would be principal, so the total interest paid would be
95695.00. The nominal interest savings would therefore be 28898.16 and
you would have paid off the loan in 23 years and 3 months ratherr than
30 years.
