Amortization Table Google Sheets: Formula and Example

An amortization table in Google Sheets shows how each loan payment is divided between interest and principal, and how the balance changes over time. Enter the loan amount, annual APR, and term to estimate the monthly payment and total interest; the schedule itself can then be built row by row in Sheets.

Formula

The fixed monthly payment is calculated using the standard level-payment loan formula:

M = P × r / [1 − (1 + r)−n]

• P is the starting principal, in dollars.
• APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
• r is the monthly rate, calculated as APR ÷ 1,200. For an 8% APR, r = 0.0066667 per month.
• n is the number of monthly payments, calculated as years × 12.
• M is the monthly payment in dollars.

The total-interest estimate shown here is M × n − P. The payment is rounded to cents before calculating that total. This convention matches a schedule that uses the same rounded payment each month, though a real loan’s final payment may be adjusted for rounding or lender-specific rules.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and a five-year term:

1. Convert APR to a monthly rate: r = 8 ÷ 1,200 = 0.0066666667.
2. Convert the term to payments: n = 5 × 12 = 60.
3. Substitute in the payment formula: M = 20,000 × 0.0066666667 ÷ [1 − (1.0066666667)−60].
4. The unrounded result is about $405.528; rounded to cents, the monthly payment is $405.53.
5. Total paid using that rounded payment is $405.53 × 60 = $24,331.80. Subtracting $20,000 gives estimated total interest of $4,331.80.

Assumptions

• The interest rate is fixed for the full term.
• Payments are made monthly, at the end of each monthly period.
• Interest is calculated monthly at APR ÷ 12, with no separate daily-interest adjustment.
• Fees, taxes, insurance, mortgage insurance, escrow, and other charges are not included.

Build the Amortization Table in Google Sheets

The calculator gives the payment and interest estimate. To create the detailed schedule, set up a sheet with columns for payment number, beginning balance, payment, interest, principal, and ending balance. Put the loan amount in a fixed input cell, the APR in another, and the term in years in a third. For example, assume those inputs are in B1, B2, and B3.

Use the following formulas in the first schedule row, assuming row 6 is payment one:

• Payment number: enter 1; in the next row use =A6+1.
• Beginning balance: in B6 use =$B$1; in B7 use =F6 and fill down.
• Payment: in C6 use =-PMT($B$2/12,$B$3*12,$B$1). The minus sign makes the result display as a positive payment when the principal is entered as a positive amount.
• Interest: in D6 use =B6*$B$2/12.
• Principal repaid: in E6 use =C6-D6.
• Ending balance: in F6 use =B6-E6.

Copy the second-row formulas down for the number of payments, which is the term in years multiplied by 12. Format money columns as currency and APR as a percentage. If APR is typed as 8%, Sheets stores it as 0.08, so the monthly-rate formula should use $B$2/12. If you type 8 as a plain number instead, use $B$2/1200. Do not mix these conventions.

How to Read Each Row

Each row represents one scheduled payment period. Interest is computed from the beginning balance and the periodic rate. The rest of the payment reduces principal, so the ending balance becomes the next period’s beginning balance. With a fixed rate and level payment, the interest portion generally falls as the balance declines, while the principal portion rises. The balance should approach zero at the end, not become substantially negative.

For example, in the first month of the default example, interest is about $133.33 ($20,000 × 0.08 ÷ 12). From a $405.53 payment, about $272.20 therefore goes to principal, leaving an ending balance near $19,727.80. Small differences can appear if the sheet retains extra decimal places internally while displayed values are rounded.

Common Spreadsheet Problems and Limits

• Wrong rate format: entering 8% and then dividing by 1,200 makes the rate far too small. For a percentage-formatted 8%, divide by 12; for the plain number 8, divide by 1,200.
• Sign confusion in PMT: spreadsheet payment functions use cash-flow signs. A positive principal commonly produces a negative PMT result; use a leading minus sign to display a positive payment.
• Rounding every component: displayed cents do not necessarily equal the full precision stored in cells. Decide whether to calculate with full precision or explicitly round monthly interest and principal, and keep the approach consistent.
• Last-row balance not zero: payment rounding, copied formulas, or an incorrect number of rows can leave a small remainder. A lender may adjust the final payment; do not treat a spreadsheet’s tiny residual as an exact payoff quote.

This schedule models a straightforward fixed-rate loan with equal monthly payments. It does not automatically model variable rates, irregular payment dates, extra payments, deferred interest, fees rolled into principal, or lender-specific accrual methods. For extra principal payments, add a separate input to principal repaid and prevent the ending balance from going below zero; the timing of extra payments affects interest.

Frequently Asked Questions

Does Google Sheets have an amortization schedule template?
Sheets may offer templates, but a schedule can also be built with ordinary formulas such as PMT and simple balance calculations. Check any template’s rate units, payment timing, and treatment of rounding before relying on its results.

What formula gives the monthly loan payment in Google Sheets?
For APR entered as a percentage-formatted value, use =-PMT(APR_cell/12, years_cell*12, amount_cell). If APR is entered as the number 8 to mean 8%, use =-PMT(APR_cell/1200, years_cell*12, amount_cell).

Why does the interest amount go down each month?
For a fixed-rate amortizing loan, interest is based on the remaining balance. As principal is repaid, the balance shrinks, so less interest accrues in later periods.

Can I include extra payments?
Yes. Add the extra amount to principal repaid for the applicable period, then use the reduced ending balance as the next beginning balance. Do not let total principal repayment exceed the balance due, and confirm how the lender applies extra payments.

Why does my final balance differ from the lender’s?
A spreadsheet is an estimate unless its inputs and calculation rules match the loan contract. Differences can come from payment dates, daily interest, rounding, fees, rate changes, or how the final payment is handled.

This result is an estimate for education, and the lender’s disclosure gives the exact figures.

Related

• Pell Grant Qualifying Income: What FAFSA Considers
• OHP Qualifying Income: Oregon Health Plan Limits
• Non-Qualifying Income: What It Means for Loan Approval

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2