Making financial forecasts or performing investment analyses that need to include depreciation? Microsoft Excel can help. Excel's DB, or declining balance, depreciation function lets you calculate fixed-declining balance depreciation.
The DB function calculates fixed declining balance depreciation for an asset given the cost, it’s salvage value, estimated economic life, the accounting period for which depreciation is being calculated, and, optionally, the number of month in first year. (If you don’t include the optional month argument, Excel sets this value to 12.) The DB function uses the following syntax:
DB (cost, salvage, life, period, month)
Suppose, for example, that you must calculate the fixed declining balance depreciation for equipment that costs $50,000, lasts five years, will have a salvage value of $10,000 at the end of the fifth year, and that was placed into service in the third month of the first year. To calculate the depreciation for the first year, you use the following formula:
=DB(50000,10000,5,1,3)
The function returns the value 3437.5. To calculate the depreciation for the second year, you use the formula
=DB(50000,10000,5,2,3)
The function returns the value 12804.69
The distinguishing feature of fixed-declining balance depreciation is that it calculates depreciation at a fixed rate based on the estimated cost, salvage value, and economic life of the asset. Excel calculates this rate using the following formula:
Fixed rate=1-((salvage/cost)^(1/life))
and then rounds this value to the nearest three decimal places. To calculate the depreciation for a period, Excel multiplies the rate by the sum of the original cost less the accumulated depreciation to date.
NOTE: The accumulated depreciation equals the original cost minus the previous periods’ depreciation.
Excel uses variations of the standard fixed-declining balance formula for the first and last periods. For the first period, Excel calculates the depreciation by using the following formula:
First-period depreciation=cost * rate * month / 12
For the last period, Excel calculates the depreciation using the following formula (which essentially just depreciates the asset down to its salvage value):
Last-period depreciation=((cost. accumulated depreciation)*rate*(12-month))/12
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
Using Excel's Xirr and Xnpv Add-in Functions
Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.
