Determining Monthly Payments


You're borrowing money and want to figure out what your monthly payments will be, given the principal amount and interest rate.


Use the PMT function.


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.

Using Excel

Getting Acquainted with Visual Basic for Applications

Collecting and Cleaning Up Data


Statistical Analysis

Time Series Analysis

Mathematical Functions

Curve Fitting and Regression

Solving Equations

Numerical Integration and Differentiation

Solving Ordinary Differential Equations

Solving Partial Differential Equations

Performing Optimization Analyses in Excel

Introduction to Financial Calculations


Excel Scientific and Engineering Cookbook
Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))
ISBN: 0596008791
EAN: 2147483647
Year: N/A
Pages: 206
Authors: David M Bourg © 2008-2020.
If you may any questions please contact us: