Loan calculator with extra payments
Bernie,
Thank you! I'll try it.
--
Sue W.
"Bernie Deitrick" wrote:
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
"jjbud44" wrote in message
...
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?
--
Sue W.
|