Create Depreciation Schedule in Excel (8 Suitable Methods)

Depreciation describes how an asset’s value decreases over time. A Depreciation Schedule is a table that displays the amount of depreciation for a fixed asset over the course of its life. In this article, I’ll show you 8 methods to create a depreciation schedule in Excel.

Let’s say you have the following information about the asset in your dataset. Now, I’ll show you how you can prepare a depreciation schedule using this information.

Download Practice Workbook

8 Methods to Prepare Depreciation Schedule in Excel

1. Straight Line Depreciation Schedule

During each period of an asset’s lifetime, the straight line technique simply subtracts a specific amount from its value. The straight line depreciation for one period is determined using the equation;

Straight line depreciation= (cost-salvage)/life.

You can prepare a straight line depreciation schedule in Excel by using the SLN function.

Let’s create the straight line depreciation schedule.

In the year when the asset was purchased, no depreciation is assumed. It mainly depends on the accounting principles which you are using to keep records. In some accounting practices, depreciation of the asset is considered immediately after purchasing the asset when some accounting methods consider depreciation from the first period.

As there is no depreciation in 2012, the depreciated asset value will be equal to the Initial cost of the asset.

Now, to calculate depreciation in the year 2013,

➤ Type the following formula in cell D12,

Here, the formula will return the depreciation of period 1 using a straight line method.

➤ Type ENTER and you will get the depreciation of the asset in 2013.

Now,

➤ Drag cell D12 to the end of your schedule.

As a result, you will get the depreciation for all the periods.

Now to calculate accumulated depreciation in your template,

➤ Type the following formula in cell E12,

The formula will add previous period accumulated depreciation and current period depreciation.

➤ Press ENTER.

you will get the accumulated depreciation till that period.

Now,

➤ Drag cell E12 to the end of your schedule.

As a result, you will get the accumulated depreciation for all the periods.

Finally, to calculate depreciated asset values,

➤ Type the following formula in cell F12,

The formula will return the balance after subtracting depreciation from the asset value for the first period.

➤ Press ENTER.

As a result, you will get the depreciated asset value in 2013.

Now,

➤ Drag the F12 cell to the end of your dataset.

So, you will get the depreciated asset value for all the periods. It will complete the straight line depreciation schedule.

2. Sum of Years’ Digit

You can also create a depreciation schedule using the Sum of Years’ Digit. There is an in-built Excel financial function to calculate depreciation using this method. The function is Excel SYD function.

The SYD function adds a fourth needed argument, per, with the cost, salvage, and life arguments to calculate the sum-of-years’ digits depreciation. The argument- per is the period for which the depreciation is calculated. The period unit must be the same as the life unit; for example, years, months, and so on.

To calculate depreciation using this function,

➤ Type the following formula in cell D12,

Here, the function will calculate depreciation using the Sum of years’ digit method for the given period ( cell C12).

➤ Press ENTER.

And you will get the sum of year’s digit depreciation for the first period.

➤ Drag the cell D12 till the last period comes.

As a result, you will get the depreciation for all of the periods.

Now, to calculate accumulated depreciation,

➤ Type the following formula in cell E12 and press ENTER,

The formula will add previous period accumulated depreciation and current period depreciation.

➤ Drag cell E12 to the end of your schedule.

As a result, you will get the accumulated depreciation for all the periods.

Finally, to calculate depreciated asset values,

➤ Type the following formula in cell F12,

The formula will return the balance after subtracting depreciation from the asset value for the first period.

➤ Drag the F12 cell to the end of your dataset.

You will get the depreciated asset value for all the periods.

As a result, an Excel Depreciation Schedule using the sum of years’ digits method will be created.

Read More: How to Create a Monthly Schedule in Excel (3 Simple Ways)

3. Declining Balance Depreciation Schedule

You can prepare a depreciation schedule using the Declining Balance depreciation method with the DB function. Cost, salvage, life, period, and month are the five inputs to the DB function. The first four arguments are mandatory, while the fifth is optional. The argument- period represents the period in which the depreciation will be calculated.

Now to prepare a depreciation template,

➤ Type the following formula in cell D12,

The formula will give declining balance depreciation for the first period.

➤ Press ENTER.

And you will get the declining balance depreciation of the first period.

➤ Now drag cell D12 to cell D18 to get the declining balance depreciation for all the periods.

After that, you have to calculate accumulated depreciation and depreciated asset value to complete your depreciation schedule. You can calculate those using the steps mentioned in Method 2.

Read More: How to Create a Recurring Monthly Schedule in Excel

4. Double Declining Depreciation Method

Depreciation Schedule can also be prepared using the double declining depreciation method. To apply the method you need to use the DDB function. The function has five arguments. The first four arguments (cost, salvage, life, and period) are mandatory and are the same as in the DB function. The fifth argument, factor, is optional. It defines how the rate of depreciation is multiplied. If this argument is left blank, Excel will use 2 as the factor.

Now, to create depreciation schedule using this method,

➤ Type the following formula in cell D12,

The formula will give depreciation of the first period using the double declining method.

➤ Press ENTER.

As a result, you will get the depreciation for the first period.

Now,

➤  Drag cell D12 to cell D18 to get the double declining balance depreciation for all the periods

