Level 2: Income statement and schedules · Exercise 21 of 60 · Pro

Depreciation waterfall with a half-year convention

A financial modeling exercise from Modeldrill. Solve it with a live spreadsheet grid in your browser, with Fill right and Fill down, and it is checked the moment you submit.

The task

Each year of capital spending (column B, bought in the year in column A) is depreciated straight line over the life in B2 with a half-year convention: half a year of depreciation in the year bought, a full year for each year after that, and the remaining half year in the year that is life years after the purchase. Fill the waterfall C5:G9 with one formula, then total each year in row 10.

What this exercise teaches

A waterfall keeps each year of spending as its own row, so a five-year life really spans six calendar years under the half-year convention. Starting with a full year in the year bought, which most first attempts do, overstates early depreciation and finishes a year too soon.

A hint if you get stuck

Three cases: the year bought or the year that is life years later gets half of cost / life; the years strictly between them get the full cost / life; everything else is zero. Lock the row for the year (C$4) and the columns for the vintage and the amount ($A5, $B5).

Functions used: AND, IF, OR, SUM