PMT in Excel: Calculate a Constant Payment and Audit the Balance

=PMT(rate, nper, pv, [fv], [type])

rate, nper and pv are required: periodic interest rate, number of payments and present value. fv defaults to zero and is the target terminal cash flow. type is zero or omitted for end-of-period payments, one for beginning-of-period payments.

The function assumes a constant interest rate and constant payments. Microsoft states that the returned payment includes principal and interest but excludes taxes, reserve payments and fees. It is therefore not automatically the complete amount a person would owe under a real contract.

Use one cash-flow perspective throughout. For a borrower receiving 1000 now, pv is positive and repayments are negative. A depositor saving toward a future receipt can instead use pv=0 and positive fv, producing a negative periodic deposit.

Put 1%, 12 and 1000 in B2:B4. In B6 enter =PMT(B2,B3,B4,0,0). Its independent target is −88.8487886783.

Start a separate balance column at 1000. For each of twelve periods, calculate positive interest as opening balance times 1%, then set closing balance to opening balance plus interest plus the negative B6 payment. The last balance mathematically approaches zero.

In the first period, interest is 10 and principal repaid is 78.84878868. The closing balance is 921.15121132. Keep the unrounded payment in the schedule if comparing it to the mathematical PMT model.

=PMT(1%,12,1000,0,1) has independent target −87.96909770. Earlier payments reduce the required magnitude because each is paid one period sooner.

For this no-terminal-balance case, the beginning payment equals the end payment divided by 1.01. In the corresponding recurrence, apply the payment first and then interest. A payment at time zero is not charged a preceding period of interest.

Do not combine type-one PMT with a type-zero audit schedule. A timing mismatch can produce a nonzero ending balance even when each individual formula is correct for its own assumptions.

With the first example’s full-precision end payment, total outgoing payments are 1066.18546414, calculated as =-B6*B3. Subtract the original 1000 receipt to obtain modeled total interest of 66.18546414.

That subtraction assumes no other fees, cash flows or remaining balance. If fv is nonzero, it must be included in the reconciliation rather than assuming all principal has been extinguished by the regular payments.

As a terminal-balance illustration, =PMT(1%,12,1000,-200,0) targets −73.07903094. The negative terminal 200 represents an additional outgoing amount at the end; the regular payments alone leave that balance to settle.

=PMT(6%/12,18*12,0,50000,0) has independent target −129.08116087 per month, matching the magnitude of Microsoft’s example.

The source table labels 18 as months but its formula multiplies by twelve and its description says eighteen years. This guide follows the explicit formula and eighteen-year interpretation: 216 monthly payments. Using eighteen periods would be a different model.

At zero rate, =PMT(0,12,1200,0,0) has independent target −100. Timing makes no difference when no interest accrues.

Validate a positive period count and consistent units. A 12% nominal annual rate with monthly periods is 12%/12; twelve yearly periods use 12% directly. Effective annual rates require a different conversion.

Do not use ABS to conceal a cash-flow sign error. Inspect the initial receipt, regular payments and terminal cash flow together. The dedicated reference does not enumerate all invalid-input error codes or unusual-rate behavior, and no native Excel errors were executed here.

Real schedules can round each payment or each interest charge to cents, causing a final adjustment. State that rounding policy rather than claiming a full-precision annuity payment is the exact contractual payment.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft PMT reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.