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 images/U2192.jpg border=0> New ... from the main menu bar to open the New Workbook Task Pane on the right side of the window. Under Templates, select "On My Computer ..." to open the Templates dialog box. Select the Loan Amortization template and press OK to open the template. Enter your loan parameters and the template will automatically compute monthly payments, along with principal and interest summaries over the term of the loan.

Категории