top of page

Prepaid expense amortization schedule in Excel with Copilot or Claude

4 minutes ago
9 min read
Cover image reading Prepaid expense amortization schedule in Excel with Copilot or Claude over a night city skyline, Data Studios original cover

A prepaid expense amortization schedule lists every payment made in advance for a future service, spreads its cost over the service period, and produces two figures each month-end: the amortization to post as expense and the remaining prepaid balance that must agree with the general ledger. Copilot in Excel and Claude for Excel can both build that schedule from a list of invoices, write the monthly formulas and draft the closing journal entry, provided the controller supplies the service dates and checks the output against an independently computed result.


The worked example below uses four illustrative prepaid items with a combined cost of $90,725. For the November 2026 close, the correct amortization is $14,300.00 and the correct prepaid balance at November 30, 2026 is $32,225.00. Those two numbers, computed outside Excel, are the benchmark for whatever formula Copilot or Claude produces.


The schedule is a small piece of work, but it is reviewed at every close and at audit, and its errors are predictable: amortization that starts on the payment date instead of the service start date, a mid-month contract spread over twelve equal months when it touches thirteen calendar months, and small purchases capitalized against the company's own threshold policy. An AI assistant can make each of these errors quickly and with tidy formatting, so the review steps carry as much weight as the prompt.


··········


WHAT A PREPAID EXPENSE AMORTIZATION SCHEDULE DOES AT MONTH-END.


Under US GAAP a payment made before the related service is received is recorded as an asset, usually in a prepaid expenses account within current assets, and is expensed as the benefit is consumed. Software subscriptions, insurance premiums, maintenance contracts and service retainers paid in advance are the most common items. When the benefit is consumed evenly over time, the expense is recognized on a straight-line basis across the service period.


The schedule turns that principle into a repeatable calculation. Each row holds one prepaid item with its payment date, amount, service start date, service end date and amortization method. Each column holds a month-end date. The cells at the intersection contain the amortization for that item in that month, and the final columns show cumulative amortization and the remaining balance. The total of the balance column at any month-end should equal the prepaid expenses balance in the general ledger. A difference points either to an item missing from the schedule or to an entry posted to the account outside the schedule.


Two methods cover most cases. The monthly method divides the cost by the number of service months and charges an equal amount to each calendar month in the service period; it fits contracts that start on the first day of a month. The daily method divides the cost by the number of service days and charges each month for the days of service it contains; it fits contracts that start mid-month. A company policy normally sets a capitalization threshold below which small prepayments are expensed immediately, and the schedule should only contain items above it. Accounting policy for prepaid expenses is covered in more depth in Prepaid expenses: accounting treatment, journal entries, and examples.


··········


COPILOT IN EXCEL AND CLAUDE FOR EXCEL: DOCUMENTED REQUIREMENTS.


Both tools work inside the workbook, and both vendors document plan requirements, supported platforms and limits. The table summarizes the official documentation as verified on November 21, 2026.


........


Item

Copilot in Excel

Claude for Excel

Plans

Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or a Copilot Chat-eligible business or enterprise plan

Claude Pro, Max, Team and Enterprise

Platforms

Windows, Mac, web; edit mode on Windows, Mac and web

Excel on the web, Windows (Microsoft 365 build 16.0.13127.20296 or later), Mac (16.46 or later)

Not supported

Strict Open XML Spreadsheet files; editing needs Automatic calculation

Excel 2016 and 2019 perpetual, Excel on iPad, Excel on Android; data tables, macros and VBA

Workbook editing

Edit, plan and chat modes; generates formulas, charts and PivotTables

Builds models, populates templates, works across tabs, cites cells

Vendor caution

Review and verify anything Copilot creates

Not recommended for audit-critical calculations without verification


........


Microsoft documents three modes for Copilot in Excel. In edit mode Copilot changes the workbook directly, in plan mode it proposes a plan before editing, and in chat mode it answers in the chat pane only. Microsoft also states that Copilot in Excel supports Claude models from Anthropic and GPT models from OpenAI for editing workbooks, and that the model switcher requires a commercial Microsoft Copilot subscription or Microsoft 365 Premium; in enterprise tenants an administrator must first enable Anthropic as a Microsoft subprocessor.


Anthropic documents Claude for Excel as an add-in that answers with cell-level citations, adjusts assumptions while keeping formula relationships intact, traces errors to their source and warns before overwriting existing data. For a prepaid schedule the cell citations are useful during review, because the reviewer can see which input cells the assistant read when it explains a balance.


··········


WORKED EXAMPLE: FOUR PREPAID ITEMS FOR THE NOVEMBER 2026 CLOSE.


