dynamic formula for asset depreciation expense

[Adam1988]

Hi all,

I am working on a formula to calculate straight line asset depreciation expense for each year from 2016-2022. Every year the company purchases some amount of new assets. I would like the term that assets depreciate to be based on an input cell (A1), so I need the depreciation expense formula to be dynamic.

I am projecting years 2016-2022.


The table below represents my data (asset purchase price label is in cell A3 and the values are in B3:H3. The Depreciation Expense label is in cell A4 and the depreciation expense formula should be in cells B4:H4.

I have included two versions of the Depreciation Expense outcome to provide examples of two possible Depreciation Term inputs (Cell A1)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]2020[/TD]
[TD="align: center"]2021[/TD]
[TD="align: center"]2022[/TD]
[/TR]
[TR]
[TD]Asset Purchase Price[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TD]Depreciation Expense (if term is 2 year in A1)[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]65[/TD]
[/TR]
[TR]
[TD]Depreciation Expense (if term is 4 year A1)[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]7.5[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]55[/TD]
[/TR]
</tbody>[/TABLE]


2 year depreciation term

• 2016: 5/2
• 2016: 5/2 + 10/2
• 2017: 10/2 + 20/2
• 2018: 20/2 + 30/2
• 2019: 30/2 + 40/2
• 2020: 40/2 + 50/2
• 2021: 50/2 + 60/2
• 2022: 60/2 + 70/2

4 year depreciation term

• 2016: 10/4
• 2017: 10/4 + 20/4
• 2018: 10/4 + 20/4 + 30/4
• 2019: 10/4 + 20/4 + 30/4 + 40/4
• 2020: 20/4 + 30/4 + 40/4 + 50/4
• 2021: 30/4 + 40/4 + 50/4 + 60/2
• 2022: 40/4 + 50/4 + 60/2 + 70/2

Any help is greatly appreciated!!

thanks,
Adam1988

 

[DRSteele]

USA, you could try this formula:

	A	B	C	D	E	F	G	H
Depreciation Life, straight-line (yr.)
Salvage ($)
Asset Purchases ($)
Aggregate Depreciation Expense ($)

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFE699, align: right"]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: center"]2[/TD]

[TD="bgcolor: #FFE699, align: right"]0[/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"][/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"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016[/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: center"]5[/TD]

[TD="align: right"]10.00[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]30.00[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]70.00[/TD]

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

[TD="bgcolor: #C6E0B4, align: right"]5.00[/TD]
[TD="bgcolor: #C6E0B4, align: right"]15.00[/TD]
[TD="bgcolor: #C6E0B4, align: right"]25.00[/TD]
[TD="bgcolor: #C6E0B4, align: right"]35.00[/TD]
[TD="bgcolor: #C6E0B4, align: right"]45.00[/TD]
[TD="bgcolor: #C6E0B4, align: right"]55.00[/TD]
[TD="bgcolor: #C6E0B4, align: right"]65.00[/TD]

</tbody>

Sheet18​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=SUMPRODUCT(SLN(OFFSET(B5,0,MAX(1-COLUMNS($B$5:B5),1-$B$1)):B5,$B$2,$B$1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=SUMPRODUCT(SLN(OFFSET(C5,0,MAX(1-COLUMNS($B$5:C5),1-$B$1)):C5,$B$2,$B$1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=SUMPRODUCT(SLN(OFFSET(D5,0,MAX(1-COLUMNS($B$5:D5),1-$B$1)):D5,$B$2,$B$1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]=SUMPRODUCT(SLN(OFFSET(E5,0,MAX(1-COLUMNS($B$5:E5),1-$B$1)):E5,$B$2,$B$1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]=SUMPRODUCT(SLN(OFFSET(F5,0,MAX(1-COLUMNS($B$5:F5),1-$B$1)):F5,$B$2,$B$1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G6[/TH]
[TD="align: left"]=SUMPRODUCT(SLN(OFFSET(G5,0,MAX(1-COLUMNS($B$5:G5),1-$B$1)):G5,$B$2,$B$1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H6[/TH]
[TD="align: left"]=SUMPRODUCT(SLN(OFFSET(H5,0,MAX(1-COLUMNS($B$5:H5),1-$B$1)):H5,$B$2,$B$1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

First, I suspect that your 2016 2-year example should be 10/2, and the denominators in your 4-year example should all be 4 in years 2021 and 2022.

Given that, you can put this formula in B3:

=SUM(OFFSET(B3,0,MAX(2-COLUMN(),1-$A$1)):B3)/$A$1

and copy it over to H3.

It gives all the expected answers.

 

[Adam1988]

First, I suspect that your 2016 2-year example should be 10/2, and the denominators in your 4-year example should all be 4 in years 2021 and 2022.

Given that, you can put this formula in B3:

=SUM(OFFSET(B3,0,MAX(2-COLUMN(),1-$A$1)):B3)/$A$1

and copy it over to H3.

It gives all the expected answers.

Hi, yes, apologies for the typos.

I'll try both suggestions.

Thanks for all the help!

Adam1988

 

[Adam1988]

Eric,

your formula works perfectly!

Thank you!

 

[Eric W]

Glad to help!