Loan calculator with extra payments

[Guest]

using the Loan Calculator template in Excel, I am trying to calculate loan
payments with optional extra payments, over 5 years. However, every time I
input the optional extra payments, Excel shortens the loan term instead of
reducing the monthly payment amount (which is what I want).

Any suggestions?

 

[Bernie Deitrick]

Sue,

Make your own loan calculator: for this example, $100,000 30 year mortgage at 6%.

In row 1, entere these headers in cols A to G
Pmt #
Note Amount (+)
Term, Months
Payment (-)
Annual Interest Rate
Principal
Extra Payment

Then in row 2, cols A to F, enter
=ROW()-ROW($A$1)
100000
360
=PMT(E2/12,C2,B2)
0.06
=D2+B2*E2/12


In row 3, cols A to F
=ROW()-ROW($A$1)
=B2+F2-G2
=C2-1
=PMT(E3/12,C3,B3)
=E2
=D3+B3*E3/12

Copy row 3 down for 358 rows, and enter any principal payments into col G (as a positive number).

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,
Thank you! I'll try it.
--
Sue W.

Sue,

Make your own loan calculator: for this example, $100,000 30 year mortgage at 6%.

In row 1, entere these headers in cols A to G
Pmt #
Note Amount (+)
Term, Months
Payment (-)
Annual Interest Rate
Principal
Extra Payment

Then in row 2, cols A to F, enter
=ROW()-ROW($A$1)
100000
360
=PMT(E2/12,C2,B2)
0.06
=D2+B2*E2/12


In row 3, cols A to F
=ROW()-ROW($A$1)
=B2+F2-G2
=C2-1
=PMT(E3/12,C3,B3)
=E2
=D3+B3*E3/12

Copy row 3 down for 358 rows, and enter any principal payments into col G (as a positive number).

HTH,
Bernie
MS Excel MVP