Excel Formula - Depreciation calculation for opening balance and additions

[cecily]

I am currently calculating depreciation (straight line method) in tw
steps:

1. Opening asset balance

= if(historical cost*depreciation rate < opening written down value,
historical cost * depreciation rate, - opening written down value)

2. Additions (i.e. capex)

= - min(capex*depreciation rate,(capex - sum(depreciation to date)))

To calculate additions, I need to use a "step table" i.e.

123456
23456
3456
456
56
6

Is there a better way to do this? Is it possible to use an array o
sumproduct function to do this calculation in the one cell, without th
"step table".

Thanks very much
Cecil