The dataset is illustrative. It represents a company with a calendar fiscal year closing November 2026, and a capitalization policy that expenses prepayments under $2,500 immediately. A $1,200 annual subscription paid on January 5, 2026 is therefore expensed when paid and stays out of the schedule.


........


ID

Item

Paid

Amount

Service start

Service end

Method

PP-01

Accounting software annual subscription

2026-02-20

$36,000

2026-03-01

2027-02-28

Monthly

PP-02

General liability insurance policy

2026-06-24

$18,600

2026-07-01

2027-06-30

Monthly

PP-03

Marketing agency retainer, Q4 2026

2026-09-28

$27,000

2026-10-01

2026-12-31

Monthly

PP-04

Equipment maintenance contract

2026-04-10

$9,125

2026-04-15

2027-04-14

Daily


........


The first three items start on the first day of a month, so the monthly method gives $3,000.00, $1,550.00 and $9,000.00 a month. The maintenance contract runs 365 days from April 15, 2026 to April 14, 2027, which makes the daily rate exactly $25.00 and touches thirteen calendar months. April 2026 carries 16 days of service ($400.00), and each later month carries $25.00 times its number of days.


The sheet layout assumed by the prompts places the inputs in columns A to G, the number of service months in column H, the number of service days in column I, and month-end dates for January to December 2026 in J1:U1. Helper formulas for row 2 are shown below; they use EOMONTH, which Microsoft documents as returning the last day of the month a given number of months before or after a start date.


H2: =(YEAR(F2)-YEAR(E2))*12+MONTH(F2)-MONTH(E2)+1
I2: =F2-E2+1
J2 (monthly method): =IF(AND(J$1>=EOMONTH($E2,0),J$1<=EOMONTH($F2,0)),ROUND($D2/$H2,2),0)
J2 (daily method):   =ROUND($D2*MAX(0,MIN(J$1,$F2)-MAX(EOMONTH(J$1,-1)+1,$E2)+1)/$I2,2)
Balance at Nov 30, 2026: =D2-SUM(J2:T2)

The prompts below give the assistant the policy rules and the expected structure instead of asking it to guess them. They are written for Copilot in edit mode or for Claude for Excel, and they can be adapted to a different sheet layout by changing the cell references.


The table in Prepaids!A1:G5 lists prepaid expenses. Build an amortization
schedule on a new sheet named Schedule. Rules:
- Amortization starts on the Service start date, never on the Paid date.
- Method "Monthly": equal amounts in each calendar month from the service
  start month to the service end month.
- Method "Daily": amount x days of service in the month / total service days.
- Columns: one per month-end from 2026-01-31 to 2026-12-31, then
  cumulative amortization and remaining balance at each month-end.
- Use formulas that reference the input cells; do not type values.
- Round each monthly amount to cents.

Using the Schedule sheet, show the November 2026 amortization by item and
the remaining balance at 2026-11-30. Then draft the November journal entry,
debiting Software subscriptions expense, Insurance expense, Marketing expense
and Repairs and maintenance expense, and crediting Prepaid expenses.
Confirm that debits equal credits and cite the cells you used.

··········


EXPECTED RESULT FOR THE NOVEMBER 2026 CLOSE.


The figures below were computed independently of Excel with the same rules. Any schedule built by Copilot or Claude for this dataset should reproduce them to the cent.


........


ID

Nov 2026 amortization

Amortized Jan-Nov 2026

Balance at Nov 30, 2026

Balance at Dec 31, 2026

PP-01

$3,000.00

$27,000.00

$9,000.00

$6,000.00

PP-02

$1,550.00

$7,750.00

$10,850.00

$9,300.00

PP-03

$9,000.00

$18,000.00

$9,000.00

$0.00

PP-04

$750.00

$5,750.00

$3,375.00

$2,600.00

Total

$14,300.00

$58,500.00

$32,225.00

$17,900.00


........


Total cost of $90,725.00 less amortization of $58,500.00 through November leaves $32,225.00, and the December charge of $14,325.00 brings the year-end balance to $17,900.00. The monthly totals for the year are $0.00 in January and February, $3,000.00 in March, $3,400.00 in April, $3,775.00 in May, $3,750.00 in June, $5,325.00 in July and August, $5,300.00 in September, $14,325.00 in October, $14,300.00 in November and $14,325.00 in December.


The November journal entry that follows from the schedule is shown below. It is the entry the second prompt should produce.


........


Account

Debit

Credit

Software subscriptions expense

$3,000.00

-

Insurance expense

$1,550.00

-

Marketing expense

$9,000.00

-

Repairs and maintenance expense

$750.00

