rate - Required. Interest rate per period. For monthly payments, divide annual rate by 12.
nper - Required. Total number of payment periods. Multiply years by 12 for monthly payments.
pv - Required. Present value - the loan amount. Use negative for money borrowed.
fv - Optional. Optional. Future value - balloon payment at end. Default is 0.
type - Optional. Optional. 0 = payment at end of period (default), 1 = payment at beginning.
| A | B | C | |
|---|---|---|---|
| 1 | Input | Value | Result |
| 2 | Annual Rate | 6% | |
| 3 | Years | 30 | |
| 4 | Loan Amount | $200,000 | |
| 5 | Monthly Payment: | =PMT(B2/12, B3*12, -B4) $1,199.10 |
$200K loan, 5% annual, 30 years
$1,073.64/month
$30K car, 6% APR, 5 years
$579.98/month
Rate in B2, Years in B3, Loan in B4
Flexible loan calculator
$150K loan, $50K balloon at end
Lower monthly payment
Calculate monthly payment for a home mortgage with different down payment scenarios using the PMT function in Excel. Perfect for real estate buyers comparing loan options, mortgage brokers creating payment projections, and financial planners helping clients understand homeownership costs. The Excel PMT formula accounts for principal, interest, and loan term to provide accurate monthly payment amounts for fixed-rate mortgages. Essential for home affordability analysis and comparing 15-year versus 30-year mortgage terms.
| A | B | C | |
|---|---|---|---|
| 1 | Input | Value | Result |
| 2 | Home Price | $350,000 | |
| 3 | Down Payment | $70,000 | |
| 4 | Loan Amount | =B2-B3 | $280,000 |
| 5 | Annual Rate | 6.5% | |
| 6 | Years | 30 | |
| 7 | Monthly Payment: | =PMT(B5/12, B6*12, -B4) $1,769.52 |
Compare monthly payments and total interest costs across different loan terms to make informed car financing decisions with the Excel PMT function. Critical for auto buyers evaluating trade-offs between lower monthly payments and total interest paid. The PMT formula in Excel helps car buyers understand how loan term affects both monthly budget impact and long-term financing costs. Finance managers use this to illustrate the true cost of extended financing and help customers choose optimal loan terms for their budget and financial goals.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan Term | Interest Rate | Monthly Payment | Total Paid |
| 2 | 3 Years | 5.5% | =PMT(B2/12,3*12,-25000) | $27,141 |
| 3 | 5 Years | 6.0% | =PMT(B3/12,5*12,-25000) | $28,999 |
| 4 | 7 Years | 6.5% | =PMT(B4/12,7*12,-25000) | =PMT(B4/12,7*12,-25000) $30,761 |
❌ The Problem:
✅ Solution:
=PMT(6%/12, 30*12, -200000)Always divide annual interest rate by 12 and multiply years by 12 when calculating monthly payments with the PMT function in Excel. The rate parameter expects the interest rate per payment period, not the annual rate.
❌ The Problem:
✅ Solution:
=PMT(6%/12, 360, -200000)Use negative loan amount (-200000) to represent money borrowed (cash outflow). The Excel PMT formula will return a positive payment amount, making the result easier to read and use in financial models.
❌ The Problem:
✅ Solution:
=(PMT(6%/12, 30*12, -200000) * 360) - 200000Calculate total interest by multiplying monthly payment by number of periods, then subtracting the original loan amount. The PMT function in Excel only returns the periodic payment - understanding total interest cost is critical for comparing loan options and making informed borrowing decisions.
Master these PMT Function variants:
From basics to advanced - AI generates perfect formulas instantly.