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

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.


Download Practice Workbook

You can download the practice workbook from here.


8 Easy Methods to Use MACRS Depreciation Formula in Excel

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.

Dataset Use Macrs Depreciation Formula in Excel


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.
=SLN(C4;C5;C6)

Inserting SLN Formula to Use Macrs Depreciation Formula in Excel

  • Next, you will get the desired result.

Showing Result to Use Macrs Depreciation Formula in Excel

Read More: How to Use SLM Method of Depreciation 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. The steps of this method are.

Steps:

  • First, in the C8 cell insert the following formula.
=SYD(C4;C5;C6;C7)

Inserting SYD function to Use Macrs Depreciation Formula in Excel

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

Showing Result to Use Macrs Depreciation Formula in Excel

Read More: Calculate Sum of Years Digits Depreciation with Formula in Excel


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.
=DDB(C4;C5;C6;C7)

Inserting Formula to Use Macrs Depreciation Formula in Excel

  • In addition, you will get the desired result.

Showing Result to Use Macrs Depreciation Formula in Excel

Read More: How to Use WDV Method of Depreciation Formula 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.
=DB(C4;C5;C6;C7;C8)

Inserting DB formula to Use Macrs Depreciation Formula in Excel

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

Showing Final Result to Use Macrs Depreciation Formula in Excel

Read More: How to Create Monthly Depreciation Schedule 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 are 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.
=VDB(C4;C5;C6;C7;C8;C9;TRUE)

  • 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 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.
=((C4-C5)/C6)*C7

  • Thereafter, 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.
=AMORDEGRC($C$5;$C$6;$B$12;$C$7;C11;$C$8)

  • 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 by 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.
=AMORLINC($C$5;$C$6;$B$12;$C$7;C11;$C$8)

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


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. Follow the ExcelDemy website for more articles like this. 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

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo