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

How to Create Monthly Depreciation Schedule in Excel

If you want to create a monthly depreciation schedule in Excel, you have come to the right place. Here, we will walk you through some easy methods to do the task smoothly.


Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


What Is a Depreciation Schedule?

The reduction in the value of an asset over multiple numbers of years is called Depreciation. A Depreciation Schedule is a dataset that represents the reduction in the amount of assets over the span of the asset’s life. This schedule is used for calculating total yearly depreciation for several numbers of assets.

Depreciation Terms:

  • Cost (C): This is the cost of the asset.
  • Salvage Value (Sn): This is the value of the asset at the end of the depreciation period.
  • Depreciation Period (n) or Recovery Period: This is the useful life period of the asset.

8 Steps to Calculate Monthly Depreciation Schedule in Excel

In the following dataset, you can see the Assets, Purchase Date, Actual Cost, Salvage Value, and Depreciations Rate columns. Further, using this dataset, we will create a monthly depreciation schedule in Excel. Here, we used Excel 365. You can use any available Excel version.

Dataset to Create Monthly Depreciation Schedule Excel


Step-1: Using Data Validation Tool to Insert Assets in Excel

Here, you can see the outline of the monthly Depreciation schedule with all the necessary terms. In this step, we will use the Data Validation feature to insert the Asset name in our Depreciation schedule. This is because we want to insert only one asset name at a time, we want to see the monthly depreciation schedule of that particular asset.

Outline for Creating Monthly Depreciation Schedule Excel

  • First of all, select merged cells B12:B14 >> go to the Data tab.
  • After that, from Data Tools >> select the Data Validation group.
  • Moreover, select Data Validation.

Using Data Validation Feature to Create Monthly Depreciation Schedule Excel

At this point, a Data Validation dialog box will appear.

  • Then, from the Allow group >> we will select List.

  • Then, we will click on the upward arrow of the Source box to select the source cells.

  • Furthermore, we will select cells B5:B7 as the Source cells.

Selecting Source Cells to Create Monthly Depreciation Schedule Excel

  • Then, we will click OK in the Data Validation dialog box.

  • Next, to insert an Asset in cell B12, we will click on the drop-down arrow of cell B12.

This will bring out the assents name.

  • Then, we will select Car since we want to create a monthly depreciation schedule in Excel for Car first.

After that, we will insert the months that we want to show in the monthly depreciation schedule in cells C12:C14.

Hence, you can see the asset along with its months in the monthly depreciation schedule in Excel.

Inserting Monts to Create Monthly Depreciation Schedule Excel

Read More: How to Create Rental Property Depreciation Calculator in Excel


Step-2: Calculating Return Down Value at Starting Month of January

In this step, we will calculate the return down value at the start of the month. Here, WDV indicates return down value.

  • In the beginning, we will type the following formula in cell D12.
=IFS(B12=B5,D5-E5,B12=B6,D6-E6,B12=B7,D7-E7)

Calculating WDV at Staring of Month to Create Monthly Depreciation Schedule Excel

Formula Breakdown

  • IFS(B12=B5,D5-E5,B12=B6,D6-E6,B12=B7,D7-E7)the IFS function finds out whether one or more conditions are met, and then finds out the value of the corresponding True condition.
  • IFS(B12=B5,D5-E5,B12=B6,D6-E6,B12=B7,D7-E7) → becomes
    • Output: $22,000
  • Explanation: Here, $22,000 is the WDV at Starting of Month of January.
  • After that, press ENTER.

Hence, you will see the result in cell D12.

After that, since January is the starting month, there will be no Addition During the Year and Deletion During the Year.

  • As a result, we put $0 in cells E12 and F12 respectively.

Therefore, you can see WDV at Starting of Month, Addition During the Year and Deletion During the Year in cells D12, E12, and F12 respectively.

Read More: How to Use WDV Method of Depreciation Formula in Excel


Step-3: Evaluating the Current Value of Assets in Depreciation Schedule

In this step, we will calculate the Current Value of Assets to create a monthly depreciation schedule.

  • First of all, we will type the following formula in cell G12.
=IFS(B12=B5,D12+E12+E5-F12,B12=B6,D12+E12+E6-F12,B12=B7,D12+E12+E7-F12)

Using IFS Function to Create Monthly Depreciation Schedule Excel

Formula Breakdown

  • IFS(B12=B5,D12+E12+E5-F12,B12=B6,D12+E12+E6-F12,B12=B7,D12+E12+E7-F12)the IFS function finds out whether one or more conditions are met, and then finds out the value of the corresponding True condition.
  • IFS(B12=B5,D12+E12+E5-F12,B12=B6,D12+E12+E6-F12,B12=B7,D12+E12+E7-F12)→ becomes
    • Output: $30,000
  • Explanation: Here, $30,000 is the Current Value of Assets of month January.
  • After that, press ENTER.

