Dynamic high level depreciation calculation (with example)

Hi All,

Having some trouble building a dynamic depreciation calculation which in essense should be a quite simple high level calc.

Criteria:
1) asset purchases depreciated over 3 years i.e. cost/3
2) 1st year of depreciation = 50% i.e. cost/3 *50%

I've added a simple working model (yellow cells = input cells) with how the output should look but containing non dynamic calculations.

Hi,

If you're happy to introduce three blank columns D:F (hide them if necessary) then in G16 copied across and down

Formula:

Please Login or Register  to view this content.

Note that this uses an offset which is driven by your B2 depreciation period. To generalise this in the event that you have longer depreciation periods you'd need to add additional hidden columns to avoid the offset returning errors. Maybe do that anyway and add as many hidden columns as the maximum B2 value might ever be

The blank columns are only needed since column C contains descriptions. If you were to use a shape that contains the descriptions or put the descriptions on the right of the table the balnk columsn won't be necessary

When is the 2nd 50% of the first year's depreciation charged? Is that in the 5th or 6th year?

....just for clarification then since the statement 50% is charged in the first year does not seem to agree with the proportions you show in #5.
Must admit my question was somewhat confusing. I meant to say is the 'balance' of the depreciation after of 50% is charged in the first year spread across the next 5 years or the next 4 years? The example below is assuming the ba;ance is spread over the following 5 years. If over 4 then obviousuly years 2-5 would be 15 each.

If the Asset cost is say 120 with a 5 year depreciation life and 50% chargeable in the first year is the depreciation charged as follows:

Y1. 60 i.e. 50% in first year
Y2. 12
Y3. 12
Y4. 12
Y5. 12
Y6. 12

Or as per your table

year 1 - 1/10 = 12
year 2 - 2/10 = 24
year 3 - 2/10 = 24
year 4 - 2/10 = 24
year 5 - 2/10 = 24
year 6 - 1/10 = 12

Sorry to draw this out but I'm still not clear.
If the Asset cost is 120 with a depreciation period of 5 years, what is the amount to be charged in each of the next years?

I now think I understand you to be saying the annual charge is 1/5 of 120 i.e. 24, but only 50% is charged in Y1. Hence the charge falls as follows

Y1. 12
Y2. 24
Y3. 24
Y4. 24
Y5. 24
Y6. 12 i.e. the balnce of the Y1 50%

Is that correct?

See attached

The new formulae are on sheet1 (2) in K16:U18.
As before I've added blank columns d:g and hidden them

The essential formula in K16 copied across and down is

Formula:

Please Login or Register  to view this content.

Purely for interest I've left the analysis in rows 24:32. These were my original workings to identify
a) The first year depreciation amount
b) The intermediated n years
c) The last depreciation year i.e. n+1