Straight Line Depreciation for Long Term Model of Depreciation

Hi -

I'm not an accountant so I don't fully understand your method. It appears that an asset is purchased over multiple years? For example Asset 1 costs $150 but $100 is in 2017 and $50 is expended in 2018? And the two costs are depreciated separately and added together? So for Asset 1, Year 1 is $5 depreciation of the $100 and in Year 2, this Asset is upgraded? Enhanced? Accumulated? So you have $5 depreciation for the original $100 Plus $2.50 for first year of the second part of Asset 1 of $50? It seems like these are being treated as separate assets for depreciation purposes and added together. Am I understanding that correctly?

Wouldn't it be easier to break Asset 1 into Asset 1A of $100 on one line and depreciate that out and have a separate line for Asset 1B of $50 and depreciate that out and then add those two lines together on a third line called Asset 1 or Asset 1 Combined? it seems like the complexity of your formula is due to the spreading of expenditures over multiple years.

Hi -

OK - So based on what you've told me, I restructured the data so that you can input different starting years for each asset, Useful Life, Starting quarter, and Asset Value. Then I used some nested IF statements to test each of the time criteria against the year (in row 29) and used a VLOOKUP to use the Depreciation Schedule based on the quarter the asset is placed in service. If an asset is placed in service partway through the year, a proportion of the first year depreciation is applied in that first part year and then full depreciation in the subsequent years plus the remainder of the part year depreciation in the last year.

The formula looks like this:

=IF($G15>J$29,0,IF(J$29>($C15+$G15),0,$B15*(1/$C15)*IF((J$29-$G15)=0,VLOOKUP($H15,$E$39:$H$42,4),IF(J$29=($C15+$G15),1-VLOOKUP($H15,$E$39:$H$42,4),1))))

I have attached your spreadsheet with the above revisions. I highlighted the areas that I modified in a light orange color. The area that is highlighted green is no longer necessary because other columns I added replace those two columns. Try changing some of the numbers (like years of service, starting quarter, starting year, etc.) to see if the results are what you expect.

Hope this helps.