Create intermediate Works with: Any AI

Loan Amortization Schedule in Excel or Google Sheets

Table output Has example

• 📊 Excel
• 📈 Google Sheets

Create a loan amortization schedule in a spreadsheet, with PMT, interest and principal columns, extra payments and a zero-balance check.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Build a loan amortization schedule for me in [SPREADSHEET_APP]. Loan amount [LOAN_AMOUNT], annual interest rate [ANNUAL_RATE], term [TERM_YEARS] years, payments per year [PAYMENTS_PER_YEAR], first payment date [FIRST_PAYMENT_DATE], optional extra payment per period [EXTRA_PAYMENT]. My loan is for [LOAN_PURPOSE].

Give me the layout as a table with column letters: Payment number, Date, Opening balance, Payment, Interest, Principal, Extra payment, Closing balance, Cumulative interest. Provide the formulas for the first two data rows and say how to fill down, including the input cells at the top (use named cells or clear references). Use PMT, IPMT and PPMT only if that is the best way, and explain the difference from a formula based on balance times rate.

Add:

1. A check row that proves the final balance is zero and the total of principal equals the loan.

2. A summary box with total interest paid, total of all payments, payoff date, and interest saved by the extra payments.

3. How to handle the last payment so rounding does not leave a few cents.

4. A short guide to changing the rate or term and what to look out for.

Show your arithmetic for the first payment so I can follow it. Do not give financial advice, and do not assume my lender calculates interest the same way: label that [VERIFY] against my loan agreement. Mark menu steps for my app [VERIFY].

Add a worked example with invented figures: 20,000 at 6% over 5 years with monthly payments.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Invented example: 20,000 at 6% for 5 years, monthly.
Monthly rate: 6% / 12 = 0.5%. Payments: 5 x 12 = 60.
Payment = PMT(0.005, 60, -20000) = about 386.66.

Row 1: opening 20,000.00; interest 20,000 x 0.005 = 100.00; principal 386.66 - 100.00 = 286.66; closing 19,713.34.
Row 2: interest 19,713.34 x 0.005 = 98.57; principal 288.09; closing 19,425.25.

Total paid: 386.66 x 60 = 23,199.60, so interest is about 3,199.60.
Check: the closing balance of payment 60 must show 0.00 after adjusting the last payment for rounding.

• spreadsheet
• template