PPMT Function in Google Sheets: Formula, Examples & Troubleshooting

=PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])

The timing meanings and period limits are documented by Google. The reviewed text marks future value optional but does not state its omitted default. Every new PPMT formula here supplies all six arguments. Google PPMT reference.

1. In B10 enter =PPMT(B2/B3,B8,B4,B5,B6,B7).
2. Expected signed principal component: −471.847323.
3. In B11 enter =PMT(B2/B3,B4,B5,B6,B7) for the total payment, approximately −531.847323.
4. In B12 enter =B11-B10. Expected interest component: −60.
5. In B13 enter =B5+B10. The first closing balance is 11,528.152677.

The principal repayment is negative from the borrower’s cash-flow perspective. Adding that negative principal component reduces the positive balance. Subtracting it would increase the debt.

For this positive-rate, zero-target, end-payment model, calculate the monthly payment as:

=-B5*(B2/B3)*(1+B2/B3)^B4/((1+B2/B3)^B4-1)

Beginning with a 12,000 opening balance, interest for each period is the negative opening balance times 0.5%. Principal is payment minus interest. Add the signed principal to obtain the next balance.

The principal portion increases as interest falls, although the total payment is constant. The final balance is mathematically zero with unrounded payments; finite-precision arithmetic may leave a tiny residual.

One explicit six-argument Google example has annual rate 7.5% divided by 12, selected period 3, 24 total periods, principal 5,000, future value 0, and beginning timing 1. Its published principal component is −194.958888. A separate beginning-timing example includes a future value of 1,000. These support signed principal components for the specific combinations shown. Published PPMT example.

Google also publishes an end/default-timing amortization table with a negative principal input and positive payment components. Those are rounded displayed documentation amounts, not full engine precision or a second runtime test. The new example above uses explicit end timing and an independently calculated ledger. Published amortization table.

Enter period numbers 1 through 24 in D2:D25. In E2 enter =PPMT($B$2/$B$3,D2,$B$4,$B$5,$B$6,$B$7) and fill down.

=SUM(E2:E13) gives the independently expected first-12-period signed principal total −5,820.502084. Therefore =B5+SUM(E2:E13) gives the remaining balance 6,179.497916. Across all 24 periods, =SUM(E2:E25) should be approximately −12,000 in the unrounded model.

This separates reduction of principal from total money paid. Twelve total payments sum to approximately −6,382.167876; the difference is the first year’s interest, approximately −561.665793. Do not label the full payment sum “principal repaid.”

Use whole periods within the documented interval. A zero period or a period beyond the term does not describe the selected payment. Match the rate to the payment frequency; 24 monthly payments require a monthly rate, not a 6% rate per month.

If principal does not reconcile with the balance, first check payment signs, optional future value, timing, and rounding. A lender schedule that rounds every payment or accrual can differ from this unrounded model. Extra payments and changing rates are outside the constant-payment calculation.

This guide does not claim every beginning-timing first-period rule, arbitrary sign combination, zero/negative-rate behavior, coercion, or exact invalid-input error token. Those were not established by the reviewed examples.

PPMT is currently documented for Google Sheets. Its introduction date, earliest functioning version, and retirement history remain unestablished. No general parity with another spreadsheet application is claimed.

Related: SUM, ROUND, and IF.

References checked October 17, 2026: Google PPMT contract and published principal calculation.