Loan Amortization Schedule in Google Sheets: How to Build

To make a loan amortization schedule in Google Sheets, enter the loan amount, annual interest rate, and term, calculate the payment with =-PMT($B$3/$B$4,$B$2*$B$4,$B$1), then calculate each period’s interest, principal, and remaining balance in separate columns. The schedule below uses a fixed-rate monthly loan and can be expanded to include due dates and extra principal payments.

What a loan amortization schedule shows

An amortization schedule breaks a loan into a sequence of payments. Each row represents one payment period and typically shows the starting balance, payment, interest charge, principal repaid, and ending balance. It makes visible a detail that can be easy to miss when looking only at a monthly payment: early payments on a standard fixed-rate loan generally include more interest because the balance is higher, while later payments reduce principal more quickly.

The schedule is useful for understanding a loan’s payment pattern and estimating the total interest under the assumptions entered. It is not necessarily the lender’s official payoff statement. Actual servicing can include rounding, payment-date rules, fees, variable rates, daily interest, or other terms that a basic spreadsheet does not model. Use the lender’s documents for the contractual amount and ask the lender or a qualified professional about decisions involving your specific loan; this spreadsheet is an illustration, not financial, tax, investment, or legal advice.

How the calculation works

For a conventional fixed-rate loan with payments at the end of each period, the payment is calculated from the principal, periodic interest rate, and number of payments. A monthly schedule converts an annual rate to a monthly rate by dividing it by 12, and converts a term in years to a number of payments by multiplying by 12. For example, a 6% annual rate becomes 0.5% per month, or 0.005 as a decimal.

Each period’s interest is the opening balance multiplied by the periodic rate. Principal paid is the payment minus that interest, and the closing balance is the opening balance minus principal paid. The next row’s opening balance is the previous row’s closing balance. If a payment is smaller than the interest charged, the balance can grow rather than shrink; that is negative amortization and needs different handling from the ordinary example here.

Set up the Google Sheets inputs

In a blank Google Sheets spreadsheet opened in a web browser, create a small input area. Use labels in column A and values in column B. Enter the loan principal in B1, the term in years in B2, the annual interest rate in B3, and payments per year in B4. For the monthly example, enter 20000, 3, 6%, and 12. Enter the date of the first payment in B6 if you want a due-date column. Format B1 and later payment amounts as currency, B3 as a percentage, and dates as dates using Format > Number.

With principal in B1, term in years in B2, annual rate in B3, and payments per year in B4, enter this in B5 to calculate the regular payment. The leading minus sign makes the displayed result positive because PMT treats a payment as cash leaving the borrower:

With the inputs in B1:B4 as described above, the formula in B5 is =-PMT($B$3/$B$4,$B$2*$B$4,$B$1). For a $20,000 loan at 6% over three years with monthly payments, it returns approximately $608.44 per month. PMT assumes equal payments and a constant periodic rate; because its optional type argument is omitted, it assumes payments occur at the end of each period.

Build the payment-by-payment table

Starting in row 9, put these headings across columns A through G: Period, Due date, Opening balance, Payment, Interest, Principal, and Closing balance. Row 10 is the first payment. Enter 1 in A10, and enter the first payment date in B6. If you do not need dates, you can omit column B. The following formulas assume the input cells above and the headings in row 9.

With the first payment period number in A10, enter =EDATE($B$6,A10-1) in B10 to set the first due date. EDATE advances a date by a specified number of months.
With the original principal in B1, enter =$B$1 in C10 for the first opening balance.
With the payment amount in B5, opening balance in C10, and period interest in E10, enter =MIN($B$5,C10+E10) in D10. This caps the last payment at the amount still owed, including that period’s interest.
With opening balance in C10 and annual rate and payment frequency in B3 and B4, enter =C10*$B$3/$B$4 in E10 to calculate interest for the period.
With payment in D10 and interest in E10, enter =D10-E10 in F10 to calculate principal repaid.
With opening balance in C10 and principal repaid in F10, enter =MAX(0,C10-F10) in G10 to calculate the closing balance without displaying a tiny negative remainder from floating-point arithmetic.

For row 11 and each later row, the period number increases by one and the opening balance links to the previous closing balance. With the first period in A10, the next period number in A11 is =A10+1. With the first due date in B10 and the next period number in A11, the due date in B11 is =EDATE($B$6,A11-1). With the prior closing balance in G10, the next opening balance in C11 is =G10. Copy the payment, interest, principal, and closing-balance formulas from row 10 into row 11; relative references will update. Then select the completed row and drag the fill handle down for the number of payments, 36 in this example.

Check that the final closing balance is zero or very close to zero. With a full-precision payment, the final payment can display as $608.44 while underlying cents contain more digits. If you round every intermediate calculation to two decimal places, the final payment or total interest can differ slightly. Currency formatting changes what is displayed; it does not itself round the stored calculation.

Worked example and useful totals

For the illustrative $20,000, three-year loan at 6% with monthly payments, the regular payment is about $608.44. In the first month, interest is $100.00 because $20,000 multiplied by 0.5% is $100.00. The principal portion is therefore about $508.44, and the balance after that payment is about $19,491.56. In the second month, interest is calculated on the lower opening balance, so it is about $97.46; the principal portion increases accordingly. Small differences in displayed cents can arise depending on whether formulas retain precision or round each period.

