Loan Payment & Prepayment Calculator in Google Sheets

Photo by Team Nocoloco on Unsplash

What this guide builds and what it does not cover

This guide teaches you to build a loan repayment calculator with an amortization schedule in Google Sheets and then publish it as a live web form using GSheetPress. The finished calculator handles fixed-rate, fixed-term loans with equal periodic payments. It does not handle variable-rate loans, balloon payments, offset accounts, redraw facilities, lender fees outside a simple upfront amount, or tax deductions. Interest assumptions are straight periodic compounding on the outstanding balance; your lender may compound differently. Always cross-check every result against your lender's official statement or disclosure document.

Key terms explained

• PMT: A Google Sheets function that returns the fixed payment needed each period to repay a loan in full, given an interest rate, a number of periods, and a loan amount.
• Amortization schedule: A table that breaks every payment into its interest portion and its principal-reduction portion, and shows the remaining balance after each payment.
• Principal: The outstanding loan amount on which interest is charged; it decreases with every repayment.
• Interest: The cost of borrowing, calculated each period as the periodic rate multiplied by the current outstanding balance.
• Periodic rate: The annual interest rate divided by the number of payment periods per year (for example, an annual rate of 6% divided by 12 gives a monthly periodic rate of 0.5%).
• Prepayment (extra repayment): Any amount paid above the minimum required payment; it reduces the principal faster, shortening the loan term and reducing total interest paid.
• Outstanding balance: The principal still owed at any point in time after accounting for all payments made so far.
• Loan term: The agreed total number of periods over which the loan is scheduled to be repaid.
• Payment frequency: How often payments are made — monthly, fortnightly, or weekly. This affects both the periodic rate and the number of periods.
• GSheetPress Calculator: A GSheetPress feature that converts a specially structured Google Sheet into a publicly accessible web calculator form.

Prerequisites

• A Google account with access to Google Sheets.
• A GSheetPress account. The Calculator feature requires the appropriate plan entitlement; check the GSheetPress pricing page to confirm your plan includes it.
• The GSheetPress add-on installed from the Google Workspace Marketplace, or access via Extensions → GS2Web SaaS in your Sheets menu.
• A dedicated sample Google Sheet created purely for this exercise. Do not use a sheet containing private financial data.

Part 1 — Build the amortization schedule manually

Before publishing a web calculator it is worth understanding the formulas. This section builds a working amortization table in a plain sheet so you can verify every number before it goes live.

Step 1 — Set up the inputs area

Open your dedicated sample workbook. On a sheet named Amortization, enter the following labels in column A and your sample values in column B:

Payments per year is 12 for monthly, 26 for fortnightly, or 52 for weekly. Confirm which frequency your lender uses before entering a value.

Step 2 — Calculate the fixed payment with PMT

In cell B7, enter the label Minimum Payment in A7, then paste this formula into B7:

=PMT(B2/100/B4, B3*B4, -B1)

PMT takes three core arguments: the periodic rate (B2/100/B4 converts the percentage to a decimal and divides by payment frequency), the total number of periods (B3*B4), and the present value as a negative number (-B1 so the result is a positive payment). With the sample values this returns approximately $1,288.60 per month.

Step 3 — Build the amortization table headers

Starting in row 10, create these column headers:

Step 4 — Enter row 1 of the schedule (row 11)

Enter these formulas exactly. Period 1 is special because the opening balance equals the loan amount.

• A11: 1
• B11: =B1
• C11: =B11*(B2/100/B4)
• D11: =MIN($B$7-C11, B11) — principal portion of the minimum payment, capped at the remaining balance.
• E11: =MIN($B$5, B11-D11) — extra repayment, capped so the balance cannot go negative.
• F11: =MAX(B11-D11-E11, 0)
• G11: =C11

Step 5 — Enter row 2 of the schedule (row 12) and fill down

Row 12 onward links back to the prior row's closing balance.

• A12: =IF(F11>0, A11+1, "") — stops counting periods once the loan is repaid.
• B12: =IF(F11>0, F11, "")
• C12: =IF(B12<>"", B12*(B$2/100/B$4), "")
• D12: =IF(B12<>"", MIN($B$7-C12, B12), "")
• E12: =IF(B12<>"", MIN($B$5, B12-D12), "")
• F12: =IF(B12<>"", MAX(B12-D12-E12, 0), "")
• G12: =IF(B12<>"", G11+C12, "")

