ASC 842 and IFRS 16 lease schedule in Excel with Copilot, Claude or ChatGPT

A lessee lease schedule in Excel starts with one number: the present value of the remaining lease payments, discounted at the rate implicit in the lease or, when that rate cannot be readily determined, at the lessee's incremental borrowing rate. For a five-year office lease with annual payments of $120,000 in arrears and a 6% discount rate, that opening lease liability is $505,483.65, and every later line of the schedule (interest, cash, closing balance, right-of-use asset and expense) follows from it.

Copilot in Excel, Claude for Excel and ChatGPT for Excel can all build this schedule from a plain-language request inside a live workbook. The useful split of work is that the AI writes the formulas and the layout, while the accountant supplies the lease terms, chooses the discount rate, decides the classification and checks the result against an independent calculation. The expected figures for the example below were computed separately so readers can test whatever the assistant produces.

The schedule also differs by framework. Under IFRS 16 every recognized lease follows a single lessee model with interest and depreciation, which front-loads expense. Under ASC 842 a finance lease looks much the same, while an operating lease produces one straight-line lease cost and a right-of-use asset whose amortization is the balancing figure. A single workbook can carry both views from the same liability table.

··········

WHAT A LESSEE LEASE SCHEDULE IN EXCEL HAS TO PRODUCE.

A lessee needs the schedule for the opening entry at commencement, for each period's interest and cash entries, for the current and noncurrent split of the lease liability on the balance sheet, and for the maturity analysis that lease disclosures require. Controllers also reuse it at every remeasurement, because a change in lease term or in an index-linked payment means rebuilding the remaining cash flows and discounting them again.

Under ASC 842 the lease liability and the right-of-use asset are both measured initially at the present value of the lease payments. Lease payments include fixed payments, variable payments that depend on an index or a rate, and the exercise price of a purchase option the lessee is reasonably certain to exercise; other variable payments are generally excluded. The discount rate is the rate implicit in the lease if readily determinable, otherwise the incremental borrowing rate, and private companies may elect a risk-free rate. IFRS 16 uses the same liability logic with its own rate rules and requires recognition of leases with a term of more than 12 months unless the underlying asset is of low value. The background to these measurement rules is covered in how leases are recognized and measured under IFRS 16 and ASC 842.

A complete schedule therefore has three blocks: an input block (commencement date, term, payment amount and timing, discount rate, initial direct costs, incentives received, prepaid rent), a liability table by period, and an asset and expense table that differs by framework and classification. The liability table is mechanically identical to a loan amortization schedule, which is why the same Excel functions apply.

··········

COPILOT, CLAUDE AND CHATGPT IN EXCEL: DOCUMENTED REQUIREMENTS FOR LEASE WORK.

The requirements below were checked on the vendors' official documentation on November 6, 2026. Plans and availability change, so a finance team should confirm them for its own tenant before standardizing a process.

........

Tool	Access as documented	Platforms	Documented limits relevant to a lease schedule
Copilot in Excel	Microsoft 365 Premium, a commercial Microsoft Copilot subscription, an eligible business or enterprise plan, or Personal or Family with AI credits	Edit mode on Windows, Mac and the web	Editing needs Calculation Options set to Automatic; Strict Open XML files must be saved as .xlsx
Claude for Excel	Claude Pro, Max, Team and Enterprise plans	Excel on the web, Windows and Mac (minimum builds apply)	Data tables, macros and VBA are unsupported; not recommended for audit-critical calculations without verification
ChatGPT for Excel	Generally available across all plans per the May 5, 2026 update	Excel add-in	Asks permission before editing; edits can be undone; Enterprise and Edu access off by default

........

Microsoft states that Copilot in Excel can run on Claude or GPT models, and that the model switcher requires a commercial Microsoft Copilot or Microsoft 365 Premium subscription, with enterprise admins first enabling Anthropic as a Microsoft sub-processor. Anthropic documents that Claude for Excel warns before overwriting existing data and that answers carry cell-level citations. OpenAI describes ChatGPT for Excel as running calculations directly in Excel so users can audit formulas, and lists among its stated limits that complex formulas or edge cases may still need manual refinement.

For a lease schedule these differences are practical. Claude's documented lack of support for Excel data tables means a sensitivity grid on the discount rate should be built with ordinary formulas. Copilot will not edit a workbook left on manual calculation, which is common in large close files. All three tools should be pointed at a working copy of the lease workbook first.

··········

WORKED EXAMPLE: A FIVE-YEAR OFFICE LEASE SCHEDULE IN EXCEL.

The dataset below is illustrative. It describes a single office lease with no initial direct costs, no incentives, no prepaid rent and no renewal or purchase option, so the right-of-use asset equals the lease liability at commencement.

........

