Calculates the payment for a loan based on periodic, constant payments and a constant interest rate. Essential for mortgage calculations, loan payments, lease payments, and determining periodic payment amounts needed to pay off loans or reach savings goals.
Master the fundamentals of Excel PMT function
The PMT (Payment) function calculates the periodic payment amount required to pay off a loan or achieve a savings goal, based on the loan principal, interest rate, and number of payment periods. PMT uses the time value of money principle to determine equal periodic payments that will fully amortize a loan or accumulate to a target future value. This function is fundamental to mortgage calculations, loan structuring, lease payments, and savings planning, enabling borrowers and investors to understand payment requirements under different scenarios.
Uses the formula: PMT = PV × [r(1+r)^n] / [(1+r)^n - 1] where r=rate, n=periods, PV=present value. This ensures loan is fully paid over n periods.
Returns the same payment amount each period that fully amortizes the loan. Early payments are mostly interest, later payments are mostly principal.
Returns negative value representing cash outflow (money you pay). Use ABS() to display as positive, or enter PV as negative to get positive result.
Rate and nper must match payment frequency. Monthly payments require monthly rate (annual/12) and monthly periods (years×12). Mismatch causes incorrect results.
Type parameter (0=end, 1=beginning) affects payment amount. Beginning-of-period payments are slightly lower due to less interest accumulation.
Payment amount is highly sensitive to loan term. Longer terms significantly reduce monthly payment but increase total interest paid.
Function-specific parameters
Function-specific return type
Calculate monthly mortgage payments for home purchases and refinancing
Determine payment amounts for auto loans, personal loans, and credit lines
Calculate periodic lease payments for equipment and property leases
Determine required periodic payments to reach savings or investment targets
Calculate payments needed to pay off debts within specific timeframes
Compare payment amounts across different loan terms and interest rates
Exact matching required
Returns numeric position
Handles missing text gracefully
=PMT(rate, nper, pv, fv, type)The interest rate per period. Enter as decimal (5% = 0.05). Must match payment period frequency (monthly rate = annual rate/12 for monthly payments).
The total number of payment periods. Must be positive. Must match rate period (360 months for 30-year monthly payments, 30 years for annual payments).
The present value, or total amount of the loan. Typically entered as positive value. For loans, this is the principal amount borrowed.
The future value, or cash balance after the last payment. Default is 0 (loan fully paid). For balloon loans, enter remaining balance. For savings goals, enter target amount.
When payments are due: 0 = end of period (default), 1 = beginning of period. Beginning payments slightly reduce payment amount.
The payment amount per period (negative value represents cash outflow)
Description: Calculates the periodic payment amount for a loan or investment
Calculate monthly mortgage payment for home loan
Calculates monthly payment of $1,073.64 for a $200,000 mortgage at 5% APR over 30 years (360 months). Result is negative (cash outflow).
Using PMT function in VBA through WorksheetFunction
Sub PMTExample()
Dim result As Double
Dim monthlyRate As Double, months As Integer, loanAmount As Double
monthlyRate = 0.05 / 12
months = 360
loanAmount = 200000
result = Application.WorksheetFunction.PMT(monthlyRate, months, loanAmount)
Range("A1").Value = Abs(result)
Range("A1").NumberFormat = "$#,##0.00"
MsgBox "Monthly Payment: quot; & Format(Abs(result), "#,##0.00")
End Sub
' Calculate payment for different loan scenarios
Sub CalculateLoanPayments()
Dim loanAmount As Double, annualRate As Double, years As Integer
Dim monthlyPayment As Double
loanAmount = Range("B1").Value
annualRate = Range("B2").Value
years = Range("B3").Value
monthlyPayment = Application.WorksheetFunction.PMT(annualRate / 12, years * 12, loanAmount)
Range("B4").Value = Abs(monthlyPayment)
Range("B4").NumberFormat = "$#,##0.00"
End Sub
' PMT with balloon payment
Sub PMTWithBalloon()
Dim result As Double
result = Application.WorksheetFunction.PMT(0.05/12, 360, 200000, 10000)
MsgBox "Payment with balloon: quot; & Format(Abs(result), "#,##0.00")
End Sub
' Compare payments for different terms
Sub CompareLoanTerms()
Dim loanAmount As Double, rate As Double
Dim payment15 As Double, payment30 As Double
loanAmount = 200000
rate = 0.05 / 12
payment15 = Application.WorksheetFunction.PMT(rate, 180, loanAmount)
payment30 = Application.WorksheetFunction.PMT(rate, 360, loanAmount)
Range("A1").Value = "15 Year: quot; & Format(Abs(payment15), "#,##0.00")
Range("A2").Value = "30 Year: quot; & Format(Abs(payment30), "#,##0.00")
End Sub
' Calculate savings payment
Sub CalculateSavingsPayment()
Dim goal As Double, rate As Double, months As Integer
Dim payment As Double
goal = 100000
rate = 0.06 / 12
months = 240
payment = Application.WorksheetFunction.PMT(rate, months, 0, goal)
MsgBox "Required monthly savings: quot; & Format(Abs(payment), "#,##0.00")
End SubCalculate monthly mortgage payment amounts
Determine monthly car loan payments
Calculate payment to reach savings target
Calculate periodic lease payment amounts
Determine payment to pay off debt in specific period
Compare payments across different loan scenarios
PMT returns negative value unexpectedly
=PMT(0.05/12, 360, 200000)Solution: This is normal and correct - PMT returns negative values because payments are cash outflows. Use ABS() to display as positive: =ABS(PMT(...)). Alternatively, enter PV as negative to get positive result: =PMT(rate, nper, -loan_amount).
PMT results don't match expected loan calculator values
=PMT(0.05/12, 360, 200000)Solution: Verify period consistency: monthly payments require monthly rate (annual/12) and monthly periods (years×12). Check rate is decimal (5% = 0.05, not 5). Verify loan amount (PV) sign and value are correct.
Payment amounts seem wrong - period inconsistency
=PMT(0.05/12, 360, 200000)Solution: Rate and nper must match payment frequency. For $200,000 mortgage: rate=0.05/12 (monthly), nper=360 (30 years × 12). For annual payments: rate=0.05, nper=30. Always convert consistently.
PMT returns unrealistic payment amounts
=PMT(0.05/12, 360, 200000)Solution: Check: 1) Rate format (decimal, not percentage), 2) Period count (years to months conversion), 3) Loan amount (PV value), 4) No sign errors. Compare with online loan calculator to validate.
Adding future value doesn't change payment as expected
=PMT(0.05/12, 360, 200000, 10000)Solution: For balloon loans, enter remaining balance as fv parameter. Payment will increase. Verify fv sign: typically positive for remaining balance. Check fv is included in formula.
PMT returns error values
=PMT(A1, B1, C1)Solution: #NUM! indicates invalid numeric input (negative nper, invalid rate). #VALUE! means non-numeric input. Verify all inputs are numbers, nper is positive, rate is valid decimal. Check cell references contain numeric values.