Select A12:G12, then fill down for enough rows to cover the full loan term. For a 25-year monthly loan that is 300 rows; fill 350 rows to be safe. Use Edit → Fill → Fill Down or drag the fill handle.

Do not use IFERROR to suppress all errors silently. If a cell shows an error, investigate the formula rather than hiding the result, because a hidden error in an amortization row will silently corrupt every balance below it.

Step 6 — Add summary statistics

Place these in a summary block, for example starting at A360:

With the sample values (loan $200,000, 6% per year, 25 years monthly, $200 extra per month) you should see approximately 297 periods instead of 300, total interest with prepayment around $147,700, total interest without prepayment around $186,600, and interest saved around $38,900. These are illustrative estimates only; do not treat them as financial advice.

For more ways to connect calculated data to web-facing outputs, see the GSheetPress Table feature, which can display dynamic sheet data in a styled web table.

Part 2 — Build the GSheetPress web calculator

This section turns the loan inputs into a public web form. The form collects five inputs from a visitor, runs the formulas in the sheet, and shows the results — without exposing the spreadsheet itself.

Create the calculator

1. Open your dedicated sample workbook in Google Sheets.
2. Click Extensions in the top menu.
3. Click GS2Web SaaS (or your installed add-on name).
4. Click Calculator, then New Calculator.
5. Enter a name, for example Loan Repayment Calculator.
6. Set the number of inputs to 5. This does not include the Calculate button.
7. Choose whether to include a graph. For this calculator, graphs are optional; skip them if you want a clean layout.
8. Click Create (or the equivalent confirm button). GSheetPress generates a new sheet tab with headers already in row 1: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules across columns A through H.

With 5 inputs, the generated layout is: rows 2–6 for inputs, row 7 for the Calculate button, row 8 for the merged Result Fields separator, row 9 for result column headings, and results start at row 10.

Input row setup

Each subsection below covers one input row. Column G (Inputs) holds the live value submitted by a web visitor. Column E (Default) holds the value that pre-fills the form. To test locally before publishing, copy E2:E6, click G2, and use Paste special → Values only so G holds numbers rather than a formula reference. Never place result formulas in column G; the web form overwrites G on every submission.

Row 2 — Loan Amount

• A2 — Label: Loan Amount ($)
• B2 — Input Type: Number
• C2 — Required: Check the Required checkbox (tick it on)
• D2 — Data: Leave blank
• E2 — Default: 200000
• F2 — ToolTips: Enter the total amount you are borrowing in dollars.
• H2 — Rules: Leave blank

Row 3 — Annual Interest Rate

• A3 — Label: Annual Interest Rate (%)
• B3 — Input Type: Number
• C3 — Required: Check the Required checkbox
• D3 — Data: Leave blank
• E3 — Default: 6
• F3 — ToolTips: Enter your annual interest rate as a number, for example enter 6 for 6%. Do not include the % symbol.
• H3 — Rules: Leave blank

Row 4 — Loan Term

• A4 — Label: Loan Term (Years)
• B4 — Input Type: Number
• C4 — Required: Check the Required checkbox
• D4 — Data: Leave blank
• E4 — Default: 25
• F4 — ToolTips: Enter the number of years over which you will repay the loan.
• H4 — Rules: Leave blank

Row 5 — Payments Per Year

• A5 — Label: Payments Per Year
• B5 — Input Type: Number
• C5 — Required: Check the Required checkbox
• D5 — Data: Leave blank
• E5 — Default: 12
• F5 — ToolTips: Enter 12 for monthly, 26 for fortnightly, or 52 for weekly payments. Check with your lender which frequency applies.
• H5 — Rules: Leave blank

Row 6 — Extra Repayment Per Period

• A6 — Label: Extra Repayment Per Period ($)
• B6 — Input Type: Number
• C6 — Required: Leave the Required checkbox unchecked (extra repayment is optional)
• D6 — Data: Leave blank
• E6 — Default: 0
• F6 — ToolTips: Optional. Enter any additional amount you plan to pay each period above the minimum. Enter 0 if none.
• H6 — Rules: Leave blank

Row 7 — Calculate button (generated, do not delete)

• A7 — Label: Calculate
• B7 — Input Type: Button
• D7 — Data: main
• E7: Leave blank (E7 is not Reset; the label in A7 is Calculate)

