How to Use MACRS Depreciation Formula in Excel (8 Methods)

This tutorial will demonstrate how to use the MACRS depreciation formula in Excel. A MACRS Depreciation Schedule is a table that displays the amount of depreciation for a fixed asset throughout its life. For any company, the most important thing is to maintain a Projected financial statement. Numerous systematic and unsystematic risks exist that could prevent the projection. The last stage of projecting the financial statement is to determine the depreciation. So, it is very important to learn how to use the MACRS depreciation formula in Excel.

How to Use MACRS Depreciation Formula in Excel: 8 Easy Methods

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have the Initial Cost in Cell B4, the salvage Value in Cell B5, and the useful life in Cell B6. We will use these cells to determine the Depreciation per yr. If you follow the steps correctly, you should learn how to use the MACRS depreciation formula in Excel on your own.

1. Using Excel SLN Function in Formula to Calculate MACRS Depreciation

We want to calculate depreciation by using the SLN function. It is an Excel Financial function that calculates the depreciation of an asset for one time period using the straight-line method for depreciation calculation. During each asset’s lifetime, the straight-line technique 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.

The steps of this method are

Steps:

• First, in the C7 cell insert the following formula.

• Next, you will get the desired result.

Read More: How to Calculate Straight Line Depreciation Using Formula in Excel

2. Use of SYD Function in Excel Formula to Calculate MACRS Depreciation

Now, our goal is to create depreciation by using the SYD formula in Excel. Assets do not depreciate linearly over time. The number of depreciation changes every year. To consider this fact, Excel has the built-in SYD function to calculate depreciation. Let’s follow the instructions below to calculate sum of years digits depreciation with the formula in Excel!

Steps:

• First, in the C8 cell insert the following formula.

• After that, the formula will return the output value we expected.

3. Applying DDB Function in Excel Formula to Calculate MACRS Depreciation

Again, we want to create depreciation by using the DDB function in Excel. A 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: cost, salvage, life, period, and factor. We will learn the whole process below

Steps:

• To begin with, in the C8 cell insert the following formula.

• In addition, you will get the desired result.

Read More: How to Calculate Double Declining Depreciation in Excel

4. Using DB Function

Next, we can also create depreciation by using the DB function in Excel. 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. The steps of this method are below

Steps:

• Firstly, in the C9 cell insert the following formula.

• Next, the formula will return the output value we expected.

Read More: How to Calculate Accumulated Depreciation in Excel

5. Use of VDB Function

This time, we want to create depreciation by using the VDB function in Excel. This function will determine the depreciation of an instrument for a specific time span. Since we are calculating the depreciation from a specific month span, the useful life is converted to the month. The Factor defines the rate at which the balance declines and the No_switch is used to determine the nature of the depreciation method. If the value is TRUE, Excel will not switch to a straight-line depreciation method. The steps of this method are below

Steps:

• First, in the C10 cell insert the following formula.

• Next, you will get the desired result.

6. Using Units of Production

In this case, our goal is to create depreciation by using the Units of Production function in Excel. You can also prepare a depreciation schedule in Excel using the Units of Production methods. The depreciation for one period using the units of production depreciation 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. The steps of this method are below

Steps:

• First, in the C8 cell insert the following formula.

• After that, the formula will return the output value we expected.

7. Utilizing AMORDEGRC Function

Furthermore, we want to create depreciation by using the AMORDEGRC function in Excel. However, the AMORDEGRC function includes a depreciation coefficient that accelerates depreciation based on the asset’s useful life. Let’s say we have the following dataset with the purchasing date of the asset.

Steps:

• To begin with, arrange a dataset like the image below.

• In addition, in the D12 cell insert the following formula.

• Furthermore, after pressing the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

• Finally, you will get the desired result.

8. Applying AMORLINC Function

Finally, we want to create depreciation using the AMORLINC function in Excel. This function calculates French declining balance depreciation. It is kind of similar to the AMORLINC function. 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. The steps of this method are below

Steps:

• First, arrange a dataset like the image below.

• Secondly, in the D11 cell insert the following formula.

• Thirdly, after pressing the Enter button, you will get the result for the cell and then use the Fill Handle to apply the formula to all the desired cells.

• Lastly, you will get the desired result.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to use the MACRS depreciation formula in Excel. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.

Related Articles

• How to Use WDV Method of Depreciation Formula in Excel
• How to Use Formula to Calculate Car Depreciation in Excel

<< Go Back to Depreciation Formula In Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel