PMT calculates a fixed payment per period. Use it for loan repayments or regular savings deposits, with the interest rate and payment count expressed in matching units.
PMT function syntax
=PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])- rate: interest per payment period. Divide a nominal annual rate by 12 for monthly payments.
- number_of_periods: total payments, such as 5*12 for five years of monthly payments.
- present_value: amount received now; future_value: ending cash flow, default 0.
- end_or_beginning: 0 for end-of-period payments (default), 1 for beginning. Enter 0 for future_value when supplying timing only.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Input | Value |
|---|---|
| Loan amount | 20000 |
| Annual rate | 0.06 |
| Years | 5 |
Calculate a monthly loan payment
Enter this formula in A9. B3/12 supplies the monthly rate, and B4*12 supplies 60 payments. The loan amount is positive because it is received now.
=PMT(B3/12,B4*12,B2)Result (rounded for display): -386.656031.
PPMT isolates the principal portion of a payment. CUMIPMT totals interest across a payment window.
Compare a shorter repayment term
Keep the same 20,000 loan and 6% annual rate, but repay it over three years. The larger monthly amount clears the balance sooner.
=PMT(B3/12,3*12,B2)Result (rounded for display): -608.438749.
Find the monthly deposit for a savings goal
To accumulate 10,000 from zero over five years, assume a constant 4% nominal annual return compounded monthly. The target goes in future_value; the deposits are negative.
=PMT(4%/12,5*12,0,10000)Result (rounded for display): -150.831887.
Move payments to the beginning of each month
The final 1 switches to beginning-of-period payments. The intervening 0 specifies that no loan balance remains at the end.
=PMT(B3/12,B4*12,B2,0,1)Result (rounded for display): -384.732369.
Calculate payments when the interest rate is zero
With no interest and no ending balance, PMT divides the amount by the number of payments. A 12,000 loan over 24 periods requires 500 per period.
=PMT(0,24,12000)Result: -500.
Leave a balance to repay at the end
For the same loan with a 5,000 balloon balance, use -5000 as future_value. That final outgoing balance is separate from the regular payment.
=PMT(B3/12,B4*12,B2,-5000)Result (rounded for display): -314.992023.
Other Google Sheets articles you may also like