Depreciation Computation in a Single Row

[josephteh]

Hi all

Need help on a single formula to calculate yearly depreciation for capital expenditure over the years. The long way that I calculate the depreciation is to tabulate them in rows 8 to 17 and then add them up in row 18.

What I desire is a formula in cell C20, dependent on Shelf Life in cell C4 and Capex in cell C6. Then populate it to Year 10.

Any help is greatly apreciated! Thank you!

Joseph

Depreciation Computation.png

[vba_php]

What I desire is a formula in cell C20, dependent on Shelf Life in cell C4 and Capex in cell C6. Then populate it to Year 10.

ok, but what is the meaning of both of those numbers/terms? shelf life and capex? I assume the shelf life is the usable period of the product? and capex is what? furthermore, do the standards of depreciation methods apply here? e.g....

=> straight line
=> sum of years
=> DDB

[Bo_Ry]

Please try at C20

=SUMPRODUCT($C6:C6,(COLUMN(C6)-COLUMN($C6:C6)<$C$4)/$C$4)

[FDibbins]

Not sure how you keep increasing the value every other year?...46, 39, 45, 23, 45 etc?
And how come dep values drop away after year 5?

[josephteh]

ok, but what is the meaning of both of those numbers/terms? shelf life and capex? I assume the shelf life is the usable period of the product? and capex is what? furthermore, do the standards of depreciation methods apply here? e.g....

=> straight line
=> sum of years
=> DDB

Capex is capital expenditure, which is the purchase of fixed assets. Oh, thanks for pointing out. That's right, the depreciation method is straight line over the usable period of the product.

[josephteh]

Not sure how you keep increasing the value every other year?...46, 39, 45, 23, 45 etc?
And how come dep values drop away after year 5?

Oh, the figures are random figures, not real figures.

[josephteh]

Please try at C20

=SUMPRODUCT($C6:C6,(COLUMN(C6)-COLUMN($C6:C6)<$C$4)/$C$4)

Thanks, Bo_Ry! It works perfectly! However, is it possible to do a non-array formula? My projection worksheet is huge and the calculation is real slow.

[josephteh]

Just realise, the formula works as well without Ctrl-Shift-Enter!

[Bo_Ry]

Sumproduct can handle Array calculation without Ctrl+Shift+Enter
For non Array calculation, please try
=Sum(C6:index(6:6,max(column()-$c$4,column($c6))))/$c$4

[josephteh]

Sumproduct can handle Array calculation without Ctrl+Shift+Enter
For non Array calculation, please try
=Sum(C6:index(6:6,max(column()-$c$4,column($c6))))/$c$4

Thanks, Bo_Ry. The formula seems like not correct. Please see attached file.

[Bo_Ry]

Need to plus 1

=SUM(C6:INDEX(6:6,MAX(COLUMN()-$C$4+1,COLUMN($C6))))/$C$4

[josephteh]

Thank you Bo-Ry & everyone who responded! Here is Rep for everyone!

[josephteh]

Is it possible to have other formulas?

[Bo_Ry]

Maybe
=SUM(INDEX($C6:C6,MAX(1,COUNT($C6:C6)-$C$4+1)):C6)/$C$4

Volatile Offset
=SUM(OFFSET(C6,,,,-MIN($C$4,COLUMNS($C6:C6))))/$C$4