bkastler001
New Member
- Joined
- Oct 5, 2009
- Messages
- 5
I need some help with a formula. The VDB formulaworks great for computing "MACRS" depreciation expense for a particular year, but I need to adjust the formula to compute accumulated depreciation (that is for several years).
MACRS is a specific tax calculation for depreciation, that is based on declining balance, with a switch to straight line if it is more advantageous. Further, for the first year, MACRS in computing depreciation expense, only take one half of the annual depreciation calculation
The VDB function is (cost, salvage value, asset life, start period, end period, factor [such as double decling balance) and will provide the expense for a particular year. I initially thought if my start period was at the beginning and end period was the current period, I would get the proper accumulated depreciation - but because MACRS switches to straight line when it is more advantageous, I believe I am not getting the correct answer.
I am thinking an array formula, that sums the annual calculation would work, but cannot figure out how to write this as an array.
The formula I am using for the annual depreciation for a 5 year asset is =VDB(1000,0,5,MAX(0,A3-1.5),MIN(A3-.05),2) [Where A3 is year 3 of the five year period (A4 is year 4, etc)]. I would think the formula is:
{=sum(VDB(1000,0,5,{MAX(0,A1-1.5:A3-1.5)},{MIN(A1-.5:A3-.5},2)} but this obviously does not work.
Any help would be appreciated. I can send the file if requested.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 1820" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl121 height=20 width=64 x:str="'=VDB(1,0,C$4,$A6-0.5,MIN($A7-0.5,C$4),2)"></TD></TR></TBODY></TABLE>
MACRS is a specific tax calculation for depreciation, that is based on declining balance, with a switch to straight line if it is more advantageous. Further, for the first year, MACRS in computing depreciation expense, only take one half of the annual depreciation calculation
The VDB function is (cost, salvage value, asset life, start period, end period, factor [such as double decling balance) and will provide the expense for a particular year. I initially thought if my start period was at the beginning and end period was the current period, I would get the proper accumulated depreciation - but because MACRS switches to straight line when it is more advantageous, I believe I am not getting the correct answer.
I am thinking an array formula, that sums the annual calculation would work, but cannot figure out how to write this as an array.
The formula I am using for the annual depreciation for a 5 year asset is =VDB(1000,0,5,MAX(0,A3-1.5),MIN(A3-.05),2) [Where A3 is year 3 of the five year period (A4 is year 4, etc)]. I would think the formula is:
{=sum(VDB(1000,0,5,{MAX(0,A1-1.5:A3-1.5)},{MIN(A1-.5:A3-.5},2)} but this obviously does not work.
Any help would be appreciated. I can send the file if requested.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 1820" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl121 height=20 width=64 x:str="'=VDB(1,0,C$4,$A6-0.5,MIN($A7-0.5,C$4),2)"></TD></TR></TBODY></TABLE>