Hence, you will see the result in cell G12.

Calculated Current Value of Asset for Creating Monthly Depreciation Schedule Excel

  • Later, depreciation during the year is 0 for the starting month of January, we put $0 in cell H11.

Therefore, you can see the Current Value of the Assets and Dep. During the Year in cells G12, and H12 respectively.

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


Step-4: Calculating Depreciation of Current Value for the Month of January

In this step, we will find out the depreciation of the current value for January to create a monthly depreciation schedule.

  • In the beginning, we will type the following formula in cell I12.
=IFS(B12=B5,D5*F5,B12=B6,D6*F6,B12=B7,D7*F7)

Calculating Dewp. Of Current Value to Create Monthly Depreciation Schedule Excel

  • Furthermore, press ENTER.

Therefore, you can see the Dept. of Current Value for the month of January in cell I12.

Read More: How to Use SLM Method of Depreciation Formula in Excel


Step-5: Computing Total Depreciation in the Monthly Depreciation Schedule

In this step, we will calculate the Total Depreciation for January by creating a monthly Depreciation schedule.

  • First of all, we will type the following formula in cell J12.
=I12+H12

This formula adds cells I12 and H12.

Calculating Total Depreciation to Create Monthly Depreciation Schedule Excel

  • After that, press ENTER.

Therefore, you can see the result in cell J12.


Step-6: Calculating Accumulated at End of Month of January

In this step, we will find out the value of Accumulated at the End of the Month, for the month of January in the monthly depreciation schedule.

  • In the beginning, we will type the following formula in cell L12.
=J12+K12

Calculating Accumulated at the End of Month for January to Create Depreciation Schedule Excel

  • After that, press ENTER.

As a result, you can see the result in cell L12.


Step-7: Evaluating Return Down Value of Depreciation Schedule at End of Month January

In this step, we will find out the WDV at End of Month for the month of January in the depreciation schedule.

  • In the beginning, we will type the following formula in cell M12.
=IFS(B$12=B$5,D$5-L12,B$12=B$6,D$6-L12,B$12=B$7,D$7-L12)

  • Then, press ENTER. Hence, you can see the result in cell M12.

As a result, you can see the depreciation schedule for January.

Calculating WDV at End of Month January for Depreciation Schedule Excel


Step-8: Creating a Depreciation Schedule for the Month of February

In this step, for the month of February, we will create the depreciation schedule.

Here, the return down value at starting of month February is equal to the return down value at the end of the month of January.

  • Hence, in cell D13, we type the following formula.
=M12

This will input the value of cell M12 in cell D13.

WDV at Starting of Month February for Monthly Depreciation Schedule Excel

  • After that, press ENTER.

Hence, you can see WDV at the Starting of Month for the month of February in cell D13.

  • Along with that, since Addition During the Year and Deletion During the Year are $0 for the month of February, we put $0in cells E13 and F13 respectively.

  • Moreover, we type the following formula in cell G13 to calculate the Current Value of Assets for the month of February.
=D13+E13-F13

Calculating Cuirrent Vakue of Asset for February to Create Monthly Depreciation Schedule Excel

  • After that, press ENTER.

Therefore, you can see the result in G13.

  • Furthermore, since Dep. During the Year is 0 for the month of February, we put $0 in cell H13.

  • Moreover, since the Dep. of Current Value is the same for the months of January and February, we put the following formula in cell I13.
=I12

This will input the value of cell I12 in cell I13.

Calculating Dep. of Current Value for February to Create Monthly Depreciation Schedule Excel

  • Then, press ENTER.

Therefore, you can see the result in cell I13.

  • Afterward, we find out the Total Depreciation by typing the following formula in cell J13.
=I13+H13

  • At this point, press ENTER.

Therefore, you can see the result in cell J13.

Calculated Total Depreciation for Month February in Depreciation Schedule in Excel

  • Furthermore, since the value of Accumulated Dep. of Starting of the Month for January and February is the same, we will type the following formula in cell K13.
=L12

Calculating Accumulated Dep. at Starting of Month February for Monthly Depreciation Schedule Excel

This will input the value of cell L12 in cell K13.

  • Then, press ENTER.

Hence, you can see the result in cell K13.

  • Moreover, to calculate the Accumulated Dep. of Starting of Month for the month of February, we will type the following formula in cell L13.
=J13+K13

Calculating Accumulated at the End of Month February for Monthly Depreciation Schedule Excel

  • Furthermore, press ENTER.

Hence, you can see the result in cell L13.

  • Later, to find out the WDV at End of Month for February, we will simply drag down the formula of cell M12 to cell M13 by the Fill Handle Tool.

Using Fill Handle Tool for Monthly Depreciation Schedule Excel

Therefore, you can see the result in cell M13. Along with that, you can see the complete February month in the depreciation schedule.

  • In a similar way, we complete the March month of depreciation schedule.

