How to use the PMT() function in Excel

Calculates the periodic payment amount (annuity) for a loan or investment based on constant payments and a constant interest rate. For loan repayment calculations, this represents the fixed payment amount that includes both principal and interest.

Syntax

PMT(Rate; Nper; Pv; [Fv]; [Type])

Arguments

Note: Either Pv or Fv must be specified.

Background

The PMT() function is part of a financial function family that includes:

• PV() (present value)
• FV() (future value)
• NPER() (number of periods)
• RATE() (interest rate)

These functions are interrelated through the financial equation:
[Financial equation graphic showing relationship between PV, FV, PMT, NPER, RATE]

Where:

• All values are compounded
• M represents the Type parameter (payment timing)
• The equation is solved for each respective function

Examples

1. Annuity Calculation (Retirement Planning)

A 60-year-old has $100,000 saved and wants monthly payments for 15 years at 4.5% annual interest.

Scenario A: Exhaust all capital

=PMT(4.5%/12, 15*12, -100000)

Result: $764.99 per month

Scenario B: Maintain $10,000 balance

=PMT(4.5%/12, 15*12, -100000, 10000)

Result: $725.99 per month

Note: Negative Pv indicates outgoing payment (capital invested)

2. Loan Repayment Calculation

$100,000 loan at 5.5% annual interest with 5-year term and $80,000 residual balance.

=PMT(5.5%/12, 5*12, 100000, -80000)

Result: $748.69 per month (negative indicates outgoing payment)

Key Differences:

• Savings calculations assume compound interest
• Mortgage loans typically use simple monthly interest (annual rate/12)

Important Notes

1. For loans, payments are negative (cash outflow)
2. Interest rates should match payment periods (annual rate/12 for monthly payments)
3. Type parameter significantly affects beginning/end of period calculations
4. Results are theoretical for accounts without true compound interest