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.

**Table of Contents**hide

## 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**,

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

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

`= E11+D12`

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

`=$C$4-E12`

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

`=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**).

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

`= 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.

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.

**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.

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

`=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.

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

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

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

`=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.

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

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

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

`=$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.

### 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**.

`=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.

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

`=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.

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.

### 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.

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

`=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.

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.

## 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.