-

Prepaid expenses

-

$14,300.00

Total

$14,300.00

$14,300.00


........


Before posting, the $32,225.00 schedule balance is compared with the prepaid expenses balance in the trial balance at November 30. When the two differ, the usual causes are an invoice coded to the prepaid account during the month and never added to the schedule, or a manual entry that relieved the account outside the schedule. The same review discipline applies to any AI-drafted entry, as described in journal entries with Copilot, Claude and ChatGPT: draft and check before posting.


··········


COMMON AI ERRORS IN A PREPAID AMORTIZATION SCHEDULE.


The most frequent error is starting amortization in the payment month. The insurance policy was paid on June 24 but covers July 1, 2026 to June 30, 2027. A formula keyed on the payment date charges June as well, which puts cumulative amortization through November at $9,300.00 instead of $7,750.00 and understates the November 30 balance of that item by $1,550.00. The first prompt names the service start date explicitly to prevent this, and the reviewer should still check the first non-zero month of every row.


The second error concerns mid-month contracts. An assistant asked to "spread over twelve months" may charge the maintenance contract $9,125 / 12 from April onward. Eight months at that rate put cumulative amortization at $6,083.33 instead of $5,750.00, overstating expense by $333.33 through November, and the final charge lands in March 2027 even though service continues to April 14. The daily method, or at least a thirteen-month view, is needed whenever service starts after the first of the month.


Rounding produces the third class of differences. When each monthly amount is rounded to cents, the sum of the rounded charges can differ from the invoice amount by a few cents on items that do not divide evenly. The example avoids this because every amount divides exactly, but a production schedule needs a rule, usually a final-month true-up, so that each item amortizes to exactly zero.


Other points need human judgement and should not be delegated: whether a payment is a prepaid expense at all or a deposit, a capital asset or a lease payment; whether the company's capitalization threshold applies; and whether a cancelled service requires writing off the remaining balance. Month-end accrual work raises similar questions in the opposite direction, covered in month-end accruals in Excel with Copilot, Claude and ChatGPT.


A short review routine catches most problems before the entry is posted. The reviewer confirms that every row starts amortizing in the month of its service start date and that cumulative amortization at the service end month equals the amount paid, recomputes one daily-method row by hand from its daily rate and day counts, agrees the schedule balance to the trial balance with any difference explained, and checks that the cells contain formulas referencing the inputs instead of typed values.


··········


DATA HANDLING FOR COPILOT AND CLAUDE IN FINANCE WORKBOOKS.


Microsoft states that prompts, responses and data accessed through Microsoft Graph are not used to train the foundation models used by Microsoft 365 Copilot, and that Copilot interactions are stored as Copilot activity history that administrators can manage with Microsoft Purview. Microsoft also states that models provided by Anthropic as a subprocessor are currently excluded from the EU Data Boundary, a point for groups that choose a Claude model inside Copilot for European entities. The Copilot in Excel documentation adds that saved Copilot changes are visible to anyone with access to the file.


Anthropic states that Claude for Excel inputs and outputs are deleted on the backend within 30 days of receipt or generation, that chat history is stored locally in the browser, and that conversations are sent to Anthropic when the user signs in with a Claude account. The add-in does not inherit an organization's custom data retention settings, and its activity is not included in Enterprise audit logs unless the Compliance API is enabled. Anthropic also warns against using the add-in with untrusted spreadsheets because of prompt-injection risk, which is a practical reason to build the schedule from the company's own invoice extract and not from a vendor-supplied file. Comparable controls for a recurring schedule are discussed in the year-end close under US GAAP: cut-off, accruals and adjusting entries.


··········


PREPAID AMORTIZATION WITH COPILOT OR CLAUDE: CONTROLS THAT STAY WITH THE CONTROLLER.


Both vendors document that their assistants can write formulas and edit the workbook directly, which covers everything a four-row schedule needs, and both document the plans, platforms and limits that apply. For this dataset the correct November 2026 amortization is $14,300.00 and the correct balance at November 30 is $32,225.00; a schedule that shows anything else contains one of the errors described above.


The parts of the task that depend on facts and policy remain with the accounting team: the service start and end dates taken from the contract, the choice between monthly and daily methods, the capitalization threshold, the treatment of rounding differences and the classification of unusual payments. The reconciliation of the schedule balance to the general ledger, and the approval of the journal entry, also stay human. The documentation of both tools asks users to review and verify the output before relying on it, and a prepaid schedule gives that instruction a concrete test in the reconciliation to the ledger.


··········


FOLLOW US FOR MORE.


·····


DATA STUDIOS


·····


datastudios.org


bottom of page