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.

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 (S_n): 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.

How to Calculate Monthly Depreciation Schedule in Excel: 8 Quick Steps

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 Microsoft 365. You can use any available Excel version.

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.

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

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.

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

Read More: How to Create Depreciation Schedule 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.

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

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.

• After that, press ENTER.

Hence, you will see the result in cell G12.

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

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.

• Furthermore, press ENTER.

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

Read More: How to Create Vehicle Depreciation Calculator 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.

This formula adds cells I12 and H12.

• After that, press ENTER.

Therefore, you can see the result in cell J12.

• Along with that, for the month of January,o we will put $0 in cell K12 for calculating the value Accumulated Dep. of Starting of the Month.

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.

• 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 the End of the Month for the month of January in the depreciation schedule.

• In the beginning, we will type the following formula in cell M12.

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

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

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.

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

• After that, press ENTER.

Hence, you can see WDV at the start of the 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.

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

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

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

• At this point, press ENTER.

Therefore, you can see the result in cell J13.

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

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.

• Furthermore, press ENTER.

Hence, you can see the result in cell L13.

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

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 completed the March month of depreciation schedule.

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

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

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

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

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.

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

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

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

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

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

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

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.

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

• Afterward, press ENTER.

Therefore, you can see the result in cell J10.

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

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

• At this point, we will press ENTER.

Therefore, you can see the result in cell L10.

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

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

Download Practice Workbook

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

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.

<< Go Back to Depreciation Template | Finance Template | Excel Templates