Straight Line Depreciation?

[hmmmidk220]

Hi!

So I have an existing worksheet that calculates SLD in a way that is somewhat manual.

The columns are the years 2017-2027 onward and the life of the asset. The rows are:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Life[/TD]
[TD]2017>>2027 (each column is a year)[/TD]
[/TR]
[TR]
[TD]Asset 1[/TD]
[TD]5[/TD]
[TD]For each year is the asset purchase amount[/TD]
[/TR]
[TR]
[TD]Asset 2[/TD]
[TD]3[/TD]
[TD]For each year is the asset purchase amount[/TD]
[/TR]
[TR]
[TD]Asset 3[/TD]
[TD]10[/TD]
[TD]For each year is the asset purchase amount[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Asset 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2017 Asset 1 purchase[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2018 Asset 1 purchase[/TD]
[/TR]
[TR]
[TD]2019..etc[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2019 Asset 1 purchase..etc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Asset 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2017 Asset 2 purchase[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2018 Asset 2 purchase[/TD]
[/TR]
[TR]
[TD]2019..etc[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2019 Asset 2 purchase[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Asset 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2017 Asset 3 purchase[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2018 Asset 3 purchase[/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD][/TD]
[TD]Each year(column) is the depreciation spread of the 2019 Asset 3 purchase[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The spread of depreciation is very simple, it's just the cost of asset / Life, and then the first and last year are cost of asset / Life / 2, a kind of simple way to account for the fact that purchases won't happen at only the beginning of the year. There is no salvage amount.

So does that make sense? Is there a better way? A clear downside is that each formula must be manually adjusted to account for start and the end of life.

I was looking at something like this:

If you’re not Modelling Depreciation like this, you’re doing it the hard way! - Access Analytic

And the "better straight line method" part makes sense but I'm not sure how to incorporate it. I think I do need some kind of IF statement?

Thanks!

 

[jorismoerings]

Hi,

try something like this:
Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Purchase	cost	Life	Year
a1
a2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]2021[/TD]
[TD="align: right"]2022[/TD]
[TD="align: right"]2023[/TD]
[TD="align: right"]2024[/TD]
[TD="align: right"]2025[/TD]
[TD="align: right"]2026[/TD]
[TD="align: right"]2027[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]33,33333[/TD]
[TD="align: right"]66,66667[/TD]
[TD="align: right"]66,66667[/TD]
[TD="align: right"]33,33333[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IF(MEDIAN(E$3,$D4,$D4+$C4)=E$3,($B4/$C4)/IF(OR(E$3=$D4,E$3=$D4+$C4),2,1),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=IF(MEDIAN(E$3,$D5,$D5+$C5)=E$3,($B5/$C5)/IF(OR(E$3=$D5,E$3=$D5+$C5),2,1),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[hmmmidk220]

Hi jorismoerings thank you for that!

That works but the format will have lots and lots of rows, I need something that is more or less transposed of that.

Please see this file:

Uploadfiles.io - sample-dep..xlsx

Basically, the asset purchases are in rows 4-10, and the columns go up to the year 2057. And there are about 7 assets, so your method would produce too many rows.

If you see the format in the above file, you can see that the depreciation spread of each asset goes only to 2027, so I really just want the 50 year max spread of depreciation for assets purchased from 2017 to 2027.

Where it gets tricky is that the formula needs to spread the deprecation by matching the year in column A to the year in row 3, then matching the asset name as well to get a coordinate location of the purchase that it needs to spread out.

I'm thinking I would need some kind of index match match possibly? Also note that the first and last years need to divide by 2 so that the total years spread is +1 (5 year life is spread over 6 years because the first and last year are divided by 2)

Does that make sense? Is this possible with a formula?

Thanks!

 

[hmmmidk220]

So I tried this huge mess of a formula on cell D16:

=IF(MEDIAN($A16,D$3,D$3+INDEX($B$4:$B$9,MATCH($A$14,$A$4:$A$10,0)))=$A16,(INDEX($D$4:$J$9,MATCH($A$14,$A$4:$A$10,0),MATCH($A16,$D$3:$J$3,0))/INDEX($B$4:$B$9,MATCH($A$14,$A$4:$A$10,0)))/IF(OR($A16=D$3,$A16=D$3+INDEX($B$4:$B$9,MATCH($A$14,$A$4:$A$10,0))),2,1),0)

And it works except that as you go along the columns in each deprecation section, the cell needs to remain fixed on the amount of the purchase in the purchase year (so you cant just use index match match to isolate the purchase amount that is in the years in column A to the years in row 3).

I'm lost now...

There's got to be a much simpler formula for this? It's such a simple thing that I need..just need to divide two numbers and then sometimes divide by 2....

Does any of this make sense? Help please

 

[jorismoerings]

Hi,

You're on the right track.
Don't forget, the difficulty isn't in the calculation but in selecting the appropriate numbers to calculate with.
I took your example and finished it

Here's my file

Hope this helps

 

[hmmmidk220]

Thank you so much!!! That works great!!!!!!!!!