Input (illustrative)	Value
Commencement date	January 1, 2027
Lease term	5 years
Payment	$120,000 per year, paid at the end of each year
Discount rate (incremental borrowing rate)	6% per year
Initial direct costs, incentives, prepaid rent	None
Classification under ASC 842	Operating lease (also shown as finance lease)

........

In Excel the opening liability is one formula. PV(rate, nper, pmt, [fv], [type]) returns the present value of a series of equal payments, with type 0 or omitted for payments at the end of the period and 1 for payments at the beginning, and with cash paid out entered as a negative number. With the inputs in a named block, the formulas look like this:

Opening lease liability:   =PV(6%, 5, -120000, 0, 0)        returns 505,483.65
Interest for the year:     =ROUND(OpeningBalance * 6%, 2)
Closing balance:           =OpeningBalance + Interest - Payment
Final-year check:          =ROUND(ClosingBalance_Year5, 2) = 0

A request to the assistant should state every term explicitly and ask for formulas rather than typed values, so the schedule stays live when an input changes:

Build a lessee lease schedule on a new sheet called "Lease_2027".
Inputs in B2:B6: commencement 1/1/2027, term 5 years, annual payment
120,000 paid at year end, discount rate 6%, no initial direct costs.
Use PV for the opening liability. For each year show opening balance,
interest (rounded to cents), payment, closing balance and principal
reduction. Add a check cell that shows TRUE when the year 5 closing
balance rounds to zero. Use formulas that reference the input cells.
Do not hard-code results.

A second prompt adds the framework views:

Next to the liability table add two blocks. Block 1, IFRS 16 / ASC 842
finance lease: right-of-use asset depreciated straight line over 5 years,
total expense = interest + depreciation. Block 2, ASC 842 operating lease:
single lease cost equal to the straight-line payment, right-of-use asset
amortization = lease cost minus interest, and closing ROU asset. Add a
row that totals each block over the lease term.

··········

THE EXPECTED LEASE LIABILITY SCHEDULE TO CHECK THE AI AGAINST.

These figures were computed independently, with interest rounded to the cent each year. The year 5 interest of $6,792.45 brings the liability exactly to zero, so no rounding plug is needed in this example.

........

Year	Opening liability	Interest at 6%	Payment	Closing liability	Principal reduction
2027	505,483.65	30,329.02	120,000.00	415,812.67	89,670.98
2028	415,812.67	24,948.76	120,000.00	320,761.43	95,051.24
2029	320,761.43	19,245.69	120,000.00	220,007.12	100,754.31
2030	220,007.12	13,200.43	120,000.00	113,207.55	106,799.57
2031	113,207.55	6,792.45	120,000.00	0.00	113,207.55
Total		94,516.35	600,000.00		505,483.65

........

At the commencement balance sheet date the current portion of the lease liability is the 2027 principal reduction of $89,670.98, and the noncurrent portion is $415,812.67. The same split rolls forward each year from the next row of the table, which is a quick test of whether the assistant has wired the classification to the schedule or typed it in.

The expense pattern is where IFRS 16 and an ASC 842 operating lease diverge. The right-of-use asset depreciates by $101,096.73 a year under IFRS 16 and under an ASC 842 finance lease. Under an ASC 842 operating lease the annual cost stays at $120,000 and the asset amortizes by the principal reduction in the liability table.

........

Year	IFRS 16 / ASC 842 finance: total expense	IFRS 16 / finance: ROU asset at year end	ASC 842 operating: lease cost	ASC 842 operating: ROU asset at year end
2027	131,425.75	404,386.92	120,000.00	415,812.67
2028	126,045.49	303,290.19	120,000.00	320,761.43
2029	120,342.42	202,193.46	120,000.00	220,007.12
2030	114,297.16	101,096.73	120,000.00	113,207.55
2031	107,889.18	0.00	120,000.00	0.00
Total	600,000.00		600,000.00

........

Both models recognize $600,000 over the term, equal to the undiscounted payments, because there are no initial direct costs or incentives. The difference is timing: the front-loaded model shows $11,425.75 more expense than the straight-line cost in 2027 and $12,110.82 less in 2031. Under an ASC 842 operating lease the cash payments are classified within operating activities. When a lease has escalating rent, the straight-line cost and the asset amortization no longer match the payment pattern, a case covered in how step leases and rent escalation clauses are recognized under IFRS 16 and ASC 842.

··········

LEASE JOURNAL ENTRIES FOR 2027 UNDER IFRS 16 AND ASC 842.

The schedule only helps if it drives the entries. Under IFRS 16 (and an ASC 842 finance lease) the 2027 entries are the commencement entry, interest accretion, the payment and depreciation.

........

