Investing in real estate? Thinking about buying new equipment for your business? The depreciation you take on your tax return can turn into a significant tax benefit. But you need to make some complex calculations. Fortunately, Microsoft Excel can help.
Excel’s VDB function calculates declining balance depreciation for an asset given the cost, its
salvage value, estimated economic life, the starting accounting period and the ending accounting period for which depreciation is being calculated, the factor at which the balance
declines, and, optionally, a switch-to-straight-line switch which is set to either TRUE or
FALSE. If you set this switch to TRUE, Excel doesn’t switch to straight-line at the point
when straight-line depreciation exceeds declining balance depreciation. If you set this value
to FALSE, Excel does switch to straight-line. If you don’t set the optional switch-to-straight-
line switch to TRUE, Excel sets this value to FALSE.
Note: By default, US tax laws do convert to straightline depreciation at the point that straightline depreciation exceeds declining balance deprecation. But back to the mechanics of the VBD function...
The VDB function uses the following syntax:
VBD(cost,salvage,life,start-period,end-period,factor,switch)
Suppose, for example, that you must calculate 150% declining balance depreciation for
equipment that costs $50,000, lasts five years, and will have a salvage value of $10,000 at
the end of the fifth year. To calculate the depreciation for the first year, you use the follow-
ing formula:
=VBD(50000,10000,5,0,1,150%)
The function returns the value 15000.00. Notice that to calculate depreciation for the first
year, you set the start period to 0 and the end period to 1. To calculate the depreciation for
the second year, you use the formula
=VBD(50000,10000,5,1,2,150%)
The function returns the value 10500.00. Notice that to calculate the depreciation for the
second year, you set the start period to 1 and the end period to 2.
In both of the two preceding examples, Excel will automatically switch to straight-line
depreciation at the point when straight-line depreciation for a period exceeds declining
balance depreciation. To instruct Excel not to make this switch, you would use the follow-
ing formula to calculate depreciation for the first year:
-VBD(50000,10000,5,0,1,150%,TRUE)
The word TRUE, which Excel interprets as 1, tells Excel not to switch to straight-line. To
instruct Excel not to make this switch in the second year, you would use the following formula to calculate depreciation:
=VBD(50000,10000,5,1,2,150%,TRUE)
CPA Stephen L. Nelson is the author of QuickBooks for Dummies and Do-It-Yourself LLC Incorporation and Formation Kits for all fifty states. Formerly an adjunct tax professor at Golden Gate University. You can reach him at www.llcsexplained.com.
|
|
