Depreciation

Excel Functions

When a company buys an asset that will produce benefits over a period of time that asset can be treated like an investment meaning that its cost can be split over a period (i.e. the life of the asset).
This is the systematic process of distributing the cost of an asset over its estimated life.
Depreciation is the reduction in the value of an asset due to wear and tear or other factors.
Depreciation is a notional entry that attempts to match the writing off of an asset with its useful life.
Which depreciation is used can affect the reported profits but not the underlying not operating cash flow.

The recording of depreciation will cause an expense to be recognised, while the net value of the asset declines on the balance sheet.

In all depreciation calculations there are three crucial bits of information:
Cost - The cost value is the original amount you paid for the asset, expressed in its monetary value. Sometimes referred to as the capital value.
Salvage - The value of the asset at the end of its useful life, expressed in its monetary value. This is the value of the asset after it has depreciated. This is also known as the residual value or scrap value.
Life - The life is the number of periods the asset will be used for. This is typically the number of years but could also be the number of months.

There are several different methods of calculating depreciation
There are several worksheet functions available in Excel to help you with your depreciation calculations. None of these functions are part of the Analysis ToolPak.

When you have constant depreciation (ie a fixed amount every period) you need to use the following function:
SLN - Straight line returns the amount an asset depreciates in a single period using a straight line method. This assumes that the rate of depreciation is constant throughout the life of the asset.

There are 4 different depreciation functions for accelerated depreciation (more in the early period and less later on):
These functions will help you to calculate the depreciation of an asset over a specifc period.
DB - Declining balance
DDB - Double declining balance (perhaps the most common)
VDB - Variable declining balance
SYD - Sum of the years digits

Refer to the examples on the website.

?
SLN - Straight line
The arguments for this function are: SLN(cost, salvage, life)
This function returns the amount an asset depreciates in a single period using a straight-line method. This is sometimes referred to an annual depreciation because the asset is consumed at a constant rate. This is the most common method and allows for equal depreciation over the life of the asset, hence the name straight line.

First you need to calculate the depreciation rate. This amount is fixed throughout and is the same throughout its entire life.
The cost of the asset minus the salvage value is reduced in equal amounts during the life of the asset.

depreciation rate(%)= ((cost-salvage)/life)=((900-100)/4)=25%

depreciation amount=(cost-salvage)*depreciation rate

How much does my laptop depreciate every year if it cost me £900 and the salvage price of the laptop after 4 years is £100? The amount returned by this function is the amount the asset will depreciate every year. In 4 years the laptop will have depreciated by £800 (900-100).

The depreciation in the first year is £200 (800 * 25%). Dividing 800 by 4 calculates the straight line depreciation rate to be 25%.
The depreciation in the second, third and fourth years is also £200.

This is the simplest method, where the amount to be written off is divided by the number of periods.

?
DB - Declining balance
The arguments for this function are: DB(cost, salvage, life, period [,month])
This function returns the amount an asset depreciates in a particular period using the declining-balance method. This function should be used when the salvage value of an asset is a significant portion of the original cost. A large part of the cost of the asset is expensed at the beginning of the life.
You must use the same units for life and period.

This is often used in UK tax depreciation calculations to write-down allowances. The standard rate is 25% of the capital balance. This means that the charge is high in the early periods and then becomes smaller and smaller. In fact it never actually reaches zero.

The period is a factor for which the depreciation will be calculated and must be in the same unit as the life argument.
The month argument is optional with a default value of 12, indicating a full year.

First you need to calculate the depreciation rate. This fixed rate is a function of the original cost, the salvage cost and the life of the asset.

depreciation rate(%)=1-[(salvage/cost)^(1?life) ] =1-[(100/900)^(1?4) ]=42.3%

depreciation amount=(cost-total amount depreciated so far)*depreciation rate
Applies the depreciation rate against the non depreciated balance.

How much will my laptop depreciate every year it costs me £900 and after 4 years it has a salvage value of £100?

The depreciation amount in the first year is £380.70 (900 * 42.3%)
The depreciation amount in the second year is £219.66 ((900-380.70) * 42.3%)
The depreciation amount in the third year is £126.75 ((900-(380.70+219.66)) * 42.3%)
The depreciation amount in the fourth year is £73.13 ((900-(380.70+219.66+126.75)) * 42.3%)