Rows 8 and 9 are generated by GSheetPress. Row 8 is the merged Result Fields separator. Row 9 holds the result column headings. Do not delete or move these rows.

Result formulas — rows 10 onward

All formulas reference G2:G6, which hold the live submitted values. G2 = Loan Amount, G3 = Annual Interest Rate (as a percentage number), G4 = Loan Term (Years), G5 = Payments Per Year, G6 = Extra Repayment Per Period.

Click each cell listed below, type the = sign, paste the rest of the formula, and press Enter.

The period count formula above uses a helper amortization sheet. A simpler and more robust approach — recommended for this build — is to use dedicated helper cells on a hidden sheet. Follow the steps below.

Helper sheet for amortization results

The GSheetPress calculator sheet itself holds inputs and result labels. The actual amortization calculation lives on a separate helper sheet, which the result cells reference. This keeps the calculator sheet clean and avoids row-count conflicts.

1. Add a new sheet tab named CalcAmort.
2. In CalcAmort, reproduce the amortization schedule from Part 1, but replace all references to the manual input cells with references to the calculator sheet's G column. Assuming your calculator tab is named Loan Repayment Calculator, the header row is row 1 of CalcAmort and data starts at row 2:
   • A1: Period  B1: Opening Balance  C1: Interest  D1: Principal  E1: Extra  F1: Closing Balance  G1: Cum Interest
   • A2: 1
   • B2: ='Loan Repayment Calculator'!G2
   • C2: =B2*('Loan Repayment Calculator'!G3/100/'Loan Repayment Calculator'!G5)
   • D2: =MIN(IFERROR(PMT('Loan Repayment Calculator'!G3/100/'Loan Repayment Calculator'!G5,'Loan Repayment Calculator'!G4*'Loan Repayment Calculator'!G5,-'Loan Repayment Calculator'!G2),0)-C2, B2)
   • E2: =MIN('Loan Repayment Calculator'!G6, B2-D2)
   • F2: =MAX(B2-D2-E2, 0)
   • G2: =C2
3. In row 3 onward, use the linked formulas from Part 1 Step 5, but replace the manual $B$7 minimum payment reference with the PMT expression using G-column values from the calculator sheet. For example:
   • A3: =IF(F2>0, A2+1, "")
   • B3: =IF(F2>0, F2, "")
   • C3: =IF(B3<>"", B3*('Loan Repayment Calculator'!G3/100/'Loan Repayment Calculator'!G5), "")
   • D3: =IF(B3<>"", MIN(IFERROR(PMT('Loan Repayment Calculator'!G3/100/'Loan Repayment Calculator'!G5,'Loan Repayment Calculator'!G4*'Loan Repayment Calculator'!G5,-'Loan Repayment Calculator'!G2),0)-C3, B3), "")
   • E3: =IF(B3<>"", MIN('Loan Repayment Calculator'!G6, B3-D3), "")
   • F3: =IF(B3<>"", MAX(B3-D3-E3,0), "")
   • G3: =IF(B3<>"", G2+C3, "")
4. Fill rows 3 through 351 (covering 350 periods, more than enough for a 30-year weekly loan).

Back on the calculator sheet — complete result rows

With CalcAmort in place, enter these formulas in the calculator sheet result rows. Select the cell, type =, paste the formula, press Enter.

IFERROR is used here only to display a readable message when inputs are missing or produce a mathematical impossibility (such as a zero interest rate). It does not mask correct calculation errors — if the formulas return wrong numbers with valid inputs, fix the formula rather than changing the IFERROR fallback.

Verify with the worked example

Copy E2:E6 on the calculator sheet, click G2, and paste with Paste special → Values only. The results in B10:B16 should show:

These figures are for illustration only. Your lender compounds interest according to their own terms and conditions; always compare against an official loan statement. For an alternative scenario, try a $300,000 loan at 7.5% over 30 years with $300 extra monthly — you should see a minimum payment around $2,097 and meaningful interest savings compared with no extra repayments.

The GSheetPress Calculator feature overview explains additional result formatting options you can apply once the formulas are confirmed correct.

Troubleshooting

PMT returns a negative number

This happens when the loan amount is entered as a positive number and the sign convention is not adjusted. Ensure the formula uses -G2 (negative loan amount) as the present value argument: =PMT(G3/100/G5, G4*G5, -G2).

Amortization rows show #VALUE! or #REF!

