Forecasted depreciation formula for different estimated arrival period of purchases

[fornight]

I need to help with formula to calculate forecasted depreciation for long term.

Example, the estimated purchase of asset is annual figure but not all the purchase will arrive at the same time. we will split the arrival of the purchase by quarters, example:

Estimate purchase of asset = $1000 in Year 2018
Arrival of asset is
30% - Q1
40% - Q2
20% - Q3
10% - Q4

Assumption is depreciation method is straight line with no salvage value and depreciation rate or life is different for each asset type.

my excel model is column as years like this:

2018 - 2019 - 2020 - 2021 - xxx

Esti purchase of asset type 1 $1000 $5000 $2000 $3000 xxx


I need to present forecasted depreciation for each asset type based on the estimate purchases above.


Estimated depreciation 2018 2019 2020 2021
asset type 1 xx xx xx xx
asset type 2 xx xx xx xx


The problem is the purchase is spread over 4 quarters and I need to be able to pick up the right amount to continue depreciating from its arrival quarter but not over depreciate it beyond its cost value.

I know SLN formula but i don't know how to take into account of the arrival of the assets.

Below is what I tried to build using simple formula but first year of calculation is easy but it gets complicated when it goes into future years.

Help!!!


depreciation schedule.png