By David M.
Reducing Balance Depreciation
In this video I’ll show you to calculate depreciation using the reducing balance method. The reducing balance method assumes that most of the asset’s value drops in the first few years of its useful life. Proponents of this method argue that this is more realistic than the straight line method which assumes that the asset’s value drops in a linear fashion. In our example, we will assume that a company has bought a machine which cost 50,000, has a salvage value of £6000 and an expected useful life of 10 years. The expected useful life is an estimate of the economic life of the machine while the salvage value is an estimate of the resale value of the machine at the end of its economic life.
Firstly, we’ll calculate the annual depreciation rate. To do this, we can use the reducing balance depreciation rate formula. We can write this formula in excel by taking 1 minus the salvage value which is also known as the residual value divided by the original cost of the asset to the power of the inverse of the useful life. Therefore our constant depreciation rate is 19.11%.
We can NOW build a depreciation schedule to show the value of the asset over its useful life – this is reflected by the net carrying value column.
Let’s start with year 1. Our annual depreciation charge is calculated by taking the depreciation rate we calculated earlier multiplied by the previous year’s net carrying value. For year 1, this refers to the current value of the machine. Since we just bought the machine, we assume that its value is still 50,000 and then multiply this by 19.11% to give our annual depreciation charge. The cost is 50,000 and here we can apply absolute references to ensure the value remains constant when we drag it down later. The accumulated depreciation can be calculated by using a running total. We type =sum, first input C14 which contains year 1’s depreciation figure: followed by a colon and then C14. In other words, we’re finding the total within the range c14 to c14 which is simply the value of cell c14, the year 1 depreciation figure. Now, before we drag this formula down later, we’re going to add absolute references to the first part of the function to ensure that this remains static as we drag it down. This calculates the accumulated depreciation each year. Finally, the net carrying value is the cost of the asset minus the accumulated depreciation. To summarise, in year 1 we had a depreciation charge of 9500 which means that our asset is now worth.
In year 2, our annual depreciation charge calculation changes slightly. Instead of taking the cost, we take the previous year’s net carrying value and multiply this by the depreciation rate. All of the other formulae remain the same. We can now drag these down to complete our depreciation table.
Notice, that the net carrying value in year 10 is £6,000 which equals our salvage value. The final year’s net carrying value must always equal the salvage value, if not it means you calculated something incorrectly. Also, notice that most of the asset’s value drops in the first few years using the reducing balance method. This is arguably more realistic than the straight line method which assumes that the value of the asset drops linearly. However, a disadvantage of the reducing balance method, is that despite being more realistic, it is more difficult to calculate which is why it is less widely used than the straight line method.
Therefore, to summarise, we first covered how to calculate the constant depreciation rate using the reducing balance depreciation rate formula. We then set up a depreciation schedule to show how our machine’s annual depreciation, accumulated depreciation and value change over time according to the reducing balance method. Please give this video a thumbs up if you found it useful and subscribe to the excel hub for weekly excel tutorials, techniques and examples.