Need to calculate loan payments, mortgage installments, or regular investment contributions in Excel? The PMT function offers a reliable way to determine periodic payment amounts for loans and investments. This guide walks you through using PMT in Excel, from basic calculations to advanced payment scenarios.

Calculate Monthly Loan Payments with PMT Function

Let’s start with the fundamental process of calculating loan payments using Excel’s PMT function.

Setting Up Your Payment Calculation

1. Open Excel and select a cell for your payment result
2. Type the PMT formula structure:

=PMT(rate, nper, pv)

1. Replace the parameters with your loan values:

• rate: Interest rate per period
• nper: Total number of payments
• pv: Principal amount (loan amount)

Example: For a $10,000 loan at 6% annual interest for 3 years:

Parameter	Value	Explanation
Rate	0.06/12	6% annual rate divided by 12 for monthly
Nper	36	3 years × 12 months
PV	10000	Loan amount

Complete formula:

=PMT(0.06/12, 36, 10000)

Calculate Monthly Mortgage Payments

Mortgage calculations require special attention to rate conversion and down payment considerations.

Steps for Mortgage Payment Calculation

1. Calculate the loan amount after down payment
2. Convert annual interest rate to monthly
3. Determine total payment periods

Example: $300,000 house, 20% down, 30-year fixed at 4.5% APR

Component	Calculation	Result
Loan Amount	$300,000 – (300,000 × 0.20)	$240,000
Monthly Rate	0.045 / 12	0.00375
Payment Periods	30 years × 12	360

Formula:

=PMT(0.045/12, 360, 240000)

Create a Car Loan Payment Schedule

Car loans involve additional considerations like trade-in value and sales tax.

Car Loan Calculation Process

1. Calculate the net purchase price after trade-in
2. Add applicable sales tax
3. Determine the final loan amount
4. Apply the PMT function

Example: $25,000 car, $5,000 trade-in, 6% sales tax, 5-year term at 4.9% APR

Step	Calculation	Amount
Net Price	$25,000 – $5,000 Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule. Get Started	$20,000
With Tax	$20,000 × 1.06	$21,200
Monthly Payment	=PMT(0.049/12, 60, 21200)	Result

PMT Function Formula Components

Understanding each component of the PMT function ensures accurate calculations:

Required Parameters

• Rate: Interest rate per period
• Nper: Total number of payment periods
• PV: Present value (principal amount)

Optional Parameters

• [FV]: Future value (default is 0)
• [Type]: Payment timing (0 = end of period, 1 = beginning)

Real-World PMT Applications

The PMT function extends beyond basic loan calculations:

Business Equipment Financing

• Calculate lease payments
• Compare purchase vs. lease options
• Factor in residual values

Investment Planning

• Determine retirement contribution amounts
• Calculate savings goals
• Plan education fund contributions

Common PMT Calculations and Adjustments

Payment Frequency Conversions

Payment Type	Rate Adjustment	Period Adjustment
Weekly	Annual rate ÷ 52	Years × 52
Bi-weekly	Annual rate ÷ 26	Years × 26
Monthly	Annual rate ÷ 12	Years × 12
Quarterly	Annual rate ÷ 4	Years × 4

Next Steps

Now that you understand how to use the PMT function, try creating different payment scenarios in Excel. Experiment with various loan amounts, interest rates, and payment frequencies to build your confidence with payment calculations.

Need to connect your payment calculations to live data sources? Try Coefficient to automatically sync real-time financial data into your spreadsheets and create dynamic payment models that update automatically.