If the schedule has 36 periods in rows 10 through 45, you can total its interest and principal. With interest amounts in E10:E45, enter =SUM(E10:E45) in a summary cell; it will show approximately $1,903.78 when based on the full-precision payment, subject to display and rounding choices. With principal amounts in F10:F45, enter =SUM(F10:F45); the total should be approximately $20,000. These checks help find an omitted row, a mistaken reference, or a formula that was not filled to the final payment.

Model extra principal payments

An extra principal payment can reduce the balance sooner and, in a simple fixed-rate model, reduce later interest. The lender’s rules determine how an additional amount is applied, whether there are prepayment conditions, and whether the scheduled payment or payoff date changes. Check the loan agreement or contact the lender rather than assuming the spreadsheet matches its servicing method.

To model extra payments without changing the basic layout, add an Extra principal heading in H9 and type each additional amount into H10 downward, using 0 in periods with no extra payment. With opening balance in C10, scheduled payment in D10, interest in E10, and extra principal entered manually in H10, use =MIN(C10,D10-E10+H10) in F10 for principal paid. This caps principal at the remaining balance. The closing balance in G10 can remain =MAX(0,C10-F10). Actual cash paid in a period is interest plus principal, so when an extra payment is entered it can be greater than the scheduled amount in D10. For clarity, add a separate Total paid column and calculate it as interest plus principal; label the scheduled payment and extra amount distinctly.

This approach keeps the regular payment from B5 fixed and shortens the modeled payoff period. After the balance reaches zero, stop entering extra amounts and avoid interpreting repeated zero-balance rows as additional payments. If you want the schedule to calculate the exact payoff month automatically, add a clear rule for suppressing rows after payoff or use a separate payoff indicator; do not treat a simple fixed number of rows as a lender quote.

Common errors and ways to check the sheet

Entering 6 instead of 6%: In B3, enter 6% or 0.06. A value of 6 means 600% in a percentage calculation and produces an unrealistic result.
Using the annual rate as a monthly rate: For monthly payments, use B3 divided by B4. If the loan compounds or accrues interest under a different convention, the lender’s method may not match this simplified calculation.
Mixing units: The number of periods must use the same frequency as the periodic rate. A three-year monthly loan has 36 periods, not 3 or 12.
Reversing interest and principal: Interest is opening balance times periodic rate; principal is payment minus interest. The remaining balance should decline on an ordinary fully amortizing loan.
Filling down an incomplete range: A 36-payment schedule needs 36 payment rows. Confirm the final period number and compare total principal with the original amount.
Rounding too early: Displaying two decimals is not the same as rounding the underlying values. A lender may round each payment-period calculation differently, so small discrepancies do not automatically mean the sheet is broken.

If a formula is rejected, check that the cell references match your layout and that the spreadsheet locale accepts commas as argument separators. Google Sheets locale settings can affect separators and number or date display. Use the sheet’s locale conventions if its formula parser expects semicolons instead. For unusual loans—such as adjustable-rate, interest-only, irregular-payment, or daily-accrual loans—a basic PMT schedule is not a faithful model unless its formulas are adapted to the actual contract.

Google Sheets versus other ways to make a schedule

Google Sheets works well when you want a transparent schedule that is easy to share and revise in a browser. Its PMT and EDATE functions are also available in Excel, and the formulas shown here use standard spreadsheet syntax. The menus differ between products, however, and this guide uses Google Sheets rather than Excel ribbon paths. In Sheets, number formatting is under Format > Number, and rules for visual highlighting are under Format > Conditional formatting.

A lender’s online calculator or amortization table may be faster for a basic estimate, but it may not expose every calculation or let you test custom extra payments. A downloadable template can save setup time, but inspect its inputs, formulas, and assumptions before relying on it. A custom sheet is most useful when you need to see how a particular assumption changes the schedule; it is not a substitute for confirming rates, fees, payoff figures, and payment rules with the lender.

Frequently Asked Questions

Does Google Sheets have a loan amortization schedule template?
Google Sheets may offer templates through its template gallery, but the available selection can change. You can also build a schedule directly with PMT and the row formulas above, which makes the assumptions and calculations visible.

What is the Google Sheets formula for a monthly loan payment?
With principal in B1, years in B2, annual rate in B3, and payments per year in B4, use =-PMT($B$3/$B$4,$B$2*$B$4,$B$1). The minus sign returns a positive payment for a positive loan amount.

Why does my amortization schedule end with a small balance?
Common causes include rounding intermediate values, an incorrect number of periods, a rate entered in the wrong units, or a payment formula that does not match the lender’s terms. Check the full-precision calculation and confirm the final payment and rounding rules with the lender.

Can I use this schedule for biweekly payments?
You can set B4 to 26 for a simplified model with 26 equal payment periods per year, but do not assume this exactly matches every lender’s biweekly method. The payment timing, rate conversion, calendar, and contract may affect the result; confirm how the lender calculates it.

Does an extra payment automatically reduce the monthly payment?
Not necessarily. Some loans apply extra money to principal while keeping the regular payment and shortening the payoff period; other arrangements may recalculate payments. The spreadsheet’s result is only a model of the rule you enter, so verify how the lender applies extra funds.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.