Provide ChatGPT with the exact loan contract terms, rate convention, payment timing, fees/prepayment rules, and any reset schedule, then use it to draft an auditable Excel amortization schedule. Verify the payment, interest, principal and ending balance against Excel formulas before relying on the result.
ROLE
You are helping me build an auditable loan amortization schedule in Excel.
LOAN CONTRACT INPUTS
- Principal advanced: [e.g. £150,000]
- Rate type / convention: [e.g. 5.0% nominal annual rate compounded monthly /
effective annual rate / other — state exactly]
- Payment frequency: [monthly / quarterly / other]
- Number of payments or contractual maturity: [e.g. 60 payments]
- First payment date: [date]
- Payment timing: [end of period = type 0 / beginning of period = type 1]
- Balloon / residual balance at maturity: [0 unless contract says otherwise]
- Fees included in financed principal: [amount or none]
- Day-count / irregular first period rule: [state if applicable; otherwise regular equal periods]
MODEL STRUCTURE
Use ONE shared Inputs block (or one Inputs sheet) for all schedules and summaries.
Do not duplicate the same loan inputs across multiple sheets.
If you use named ranges, prefer workbook-level names so every schedule and summary
references the same source inputs.
CALCULATION AND VERIFICATION RULES
1. Use Excel formulas as the calculation engine. Do not manually calculate a long
multi-period amortization schedule in prose.
2. Every value that feeds another calculation must come from an Excel formula or an
explicitly supplied input, not from a manually rounded intermediate result.
3. Keep full formula precision throughout the schedule. Round only displayed values
unless the contract explicitly requires rounded cash payments each period.
4. Build the schedule sequentially, one period/row at a time. Do not approximate or
skip periods when deriving later balances.
5. Do not assume Annual Rate / Payments per Year is correct unless the supplied rate
is a nominal annual rate compatible with that conversion.
6. If the supplied rate is an effective annual rate, use:
=(1+EffectiveAnnualRate)^(1/PaymentsPerYear)-1
7. Keep rate units and period units consistent. State the sign convention once.
8. For a regular fixed-rate annuity, use Excel:
=-PMT(PeriodicRate,NumberOfPayments,Principal,-Balloon,PaymentType)
Do not hard-code 0 as future value when Balloon is a supplied input.
9. Build each row from Opening Balance:
Interest = Opening Balance × Periodic Rate
Scheduled Principal = Payment - Interest
Maximum Principal Reduction = MAX(0,Opening Balance-Balloon)
Actual Principal = MIN(Scheduled Principal,Maximum Principal Reduction)
Closing Balance = Opening Balance - Actual Principal
10. Do not allow Closing Balance to fall below the contractual Balloon/Residual.
If Balloon = 0, the schedule may amortize to zero.
11. If contract payments are rounded to cents, cap the final payment so the schedule
reaches the contractual Balloon/Residual without overpaying.
12. Do not invent fees, compounding, rate basis, payment timing, day-count conventions
or other contract terms. If a required input is missing, return INPUT NEEDED.
13. Never label numerical totals as verified merely because the formulas appear logical.
If the formulas have not actually been executed in Excel or another calculation
environment, label numerical totals UNVERIFIED and provide the formulas instead.
INDEPENDENT CONTROL FORMULAS
Controls must test the model; do not rely only on a formula that has already forced
the result with MAX/MIN.
Include:
- first-period interest recomputation
- Payment = Principal + Interest for each regular period
- sum of principal = Principal - Balloon
- final balance = contractual Balloon/Residual
- maximum principal reduction not exceeded in any row
- no unexplained gap between expected and actual ending balance
- row count = contractual number of payments
OUTPUT
1. Assumptions / Inputs block
2. Excel column structure:
Period | Payment Date | Opening Balance | Payment | Principal | Interest | Closing Balance
3. Exact Excel formulas for Periods 1 and 2 and the final-period cap
4. Independent control formulas and summary formulas for total cash payments,
total interest and total principal
5. If you actually execute the formulas, you may also show verified numerical results.
Otherwise do not manually simulate all periods in prose.
Replace the bracketed values with your loan terms, then paste the prompt into ChatGPT.
I need to model principal prepayments on an existing loan amortization schedule in Excel.
BASE LOAN TERMS
[Paste the exact terms from the Standard Amortization prompt, including rate convention,
payment timing, number of payments, balloon, fees and day-count rule.]
PREPAYMENT TERMS FROM THE CONTRACT
- Are principal prepayments permitted? [yes/no]
- Prepayment fee / penalty: [amount/rule or none]
- How does the lender treat a prepayment?
[A = keep regular payment and shorten term /
B = recast regular payment over remaining term /
C = other — state rule]
- Timing of extra principal relative to the scheduled payment:
[after scheduled payment / before scheduled payment / exact date]
- Any minimum/maximum prepayment rule: [state]
EXTRA PRINCIPAL PAYMENTS
Period or Date | Amount
[paste rows]
MODEL STRUCTURE
Use ONE shared Inputs block (or one Inputs sheet) for:
- loan terms
- balloon/residual
- prepayment amounts/rules
- baseline schedule
- prepayment schedule
- summary
If you use named ranges, prefer workbook-level names so Baseline, Prepay and Summary
all reference the same inputs.
CALCULATION AND VERIFICATION RULES
1. Use Excel formulas as the calculation engine. Do NOT manually calculate multi-period
balances, cumulative interest or payoff timing in prose.
2. Build TWO complete Excel schedules from identical shared inputs:
- Baseline with no extra payments
- Prepayment scenario
3. Build every period sequentially. Do not skip blocks of periods or reconstruct later
balances using manually approximated powers or rounded intermediate values.
4. Keep full formula precision. Do not hard-code displayed rounded values into later rows.
5. Follow the supplied prepayment treatment exactly:
- Under Treatment A, keep the original contractual PMT fixed and shorten the term.
- Under Treatment B, recalculate PMT only after the prepayment using remaining balance,
current applicable rate, remaining contractual periods, Balloon and payment timing.
- Under Treatment C, follow the rule I supply.
6. Apply each extra payment at the exact timing I supply. Do not assume every contract
applies a prepayment after the scheduled payment.
7. Apply extra principal only once and never reduce Closing Balance below Balloon/Residual.
8. Scheduled principal must be capped at:
MAX(0,Opening Balance-Balloon)
and extra principal must be capped at the remaining reducible principal after the
scheduled principal is applied.
9. Payoff Period = the first period in which Closing Balance equals the contractual
Balloon/Residual within the defined rounding tolerance.
The prior period must have Closing Balance greater than Balloon/Residual.
10. Derive Baseline Total Interest and Prepayment Total Interest by summing the completed
Excel schedules. Do not estimate them independently.
11. Interest Saved = Baseline Total Interest - Prepayment Total Interest.
Show prepayment fees separately. Net Financing-Cost Saving = Interest Saved - Fees.
12. Do not invent rates, fees, lender rules, dates or contract conventions.
13. If formulas have not actually been executed in a calculation environment, label all
numerical totals UNVERIFIED rather than estimating them.
INDEPENDENT CONTROL FORMULAS
Include formulas that verify:
- baseline principal reconciliation = Principal - Balloon
- prepayment principal reconciliation = Principal - Balloon
- Closing Balance never falls below Balloon/Residual
- Scheduled Principal + Extra Principal never exceeds Opening Balance - Balloon
- each requested extra payment appears in the correct Period/Date and correct amount
(subject only to an explicit contractual cap or an already-reached residual balance)
- no extra payment appears in any unrequested period/date
- payoff period is the first row at Balloon/Residual
- the row immediately before payoff is above Balloon/Residual
- total interest in each schedule comes from SUM of schedule interest
- Interest Saved comes only from the two schedule totals
OUTPUT
1. Shared Inputs structure
2. Excel structure and formulas for the complete baseline schedule
3. Excel structure and formulas for the complete prepayment schedule
4. Exact formulas for:
- Period 1
- a normal period
- each prepayment period
- the final payoff/residual period
5. Independent control formulas for all checks above
6. Comparison formulas for payoff period/date, total interest, interest saved, fees and
net financing-cost saving
7. Do not manually simulate all periods in prose. If you execute the formulas, you may
show verified numerical results; otherwise return formulas and UNVERIFIED totals only.
Use one shared Inputs block for both schedules. Let Excel calculate balances and savings, and verify each requested prepayment by period/date and amount.
I need to model a step-rate / variable-rate loan in Excel.
BASE TERMS
- Principal: [ ]
- Contractual maturity / total periods: [ ]
- Payment frequency: [ ]
- First payment date: [ ]
- Payment timing: [type 0 end / type 1 beginning]
- Balloon at maturity: [ ]
- Rate convention: [nominal annual / effective annual / index + margin / other]
- Day-count / irregular-period rule: [state]
RATE RESET SCHEDULE
Effective Period/Date | Annual Rate or Index+Margin | Rate Convention
[paste rows]
PAYMENT RESET RULE FROM THE CONTRACT
Choose/provide the actual rule:
[A = recalculate PMT only at each listed rate reset over the remaining contractual term
B = payment stays fixed and maturity/balloon changes
C = payment cap/floor or other rule — describe]
MODEL STRUCTURE
Use ONE shared Inputs block (or one Inputs sheet) for the base loan terms, Balloon,
rate-reset schedule and payment-reset rule. If you use named ranges, prefer workbook-level
names so the full schedule and reset summary reference the same source inputs.
CALCULATION AND VERIFICATION RULES
1. Use Excel formulas as the calculation engine. Do not manually simulate the full
variable-rate schedule in prose.
2. Build every period sequentially. Each Opening Balance must link to the prior period's
Closing Balance; do not skip periods when deriving a reset balance.
3. Keep full formula precision and round only displayed values.
4. Convert each rate to the periodic rate according to its supplied convention.
5. Apply a new rate beginning in the exact supplied effective period/date.
6. Follow the supplied payment-reset rule exactly. Do not propose or implement alternative,
"more general", rolling or hypothetical payment mechanics unless I explicitly ask for them.
7. Under Rule A:
- Recalculate PMT ONLY in a period explicitly listed as a rate-reset period.
- At that reset use:
=-PMT(NewPeriodicRate,RemainingPeriods,OpeningBalance,-Balloon,PaymentType)
- Between listed reset periods, carry forward the payment calculated at the most recent
reset unchanged.
- Never re-amortize or recalculate PMT every period.
8. Under Rule B, keep payment fixed and calculate the resulting contractual
maturity/balloon effect.
9. Under Rule C, implement only the cap/floor/other mechanics I supply.
10. Do not interpolate, forecast or invent a missing future floating rate. Use only a
user-supplied scenario/rate and label it as an assumption.
11. Keep a Rate Applied column in every row so each reset can be audited.
12. Do not allow scheduled principal to reduce Closing Balance below Balloon/Residual.
13. At every reset, add independent controls:
- prior Closing Balance = new Opening Balance
- new rate starts in the correct period/date
- reset PMT is calculated only in the reset period
- the same reset PMT is carried unchanged through subsequent non-reset periods
- remaining-period count is correct under Rule A
- principal reduction does not breach Balloon/Residual
14. Control formulas must test the pre-cap/pre-floor calculation independently.
Do not use Closing Balance after MAX/MIN as the sole evidence that the
Balloon/Residual floor was respected.
15. Principal reconciliation must equal Principal - Balloon, not Principal,
unless Balloon = 0.
16. Derive summary totals from the completed Excel schedule, not from separate manual arithmetic.
17. Final balance must match the contractual Balloon/Residual; do not force zero when the
contract specifies otherwise.
18. If formulas have not actually been executed in Excel or another calculation environment,
do not claim numerical totals are verified. Return the formulas and label totals UNVERIFIED.
OUTPUT
1. Shared Inputs structure
2. Excel schedule structure:
Period | Date | Opening Balance | Rate Applied | Payment |
Interest | Principal | Closing Balance
3. Exact Excel formulas for:
- Period 1
- one normal pre-reset period
- the first reset period
- one post-reset NON-RESET period that must carry forward the reset PMT unchanged
- final Balloon/Residual period
4. Reset-summary table driven from schedule formulas
5. Independent control formulas for:
- reset timing
- balance continuity
- remaining periods
- reset PMT
- unchanged PMT between reset periods
- pre-cap/pre-floor principal reduction
- Principal - Balloon reconciliation
- final balance
6. Do not manually simulate all periods in prose. Show numerical totals only if actually executed.
Use the exact reset rule from the loan agreement. Under Rule A, recalculate PMT only at listed reset periods and carry that payment unchanged until the next reset.
| Period | Date | Payment | Principal | Interest | Balance |
|---|---|---|---|---|---|
| 1 | 01/08/2025 | £2,830.46 | £2,205.46 | £625.00 | £147,794.54 |
| 2 | 01/09/2025 | £2,830.46 | £2,214.65 | £615.81 | £145,579.89 |
| … | … | … | … | … | … |
| 59 | 01/06/2030 | £2,830.46 | £2,806.96 | £23.50 | £2,833.96 |
| 60 | 01/07/2030 | £2,830.46 | £2,818.65 | £11.81 | +£15.30 ⚠ |
| Period | Date | Payment | Principal | Interest | Balance |
|---|---|---|---|---|---|
| 1 | 01/08/2025 | £2,830.69 | £2,205.69 | £625.00 | £147,794.31 |
| 2 | 01/09/2025 | £2,830.69 | £2,214.88 | £615.81 | £145,579.44 |
| … | … | … | … | … | … |
| 59 | 01/06/2030 | £2,830.69 | £2,807.24 | £23.44 | £2,818.94 |
| 60 | 01/07/2030 | £2,830.69 | £2,818.94 | £11.75 | £0.00 ✓ |
For a regular end-of-month annuity: Periodic rate = 5.00% / 12 PMT = -PMT(5%/12,60,150000) = £2,830.685046... → £2,830.69 display Period 1 interest = £150,000 × 5%/12 = £625.00 Total interest = £19,841.10 Total principal = £150,000.00 Ending balance = £0.00 ✓ Use the unrounded PMT formula internally; do not hard-code £2,830.69 into every calculation.
| Period | Reg. PMT | Extra PMT | Principal | Interest | Balance |
|---|---|---|---|---|---|
| 1 | £2,830.69 | £0 | £2,205.69 | £625.00 | £147,794.31 |
| 12 | £2,830.69 | £0 | £2,308.91 | £521.77 | £122,916.71 |
| 24 | £2,830.69 | £0 | £2,427.04 | £403.65 | £94,447.79 |
| … | … | … | … | … | … |
| 60 | £2,830.69 | £0 | £2,818.94 | £11.75 | £0.00 — 60 months |
| Period | Reg. PMT | Extra PMT | Principal | Interest | Balance |
|---|---|---|---|---|---|
| 1 | £2,830.69 | £0 | £2,205.69 | £625.00 | £147,794.31 |
| 12 | £2,830.69 | £5,000 | £7,308.91 | £521.77 | £117,916.71 |
| 24 | £2,830.69 | £10,000 | £12,448.85 | £381.84 | £79,191.98 |
| … | … | … | … | … | … |
| 54 | £2,287.23 | £0 | £2,277.74 | £9.49 | £0.00 ✓ 6 months early |
Assumption for this example: prepayments are allowed, no penalty, applied after the scheduled payment, regular PMT stays fixed and the term shortens. Baseline interest: £19,841.10 / 60 periods With prepayments: £17,313.54 / 54 periods Interest saved: £2,527.57 ✓ Term shortened: 6 periods ✓ If the contract recasts the payment or charges a prepayment fee, these results change.
| Period | Rate | Payment | Principal | Interest | Balance |
|---|---|---|---|---|---|
| 1 | 5.00% | £2,830.69 | £2,205.69 | £625.00 | £147,794.31 |
| 12 | 5.00% | £2,830.69 | £2,308.91 | £521.77 | £122,916.71 |
| 25 | 5.75% | £2,830.69 | £2,378.12 | £452.56 | £92,069.67 |
| 60 | 5.75% | £2,830.69 | £2,811.22 | £19.46 | £1,250.66 ✗ |
| Period | Rate | Payment | Principal | Interest | Balance |
|---|---|---|---|---|---|
| 1 | 5.00% | £2,830.69 | £2,205.69 | £625.00 | £147,794.31 |
| 24 | 5.00% | £2,830.69 | £2,427.04 | £403.65 | £94,447.79 |
| 25 | 5.75% | £2,862.60 | £2,410.04 | £452.56 | £92,037.76 |
| … | … | … | … | … | … |
| 60 | 5.75% | £2,862.60 | £2,848.95 | £13.65 | £0.00 ✓ |
Example assumes Rule A: payment is reset over the remaining 36 periods. Remaining balance after Pd 24: £94,447.79 New periodic rate = 5.75% / 12 = 0.4791667% New PMT = -PMT(5.75%/12,36,94447.79) = £2,862.60 display Period 25: Interest = £94,447.79 × 5.75%/12 = £452.56 Principal = £2,862.60 - £452.56 = £2,410.04 Ending balance at Period 60 = £0.00 ✓
Identify exactly how the contract states the rate. For a nominal annual rate compounded monthly, 5%/12 is the monthly periodic rate. For an effective annual rate, use the equivalent periodic-rate conversion instead. Do not infer the convention from the percentage alone.
Before modelling a prepayment, specify whether the lender keeps the payment fixed, recasts it, charges a penalty, or uses another rule, and state when the extra principal is applied. Compare against an identical no-prepayment baseline.
For rate resets, provide both the rate schedule and the contractual payment-reset rule. Under Rule A, recalculate PMT only at an explicitly listed reset period and carry that payment unchanged until the next reset; do not re-amortize every period.
Recompute at least: (1) first-period interest from opening balance and periodic rate; (2) Payment = Principal + Interest; (3) sum of principal reconciles to principal less any contractual balloon; (4) final balance matches the contractual residual. Controls should be independent of the formulas they test. Also verify rate timing, dates, fees, prepayment/reset rules and any contractual balloon/residual.