Google Amortization Table: Calculate Loan Payments

A Google amortization table is a loan-payment schedule you can build in Google Sheets; this calculator estimates a fixed-rate loan’s monthly payment and total interest from its principal, APR, and term. Enter those values to see the payment and interest estimate, then use the schedule guidance below to create a month-by-month table in Sheets.

Formula

The calculator uses the standard fixed-payment amortization formula. It calculates a monthly payment that, under the stated assumptions, repays the starting balance over the selected number of monthly payments.

• Monthly payment: M = round[P × r ÷ (1 − (1 + r)⁻ⁿ), 2].
• Total interest: I = (M × n) − P.
• Monthly rate: r = APR ÷ 1,200.
• Number of payments: n = years × 12.

P is the loan principal in dollars, APR is the annual percentage rate entered as a percentage, M is the monthly payment in dollars, I is total interest in dollars, and r is the monthly rate as a decimal. The payment is rounded to cents before total interest is calculated, so the total reflects the displayed payment multiplied by the payment count. In a real account, payment rounding and the final payment may be handled differently.

Worked Example

Using the calculator’s default values—$20,000 principal, 8% APR, and a five-year term—the monthly rate is 8 ÷ 1,200 = 0.0066667, and the schedule has 5 × 12 = 60 payments.

1. Substitute the values: M = 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰].
2. Evaluate the formula and round the monthly payment to cents: M = $405.53.
3. Calculate total interest using the rounded payment: I = ($405.53 × 60) − $20,000 = $4,331.80.

The displayed figures are estimates from the formula and its rounding convention. They are not a lender’s payoff quote or an account statement.

Assumptions

• The interest rate stays fixed for the entire term.
• Payments are equal, made monthly, and occur at the end of each monthly period.
• Interest is modelled monthly at APR ÷ 12; the formula does not model daily interest accrual.
• Fees, taxes, insurance, PMI, escrow, late payments, and extra payments are not included.

Build a Google Sheets Amortization Table

Google Sheets can show how each payment is divided between interest and principal, along with the remaining balance. Start with the loan amount, annual APR, and term in separate cells. For example, place the principal in B1, APR in B2 as a percentage such as 8%, and the term in years in B3. Set up columns for payment number, beginning balance, payment, interest, principal paid, and ending balance.

1. In the first schedule row, use payment number 1 and link beginning balance to the principal cell.
2. Calculate monthly interest as beginning balance multiplied by the APR cell divided by 12.
3. Calculate principal paid as the monthly payment less that month’s interest.
4. Calculate ending balance as beginning balance less principal paid.
5. For the next row, increase the payment number by one and link its beginning balance to the prior row’s ending balance. Copy the formulas down for the number of monthly payments.

For example, if B1 holds principal, B2 holds APR, B3 holds years, and the first schedule row is row 7, a payment formula can be =-PMT($B$2/12,$B$3*12,$B$1). The minus sign makes the payment display as a positive amount under the usual cash-flow sign convention. In row 7, if beginning balance is in B7 and payment is in C7, the interest formula is =B7*$B$2/12, principal paid is =C7-D7, and ending balance is =B7-E7. Adapt cell references to match your sheet. Use a final-row adjustment if rounding leaves a small residual balance.

Reading the Schedule and Common Errors

At the beginning of a standard amortizing loan, the balance is higher, so the interest portion of a level payment is generally larger. As principal is repaid, the balance falls and later payments allocate more to principal. The payment amount can remain level even while its two components change.

• Entering APR as a whole number or decimal incorrectly: If the cell contains 8%, use the monthly-rate expression APR/12. If it contains 8 rather than 8%, divide by 1,200 instead.
• Mixing years and months: A five-year term means 60 monthly payments, not five.
• Rounding every intermediate value: Displaying cents is useful, but rounding the balance or interest formula at each step can create a small discrepancy. Keep full precision in formulas and format cells as currency.
• Assuming every loan has fixed monthly payments: Variable rates, irregular payment dates, fees added to the balance, and payment holidays require a different model.
• Expecting the last balance to equal exactly zero: Payment rounding can leave a few cents. A lender may adjust the final amount.

Extra payments also change the schedule. To model them, include the extra amount as a separate input in each relevant month and reduce principal by the regular principal portion plus that extra amount, subject to the account’s actual payment rules. Do not simply keep the original payoff date and total-interest result after changing the payment pattern.

What the Calculator Does Not Show

This calculator returns a level monthly payment and a simple total-interest estimate. It does not generate the full row-by-row schedule in the calculator block; the steps above show how to construct one in Sheets. It also does not calculate APR from fees, compare loan offers, or account for rate changes. Check the loan documents or ask the lender for the exact payment schedule, fee treatment, and payment allocation. For an adjustable-rate loan, later payments may differ from this fixed-rate estimate.

Frequently Asked Questions

Does Google Sheets have an amortization schedule template?
Sheets may offer templates through its template gallery, and you can also create a schedule with formulas. Template availability can change, so verify the formulas and assumptions rather than relying on the title alone.

What formula gives the monthly payment in Google Sheets?
The PMT function is commonly used: =-PMT(APR/12, years*12, principal). For example, with the inputs in the cells above, use =-PMT($B$2/12,$B$3*12,$B$1).

Why does my amortization table show a negative payment?
Spreadsheet financial functions use positive and negative signs to represent money received and paid. Negate the PMT result when you want the payment displayed as a positive number.

Why is the final balance not zero?
Small residuals usually come from rounding or a formula-reference error. Keep full precision in intermediate calculations, check the number of payment rows, and confirm the final payment treatment with the lender.

Can I use this for extra payments or a variable-rate loan?
The calculator assumes a fixed rate and regular level payments. A schedule can be extended with extra-payment inputs, but a variable rate needs a rate for each period, and the result depends on how the loan applies those changes.

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

Related

• Car Loan Amortization Table Calculator
• HELOC Amortization Table Calculator and Payment Guide
• 15-Year Loan Amortization Table and Calculator

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2