Determining Monthly Payments
Problem
You're borrowing money and want to figure out what your monthly payments will be, given the principal amount and interest rate.
Solution
Use the PMT function.
Discussion
Let's say you're borrowing $25,000 and the term of the loan is five years. Payments are due at the end of each month and the annual interest rate is 3.9%. What are your required monthly payments?
PMT can figure this out for you. The syntax for PMT is =PMT(rate, nper, pv, fv, type), where rate is the interest rate per period, nper is the number of payment periods, pmt is the payment per period, pv is the present value, fv is the future value, and type is an indicator of the payment type (0 for payments at the end of each pay period or 1 for payments at the beginning of each pay period).
For this example, the formula =PMT(0.039/12, 12*5, 25000, 0, 0) returns -$459.29, which means you'd have to pay $459.29 per month to cover principal and interest.
See Also
Excel includes a loan amortization template that's very handy when investigating loan options. Select File