Projected depreciation in Excel

A basic depreciation schedule assumes a full year of use every year. Real assets don't always work that way. Some wear out with use rather than with time, some start partway through a year, and a business usually wants to see every asset it owns projected forward at once. This page builds all three projections step by step.

What projected depreciation means

Depreciation spreads the cost of a long-lived asset, less its expected salvage value, over the period the business uses it. The depreciation schedule in Excel page covers the standard methods for full years: straight-line with SLN, and the accelerated methods DDB and SYD. It also explains accumulated depreciation and book value. This page builds on those ideas.

Projecting depreciation means working out the expense for future years before they happen. A business does this for budgets and forecasts: next year's depreciation expense is a known cost, and the planned figures should include it. Three situations need more than a fill-down SLN formula:

• Depreciation by usage. A machine that wears out with running hours can be depreciated by hours used instead of by years. This is the units-of-production method. A busy year gets more depreciation; a quiet year gets less.
• A mid-year start. An asset placed in service in June has only seven months of use in its first year. Its first and last calendar years are partial.
• A whole asset register. A register is the list of all the business's long-lived assets, with cost, salvage value, life and start year. Projecting it means one grid of formulas that knows when each asset starts and stops depreciating.

Excel has no dedicated function for units of production, so each case here comes down to plain arithmetic, written once with the right cell references and filled down.

Why this matters for MO-220 practice

The MO-220 exam objective "Perform routine accounting activities" includes depreciation schedules, which cover calculating periodic depreciation and projecting future depreciation. This lesson is about the second part. MO-220 task instructions describe the purpose rather than naming the function: "show the depreciation for the hours used", not "multiply by a per-hour rate". The exercises are worded the same way and run in a spreadsheet in your browser, using the same formulas you would write in Microsoft® Excel® workbooks. Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example 1: depreciation by machine hours

Maple Lane Catering bought a forklift for $72,100. It expects the forklift to run for 63,000 machine hours in total and then be worth $3,000. The inputs sit in column B, and the projection starts in row 9:

Step 1: the rate per hour

The amount to depreciate is cost minus salvage: $72,100 − $3,000 = $69,100. Spread evenly over 63,000 hours, each hour of use costs $69,100 ÷ 63,000, about $1.0968. Keep the full value in the formula rather than typing a rounded rate, so the schedule ends exactly at salvage value.

Step 2: each year's depreciation

Each year's depreciation is the rate per hour times the hours used that year, which are in column B. In C9:

=($B$3-$B$4)/$B$5*B9

The inputs are locked with dollar signs. The hours, B9, are relative, so they move to B10, B11 and so on when you fill down. In 2026 the forklift runs 11,454 hours, so depreciation is about $1.0968 × 11,454 = $12,563.04.

Step 3: accumulated depreciation and book value

Accumulated depreciation is a running total. One way to write it is a range that grows as you fill down. In D9:

=SUM($C$9:C9)

The start of the range is locked and the end is not, so in row 10 it becomes =SUM($C$9:C10). Book value in E9 is cost minus accumulated depreciation: =$B$3-D9.

The yearly amounts rise and fall with the hours. That is the point of the method: 2028 was the busiest year, so it carries the most expense. The hours add up to exactly 63,000, so accumulated depreciation reaches $69,100 and the book value lands on the $3,000 salvage value.

Worked example 2: straight-line from a mid-year start

Summit Bike Repair placed point-of-sale terminals in service in month 6 of 2026. They cost $85,500, have a salvage value of $2,500 and a useful life of 5 years. Five years from June 2026 runs to May 2031, so the schedule covers six calendar years. Column B holds the months in service each year: 7 in 2026 (June to December), 12 in each of 2027 to 2030, and 5 in 2031.

A full year of straight-line depreciation is ($85,500 − $2,500) ÷ 5 = $16,600. Divide by 12 for one month, $1,383.33, and multiply by the months in service. In C9:

=($B$3-$B$4)/$B$5/12*B9

Accumulated depreciation and book value use the same formulas as before.

The first and last years together make one full year: 7 + 5 = 12 months, and $9,683.33 + $6,916.67 = $16,600. Spotting that pairing is a quick way to confirm the partial years are right.

Worked example 3: projecting a whole asset register

Harbor Bakery wants straight-line depreciation for 2026 to 2030 for all eight assets in its register. Each asset has its cost in column B, salvage in C, life in D and first year in E. The years 2026 to 2030 run across row 4, from G4 to K4. The task is one formula in G5 that fills right to column K and down to row 12.

An asset depreciates in a year when two things are true: the year has started for it (year ≥ first year), and its life is not used up (year < first year + life). A forklift with a 5-year life starting in 2022 depreciates in 2022 through 2026; in 2027 the test fails, because 2027 is not less than 2022 + 5, so it stops. Both tests must hold, so they go inside AND, and IF returns the annual amount or 0:

=IF(AND(G$4>=$E5,G$4<$E5+$D5),($B5-$C5)/$D5,0)

The mixed references make the fill work. G$4 locks the row, so every row reads its year from row 4, while the column moves from G to K. $E5, $D5, $B5 and $C5 lock the column, so every year reads the asset's own details, while the row moves from 5 to 12. Each year is totaled in row 13 with =SUM(G5:G12).

The other three assets, the security system, the air compressor and the walk-in freezer, finished depreciating before 2026, so their rows are all 0. They still belong in the register, and the formula handles them without any special case. The totals show the shape of the forecast: depreciation drops in 2027 as two assets finish, then rises again in 2028 when the packaging machine starts.

Common mistakes

These are the mistakes the exercises check for, with the wrong numbers they produce.

Leaving out the salvage value

Spreading the full cost, =$B$3/$B$5*B9, depreciates the part the business expects to get back. Maple Lane's 2026 depreciation becomes $13,108.47 instead of $12,563.04, and the forklift ends at a book value of 0 instead of $3,000. For Summit's terminals, 2026 becomes $9,975.00 instead of $9,683.33, again ending at 0.

Input references that move when filled down

Without dollar signs, =(B3-B4)/B5*B9 becomes =(B4-B5)/B6*B10 one row down. B6 is empty, so the second year shows a division-by-zero error, and every row after it goes wrong too. Lock the inputs: $B$3, $B$4, $B$5.

Taking a full year in the first year

The terminals were in service for only 7 months of 2026. Charging the full $16,600 in 2026 leaves a book value of $68,900 instead of $75,816.67 at year end, uses up the asset by the end of 2030, and leaves nothing for the 5 months of 2031, which should carry $6,916.67.

No end check

Testing only that the year has started, without AND, keeps every asset depreciating forever. Harbor Bakery's 2026 total jumps from $17,333.33 to $40,891.67, because the security system, air compressor and freezer, long finished, are charged again.

One year too many

Using <= instead of < in the end test gives each asset an extra year. The forklift and the terminals would both depreciate in 2027, pushing that total to $18,903.33 instead of $9,403.33, and the server rack would run into 2029. A 5-year life covers five years: the first year plus four more.

How to check a projection

• End point: once an asset's life or hours are used up, its accumulated depreciation equals cost minus salvage and its book value equals salvage.
• Partial years: the first and last partial years should add up to one full year.
• Register rows: count the nonzero years in each row against the asset's life and start year.

To record a year's depreciation once it is calculated, see the depreciation journal entry page. The absolute cell references page explains the dollar signs used in every formula above.