Loan amortization schedule in Excel with Copilot, Claude or ChatGPT: PMT, IPMT and checks

A loan amortization schedule splits every payment on a fixed-rate term loan into interest and principal and tracks the balance that remains after each period. Excel already has the functions that do the arithmetic: PMT returns the level payment, IPMT and PPMT return the interest and principal in a given period, and CUMIPMT and CUMPRINC add those amounts over a range of periods. Copilot in Excel, Claude for Excel and ChatGPT for Excel can build the whole table from a short instruction, but the result is only as reliable as the inputs and the conventions behind it, so the reviewer needs an independent figure to compare against.

For the illustrative loan used below, a $250,000 note at 7.20% fixed annual interest repaid in 60 monthly payments, the correct level payment is $4,973.92, interest in the first month is $1,500.00, and total interest over the life of the loan is $48,435.49 when each month is rounded to the cent and the last payment absorbs the rounding difference. A schedule that shows a monthly payment of about $18,282 has used the annual rate against a monthly term, which is the most frequent error in AI-built schedules and is easy to catch once the expected payment is known.

The schedule feeds several accounting outputs at once. Monthly interest expense comes from the interest column, the split between the current and noncurrent portions of the debt comes from the principal due in the next twelve months, and the cash flow statement and the debt footnote reuse the same figures. The background on how the liability itself is recognized and tested against covenants is in our article on loans payable, interest accrual, amortization schedules and covenants.

··········

WHICH AI TOOLS CAN BUILD A LOAN AMORTIZATION SCHEDULE IN EXCEL.

All three assistants work inside the workbook, which keeps the formulas visible and auditable. Their documented requirements differ, and the table below reflects the official vendor pages as verified on November 5, 2026. None of the vendors publishes a row limit for this kind of task; a 60-row or 360-row schedule is small by Excel standards.

........

Tool	Documented access	Where it runs	Documented limits and warnings
Copilot in Excel	Microsoft 365 Premium, Personal or Family with AI credits, commercial Copilot, or Copilot Chat-eligible business plans	Windows, Mac, web; chat on mobile	Editing needs Calculation Options set to Automatic; review everything before relying on it
Claude for Excel	Claude Pro, Max, Team and Enterprise	Excel on the web, Windows (Microsoft 365), Mac 16.46 or later	No data tables, macros or VBA; not for audit-critical calculations without verification
ChatGPT for Excel	Generally available across ChatGPT plans; off by default in Enterprise, Edu and Teacher workspaces	Excel add-in	Complex formulas or edge cases may need manual refinement; asks permission before editing

........

Microsoft states that Copilot in Excel offers edit, plan and chat modes on Windows, Mac and the web, with edit mode changing the workbook directly and plan mode proposing a plan before editing. Microsoft also tells users to avoid relying on Copilot for decisions in sensitive areas such as finance and to review, edit and verify what it creates. A SharePoint file that is checked out, or a workbook saved in Strict Open XML format, can produce an unsupported file state error on Windows and Mac.

Anthropic documents that Claude for Excel answers with cell-level citations, updates assumptions while keeping formula relationships intact, and warns before overwriting existing data. Its documentation lists data tables as unsupported, which affects a common way of building rate or term sensitivity grids on top of the schedule, so that part has to be built with ordinary formulas or by hand. OpenAI describes ChatGPT for Excel as building and updating live models directly in the workbook, with calculations running in Excel so that results can be traced.

··········

THE EXCEL FUNCTIONS BEHIND AN AMORTIZATION SCHEDULE.

The functions share the same arguments, and Microsoft's documentation repeats one rule on every page: the units of rate and nper must match. For monthly payments the annual rate is divided by 12 and the term is expressed in months. Cash paid out is returned as a negative number, so a payment on a loan received appears in parentheses unless the principal is entered as a negative value or the formula is preceded by a minus sign.

........

Function	Syntax	Returns
PMT	PMT(rate, nper, pv, [fv], [type])	Level payment of principal and interest
IPMT	IPMT(rate, per, nper, pv, [fv], [type])	Interest in period per
PPMT	PPMT(rate, per, nper, pv, [fv], [type])	Principal in period per
CUMIPMT	CUMIPMT(rate, nper, pv, start_period, end_period, type)	Total interest between two periods
CUMPRINC	CUMPRINC(rate, nper, pv, start_period, end_period, type)	Total principal between two periods

........

Three details from the documentation cause most of the wrong answers. PMT covers principal and interest only and excludes taxes, reserve payments and fees, so a loan with an escrow or servicing fee needs a separate column. The type argument switches between payments at the end of the period (0, the default) and at the beginning (1); for CUMIPMT and CUMPRINC it is required, and any value other than 0 or 1 returns #NUM!. The same two functions also return #NUM! when rate, nper or pv is zero or negative, which means the principal must be entered as a positive amount and the results come back negative.