IFRS 16 entry, 2027	Debit	Credit
Right-of-use asset	505,483.65
Lease liability		505,483.65
Interest expense on lease liability	30,329.02
Lease liability		30,329.02
Lease liability	120,000.00
Cash		120,000.00
Depreciation expense, right-of-use asset	101,096.73
Accumulated depreciation, right-of-use asset		101,096.73
Total	756,909.40	756,909.40

........

Under an ASC 842 operating lease the commencement entry is the same, and the year-end entry records a single lease cost while reducing the liability and the asset by the same principal amount.

........

ASC 842 operating lease entry, 2027	Debit	Credit
Right-of-use asset	505,483.65
Operating lease liability		505,483.65
Operating lease cost	120,000.00
Operating lease liability	89,670.98
Cash		120,000.00
Right-of-use asset		89,670.98
Total	715,154.63	715,154.63

........

Asking the assistant to produce these entries from the schedule, with each amount referenced to a schedule cell, gives the reviewer a direct trail from journal to calculation. The review approach for AI-drafted entries is set out in journal entries with Copilot, Claude or ChatGPT: draft and check before posting.

··········

REVIEWING AN AI-BUILT LEASE SCHEDULE: ERRORS TO TEST FOR.

The most frequent mechanical error is payment timing. Office leases often require rent in advance, and an assistant given an ambiguous prompt may default to payments in arrears, or the reverse. With payments at the beginning of each year the same inputs give a present value of $535,812.67 instead of $505,483.65, so the type argument of PV and the period in which the first payment reduces the liability need an explicit check.

The second group of errors concerns the rate and the period. A monthly lease needs a monthly rate and a number of months, and an annual rate dropped into a monthly schedule overstates each period's interest roughly twelvefold. An assistant may also round each year's interest differently from the close process, leaving a small residual balance in the final period; the zero-balance check cell catches it.

The third group is judgment the AI cannot supply. Classification under ASC 842, the lease term including renewal options the lessee is reasonably certain to exercise, the incremental borrowing rate, the treatment of non-lease components and the use of short-term or low-value exemptions are accounting decisions that need documented support. The assistant can lay out the five ASC 842 classification criteria as a checklist, but the answers come from the contract and the asset's economic life and fair value.

A practical review sequence for any schedule an assistant builds:

........

Check	How to test it
Opening liability	Recompute with PV in a separate cell and compare
Payment timing	Confirm the type argument and the first payment period against the lease
Rate and periods	Confirm rate per period and number of periods use the same unit
Final balance	Year-end closing liability rounds to zero
Totals	Total interest plus principal equals total payments
Entries	Debits equal credits; each amount links to a schedule cell
Classification	Documented outside the AI, with the criteria and evidence

........

The liability mechanics are the same as the ones tested in a loan amortization schedule in Excel with Copilot, Claude or ChatGPT, so a team that already uses a reviewed loan template can reconcile the two.

··········

DATA HANDLING WHEN LEASE CONTRACTS GO INTO AN AI ASSISTANT.

Lease workbooks often hold landlord names, addresses and rent terms that are commercially sensitive. Anthropic documents that Claude for Excel sends conversations to Anthropic, including the workbook content Claude reads and any attached files, that inputs and outputs are deleted on the backend within 30 days except in stated cases, that chat history is stored locally in the browser, and that the add-in does not inherit custom data retention settings or appear in Enterprise audit logs, although sessions are included in the Compliance API for Enterprise organizations that enable it. Anthropic also warns that files from external sources, such as a landlord's or vendor's template, can contain hidden instructions, and recommends using the add-in only with trusted spreadsheets.

Microsoft documents that changes Copilot saves to a workbook are visible to anyone with access to the file, and that Copilot in Excel is governed by the Microsoft Copilot Chat admin settings and cannot be switched off separately. OpenAI's statement that data shared with ChatGPT Enterprise is not used for training by default is a general ChatGPT Enterprise commitment; the ChatGPT for Excel page carries no add-in-specific data-handling statement, so teams should confirm the terms for their workspace.

··········

ASC 842 AND IFRS 16 LEASE SCHEDULES WITH AI: WHERE THE ACCOUNTANT DECIDES.

The liability side of a lease schedule is deterministic once the inputs are fixed: present value at the chosen rate, interest on the opening balance, payment, closing balance. All three assistants are documented as able to build models from a plain-language request, and the example's figures ($505,483.65 opening liability, $94,516.35 total interest, $600,000 total expense under every model) give a reviewer a fixed target to compare against.

The inputs and the model choice stay with the accountant. Classification under ASC 842, the lease term, the discount rate, payment timing and the treatment of incentives, initial direct costs and variable payments all change the schedule, and none of them can be inferred reliably from a prompt. A workable control is to keep those decisions in a documented input block, let the AI generate the formulas from it, and approve the result only after the independent PV check, the zero-balance check and the balanced journal entries all pass.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org