The PMT Function Is More Annoying Than It Looks
You open your spreadsheet, type =PMT(), and expect it to hand you a clean monthly payment number. Half the time it works fine. The other half you get weird results because the timing assumption doesn't match reality. I learned this the hard way on a commercial lease model where the payments came due at the beginning of each period instead of the end. The output looked correct to three decimal places, but it was off by exactly one period of interest. That discrepancy turned into a five-figure variance when I scaled the model up across twelve months. PMT computes the fixed periodic payment for a loan or annuity assuming constant payments and a constant interest rate. The standard formula is P = r × PV / (1 - (1 + r)^-n), where r is the periodic rate, PV is the present value or principal, and n is the total number of periods. Every finance textbook presents this. What they usually don't emphasize enough is that the formula assumes payments happen at the end of each period unless you explicitly tell it otherwise with the type parameter. In Excel or Google Sheets, the function signature is PMT(rate, nper, pv, [fv], [type]). Rate is the interest per period. Nper is total periods. Pv is the present value, typically entered as a negative number if you want the payment to come out positive. Fv is optional and defaults to zero. Type is 0 for end-of-period or 1 for beginning-of-period. Skip these and Excel guesses, which is how mistakes happen.
Setting It Up Without Making Common Mistakes
Before you plug numbers in, convert everything to the same time unit. This is where most people stall out. If you have an annual interest rate of 6 percent and monthly payments, divide the rate by 12 to get 0.5 percent per period. Multiply the loan term in years by 12 for nper. Don't mix annual rates with monthly periods. The function won't warn you. It will just give you a wrong answer with complete confidence. Sign convention matters. If you borrow $100,000, enter pv as -100000 so the payment displays as a positive outflow. Flip the signs and you'll get a negative payment, which isn't wrong mathematically but confuses anyone reading the spreadsheet later. I've seen junior analysts spend twenty minutes debugging a PMT call only to realize the principal was positive instead of negative. They thought the formula was broken.
A Real Problem I Ran Into
Last year I built a model for a small business loan with irregular payment dates. The bank scheduled payments on the 15th of each month, but the disbursement date was the 3rd. That meant the first period was shorter than a full month. The standard PMT function can't handle this. It assumes equal intervals between every payment. I tried forcing it and got a payment that was about forty dollars too high per period because the function spread the interest over uniform buckets that didn't match the actual cash flow timeline. The workaround was to build a custom amortization schedule. I calculated the interest accrued during that short first period manually using the daily count between disbursement and the first payment date, then applied the standard annuity formula only to the remaining full periods. It took about twenty minutes to set up compared to the five minutes a naive PMT call would have taken. The difference in total interest paid across the life of the loan was roughly $340. Not dramatic on a small loan, but noticeable when the principal was in the millions.
Get the Full Details
Things PMT Can't Handle Well
Variable rates. If the interest rate changes over the life of the loan, PMT gives you nothing useful. You need a custom amortization schedule that recalculates the payment or the remaining balance at each rate change point. I've seen people try to approximate this by averaging the rates across the loan term. That approach introduces systematic bias. The error compounds differently depending on when rates change. Early rate increases hurt more because they hit a larger remaining balance. Fees baked into the principal. Some lenders roll origination fees into the loan amount. PMT will calculate the payment on the inflated principal, but the effective interest rate your borrower actually pays is higher than the stated rate. You can back into the true rate using the XIRR function on the actual cash flows, but PMT itself doesn't do this adjustment for you. Prepayments. If the borrower pays extra whenever they can, the standard PMT output becomes irrelevant quickly. The function locks in a payment amount and never adjusts. In practice, this means the model overstates the total interest cost if prepayments are significant. For a rough adjustment, some modelers reduce nper manually to reflect an accelerated payoff schedule, but this is approximate and doesn't capture the exact timing of irregular prepayments.
When PMT Works Exactly Right
Fixed-rate installment loans with regular payment dates and no fees or prepayments. Auto loans, mortgages with standard amortization, student loans. These are the scenarios the formula was designed for. A typical residential mortgage at 5.25 percent annual rate over thirty years on a $350,000 principal comes out to about $1,933 per month with PMT. The math checks out. The function delivers in seconds. Use it here without second-guessing. Always label your rate and period inputs clearly in adjacent cells so anyone reviewing the sheet knows what time unit they represent. Put the PMT formula in its own cell with a clear label rather than burying it in a block of numbers. Use the IPMT and PPMT functions alongside PMT to break each payment into interest and principal portions. This takes maybe thirty seconds extra and saves hours of confusion later when someone asks why the balance isn't dropping the way they expected. I check IPMT on the first payment of any new loan model as a quick sanity test. If the interest portion is more than half the payment in the early periods, something is wrong with the rate or period inputs. For loans with beginning-of-period payments, always set the type argument to 1. I've seen this skipped at least once a month on the forums I read. The resulting payment is slightly lower because each payment earns interest for one period less, but the difference adds up. On a $500,000 commercial loan at 7 percent over ten years with monthly payments, forgetting the type flag changes the monthly payment by about $24 and the total interest by roughly $2,880 over the full term.
If you need to handle irregular cash flows or compute the implicit rate, switch to the RATE or XIRR functions instead of fighting PMT. They solve a different part of the same problem but give you results that actually match the cash flow pattern. PMT is a tool, not a universal solution. Knowing its limits is the expensive part of learning it.
