From the course: Excel: Financial Functions in Depth

Unlock this course with a free trial

Join today to access over 24,700 courses taught by industry experts.

Creating a depreciation schedule

Creating a depreciation schedule - Microsoft Excel Tutorial

From the course: Excel: Financial Functions in Depth

Start my 1-month free trial Buy for my team

Creating a depreciation schedule

“

- [Instructor] Now that we've calculated the depreciation on a monthly basis, we'll be able to create the depreciation schedule below. So, we've got our opening balance already calculated here for you, just using a SUMIFS function that will pick up all of the fixed assets that were purchased prior to the beginning of our schedule. So we can see that there was $405,000 that needs to roll forward for our opening balance. So, let's go in now and create another SUMIFS function that is going to pick up all of our CapEx that is purchased for each month. So we need to deduct that from our schedule. The problem though, as we discussed back in chapter four, is that the dates that we have used here for our spend date, very different to the dates that we have on our schedule. So we've used an EOMONTH and a SEQUENCE plus one, which is the first of the month, and we can see that our spend dates are all over the place, so they are not going to be picked up correctly in our SUMIFS function. So…

Contents