The functions work on unrounded amounts. A lender's statement rounds each payment and each interest charge to the cent and adjusts the final payment, so a schedule built only with IPMT and PPMT will drift from the bank's figures by a few cents. Both versions are defensible, but the workbook has to state which one it follows, and the reconciliation to the lender's statement has to use the same convention.

··········

WORKED EXAMPLE: A $250,000 TERM LOAN AT 7.20%.

The data below is illustrative. It describes a five-year equipment term loan with level monthly payments in arrears, a fixed rate and no fees. The inputs sit in cells B2 to B5 of a sheet named Loan, and the schedule starts on row 9.

........

Input	Cell	Value
Principal	B2	250,000.00
Annual interest rate	B3	7.20%
Number of monthly payments	B4	60
Payment timing (type)	B5	0 (end of month)

........

A schedule that matches a lender's statement uses a rounded payment and computes each month's interest from the opening balance, rounded to the cent. The core formulas for row 9, the first period, can be written as follows and copied down to row 68; the last row replaces the payment with opening balance plus interest so that the loan closes at exactly zero.

Payment (B7):        =ROUND(-PMT(B3/12, B4, B2, 0, B5), 2)
Opening balance B9:  =B2
Interest C9:         =ROUND(PRODUCT(B9, $B$3/12), 2)
Principal D9:        =$B$7 - C9
Closing balance E9:  =B9 - D9
Opening balance B10: =E9
Check (unrounded):   =-IPMT($B$3/12, A9, $B$4, $B$2)

The request to the assistant should name the conventions explicitly, because none of them can be inferred from the numbers alone. The same prompt works in Copilot's edit or plan mode, in Claude for Excel and in ChatGPT for Excel.

On the sheet Loan, inputs are in B2:B5 (principal, annual rate,
number of monthly payments, type). Build a monthly amortization
schedule starting on row 9 with columns: period, opening balance,
interest, principal, closing balance. Use a monthly rate of B3/12.
Round the payment and each month's interest to the cent, and make
the final payment equal to opening balance plus interest so the
closing balance is exactly zero. Use formulas only, no hardcoded
values. Add a check row that compares total principal with B2.

The expected values below were computed independently in Python with the same rounding rules, so each row of the AI's schedule can be compared line by line.

........

Period	Payment	Interest	Principal	Closing balance
1	4,973.92	1,500.00	3,473.92	246,526.08
2	4,973.92	1,479.16	3,494.76	243,031.32
3	4,973.92	1,458.19	3,515.73	239,515.59
12	4,973.92	1,263.72	3,710.20	206,909.40
13	4,973.92	1,241.46	3,732.46	203,176.94
59	4,973.92	59.16	4,914.76	4,944.54
60	4,974.21	29.67	4,944.54	0.00

........

The final payment is $4,974.21, which is $0.29 more than the level payment because the cent rounding of the earlier rows leaves a small residual in the balance. Total payments are $298,435.49, of which $250,000.00 is principal and $48,435.49 is interest. The unrounded total implied by PMT is $48,435.42, so a difference of seven cents between the two methods is expected and does not indicate an error.

........

Year	Interest	Principal	Closing balance
1	16,596.44	43,090.60	206,909.40
2	13,389.46	46,297.58	160,611.82
3	9,943.80	49,743.24	110,868.58
4	6,241.71	53,445.33	57,423.25
5	2,264.08	57,423.25	0.00

........

The annual summary is a useful second check because it can be reproduced without the detailed rows. CUMIPMT(B3/12, B4, B2, 1, 12, 0) returns about -16,596.44 for the first year and CUMPRINC(B3/12, B4, B2, 13, 24, 0) returns about -46,297.63 for the second, which agree with the rounded schedule to within a few cents.

··········

FROM THE SCHEDULE TO JOURNAL ENTRIES AND THE BALANCE SHEET.

The schedule supplies the amounts for the monthly entries. At drawdown the company records the cash received and the note payable, and each payment then reduces the liability by the principal column and records the interest column as expense. The entries below use the illustrative loan; debits equal credits in each entry.

........

Entry	Account	Debit	Credit
Drawdown	Cash	250,000.00
Drawdown	Notes payable		250,000.00
Payment 1	Interest expense	1,500.00
Payment 1	Notes payable	3,473.92
Payment 1	Cash		4,973.92
Reclass at year 1 end	Notes payable, noncurrent	46,297.58
Reclass at year 1 end	Current portion of notes payable		46,297.58

........

