Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

dataset


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.

Depreciated asset

Now, to calculate depreciation in the year 2013,

➤ Type the following formula in cell D12,

=SLN($C$4,$C$6,$C$7)

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

depreciation schedule in Excel

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

depreciation schedule in Excel

Now,

➤ Drag cell D12 to the end of your schedule.

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

depreciation schedule in Excel

Now to calculate accumulated depreciation in your template,

➤ Type the following formula in cell E12,

= E11+D12

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

accumulated depreciation

➤ Press ENTER.

you will get the accumulated depreciation till that period.

accumulated depreciation

Now,

➤ Drag cell E12 to the end of your schedule.

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

accumulated depreciation

Finally, to calculate depreciated asset values,

➤ Type the following formula in cell F12,

=$C$4-E12

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

Depreciated asset

➤ Press ENTER.

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

Depreciated asset

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.

Depreciated asset


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,

=SYD($C$4,$C$6,$C$7,C12)

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

depreciation schedule in Excel

➤ Press ENTER.

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

depreciation schedule in Excel

➤ 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,

= E11+D12

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.

accumulated depreciation

Finally, to calculate depreciated asset values,

➤ Type the following formula in cell F12,

=$C$4-E12

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.

Depreciated asset

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,

=DB($C$4,$C$6,$C$7,C12)

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

depreciation schedule in Excel

➤ Press ENTER.

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

depreciation schedule in Excel

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

depreciation schedule in Excel

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.

Depreciated asset

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,

=DDB($C$4,$C$6,$C$7,C12)

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.

depreciation schedule

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.

accumulated depreciation

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.

depreciation schedule in Excel

First,

➤ Type in the following formula in cell E12,

=(($C$4-$C$6)/$C$8)*D12

The formula will determine depreciation for the first period.

depreciation schedule in Excel

After that,

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

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

accumulated depreciation

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.

Depreciated asset


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,

=VDB($C$4,$C$6,$C$7,C11,C12)

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.

depreciation schedule in Excel

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.

depreciation schedule in Excel

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

➤ Type the following formula in cell E12,

=VDB($C$4,$C$6,$C$7,$C$11,C12)

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

depreciation schedule in Excel

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

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

accumulated depreciation

At last,

➤ Type the formula in cell F12,

=$C$4-E12

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.

Depreciated asset


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.

depreciation schedule in Excel

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.

=AMORLINC($C$4,$C$5,$B$11,$C$6,C10,$C$7)

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.

depreciation schedule in Excel

➤ Press ENTER and drag the cell to the end.

You will get the depreciation for all the periods.

depreciation schedule in Excel

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,

=E10+D11

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.

accumulated depreciation

At last,

➤ Type the formula in cell F10,

=$C$4-E10

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.

Depreciated asset


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.

=AMORLINC($C$4,$C$5,$B$11,$C$6,C10,$C$7)

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.

depreciation schedule in Excel

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.

depreciation schedule in Excel

After that, to get the accumulated depreciation,

➤ Type the following formula in cell E11,

=E10+D11

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.

accumulated depreciation

Finally,

➤ Type the formula in cell F11,

=$C$4-E11

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.

Depreciated asset


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

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo