I am having problems trying to figure out a formula here and hope someone can help me out.
Yr Value Depreciation % Depreciation Amount New Value
1 100 20% 20 80
2 80 40% 32 48
3 48 60% 28.8 19.2
4 19.2 80% 15.36 3.84
5 3.84 100% 3.84 0
As you can see year 1 it depreciates a full 20% and the new value of year 1 becomes 80, but what if I am trying to get a value that is 18 month, which would include a Year 1 and half of year two. What should be the formula if I just want to plug in 18 as a value? TIA
You have to add one column for how many months you want to calculate depreciation. which I had in attached Column D. see attached new version in which you have only add values in Column D (months to depreciate).
Wouldn't you figure 1.5 years of depreciation as 1 year at 20% and a half year at 40%? I realize that the depreciation is probably governed by accounting and tax rules in your country, but I am trying to apply logic where logic may not necessarily be invited.
If my assumption is correct, then assuming a starting value of $100, you would have $80 after 1 year and $64 after 1.5 years.
The array-entered formula for this would be:
=B1*PRODUCT(100%-OFFSET(G3,0,0,B2+1))*(100%-(B2-INT(B2))*VLOOKUP(B2+1,tbDepreciation,2))
To array-enter a formula:
1. Select the cell, then click in the formula bar
2. Hold the Control and Shift keys down
3. Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If not, repeat steps 1 to 3.
In the above formula, B1 is the initial value and B2 is the number of years of depreciation. G3 is the top left cell in a two column table called tbDepreciation that contains the number of years of depreciation in the first column and the depreciation rate in the second column. This table starts with year 0 and 0% depreciation.
Note: my formula assumes that you enter the number of years as a number with a decimal fraction. For 18 months, you would enter 1.5. For 36 months, you would enter 3. If you would rather enter months directly, then replace B2 with B2/12 wherever you see it in my formula. DepreciationTableQ28347065.xlsx
0
Featured Post
Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.
See attached file.
Thanks
Depreciation.xlsx