At the end of the first year the outstanding balance is $206,909.40. The current portion is the principal due in periods 13 to 24, $46,297.58, and the remaining $160,611.82 is noncurrent. The reclassification entry assumes the full note was recorded as noncurrent at drawdown; a company that already carries a current portion adjusts only the difference. If the payment date falls before the period end, no interest accrual is needed for that month, while a payment date inside the month requires an accrual for the days elapsed, which the simple schedule above does not compute.

Assistants are good at drafting these entries from the schedule, and the same review applies as for any AI-prepared entry, as described in our guide to drafting and checking journal entries with Copilot, Claude or ChatGPT. For the presentation of the interest paid in the cash flow statement, which differs between IFRS and US GAAP, see how interest and dividends are classified in cash flows under IAS 7 and ASC 230.

··········

COMMON ERRORS IN AI-BUILT AMORTIZATION SCHEDULES.

The rate and term mismatch produces the largest error. Using 7.20% as the periodic rate with 60 monthly periods gives a payment of about $18,282.06 instead of $4,973.92, and the schedule still balances to zero, so a check on the closing balance alone will not reveal it. Comparing the first month's interest with principal multiplied by the annual rate divided by 12, which gives $1,500.00 here, catches it immediately.

Payment timing is the second source of differences. With type set to 1 the payment drops to about $4,944.26 because each payment is made one month earlier; that is correct for leases and some rental agreements and wrong for a standard term loan paid in arrears. Sign handling is the third: an assistant that wraps PMT in ABS or flips the sign of the principal can produce a schedule with the right numbers and the wrong direction in the downstream formulas, which surfaces later as an interest expense entry with the opposite sign.

Hardcoded values are a quieter problem. An assistant asked to fill a schedule may write the computed numbers rather than formulas, which leaves a table that looks correct but does not update when the rate or term changes. A quick test is to change the rate in B3 to 7.30% and confirm that every row recalculates. For tracing formulas that an assistant has written, the techniques in our guide to auditing and explaining Excel formulas with Copilot, Claude or ChatGPT apply directly to this schedule.

Loan terms outside the simple annuity are often ignored. Variable rates, interest-only periods, balloon payments, day-count conventions such as actual/360, origination fees and prepayments all change the schedule, and the assistant will not know about them unless the prompt includes them. Under both IFRS and US GAAP, fees paid to the lender are generally reflected in the effective interest rate rather than expensed at drawdown, which means the accounting schedule can differ from the lender's payment schedule even when the cash payments are identical.

··········

REVIEW CHECKLIST AND DATA HANDLING FOR AI LOAN SCHEDULES.

A short set of checks covers most of the risk before the schedule is used for entries or disclosures.

........

Check	Expected result for the example
Level payment equals PMT, rounded	4,973.92
First-month interest equals principal × rate ÷ 12	1,500.00
Sum of principal column equals original principal	250,000.00
Closing balance after the last period	0.00
Year 1 interest equals CUMIPMT for periods 1 to 12	16,596.44 (within cents)
Every row is a formula, inputs only in B2:B5	Changing B3 recalculates all rows

........

Data handling depends on the tool and the account type. Anthropic states that when a user signs in with a Claude account, the add-in sends conversations to Anthropic, including the workbook content Claude reads, that inputs and outputs are deleted on the backend within 30 days except in documented cases, and that chat history is stored locally in the browser. Anthropic also warns that files from external sources can contain hidden instructions that try to manipulate the add-in, which is relevant when a lender or a borrower sends a schedule template. OpenAI states that data shared with ChatGPT Enterprise is not used to train its models by default. Microsoft notes that changes Copilot saves are visible to anyone with access to the file, including coauthors, and that Copilot in Excel is governed by the organization's Copilot Chat settings.

··········

LOAN AMORTIZATION SCHEDULES WITH AI: WHAT TO CONFIRM BEFORE POSTING.

The arithmetic of a fixed-rate amortizing loan is fully determined by four inputs, and Excel's PMT, IPMT, PPMT, CUMIPMT and CUMPRINC functions reproduce it exactly. Copilot in Excel, Claude for Excel and ChatGPT for Excel can lay out the schedule, write the formulas and draft the related entries quickly, and the vendors' own documentation tells users to review the output before relying on it. For the $250,000 example the figures to confirm are a payment of $4,973.92, first-month interest of $1,500.00, total interest of $48,435.49 on the rounded schedule and a current portion of $46,297.58 at the end of year one.

The parts that require judgement sit outside the formulas: which rounding convention matches the lender, how fees and the effective interest rate are treated in the books, how accruals are handled when payment dates do not coincide with period ends, and whether any covenant or refinancing changes the classification of the debt. The same discipline applies to the related fixed-asset side of the transaction, covered in our guide to the capex and depreciation schedule in Excel with Copilot, Claude or ChatGPT.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org