Straight line depreciation

[Ian]

Background:
I am compiling a simple depreciation sheet neccesary to feed a 5 year P&L.
Depreciation method:
1)Simple straight line depreciation.
Sheet contains entry cells foruser to enter:
*Asset cost
*Depreciation period. ( How long asset will be depreciated for -- 3, 5, 10
years what ever)
*Depreciation start date. (year 1, 2, 3, etc etc)
I need help to determine:
A) A formulae that can be applied to any of the years 1 through 5 that
recognises when the "Annualized depreciation" comes into effect (Starts) i.e.
it needs to phase shuch according to the depreciation period and what year it
starts in.
eg: If I have a depreciation term of 3 years that starts in year 1, I do not
want annualized values appearing in the year 4 and 5 columns. Values should
only appear in columns for years 1-3. Attached is an example of the formulae
I have put together for the year 4 annualized depreciation value.
=IF(OR(F7=1,F7=2,F7=3,F7=4),E7/D7,0)
Where f7 = when depreciation will start (year 1, year 2 year 3 etc etc), E7
= asset cost and d7=period over which asset will be depreciated/depreciation
term (Years- 3 years, 5 years 10 years etc etc))
My formulae fails to recognize the other variable ---- depreciation term (3
years) and as such posts the 3 year depreciation value into year 4 when in
effect the asset has been fully depreciated.
for additional info this is what the formulae for year 3 looks
=IF(OR(F7=1,F7=2,F7=3),E7/D7,0)
Any help is welcome --- thanks in advance.

 

[Pete_UK]

Ian,

You seem to want to have the values across the sheet, so put this
formula in the cell for the first year:

=IF($F7>COLUMN(A1),0,IF($D7+$F7>COLUMN(A1),$E7/$D7,0))

and then copy it across for however many years you feel you might
need. I tested it for up to 10years.

Hope this helps.

Pete

 

[JoeU2004]

I am compiling a simple depreciation sheet neccesary to feed a 5 year P&L.
[....]
Attached is an example of the formulae I have put together for the year 4
[....]
E7 = asset cost and d7=period over which asset will be depreciated

.... And it appears that F7 is the fiscal period when depreciation starts
(year 1, 2, 3, 4 or 5).

I assume that data for each fiscal period is in row 4 for year 1 through row
8 for year 5.

I also assume that "asset cost" is really the initial depreciation value,
i.e. cost basis less salvage value.

It would be nice to have a column with the fiscal period number (1, 2, 3, 4
or 5). I will refer to that as column B for example.

I need help to determine:
A) A formulae that can be applied to any of the years 1 through 5 that
recognises when the "Annualized depreciation" comes into effect (Starts)
i.e.
it needs to phase shuch according to the depreciation period and what year
it
starts in.

=IF(AND($F7<=$B7, $B7<$F7+$D7), $E7/$D7, 0)

Copy the formula into rows 4-6 and 8. Be careful to use "$" exactly as
shown; for example, do __not__ write $F$7.

If you do not want to use a column (B) for the fiscal period number, you can
replace references to $B7 with the expression ROW(E7)-ROW(E$3).

I do not use simply ROW(E4) or COLUMN(E4), which returns 4 for the 4th
period, because that will return the wrong number if you insert rows above
or columns to the left.

On the other hand, ROW(E7)-ROW(E$3) works only if the data for all fiscal
periods are in contiguous rows, namely row 4 through row 8, the assumption I
stated above. If that is not the case, it would be helpful if you indicated
what rows contain the data of each fiscal period.


But ....

Values should only appear in columns for years 1-3.

So, would you prefer:

=IF(AND($F7<=$B7,$B7<$F7+$D7), $E7/$D7, "")

Caveat emptor: The null string ("") result might affect other
calculcations. There is no problem if you do SUM(G4:G8). But if you want
to do explicit arithmetic, e.g. G4+G5+G6+G7+G8, and if you some other
functions (e.g. ROUND), you might need to use the N() function, i.e.
N(G4)+N(G5)+N(G6)+N(G7)+N(G8). And if you want to propagate the null-string
result, you would need a formula like IF(G4="", "", G4). In general, if you
get a #VALUE error, look to see if it is because a referenced cell contains
the null string. If so, use one of the aforementioned solutions, or post to
this NG for specific instructions.


----- original message -----

 

[Ian]

Fantastic --- Many thanks Pete. I used the basic construct of your formulae
and it now works. (only change I made was to remove Column and replace with a
row referance that I inserted and numbered 1 through 6 etc.) Thanks again.

 

[Pete_UK]

Ah well, glad it worked for you, Ian - thanks for feeding back.

I intially put the numbers 1 to 10 in the cells immediately above and
developed the formula, and then decided I could omit that requirement
by using COLUMN.

Pete