Capex and depreciation schedule in Excel with Copilot, Claude or ChatGPT
A capex and depreciation schedule in Excel can be drafted by Copilot in Excel, Claude for Excel or ChatGPT for Excel, but the numbers it produces have to be checked against a schedule whose result you already know. In the illustrative example below, four additions placed in service during 2027, one disposal and one construction project produce total 2027 depreciation of $299,000, a December monthly charge of $28,000, a $2,500 gain on disposal and a closing net book value of $1,155,000 for assets in service, with $90,000 left in construction in progress. Those are the figures an AI-built schedule has to reproduce before anyone posts from it.
The schedule itself is one of the most reused workbooks in a finance team. It feeds the monthly depreciation journal, the fixed asset roll-forward in the year-end file, the capex line of the cash flow forecast and the budget for next year. Because each asset follows a simple rule but the rules differ by start month, useful life, salvage value and method, the work is repetitive enough for an AI assistant to draft and error-prone enough that a reviewer must test it.
The three assistants work inside the workbook, so the realistic division of labor is this: the AI builds the structure, writes the formulas and explains them, while the accountant fixes the policy inputs (convention, lives, salvage values, capitalization threshold) and reconciles the output to the general ledger.
··········
WHAT A CAPEX AND DEPRECIATION SCHEDULE HAS TO DO.
A working schedule has three layers. The first is the asset register: one row per asset or asset group with description, asset class, cost, in-service date, useful life, salvage value and method. The second is the calculation grid: one column per month, with the depreciation charge for each asset in each month. The third is the summary: monthly and quarterly totals, a roll-forward of gross cost and accumulated depreciation, and the capex cash outflows by period.
The accounting rules sit outside the spreadsheet and must be written down before the AI is asked to build anything. A company decides whether depreciation starts in the month an asset is placed in service, the following month or mid-month; whether salvage values are used; and whether assets still under construction are kept in a separate construction in progress line that is not depreciated until the asset is ready for use. Depreciation begins when the asset is available for use under IFRS and when it is placed in service under US GAAP, and IFRS also requires significant components with different lives to be depreciated separately, a point covered in our article on how component depreciation works under IAS 16 and ASC 360.
Book depreciation and tax depreciation are also two different schedules. A US company that takes Section 179 expensing or bonus depreciation for tax keeps its straight-line book schedule unchanged and tracks the difference as a temporary difference, as explained in our piece on Section 179 vs bonus depreciation for 2026. The example here is a book schedule only.
··········
COPILOT, CLAUDE AND CHATGPT IN EXCEL: DOCUMENTED REQUIREMENTS.
All three products can read and edit a live workbook. Their documented requirements differ, and the table summarizes what each vendor's official documentation states as of November 5, 2026.
........
Tool | Who can use it (as documented) | Where it runs | Documented limits relevant to a schedule |
Copilot in Excel | Microsoft 365 Premium, Personal or Family with an AI credits plan, a commercial Microsoft Copilot subscription, or a Copilot Chat-eligible business subscription | Windows, Mac and web (edit, plan and chat modes) | Editing requires Calculation Options set to Automatic; Strict Open XML files unsupported |
Claude for Excel | Claude Pro, Max, Team and Enterprise plans | Excel on the web, Windows (Microsoft 365) and Mac | Data tables and macros or VBA unsupported; not recommended for audit-critical calculations without verification |
ChatGPT for Excel | Generally available across all ChatGPT plans since the May 5, 2026 update | Inside Excel workbooks | OpenAI notes complex formulas or edge cases may still require manual refinement |
........
Microsoft's Copilot in Excel FAQ adds that Copilot output can be inaccurate and advises against relying on it for decisions in sensitive areas such as finance. It also notes that changes Copilot saves to a workbook are visible to anyone with access to the file, including coauthors, and that earlier versions can be restored. For a shared fixed asset file, that means the AI should work on a copy until the schedule has been reviewed.
Anthropic's documentation for Claude for Excel states that answers include cell-level citations, that Claude warns before overwriting existing data, and that risky operations require confirmation. OpenAI describes ChatGPT for Excel as asking for permission before editing and linking answers to the cells it references. These review points make it easier to see which formulas the assistant wrote, but they do not test whether those formulas apply the right policy.
··········
THE ILLUSTRATIVE DATASET FOR A 2027 CAPEX SCHEDULE.
The example uses a fictional manufacturer with a calendar fiscal year. Existing assets at December 31, 2026 have gross cost of $1,200,000 and accumulated depreciation of $450,000, with a run-rate depreciation charge of $20,000 a month. One forklift in that pool, cost $40,000, depreciates at $500 a month and has accumulated depreciation of $33,000 at the start of 2027. It is sold on June 30, 2027 for $6,500 in cash.
The policy is straight-line depreciation, starting with a full month in the month the asset is placed in service, and capex is paid in the month the asset is placed in service. All figures are illustrative.
........
Asset | Cost (USD) | In service | Life (years) | Salvage (USD) | Monthly charge (USD) |
Packaging line | 360,000 | March 2027 | 10 | 0 | 3,000 |
Delivery trucks (2) | 180,000 | May 2027 | 5 | 30,000 | 2,500 |
Server cluster | 96,000 | August 2027 | 4 | 0 | 2,000 |
Office fit-out | 72,000 | November 2027 | 6 | 0 | 1,000 |
Warehouse racking (CIP) | 90,000 | Not in service at year-end | n/a | n/a | 0 |
........
The racking project is paid in two installments, $50,000 in October and $40,000 in December, and stays in construction in progress at December 31, 2027 because it is not yet available for use. Excluding it from the depreciation grid is one of the checks the AI output must pass.
··········
PROMPTS THAT BUILD THE SCHEDULE IN EXCEL.
The most reliable approach is to give the assistant the register as a table on its own sheet and then state the policy explicitly in the prompt. Vague prompts such as "add depreciation" leave the convention, the treatment of salvage value and the handling of construction in progress to the model's guess.
On the sheet "Register", each row is an asset with Cost, InServiceDate, LifeYears,
Salvage and Status. Create a sheet "Dep2027" with one row per asset and one column
per month from Jan-2027 to Dec-2027. Use straight-line depreciation with a full
month charged in the month of InServiceDate. Monthly charge = (Cost - Salvage) /
(LifeYears * 12). Charge nothing for assets with Status = "CIP". Stop charging once
accumulated depreciation reaches Cost - Salvage. Use formulas that reference the
Register sheet, no hard-coded numbers.A typical formula for one asset and one month, assuming the month-start date sits in row 1 and the register columns are referenced on the same row, looks like the following. SLN and EDATE are standard Excel functions; SLN(cost, salvage, life) returns straight-line depreciation for one period, so passing the life in months returns the monthly charge.
=IF(AND($G2<>"CIP", E$1>=EOMONTH($C2,-1)+1, E$1<EDATE(EOMONTH($C2,-1)+1, $D2*12)),
SLN($B2, $F2, $D2*12), 0)Once the grid exists, the second prompt asks for the summaries and the disposal.
Add a "Summary" sheet with: total depreciation by month and by quarter; a
quarterly roll-forward of gross cost, accumulated depreciation and net book value
starting from gross cost 1,200,000 and accumulated depreciation 450,000; a
separate CIP line; and the disposal of the forklift on 30-Jun-2027 (cost 40,000,
monthly depreciation 500 through June, proceeds 6,500). Show the gain or loss and
the journal entry. Explain every formula you add with cell references.··········
EXPECTED RESULT OF THE CAPEX AND DEPRECIATION SCHEDULE.
These figures were computed independently in Python from the dataset above. If the AI-built workbook does not match them, the formulas or the policy inputs are wrong.
........
Item | Expected 2027 amount (USD) |
Existing assets (20,000 Jan-Jun, 19,500 Jul-Dec) | 237,000 |
Packaging line (10 months) | 30,000 |
Delivery trucks (8 months) | 20,000 |
Server cluster (5 months) | 10,000 |
Office fit-out (2 months) | 2,000 |
Total depreciation 2027 | 299,000 |
December 2027 monthly charge | 28,000 |
Capex cash paid in 2027 (including CIP) | 798,000 |
........
The monthly totals run from $20,000 in January and February to $23,000 in March and April, $25,500 in May and June, $25,000 in July after the forklift leaves the pool, $27,000 from August to October and $28,000 in November and December. The quarterly totals are $63,000, $74,000, $79,000 and $83,000.
........
Quarter-end 2027 | Gross cost (USD) | Accumulated depreciation (USD) | Net book value (USD) |
Q1 | 1,560,000 | 513,000 | 1,047,000 |
Q2 | 1,700,000 | 551,000 | 1,149,000 |
Q3 | 1,796,000 | 630,000 | 1,166,000 |
Q4 | 1,868,000 | 713,000 | 1,155,000 |
........
Construction in progress of $90,000 is shown on a separate line at December 31, 2027 and is not part of the table above. The Q2 row already reflects the forklift disposal, which removes $40,000 of cost and $36,000 of accumulated depreciation ($33,000 opening plus six months at $500).
The two journal entries the schedule should generate are the December depreciation charge and the forklift disposal.
........
Entry | Account | Debit (USD) | Credit (USD) |
Dec 31, 2027 depreciation | Depreciation expense | 28,000 | |
Dec 31, 2027 depreciation | Accumulated depreciation | 28,000 | |
Jun 30, 2027 disposal | Cash | 6,500 | |
Jun 30, 2027 disposal | Accumulated depreciation | 36,000 | |
Jun 30, 2027 disposal | Property, plant and equipment | 40,000 | |
Jun 30, 2027 disposal | Gain on disposal of assets | 2,500 |
........
The disposal entry balances at $42,500 on each side: net book value of $4,000 against proceeds of $6,500 gives the $2,500 gain.
··········
DDB AND VDB IN EXCEL: AN ACCELERATED-METHOD CHECK.
Some companies depreciate IT equipment on a declining-balance basis, and assistants often reach for the DDB function when asked for "accelerated depreciation". Microsoft's documentation for DDB states that the factor defaults to 2, that each period's charge is limited so the asset does not fall below salvage value, and that VDB should be used to switch to straight-line when straight-line exceeds the declining-balance amount. The difference shows up clearly on the $96,000 server cluster with a four-year life and no salvage value, calculated by year.
........
Year | DDB charge (USD) | VDB with switch to straight-line (USD) |
1 | 48,000 | 48,000 |
2 | 24,000 | 24,000 |
3 | 12,000 | 12,000 |
4 | 6,000 | 12,000 |
Total | 90,000 | 96,000 |
........
DDB alone leaves $6,000 of cost undepreciated at the end of the useful life, because the declining-balance charge in year four is half of the remaining $12,000. A schedule built with DDB and zero salvage therefore needs either a final-period true-up or the VDB function. An AI-generated grid that uses DDB without that adjustment will look plausible and still misstate the last year. The broader trade-offs between methods are set out in our article on depreciation methods: straight-line, DDB and units of production.
··········
REVIEW CONTROLS FOR AN AI-BUILT DEPRECIATION SCHEDULE.
The errors AI assistants make in this task are predictable, so the review can be targeted. The first is the start-month convention: a model told nothing will often start depreciation in the month after the in-service date or prorate by days, and a one-month shift on the packaging line alone moves 2027 depreciation by $3,000. The second is salvage value, which is frequently ignored in the monthly charge; on the trucks that error raises the monthly charge from $2,500 to $3,000. The third is construction in progress being depreciated from the payment date. The fourth is assets that pass the end of their useful life during the year and keep being charged.
A short set of tie-outs catches most of these problems. Total depreciation in the grid must equal the change in accumulated depreciation in the roll-forward plus accumulated depreciation removed on disposals. Closing gross cost plus construction in progress must agree to the fixed asset accounts in the trial balance. For each new asset, cost minus salvage divided by the monthly charge must equal the life in months. Capex cash in the schedule must reconcile to the purchases of property, plant and equipment in the cash flow statement. For larger registers, a filter on assets with a charge in a month before their in-service date should return no rows.
Policy decisions stay with the accountant: capitalization thresholds, useful lives by asset class, the decision that an asset is available for use, impairment indicators and the classification of costs between capex and repair expense. The capitalization criteria themselves are discussed in our article on how capital expenditures are planned, capitalized and depreciated in financial statements. The AI can draft the memo that documents these choices, but it cannot make them.
··········
DATA HANDLING WHEN THE FIXED ASSET REGISTER GOES TO AN AI ADD-IN.
A fixed asset register is usually less sensitive than payroll data, but it can still reveal planned investments, site locations and supplier names. Anthropic's documentation 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 the cases its privacy terms describe, and that Claude for Excel does not inherit custom data retention settings an organization may have set. It also warns users to work only with trusted spreadsheets because files from external sources can carry hidden instructions.
OpenAI states that data shared with ChatGPT Enterprise is not used to train its models by default and describes encryption in transit and at rest for Enterprise; the ChatGPT for Excel page does not state equivalent terms for other plans. Microsoft notes that Copilot in Excel cannot be switched off on its own and is governed by the existing administrative controls for Microsoft Copilot Chat. Teams that handle confidential capital plans should confirm which plan and which administrative settings apply before the register is opened with any of the three assistants.
··········
CAPEX AND DEPRECIATION SCHEDULES WITH AI: WHAT THE EXAMPLE SHOWS.
Copilot in Excel, Claude for Excel and ChatGPT for Excel can all build a monthly capex and depreciation grid, a roll-forward and the related journal entries from a clear prompt, and their documentation describes ways to trace the cells and formulas they change. None of the three vendors documents the product as a fixed asset subledger, and all three caution against relying on the output without review.
For the illustrative 2027 dataset, a correct schedule shows $299,000 of depreciation, a $28,000 December charge, a $2,500 gain on the forklift, $1,155,000 of net book value for assets in service and $90,000 of construction in progress. A workbook that reproduces those figures with live formulas tied to the register is ready for review; one that does not usually has a convention, salvage value or construction-in-progress error that the tie-outs above will locate.
The judgement areas remain the same as in a manually built schedule: when an asset is available for use, which useful lives and salvage values are supportable, whether components need separate depreciation, and whether a declining-balance method needs a switch to straight-line. Those inputs decide the result, and they belong to the accountant who signs off the fixed asset reconciliation.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
