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)

	2016	2017	2018	2019	2020	2021	2022
Asset Purchase Price	10	20	30	40	50	60	70
Depreciation Expense (if term is 2 year in A1)	5	15	25	35	45	55	65
Depreciation Expense (if term is 4 year A1)	2.5	7.5	15	25	35	45	55

<tbody>
</tbody>

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
1	Depreciation Life, straight-line (yr.)	2
2	Salvage ($)	0
3
4		2016	2017	2018	2019	2020	2021	2022
5	Asset Purchases ($)	10.00	20.00	30.00	40.00	50.00	60.00	70.00
6	Aggregate Depreciation Expense ($)	5.00	15.00	25.00	35.00	45.00	55.00	65.00

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet18​

Worksheet Formulas Cell Formula B6 =SUMPRODUCT(SLN(OFFSET(B5,0,MAX(1-COLUMNS($B$5:B5),1-$B$1)):B5,$B$2,$B$1)) C6 =SUMPRODUCT(SLN(OFFSET(C5,0,MAX(1-COLUMNS($B$5:C5),1-$B$1)):C5,$B$2,$B$1)) D6 =SUMPRODUCT(SLN(OFFSET(D5,0,MAX(1-COLUMNS($B$5:D5),1-$B$1)):D5,$B$2,$B$1)) E6 =SUMPRODUCT(SLN(OFFSET(E5,0,MAX(1-COLUMNS($B$5:E5),1-$B$1)):E5,$B$2,$B$1)) F6 =SUMPRODUCT(SLN(OFFSET(F5,0,MAX(1-COLUMNS($B$5:F5),1-$B$1)):F5,$B$2,$B$1)) G6 =SUMPRODUCT(SLN(OFFSET(G5,0,MAX(1-COLUMNS($B$5:G5),1-$B$1)):G5,$B$2,$B$1)) H6 =SUMPRODUCT(SLN(OFFSET(H5,0,MAX(1-COLUMNS($B$5:H5),1-$B$1)):H5,$B$2,$B$1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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!