Forecasting depreciation from an asset schedule

Forecasting depreciation means projecting the book expense of existing assets and planned additions, with retirements and remaining depreciable amounts respected. Start with the fixed asset register, calculate each month's charge and reconcile the result to both the asset balances and ledger actuals.

Keep book depreciation separate from tax

Depreciation spreads an asset's depreciable cost across its useful life. The SEC's financial-statements guide describes that allocation and treats depreciation as a noncash adjustment when reconciling income to operating cash flow. Buying an asset and recording its depreciation belong in different schedules; see operating versus capital expenditure for that boundary.

This example forecasts book depreciation using an assumed straight-line policy. It does not calculate tax deductions. IRS Publication 946 covers tax depreciation, including MACRS, Section 179 and special depreciation allowances. Those rules do not determine the useful lives or monthly convention in this book example.

The controller owns the method, useful lives, residual values and partial-period convention. FP&A owns the forecast version and planned additions; asset owners confirm readiness dates and retirements. Changing a policy assumption is a review decision, not a plug to make the forecast match a target.

Export the opening register and planned movements

Use one asset ID per row with class, cost center, original cost, residual value, useful life, method, accumulated depreciation and service dates. Tie the opening cost and accumulated depreciation to the closed ledger before projecting another month.

Add approved capital projects separately, with their expected capitalized costs and first depreciation months. Record the first month excluded from depreciation for each retirement. A cash payment schedule cannot substitute for these service dates.

A US distributor forecasts January through March 2027 from its December 31, 2026 register. Its assumed policy charges a full month beginning with the first depreciation month and stops before the first excluded month. The van is retired on February 1, 2027; servers enter service that day.

The press and van are opening assets. The servers are a planned addition, so their cost is excluded from the December opening balance. Opening cost is $120,000 + $36,000 = $156,000; opening accumulated depreciation is $54,000 + $18,000 = $72,000.

Calculate the monthly charges in Excel

Monthly straight-line charge = (Capitalized cost - Residual value) / Useful life in months

Forecast charge = Eligible monthly charge, capped at the remaining depreciable amount

Lay out the input table in A:H, with cost in C, residual in D, life in E, opening accumulated depreciation in F, first depreciation month in G and first excluded month in H. Use actual dates on the first day of each month; leave H blank when there is no planned retirement.

Put monthly charge in I. In I2, enter and fill down:

=SLN(C2,D2,E2)

Microsoft's SLN documentation specifies cost, salvage and life as the inputs for straight-line depreciation. Life is in months here, so the result is monthly. Covirage is not affiliated with Microsoft.

Set J1, K1 and L1 to January 1, February 1 and March 1, 2027. January's charge in J2 is:

=IF(AND(J$1>=$G2,OR($H2="",J$1<$H2)),MIN($I2,MAX(0,$C2-$D2-$F2)),0)

February's charge in K2 deducts depreciation already forecast during the quarter:

=IF(AND(K$1>=$G2,OR($H2="",K$1<$H2)),MIN($I2,MAX(0,$C2-$D2-$F2-SUM($J2:J2))),0)

Copy K2 across to L2, then fill the monthly formulas down. The expanding sum prevents charges beyond the remaining depreciable amount. Flag missing lives, residual values above cost and opening depreciation above depreciable cost before calculating; do not let the cap conceal bad inputs.

All calculations produce whole dollars; no rounding adjustment is needed. The press's monthly charge is ($120,000 - $12,000) / 60 = $1,800. The van receives only January's charge, while the servers receive February and March charges.

Reconcile the ending asset balances

The retirement removes the van's $36,000 cost and $18,500 accumulated depreciation: its $18,000 opening balance plus January's $500 charge. Its net book value removed is therefore $17,500. Disposal proceeds and any gain or loss require separate entries and are outside this depreciation schedule.

Three checks prove the schedule:

• Cost: $156,000 opening + $36,000 additions - $36,000 retired = $156,000 closing.
• Accumulated depreciation: $72,000 opening + $7,900 expense - $18,500 removed = $61,400 closing.
• Net book value: $84,000 opening + $36,000 additions - $7,900 depreciation - $17,500 retired book value = $94,600 closing.

The opening net book value is $156,000 - $72,000 = $84,000. Closing cost less closing accumulated depreciation also gives $156,000 - $61,400 = $94,600. Keep both checks; a retirement can leave cost correct while accumulated depreciation is wrong.

Compare with actual depreciation by class

Preserve the forecast before importing the quarter's depreciation journals. Use posting month, asset class, cost center and book basis as matching fields. Budget versus forecast explains why the saved comparison should remain visible when expectations change.

Suppose the supporting journals show an extra February van charge of $500 and a missing February server charge of $1,000. Reversing the former and posting the latter changes ledger expense by -$500 + $1,000 = $500, taking $7,400 to $7,900. The net favorable variance hid offsetting posting errors.

If the servers actually entered service later, the difference would instead be timing evidence for the next forecast. Confirm the service record before requesting a correction. Only the accounting owner should decide whether a journal or a forecast assumption needs changing.

Where depreciation forecasts go wrong

• Tax charges mixed with book expense. Keep basis and policy identifiable in every schedule and comparison.
• Payment dates substituted for readiness. A deposit or invoice does not by itself establish the first depreciation month.
• Retired assets continue depreciating. Remove their cost and accumulated depreciation as well as stopping their charges.
• Fully depreciated assets disappear. Stop the charge at the approved residual value while retaining assets still in service.
• Book value changes called depreciation. Identify impairments, transfers and disposal gains or losses separately instead of forcing them into the monthly charge.

Compare your asset schedule in Covirage

Bring a sample of your own asset schedule, additions plan and depreciation ledger export. Covirage maps the columns, and its tools compute and check the supplied schedule against actual depreciation; the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account, then set up your business and bring those files.

See forecast analysis for the supporting forecast comparison.