?
DDB - Double declining balance
The arguments for this function are: DDB(cost, salvage, life, period [,factor])
This function returns the amount an asset depreciates in a particular period using the double-declining balance method or other. This is as example of an accelerated depreciation method.

You must use the same time units for life and period.
The larger the factor the greater the depreciation amount at the start of the assets life.

This function calculates the total percentage of depreciation in the first year using the straight line method and then doubles it. Each subsequent year the same percentage is multiplied by the remaining balance to be depreciated.

The factor argument is optional with a default value of 2, hence the name double declining.

Not just Double-Declining
Although this function is called double declining method it should really have a different name because it can actually be used to calculate depreciation using any other declining balance multiples, not just double (i.e. factor = 2) This function can be used to calculate the depreciation using any declining balance multiple.

If factor = 1.5 then
If factor = 2.5 then

The declining balance depreciation is determined by multiplying the book value by a constant rate

depreciation rate(%)=1-[(salvage/cost)^(1?life) ]*factor

depreciation amount=(cost-total amount depreciated so far)*(depreciation rate)

How much will my laptop depreciate every year it costs me £900 and after 4 years it has a salvage value of £100? The amount returned is the amount the asset will depreciate every year (or period).

We can use this information to calculate that the double declining depreciation percentage would therefore be 40%.

In the first year the asset will depreciate by £450 (900 * 40%).
In the second year we take the same percentage (40%) and multiple that by the remaining amount to be depreciated £450 (900 - 450).
In the second year the asset will depreciate by £225 (450 * 40%).

Switching to Straight line method
At some point the value will be lower than the straight line charge, at which point the straight line method is used for the remainder of the asset life.

The DDB rate should be applied until the book value of the asset reaches the salvage value, after that you should us the straight line method.

SYD - Sum-of-years digits

The arguments for this function are: SYD(cost, salvage, life, per)
This function returns the amount an asset depreciates in a particular period using the sum-of-years digits method. This is more complicated than the straight line method and is less likely to be used in accounting. However this is often used for splitting interest on loans, hire purchases or lease purchase agreements in order to book more interest in the early periods. This method if also referred to as the Rule of 78. This method has the largest depreciation in the first year and the smallest depreciation in the last year. This is calculated by adding the years together to get the total sum which is then used to calculate fractions for each year.

The depreciation is not constant but is different every year and is an example of accelarted depreciation.

factor=((n*(n+1))/2)=((4*(4+1))/2)=((4*5)/2)=(20/2)=10
Every period is assigned a consecutive number, for example the first year is given the number 1, the second year is given the number 2, etc. and then all these numbers are added together to form a total.
In our example we are depreciating over 4 years so our total is 1 + 2 + 3 + 4 which equals 10.
Each year is then divided by this total to create the following equation:

depreciation rate(%)=

depreciation amount=

How much will my laptop depreciate every year it costs me £900 and after 4 years it has a salvage value of £100?

These fractions are then used to calculate the depreciation for each of the 4 years.
The depreciation rate in the first year is 40% (4/10) so the depreciation amount is £320 ((900-100) * 40%)
The depreciation rate in the second year is 30% (3/10) so the depreciation amount is £240 ((900-100) * 30%)
The depreciation rate in the third year is 20% (2/10) so the depreciation amount is £160 ((900-100) * 20%)
The depreciation in the final year is 10% (1/10) so the depreciation amount is £80 ((900-100) * 10%)

VDB – Variable declining balance

The arguments for this function are: VDB(cost, salvage, life, start_period, end_period [,factor] [,no_switch])
This function returns the

This function is an improvement over the DDB function and is slightly more advanced because it allows for additional inputs.
Use declining balance method allowing you to switch to straight line at the optimum point.

Calculates the depreciation of an asset for any complete or partial period using any type of accelerated depreciation.

The "start_period" is the period after which the depreciation will be calculated.
The "end_period" is the last period for which depreciation will be calculated.
The "life", "start_period" and "end_period" arguments must all have the same units.

The "factor" argument is the rate at which the balance will depreciate. The default is 2 meaning this function will default to calculating at double declining rate.

The "no_switch" argument allows you to decide whether you want to automatically switch to the straight line method when the straight-line depreciation is greater than the declining balance. The default value is 0 which means to switch across to straight-line when appropriate.
Entering 1 (or True) will prevent this from happening.

This function is extremely versatile and can be used with any of the following methods of depreciation calculation:
Straight line
Declining balance, specifying the rate (inc double and triple)