Generate a Loan Amortization Schedule in Excel with Claude AI
Progress1 of 4
1
The finished schedule
2
Set up your loan data
3
Prompt, FAQ & related
4
Quick quiz
Section 01
What the finished amortization schedule looks like
Generate a Loan Amortization Schedule in Excel with Claude AI
This course gives you a Claude prompt that converts basic loan terms — principal, rate, tenor, and payment frequency — into a complete amortization schedule you can paste into Excel. Every row shows the payment breakdown between principal and interest, plus the remaining balance after each instalment.
Your Finished Amortization Table
Here is a sample schedule Claude produces for a £150,000 term loan at 5.0% annual interest over 5 years (60 monthly payments). Notice how the interest portion shrinks and the principal portion grows with each payment — the hallmark of a standard amortizing loan.
A
B
C
D
E
F
1
Period
Payment
Principal
Interest
Extra Pmt
Balance
2
0
—
—
—
—
150,000.00
3
1
2,830.46
2,205.46
625.00
0.00
147,794.54
4
2
2,830.46
2,214.65
615.81
0.00
145,579.89
5
3
2,830.46
2,223.88
606.58
0.00
143,356.01
6
…
…
…
…
…
…
62
60
2,830.46
2,818.72
11.74
0.00
0.00
Period 0 is the loan origination row. The final balance reaches exactly zero, confirming the schedule is mathematically complete.
Period-by-period breakdown of principal repayment versus interest expense
Extra Payment column for modelling lump-sum prepayments and their effect on total interest
Automatic recalculation of remaining balance after each instalment
Summary row showing total interest paid, total principal repaid, and effective cost of borrowing
Where this fits: Amortization schedules are one of many finance templates in our AI for Excel course library. If the loan feeds into a larger financial model, the 3-statement financial model course shows how to wire the interest expense and principal repayment into your income statement and balance sheet.
Set Up Your Loan Parameters
Claude needs the core terms of the loan. Most of this comes from the term sheet or loan agreement. If you're modelling a prospective loan, use your best estimates — the schedule is easy to regenerate once terms are finalised.
Loan Input Table
A
B
1
Parameter
Value
2
Loan principal
£150,000
3
Annual interest rate
5.0%
4
Loan term
5 years
5
Payment frequency
Monthly
6
First payment date
01/08/2025
7
Extra payments
£5,000 in period 12; £10,000 in period 24
01
Confirm the rate basis
Is your rate nominal or effective? Most loan agreements quote a nominal annual rate. If the agreement says "5% per annum compounded monthly," the periodic rate is 5% ÷ 12 = 0.4167%. State this clearly in the prompt so Claude doesn't assume the wrong convention.
02
Decide on extra payments upfront
If you plan to model prepayments, list each one with its period number and amount. Claude will apply the extra payment to principal immediately and recalculate interest for every subsequent period. This is how you measure the interest savings from early repayment.
03
Handle variable rates as a rate schedule
For loans with rate resets — a fixed rate for 24 months then a floating rate — provide a simple table: periods 1–24 at 5.0%, periods 25–60 at 5.75%. Claude recalculates the payment amount at each step change, which is the same approach used in the fixed asset depreciation course when switching between depreciation methods mid-life.
Tip: If your loan has an interest-only period followed by full amortization, specify both phases. For example: "Months 1–6 interest only, months 7–60 fully amortizing." Claude will generate zero-principal rows for the interest-only phase and recalculate the payment for the amortizing phase based on the remaining balance.
The Exact Claude Prompt for a Loan Amortization Schedule
Copy this prompt into Claude. Replace the bracketed values with your actual loan terms. The prompt includes guardrails to prevent Claude from rounding prematurely or dropping the final penny of balance.
Prompt — paste into Claude
I need a complete loan amortization schedule for Excel. Here are the loan terms:
LOAN PARAMETERS:
- Principal: [e.g. £150,000]
- Annual interest rate: [e.g. 5.0% nominal, compounded monthly]
- Loan term: [e.g. 5 years]
- Payment frequency: [e.g. Monthly]
- First payment date: [e.g. 01/08/2025]
EXTRA PAYMENTS (optional):
[e.g. Period 12: £5,000 extra principal; Period 24: £10,000 extra principal]
RATE CHANGES (optional — omit if rate is fixed for the full term):
[e.g. Periods 1–24: 5.0%; Periods 25–60: 5.75%]
Rules:
- Calculate the periodic rate as Annual rate ÷ Number of payments per year.
- Use the standard annuity formula for the fixed payment: PMT = P × [r(1+r)^n] / [(1+r)^n − 1].
- If extra payments are provided, reduce the outstanding balance immediately and recalculate interest from the next period onward. Do NOT recalculate the fixed payment amount unless there is a rate change.
- If there is a rate change, recalculate the fixed payment at the new rate based on the remaining balance and remaining periods at the point of change.
- Carry all calculations to at least 6 decimal places internally. Round displayed amounts to 2 decimal places.
- The final period's balance must be exactly 0.00 (adjust the last payment if rounding creates a residual of a few pence).
- Do NOT invent loan terms. Use ONLY the parameters I provided.
Output:
1. An amortization table with columns: Period | Payment Date | Payment | Principal | Interest | Extra Payment | Remaining Balance
2. A summary showing: Total payments made, Total interest paid, Total principal repaid, Interest saved by extra payments (vs. no extra payments), Effective payoff period (if shortened by prepayments)
Format the output as an Excel-ready table I can paste directly into a spreadsheet.
When You Actually Need an Amortization Schedule
Excel's PMT function tells you the monthly payment. That's useful, but it's a single number. An amortization schedule tells you something PMT can't: exactly how much of your December payment goes to principal versus interest, and what the outstanding balance will be on 15 March next year. That granularity matters in at least three situations.
First, accrual accounting requires you to book interest expense in the period it accrues, not when you pay it. The schedule gives you the interest figure for each month without a separate IPMT calculation. Second, covenant compliance often hinges on the outstanding balance at a specific date — the schedule gives you that directly. Third, if you're evaluating whether to prepay, you need to see the cumulative interest saved period by period, not just the headline total.
Common Amortization Errors
A
B
C
1
Error
Impact
Prevention
2
Using annual rate as the periodic rate
Massively overstates interest — a 5% monthly rate vs. 0.417%
State "compounded monthly" in the prompt; Claude divides by 12
3
Final balance not reaching zero
Rounding errors accumulate across 60+ rows
The prompt tells Claude to adjust the last payment to zero out the balance
4
Recalculating PMT after an extra payment
Changes the fixed payment, confusing borrowers and accountants
Prompt specifies: hold PMT constant; extra payments shorten the term instead
Frequently Asked Questions
Can Claude handle variable interest rates in an amortization schedule?
Yes. Include a rate schedule in your prompt — for example, 4.5% for months 1–24, then 5.2% from month 25 onward. The prompt instructs Claude to recalculate the payment amount at each rate change so principal and interest splits remain accurate.
How does Claude calculate the monthly payment amount?
Claude uses the standard annuity formula: PMT = P × [r(1+r)^n] / [(1+r)^n − 1], where P is the principal, r is the periodic interest rate, and n is the total number of payments. This is the same formula behind Excel's PMT function.
Can I model extra principal payments or lump-sum prepayments?
Yes. Add the period number and amount to the Extra Payments section of the prompt. Claude reduces the outstanding balance immediately and recalculates interest for all subsequent periods, showing you exactly how much interest the prepayment saves.
Will the schedule work for quarterly or annual payment frequencies?
Yes. Specify the frequency in the prompt — monthly, quarterly, semi-annual, or annual. Claude adjusts the periodic rate and number of periods. A 5-year loan at 6% annual with quarterly payments becomes 20 periods at 1.5% per period.
How do I verify Claude's amortization output?
Check three things: the first payment's interest equals the principal multiplied by the periodic rate; the final remaining balance is exactly zero; and the sum of all principal payments equals the original loan amount. If any of these fail, the schedule has an error.