Provide ChatGPT with a controlled asset register, depreciation policy, in-service dates, opening accumulated depreciation and reporting period, then use it to draft an auditable Excel depreciation schedule. The workflow separates Excel formula mechanics from the accounting policy you have actually approved.
ROLE
You are helping me build an auditable fixed-asset depreciation schedule in Excel.
REPORTING SETTINGS
- Reporting period / year end: [e.g. year ended 31 Dec 2026]
- Currency / units: [e.g. GBP]
- Depreciation start policy: [state when depreciation begins, e.g. in-service / available-for-use]
- Proration convention: [none / half-year / mid-month / exact-day / other]
- Rounding policy: [e.g. calculate at full precision, display 2 decimals]
ASSET REGISTER
Provide one row per asset:
Asset ID | Asset Name | Depreciable Cost/Basis | Residual/Salvage Value |
Useful Life | Method | In-Service Date |
Opening Accumulated Depreciation | Opening NBV
[Paste rows]
Rules:
1. Do not choose or change the depreciation method, useful life, residual value,
depreciable basis, in-service date or proration convention.
If an input is missing, return "INPUT NEEDED".
2. Validate:
- Asset ID unique
- Cost/Basis >= Residual Value
- Useful Life > 0
- Opening NBV = Cost/Basis - Opening Accumulated Depreciation, unless I provide
a documented adjustment/impairment/revaluation basis
- Opening NBV >= Residual Value unless an explained exception is supplied
3. Use the Excel function matching the supplied method:
- SLN(cost,salvage,life)
- DDB(cost,salvage,life,period,[factor])
- SYD(cost,salvage,life,period)
Use life and period in the same units.
4. For DDB, use factor = 2 only when my method is genuinely double-declining balance.
Do not switch automatically to straight-line. If I request a switch-to-SLN approach,
flag VDB as the Excel function designed for that behaviour.
5. Never request a depreciation period outside the asset's defined schedule.
6. Period depreciation must not reduce NBV below Residual/Salvage Value.
Use a cap based on Remaining Depreciable Amount:
=MAX(0,MIN(MethodDepreciation,PriorNBV-ResidualValue))
7. Accumulated Depreciation:
= Opening Accumulated Depreciation + depreciation recognised in schedule periods
8. NBV:
= Cost/Basis - Accumulated Depreciation
9. Do not call an asset "fully depreciated" merely because the nominal useful-life year
has been reached; confirm Remaining Depreciable Amount = 0 (subject to residual value).
10. Calculate at full precision and round only display/output according to my rounding policy.
11. Add controls:
- NBV never below residual value
- accumulated depreciation never above Cost/Basis - Residual Value
- opening + current depreciation ties to closing accumulated depreciation
- closing NBV ties to Cost/Basis - closing accumulated depreciation
12. Do not invent accounting policy, impairment, disposal, revaluation or tax depreciation.
OUTPUT
1. Asset validation exceptions
2. Depreciation schedule
3. Exact Excel formulas
4. Period depreciation total
5. NBV / accumulated-depreciation control checks
Use tab-separated tables suitable for Excel.
Replace the bracketed section with your real data, then paste the prompt into ChatGPT.
I need to compare Excel depreciation calculations for one asset. This is a scenario comparison only — do not recommend which accounting method I should adopt. ASSET DETAILS Cost / Depreciable Basis: [e.g. 40,000] Residual/Salvage Value: [e.g. 4,000] Useful Life: [e.g. 5 years] Period unit: [years / months] DDB factor: [e.g. 2] Proration: [none for this comparison unless stated] Compare: 1. SLN 2. DDB using the supplied factor 3. SYD Requirements: - Show every period from 1 through Useful Life. - Use the exact Excel formulas for every method and Year/Period 1. - Show annual/period depreciation, accumulated depreciation and ending NBV. - Verify each method independently against its Excel-function behaviour. - Do not assume methods must have identical period patterns. - Add a control showing each method's ending NBV and cumulative depreciation. - If a method does not reach the intended residual value under the specified settings, flag the result rather than forcing a final depreciation amount. - For DDB, do not silently switch to straight-line; mention VDB only if I explicitly want a declining-balance method that can switch to straight-line. - Show P&L timing difference versus SLN as: EBIT impact = SLN depreciation - comparison-method depreciation (negative = lower EBIT than SLN; positive = higher EBIT than SLN). - Do not use this comparison to select accounting policy for me.
Use this to visualize how different methods front-load expense before committing to a policy.
My fixed-asset schedule needs proration for additions placed in service during the reporting year. REPORTING SETTINGS - Financial year start: [date] - Financial year end: [date] - Approved depreciation-start rule: [e.g. in-service / available-for-use date] - Convention: [Half-year / Mid-month / Exact-day / other] - Exact convention definition: [For example, if "mid-month", state whether a half-month is taken in the placement month and how the disposal/last period is treated.] - Exact-day day-count basis: [actual days / 365 / 366 / other] - Rounding: calculate at full precision, display [e.g. 2] decimals ASSET REGISTER Asset ID | Asset Name | Cost/Basis | Residual Value | Useful Life | Method | In-Service Date [Paste rows] Requirements: 1. Do not infer the convention definition from its name alone. 2. Confirm each In-Service Date falls in or before the reporting period. 3. Calculate full-period method depreciation first at full precision. 4. Apply the supplied proration convention only where it is compatible with the selected depreciation method/policy. 5. Show the exact proration factor and formula for each asset. 6. Do not round full-year depreciation before multiplying by the proration factor. 7. Show first-period depreciation and remaining depreciable amount. 8. Explain how the convention affects the final depreciation period so cumulative depreciation does not exceed Cost/Basis - Residual Value. 9. Do not invent tax conventions or assume half-year/mid-month treatment is required by financial-reporting rules. Output a tab-separated schedule plus control totals.
Use this when your accounting policy requires a stated proration convention for additions during the year.
| Asset | Cost | Salvage | Life | Copied formula after life |
|---|---|---|---|---|
| CNC Machine | £40,000 | £4,000 | 5yr | Yr 6: £7,200 → NBV −£3,200 ✗ |
| Delivery Van | £28,000 | £3,000 | 4yr | Formula still copied past Yr 4 ✗ |
| Server Rack | £12,000 | £0 | 3yr | Yr 4: £4,000 → NBV −£4,000 ✗ |
| Asset | Year | Ann. Depr | Accum. Depr | NBV | Status |
|---|---|---|---|---|---|
| CNC Machine | 1 | =SLN(40000,4000,5) | £7,200 | £32,800 | |
| CNC Machine | 5 | =MAX(0,MIN(MethodDepr,PriorNBV-Salvage)) | £36,000 | £4,000 | Residual reached |
| CNC Machine | 6 | £0 | £36,000 | £4,000 ✓ | Stopped ✓ |
| Delivery Van | 1 | =DDB(28000,3000,4,1) | £14,000 | £14,000 | |
| Server Rack | 1 | =SLN(12000,0,3) | £4,000 | £8,000 | |
| Total Yr 1 | £25,200 | ✓ | |||
=MAX(0,MIN(Method_Depr,Prior_NBV-Salvage)) Control: Closing NBV = Cost - Closing Accumulated Depreciation Closing NBV >= Salvage / Residual Value Do not call DDB/SYD for periods outside the defined schedule. The shown assets stop once remaining depreciable amount reaches £0. ✓
| Year | SLN (current) | DDB impact? | SYD impact? | Early EBIT effect |
|---|---|---|---|---|
| 1 | £7,200 | ? | ? | Unknown |
| 2 | £7,200 | ? | ? | Unknown |
| 3 | £7,200 | ? | ? | Unknown |
| Year | SLN | DDB | SYD | DDB vs SLN (EBIT) |
|---|---|---|---|---|
| 1 | £7,200 | £16,000 | £12,000 | −£8,800 |
| 2 | £7,200 | £9,600 | £9,600 | −£2,400 |
| 3 | £7,200 | £5,760 | £7,200 | +£1,440 |
| 4 | £7,200 | £3,456 | £4,800 | +£3,744 |
| 5 | £7,200 | £1,184 | £2,400 | +£6,016 |
| Total | £36,000 | £36,000 | £36,000 | £0 net ✓ |
SLN: =SLN(40000,4000,5) → £7,200/yr flat DDB: =DDB(40000,4000,5,1) → £16,000 Yr 1 SYD: =SYD(40000,4000,5,1) → £12,000 Yr 1 In this specific example, all three functions finish at £4,000 residual value and cumulative depreciation is £36,000. Verify this independently for other inputs. ✓
| Asset | Cost | Salvage | In-Service | Life | Full Yr Depr | Yr 1 Claimed | Correct? |
|---|---|---|---|---|---|---|---|
| Forklift | £24,000 | £4,000 | 03 Jan | 6yr | £3,333.33 | £3,333.33 | ✗ |
| Racking | £18,000 | £0 | 01 Oct | 5yr | £3,600 | £3,600 | ✗ |
| Server | £12,000 | £0 | 15 Aug | 3yr | £4,000 | £4,000 | ✗ |
| Van | £28,000 | £0 | 30 Nov | 4yr | £7,000 | £7,000 | ✗ |
| Total full-year depreciation before convention | £17,933.33 | Example policy applies 50% | |||||
| Asset | Cost | Salvage | In-Service | Full Yr | Factor | Yr 1 Depr | Corrected by |
|---|---|---|---|---|---|---|---|
| Forklift | £24,000 | £4,000 | 03 Jan | £3,333.33 | 50% | £1,666.67 ✓ | −£1,666.67 |
| Racking | £18,000 | £0 | 01 Oct | £3,600 | 50% | £1,800 ✓ | −£1,800 |
| Server | £12,000 | £0 | 15 Aug | £4,000 | 50% | £2,000 ✓ | −£2,000 |
| Van | £28,000 | £0 | 30 Nov | £7,000 | 50% | £3,500 ✓ | −£3,500 |
| Total Yr 1 after half-year convention | £8,966.67 | −£8,966.66* | |||||
Example policy: Factor = 0.5 for every shown addition. Forklift: Full precision = (£24,000-£4,000)/6 = £3,333.333... Yr 1 = £3,333.333... × 0.5 = £1,666.666... → £1,666.67 display Full-year total at full precision = £17,933.333... Yr 1 total = £8,966.666... → £8,966.67 display *Displayed "reduction" can differ by £0.01 if subtracting rounded display values. Use unrounded formulas for accounting totals. ✓
Include Asset ID, depreciable cost/basis, residual value, useful life, approved method, in-service date, opening accumulated depreciation and opening NBV. Enter residual value as 0 only when that is an intentional supported assumption; do not convert a missing value to zero.
If period depreciation depends on timing, provide the in-service/available-for-use date and the exact convention your policy uses. "Half-year", "mid-month" and "exact-day" labels are not enough unless their calculation rules are defined.
Use the Build prompt after validating the register. ChatGPT should produce candidate Excel formulas using the method you supplied; verify the first asset under each method directly in Excel before filling the schedule down.
Check an asset whose remaining depreciable amount has reached zero. Subsequent depreciation should be zero and NBV should not fall below residual value. The final year itself may still contain depreciation; do not assume "end of useful life" means the current year's charge is automatically zero.
SLN function, which takes three arguments: cost, salvage value, and useful life. The formula is =SLN(cost, salvage, life). ChatGPT can also build the calculation manually as (Cost − Salvage) / Life if you prefer.