PPMT in Excel: Calculate the Principal Portion of a Periodic Payment

=PPMT(rate, per, nper, pv, [fv], [type])

rate is interest per period. per selects the period, from one through nper, the total term. pv is present value. fv is an optional terminal cash flow, default zero. type is zero or omitted for end payments and one for beginning payments.

The calculation assumes periodic constant payments and a constant interest rate. A positive present receipt is usually balanced by negative repayment components. PPMT therefore reports a negative principal payment in the borrowing example below.

Do not confuse principal paid in one period, cumulative principal paid and principal remaining. They are separate quantities with different formulas and, depending on display convention, different signs.

Enter 1%, 12 and 1000 in B2:B4. Use =PPMT(B2,1,B3,B4,0,0) in B6. The independent target is −78.8487886783.

The corresponding PMT is −88.8487886783, while IPMT is −10. Their relationship is PMT=PPMT+IPMT, so principal equals the full signed payment minus the signed interest component.

The remaining balance after period one is 921.1512113217, obtained by adding the negative principal component to the opening 1000. Subtracting that negative component would incorrectly increase the balance.

Put periods one through twelve in D2:D13. Enter =PPMT($B$2,D2,$B$3,$B$4,0,0) in E2 and fill down. Keep the input cells fixed with dollar signs while allowing the period cell to change.

The independent period-two principal target is −79.6372765651; period twelve targets −87.9690977013. Under the stated positive-rate model, the principal payment magnitude grows as the interest portion shrinks.

Summing all twelve full-precision principal components independently gives −1000, fully offsetting the initial receipt. The cumulative balance at each row is the initial amount plus the accumulated principal components, not plus the accumulated full payments.

Add an IPMT column and a constant PMT column using exactly the same parameters. For each row, subtract PPMT+IPMT from PMT. The mathematical residual is zero.

Across the first example, total outgoing payments are 1066.18546414 and total interest is 66.18546414. Signed principal sums to −1000, while signed interest sums to approximately −66.18546414. These totals explain the full repayment amount without treating interest as principal reduction.

A rounded schedule may show small residuals. Rounding every component to cents is a distinct model from summing unrounded formula values and rounding only the final displayed total.

With type one, the first payment occurs at time zero. The independent first interest portion is zero, so =PPMT(1%,1,12,1000,0,1) equals the full beginning payment, approximately −87.9690977013.

The next principal portion independently targets −78.8487886783, with interest −9.1203090230. These combine to the same beginning-payment amount. Use type one consistently for PMT, IPMT and PPMT; changing only one formula creates a timing mismatch.

Microsoft’s first example =PPMT(10%/12,1,2*12,2000) displays approximately −75.62. Its two-year term has 24 monthly periods. The annual rate and years cannot be passed unchanged into a monthly formula.

At zero rate, =PPMT(0,1,12,1200,0,0) has mathematical target −100. The whole regular payment is principal because the interest component is zero. A nonzero terminal balance changes how much principal the regular payments repay and must be included in the reconciliation.

Microsoft specifies per from one through nper. Use a valid whole-number period index and consistent periodic units. The dedicated source does not enumerate every error code or fractional-period behavior; no native error display is asserted here.

Check whether a displayed negative value is simply the correct outgoing-cash sign. Do not apply ABS throughout a model without preserving its accounting meaning. PPMT evaluates a fixed-rate, fixed-payment annuity; it does not read extra payments, fees or changed rates from elsewhere in a workbook.

• 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 PPMT 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.