Caluclation of depreciation

[LR]

Col 1 Col 2 Col3 Col4 Col6
Col7 Col8 and so on 20 yrs


01-Dec-06 01-Jan-07 01-Feb-07

31-Dec-06 31-Jan-07 30-Apr-07
No of months 1
1 3

________________________________________
{}
{}
Asset Value Date of Life of {}
1st purchase asset {}
{}
{}
1 20000 01/01/09 3 ()
2 30000 01/10/14 5 {}


I have tried to create the rows and columns as how it will look in a
spreadsheet

The above table lists the asset value in the month that is purchased
which goes to a period of about 30 years. The period for the first
three months is listed by month and it then changes to a quarterly
period.

Now I want to create another table to calculate the depreciation line
by line. What makes it complicated is the time period can change from 1
month to 3 months above and also that each line has got a different
purchase date and a different life cycle. A depreciation formula that
will go through each line has to be formulated.

Has anyone got ideas.

 

[Roger Govier]

HI

It is difficult to see your layout because of line wrapping but I set up
a table with
E1 =1, F1=1, G1=3, H1 .... =3
E2 = 01/Dec/06, F2 = 01/Jan/07, G2 = 01/Feb/07, H2 = 01/May/07 etc
E3 = 31/Dec/06, F3 = 31/Jan/07, G3 = 30/Apr/07 , H3 = 30/Jun/07 etc

B4=20000, C4 = 15/Dec/2006, D4=3
B5=30000, C5 = 01/Oct/2008, D5=5

In cell E4 enter (the rather horrendous formula)
=IF(E$3<$C4,0,
IF(E$2>DATE(YEAR($C4),MONTH($C4)+($D4*12),1),0
,($B4/($D4*12)*E$1)))
*IF(AND($C4>E$2,$C4<E$3),DATEDIF($C4,E$3,"md")/(E$3-E$2),1)
and copy across as far as required, then copy the whole row of formulae
down as far as required.

I deliberately used different dates for Asset Purchase than you showed,
so I could see if the formula worked.
It produced values of
296.3 in E4, part month's worth of depreciation
555.6 in cell F4, complete month of depreciation
1666.7 in G4, 3 months of depreciation

371.9 in L5, part quarters depreciation
1500.0 in M5 complete quarters depreciation

 

[LR]

This seems to work except that when it comes to renewing the assets as
I have mentioned earlier that it might be up to 30 years it gets stuck.

Is there any way out

 

[Roger Govier]

Hi

I posted you a solution the other day which provided a cash flow for the
asset replacement over a 30 year period.
You just extend your list of assets downward, with the dates when they
are to be purchased.

If every asset is to be replaced automatically on the appropriate
anniversary of its initial purchase, then you don't need to curtail
depreciation after 36, 48 or 60 months etc., but just keep it going on,
as the depreciation of the new asset will replace that of the asset
which is now fully depreciated.

Replacement cost I guess in most case will be higher (due to inflation)
but some could be lower due to technological progress. You could just
apply a weighting to depreciation after certain time periods to allow
for inflation in the value of replacements.

Personally, I wouldn't worry too much about this level of accuracy,
especially over a 30 year time frame.
Depreciation in this context is merely an estimate, you cannot know what
the true depreciation is until the point at which the asset has to be
replaced, and what its salvage value is at that point. Any realisation
for an asset will become a Profit on Disposal in that time period, as
the asset will have been depreciated to zero.