Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
The declining balance method of calculating depreciation accelerates the rate at which an asset loses its value over time. You use the DB function to calculate depreciation with the declining balance method in Excel. The declining balance method is so named because it reduces an asset's value by the amount it depreciated in the previous years. You then calculate the new depreciation based on that lower value, hence the name declining balance method. The DB function has four arguments. Cost, salvage value, economic life and period.
The Cost, which is in cell B7, is what you pay for the asset. The Salvage Value, which is in cell B9, is what you can sell the asset for as scrap. And then next we have the Economic Life, which in this case is assumed to be 7 years. And then you have the Period and that is the year in the depreciation schedule and I have a series of those years set up here in cells D6 through D12. So I'll be calculating depreciation for years 1 through 7. There is one final argument and that is whether you're going monthly or yearly.
Almost every asset you deal with will depreciate over years instead of months. So I've decided to stay with years for this example. Now I'll create my first formula and to do that I'll click in cell E6 and then type an =db, to start entering formula using the Declining Balance function. Type a left parenthesis and then we have the cost that's in b7, type a comma, then the salvage value that's in b9, the economic life in years and that is in cell b11, then a comma, and the period.
Now the first period is in cell D6, so that is the first year. So type d6. Now what's going to happen when I press Enter to put this formula into this table cell is that Excel will copy it down to the rest of the cells in a table column. When it does that, the references to cells B7, B9, and B11 will change because they are currently relative references. However, there aren't other values here in the side of the worksheet. I want those references to stay the same. So I need to convert those cell references to absolute values as opposed to relative values.
And to do that in the formula, I click in the cell reference that I want to make absolute and then press F4. That changes the reference from a relative reference to an absolute reference. Then I do the same thing for B9, click inside of it in the formula and then press F4 and the same for B11. Click inside of its reference and then press F4. Now D6 which is the cell here for the year, I do want to change because as it copies down I want to look to D7, then D8, then D9 and so on.
So with those changes in place I'll click to the right of the D6 reference, then type a right parenthesis and press Enter. And when I do, Excel fills in my table. I now have depreciation for years 1 through 7. The declining balance depreciation method enables companies to capture more depreciation benefits early in an asset's economic life, decreasing tax payments and thereby freeing up capital to invest in other areas.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.