After that, you have to calculate accumulated depreciation and depreciated asset value to complete your depreciation schedule. You can calculate those using the steps mentioned in Method 2.

Read More: How to Make a Daily Schedule in Excel (6 Practical Examples)

5. Units of Production

You can also prepare a depreciation schedule in Excel using the Units of Production methods. The depreciation for one period using units of production method is determined using the equation; depreciation=(cost-salvage)/life in units)*Units produced per period.

To use this method you need to know the unit produced per period. Consider the following dataset where you have the unit production of every period.

First,

➤ Type in the following formula in cell E12,

The formula will determine depreciation for the first period.

After that,

➤ Press ENTER and drag the cell E12 to the cell E18.

As a result, you will get depreciation for all the period.

After calculating the depreciation, you have to calculate accumulated depreciation and depreciated asset value to complete your depreciation schedule. You can calculate those using the steps mentioned in Method 2.

6. Variable Declining Balance

You can also prepare a depreciation schedule with the Variable Declining Balance method using the VDB function.

First,

➤ Type the following formula in cell D12,

Here, $C$4 refers to the initial cost of the asset, $C$6 refers to salvage value, $C$7 refers to life (in years). C11 is the start period and C12 is the end period. The function will give Variable declining balance depreciation for the first period.

After that,

➤ Press ENTER and drag cell D12 to the end of your dataset.

As a result, you will get the variable declining balance depreciation for all of the periods.

You can also calculate the accumulated depreciation for each period using the VDB function.

➤ Type the following formula in cell E12,

The formula will give the accumulated depreciation for the first period.

➤ Press ENTER and drag cell E12 to end of your dataset.

As a result, you will get accumulated depreciation for each period.

At last,

➤ Type the formula in cell F12,

The formula will give the depreciated asset value at the end of the first period.

➤ Press ENTER and drag cell E12 to the end of your dataset.

As a result, you will get depreciated asset value at the end of every period. It will complete your depreciation schedule.

7. French Straight Line Depreciation Schedule

If you know the purchasing date of an asset, you can prepare a French straight line depreciation schedule with a constant depreciation rate using the AMORLINC function. Let’s say we have the following dataset with the purchasing date of the asset.

In this method, we will calculate depreciation considering the starting period because the AMORLINC function calculates depreciation from the beginning (period 0). We can modify this formula to calculate depreciation from period 1. We will see that in the next method (using another function with similar syntax)

Now,

➤ Type the following formula in cell D10.

Here, $C$4 is the initial cost of the asset, $C$5 purchasing date, $B$11 is the first period date, $C$6 is the salvage value, C10 is the period 0 and $C$7 is the depreciation rate.

➤ Press ENTER and drag the cell to the end.

You will get the depreciation for all the periods.

If you observe, you will see depreciation in the last two years is 0. This is because the AMORLINC function depreciates the asset at a predetermined constant rate for each year. Here the number of periods doesn’t matter.

Now, the accumulated depreciation for the first period will be the same as the depreciation.

To get the accumulated depreciation in all other periods,

➤ Type the following formula in cell E11,

The formula will calculate the accumulated depreciation for each period.

➤ Press ENTER and drag the cell E11 to the end.

So, you will get accumulated depreciation for all the periods.

At last,

➤ Type the formula in cell F10,

The formula will give the depreciated asset value at the end of period 0.

➤ Press ENTER and drag the cell E10 to the end of your dataset.

As a result, you will get depreciated asset value at the end of every period. It will complete your depreciation schedule.

8. French Declining Balance

In the last method, we will see how to prepare a depreciation schedule using the French Declining Balance method. In this method, we will use the AMORDEGRC function. This function calculates French declining balance depreciation. It is kind of similar to the AMORLINC function. However, the AMORDEGRC function includes a depreciation coefficient that accelerates depreciation based on the asset’s useful life.

To prepare a depreciation schedule using the AMORDEGRC function,

Type the following formula in cell D11.

Here, $C$4 is the initial cost of the asset, $C$5 purchasing date, $B$11 is the first period date, $C$6 is the salvage value, C10 is the period 0 and $C$7 is the depreciation rate.

Here depreciation is considered from the first period.

Now,

➤ Press ENTER and drag cell D11 to the end of the dataset.

It will give the French declining depreciation for all the periods.

After that, to get the accumulated depreciation,

➤ Type the following formula in cell E11,

The formula will calculate the accumulated depreciation for each period.

➤ Press ENTER and drag the cell E11 to the end.

So, you will get accumulated depreciation for all the periods.

Finally,

➤ Type the formula in cell F11,

The formula will give the depreciated asset value at the end of the first period.

➤ Press ENTER and drag the cell E11 to the end of your dataset.

As a result, you will get the depreciated asset value at the end of every period. It will complete your depreciation schedule.

Conclusion

I hope now you know how to prepare a depreciation schedule in Excel. You can use any of these methods according to your accounting principles. If you have any confusion please leave a comment.

Related Articles

• How to Create a Weekly Schedule in Excel (2 Suitable Methods)
• Make an Hourly Schedule in Excel (with Easy Steps)
• How to Create a Project Schedule in Excel (with Easy Steps)
• Create a Workback Schedule in Excel (with Easy Steps)