Loan Payment Schedule in Google Sheets: Formula & Example

A loan payment schedule in Google Sheets is an amortization table showing each payment’s interest, principal, and remaining balance. Enter the loan amount, annual APR, and term below to estimate the fixed monthly payment and total interest; the schedule itself can then be built in Sheets using the same figures and formulas.

Formula

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

Payment = P × r ÷ [1 − (1 + r)⁻ⁿ]

• P is the original loan principal, in dollars.
• r is the monthly interest rate as a decimal: APR ÷ 12 ÷ 100.
• n is the number of monthly payments: years × 12.

Total interest in this calculator is monthly payment × number of payments − principal. In a spreadsheet, calculate the payment with =-PMT(apr/12, years*12, amount) when the APR cell contains a percentage such as 8%. The minus sign makes the result display as a positive payment under the spreadsheet function’s cash-flow sign convention. This is the same fixed-payment calculation, not a separate fee or lender quote.

Worked Example

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

1. Convert the APR to a monthly decimal rate: 8 ÷ 100 ÷ 12 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Apply the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = $405.53 per month, rounded to cents.
4. Calculate the total of the rounded payments: $405.53 × 60 = $24,331.80.
5. Subtract the original principal: $24,331.80 − $20,000 = $4,331.80 total interest.

The result is an estimate based on a rounded monthly payment. A lender’s actual payment schedule may differ if its calculation conventions, payment dates, fees, or other loan terms differ.

Build the Schedule in Google Sheets

Place the loan amount, APR, and term in separate input cells—for example, B1, B2, and B3. Put the monthly payment formula in B4: =-PMT(B2/12,B3*12,B1). If B2 is entered as 8%, Sheets treats it as 0.08, so dividing by 12 gives the monthly rate. If you enter 8 as a number instead, use =-PMT(B2/1200,B3*12,B1).

Create headings in row 7 for payment number, starting balance, payment, interest, principal, and ending balance. In row 8, enter the first payment and use formulas like these, assuming the inputs above:

• A8: 1 — the payment number.
• B8: =$B$1 — beginning balance.
• C8: =$B$4 — scheduled payment.
• D8: =B8*$B$2/12 — interest for the month.
• E8: =C8-D8 — principal repaid.
• F8: =B8-E8 — ending balance.

In row 9, set the payment number to =A8+1, the beginning balance to =F8, and copy the payment, interest, principal, and ending-balance formulas down. Use absolute references, such as $B$2, so the input references stay fixed when copied. For a five-year loan, extend the table through 60 payment rows.

How to Read Each Payment

Each scheduled payment is split between interest and principal. Interest is calculated on the outstanding balance, so the interest portion generally declines as the balance falls; the principal portion generally rises while the total payment remains level. The ending balance of one row becomes the starting balance of the next.

For the example, first-month interest is $20,000 × 8% ÷ 12, or about $133.33. The remainder of the $405.53 payment—about $272.20—reduces principal. The exact display can vary by a cent depending on whether individual schedule cells are rounded. If rounding each row to cents, the last payment may need adjustment to clear a small residual balance; do not treat a rounding remainder as a new loan charge.

When This Schedule Is Useful

A schedule helps explain how a fixed-payment loan changes over time. It can show the balance after a given number of payments, the cumulative interest paid, and how much of a payment reduces principal. It is also useful for checking whether a spreadsheet’s inputs and formulas produce a coherent estimate.

It does not determine whether a borrower qualifies, what a lender will charge, or what payment fits a household budget. Use the APR, principal, and term from the applicable loan disclosure or agreement when comparing the spreadsheet’s arithmetic with a stated schedule. The APR may not represent every charge in the same way the spreadsheet’s simple monthly-interest model does.

Common Spreadsheet Problems and Limits

• APR entered in the wrong format: 8% and 8 are different spreadsheet values. Match the formula to the cell format.
• Annual rate used as a monthly rate: Divide an APR entered as a percentage by 12, or divide a whole-number APR by 1,200.
• Wrong number of periods: A term in years must be multiplied by 12 for monthly payments.
• Changing references when copying: Lock input cells with dollar signs, such as $B$2.
• Rounding too early: Keep full precision in formulas and format cells to show two decimal places. Rounding every intermediate calculation can create a small final balance difference.

This model assumes a fixed APR and equal monthly payments, with interest accrued monthly at APR divided by 12. It does not model a variable rate, irregular payment dates, fees, taxes, insurance, PMI, escrow, payment holidays, or lender-specific rounding. For a different payment frequency or changing rate, the schedule needs corresponding inputs and formulas rather than a simple monthly table.

Assumptions

• Interest rate is fixed for the full modeled term.
• Payments are equal, monthly, and made at the end of each period.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, PMI, and escrow are not included.

Frequently Asked Questions

Can Google Sheets make a loan amortization schedule?
Yes. Use PMT to calculate the scheduled payment, then calculate each row’s interest from the starting balance, subtract interest from the payment to find principal, and subtract principal to find the ending balance.

What is the Google Sheets formula for a monthly loan payment?
For an APR stored as a percentage, use =-PMT(apr_cell/12,term_years_cell*12,loan_amount_cell). The minus sign displays the payment as a positive number.

Why does my ending balance not equal zero?
Small differences commonly come from rounding each row to cents or from using a payment that was rounded differently. Keep full precision in calculations, and check the final payment against the lender’s schedule.

Does a loan schedule include fees and insurance?
Not unless you add them as separate inputs and define how they are paid. The basic amortization formulas here cover principal and interest only.

Is APR the same as the monthly interest rate?
No. In this estimate, the monthly rate is APR divided by 12, with the APR converted from a percentage to a decimal. The lender’s disclosure gives the applicable loan figures and terms.

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

Related

• Auto Loan Down Payment Assistance: What to Know
• Auto Loan Miami No Down Payment: Estimate Payments
• Auto Loan Calculator With Down Payment

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