Financial analysis
PMT function: calculate a fixed monthly loan payment
Convert an annual interest rate and term into matching monthly units inside PMT, interpret the negative cash-outflow sign, and test zero-interest financing.
Match the interest period to the payment frequency
A workshop finances a 12,000 compressor (B2) at a 6% annual rate (0.06 in B3) over 36 monthly instalments (B4). Passing 0.06 directly as rate would charge 6% per month; divide the annual rate by 12 so both rate (0.06/12 = 0.005) and nper (36) are monthly.
| Input | Cell | Value |
|---|---|---|
| Principal (pv) | B2 | 12000 |
| Annual rate | B3 | 0.06 |
| Monthly periods (nper) | B4 | 36 |
Why PMT returns a negative number
Financial functions follow cash-flow direction: receiving 12,000 today is positive (+12000), so paying the lender each month is negative. =ROUND(PMT(B3/12, B4, B2), 2) returns -365.06. Negate PMT (or pass -B2) when a budget table lists positive expense amounts.
=ROUND(PMT(B3/12,B4,B2),2)Total interest and the 0% promotional check
Across 36 months, total repayments equal 36 * 365.0632 = 13,142.28, or 1,142.28 in total interest.
Set B3 to 0 in the practice grid to verify zero-interest financing divides principal evenly: -12,000 / 36 = -333.33 per month.
=ROUND(-PMT(B3/12,B4,B2)*B4-B2,2)Assumptions in this example
This example uses a constant nominal annual interest rate divided by 12 and payments at the end of each month. Fees, taxes and changes in rates are excluded. Actual lender or savings terms can differ.
Try the example
Use the original values or change them to test your understanding.