View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK Pete_UK is offline
external usenet poster
 
Posts: 8,856
Default Straight line depreciation

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

On Aug 26, 4:02*pm, Ian wrote:
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..
--
Ian



"Ian" wrote:
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.
--
Ian- Hide quoted text -


- Show quoted text -