Hence, you can see the complete monthly depreciation schedule for the asset Car.

Complete Monthly Depreciation Schedule Excel for Asset Car

  • Along with that, you can see the complete monthly depreciation schedule for asset Machinery.

Complete Monthly Depreciation Schedule Excel for Asset Machinery

  • Moreover, you can see the complete monthly depreciation schedule for asset Furniture.


How to Create a Fixed Asset Depreciation Template in Excel

Here, we will show you how you can create a fixed asset depreciation schedule.

In the following table, you can see the properties of the asset Car.

Next, we will create a fixed asset depreciation template for the asset Car.

Here, you can see the outline and months for the fixed asset depreciation template.

Next, we will find the depreciation schedule template for the asset Car.


Step-1: Calculating Depreciation for January

In this step, we will find out the depreciation schedule for January.

  • First of all,  to calculate the WDV at Starting of Month, we will type the following formula in cell D9.
=D5-E5

  • After that, press ENTER.

As a result, you can see the result in cell D9.

After that, since January is the starting month, there will be no Addition During the Year and Deletion During the Year.

  • As a result, we put $0 in cells E9 and F9 respectively.

Therefore, you can see WDV at Starting of Month, Addition During the Year, and Deletion During the Year in cells D9, E9, and F9 respectively.

  • Afterward, to find out the Current Value of the Asset, we will type the following formula in cell G9.
=IFS(B12=B5,D12+E12+E5-F12,B12=B6,D12+E12+E6-F12,B12=B7,D12+E12+E7-F12)

  • After that, press ENTER.

As a result, you can see the result in cell G9.

  • Along with that, since the Dep. During the Year is $0 for the month of January, we put $0 in cell H9.

  • Furthermore, to find out the Dep. of Current Value, we will type the following formula in cell I9.
=D5*F5

  • Afterward, press ENTER.

Therefore, you can see the result in cell I9.

  • In addition, to find out the Total Depreciation, we will type the following formula in cell J9.
=I9+H9

  • After that, press ENTER.

As a result, you can see the result in cell J9.

  • Along with that, since Accumulated Dep. at Starting of Month is 0 for January, we put $0 in cell K9.

  • Moreover, to calculate the Accumulated at End of Month, we type the following formula in cell L9.
=J9+K9

  • Furthermore, we will type ENTER.

Hence, you will see the result in cell L9.

  • After that, to calculate the WDV at End of Month, we will type the following formula in cell M9.
=$D$5-L9

  • Then, press ENTER. As a result, you can see the result in cell M9.

Therefore, you can see the depreciation schedule for January.


Step-2: Creating a Depreciation Schedule for February

In this step, we will create a depreciation schedule for February for a fixed asset.

  • Here, since WDV at End of the Month of January is equal to WDV at Starting of Month of February, first of all, in cell D13, we type the following formula.
=M9

This will input the value of cell M9 in cell D10.

  • After that, press ENTER.

Hence, you can see WDV at the Starting of Month for the month of February in cell D10.

  • Along with that, since Addition During the Year and Deletion During the Year are $0 for the month of February, we put $0 in cells E10 and F10 respectively.

  • Then, to find out the Current Value of the Asset for the month of February, we will drag down the formula of cell G9 to cell G10 with a Fill Handle tool.

Therefore, you can see the Current Value of the Asset for the month of February in cell G10.

  • Along with that, since Dep. During the Year for the month of February is 0, we put $0 in cell H10.

  • Furthermore, since the value of Dep. of Current Value for January is equal to Dep. of Current Value in February, we will type the following formula in cell I10.
=I9

  • Furthermore, press ENTER.

Hence, you can see the result in cell I10.

  • Afterward, to find out the Total Depreciation, we will type the following formula in cell J10.
=I10+H10

  • Afterward, press ENTER.

Therefore, you can see the result in cell J10.

  • In addition, since the Accumulated Dep. at Starting of Months for January and February is equal, we will type the following formula in cell K10.
=L9

  • At this point, press ENTER.

Hence, you can see the result in cell K10.

  • Furthermore, to find out the Accumulated at the End of Month for February, we will type the following formula in cell L10.
=J10+K10

  • At this point, we will press ENTER.

Therefore, you can see the result in cell L10.

  • After that, to find out the WDV at End of Month for February, we will type the following formula in cell M10.
=$D$5-L10

  • Furthermore, press ENTER. Therefore, you can see the result in cell M10.

As a result, you can see the complete depreciation schedule for February.

  • In a similar way, we created the depreciation schedule for the month of March.

Hence, you can see the depreciation schedule template for a fixed asset.

Read More: How to Create Fixed Asset Depreciation Calculator in Excel


Practice Section

You can download the above Excel file to practice the explained method.


Conclusion

Here, we tried to show you some easy steps to create a monthly depreciation schedule in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo