Formula Assistance

[tommo]

Hi all,

cost aggregate dep c/fwd charge for year agg dep b/fwd value
2007
1000 100 50 150
850

I have a spreadsheet set out as above. Is there a way i can create a formula
in the agg dep c/fwd cell that will increase by the charge for the year
without having to manually input figures every year? (instead of just typing
150 in the cell) And that would also work for subsequent years.

i.e. cells to read 1000 150 50 200 800 850
800 being this years value with a further years depreciation charged.

 

[JE McGimpsey]

One way:

Assuming the following cell layout:

A B C D E
1 cost adc/f cfy adb/f valueEOY
2 1000 100 50 150 850

then

A3: =A2
B3: =D2
C3: 50 (or whatever depreciation formula you're using)
D3: =B3+C3
E3: =A3-D3

Copy A3:E3 down as far as desired.

 

[tommo]

I wish it was that simple. Unfortunately, i wouldnt be creating anything in
A3. I should have mentioned that i have hundreds of lines therefore 'A3' is
occupied by another asset. All info is on 1 row per asset

Essentially i always have 2 year end figures showing. Current year and the
previous year, so everything moves on a year as i update it.

 

[JE McGimpsey]

Ah. One way:


B3: =C3*(YEAR(TODAY()) - 2005)

Where 2005 is the date the asset was put into service.

Adjust to suit.