Check that the calculator sheet tab name exactly matches what is used in the CalcAmort formula references, including spaces and capitalisation. Any mismatch causes a reference error across all 350 rows.

Closing balance never reaches zero

This usually means the periodic interest charge equals or exceeds the minimum payment, so the principal never decreases. It can happen when the interest rate is very high or the loan term is very short. Increase the loan term or reduce the rate to produce a viable repayment schedule.

Periods Saved shows zero even with extra repayments

Verify that G6 on the calculator sheet holds a number greater than zero after you paste the defaults. If G6 is empty or contains text, the extra repayment column in CalcAmort evaluates to zero and no periods are saved.

Results do not update after changing G values

Check that Google Sheets recalculation is set to automatic: go to File → Settings → Calculation and set it to On change.

The web form submits but shows old results

After publishing a change to the calculator sheet, open Extensions → GS2Web SaaS → Calculator and republish. The existing public link is retained; only the content updates.

Frequently asked questions

Does the calculator handle fortnightly or weekly payments correctly?

Yes, as long as you enter the correct Payments Per Year value. Enter 26 for fortnightly or 52 for weekly. The PMT function and the amortization rows both divide the annual rate by this number, so the periodic rate adjusts automatically. Be aware that some lenders calculate fortnightly payments as exactly half a monthly payment rather than using a true fortnightly rate; ask your lender which method they use and verify accordingly.

Can I include a one-off lump-sum extra repayment rather than a fixed amount every period?

The current build assumes a constant extra repayment each period. To model a one-off lump sum, you can add a sixth input called Lump Sum Period and a seventh called Lump Sum Amount, then adjust the CalcAmort formula in the Extra column to add the lump sum only in the matching period row. This requires expanding the calculator to seven inputs and updating the row layout accordingly (rows 2–8 for inputs, button in row 9, separator row 10, headings row 11, results from row 12).

Is this calculator suitable for showing clients or embedding on a website?

The published web calculator can be embedded on a website using the iframe code from column D of the Calculator Links sheet. It is suitable for illustrative and educational use. It should not replace a licensed financial adviser's disclosure document or a lender's official repayment schedule. Include a clear disclaimer on your page stating that results are estimates based on the assumptions entered and that users should confirm figures with their lender. If you need to understand embedding options further, the GSheetPress support page provides guidance on iframe usage and display settings.

Publish and test your web calculator

Step 1 — Share the spreadsheet

1. Open your dedicated sample workbook.
2. Click Share in the top-right corner.
3. Under General access, change the setting to Anyone with the link.
4. Set the role to Editor.
5. Click Done.

Important: Setting access to Editor means anyone who obtains the spreadsheet URL can modify the sheet directly. Use only a dedicated sample workbook with no sensitive data. Do not apply this sharing setting to sheets containing private financial information.

Step 2 — Publish the calculator

1. Click the calculator sheet tab to make it the active sheet.
2. Click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
3. Wait for the confirmation message. GSheetPress processes the sheet and registers the public endpoint.

Step 3 — Find and open the public link

1. In the GSheetPress sidebar or via the Calculator menu, open the Calculator Links sheet (or click Get Links to refresh it).
2. Find the row where the Sheet Name column matches your calculator tab name.
3. Open the URL in column C (Link) — this is the public calculator form. Do not use column B (Edit Link), which opens the sheet editor.
4. If column C is empty or stale, click Get Links to refresh the list.

Step 4 — Test the published calculator

1. In the public calculator form, enter the worked example values: Loan Amount 200000, Annual Interest Rate 6, Loan Term 25, Payments Per Year 12, Extra Repayment Per Period 200.
2. Click Calculate.
3. Confirm that the displayed results match the expected values from the verification table above (minimum payment approximately $1,288.60, interest saved approximately $38,879).
4. If results do not match, return to the sheet, check the G-column values after a test paste, correct any formula, and republish. Republishing the same calculator retains its existing public link.

Step 5 — Embed on a website (optional)

Copy the iframe code from column D of the Calculator Links sheet and paste it into an HTML block on your website. Adjust the width and height attributes to suit your page layout. Add a disclaimer near the calculator stating that results are illustrative estimates only and that users should verify figures with their lender before making financial decisions.

For further configuration of published outputs, see the GSheetPress shop and plan details page, and refer to the official Google Sheets PMT function documentation if you need to adapt the payment formula for different compounding conventions.