# Excel formula qn - Extending a Depreciation Schedule...

#### Luke

Hey all, looking for an excel guru out there

I have a six year old depreciation schedule, and I need to figure out how to extend the useful life of it... (you're speaking to my accountant)
As it stands, I know everything on a standard DepSched. - original cost, already claimed, the residual and the percentage rate (I used diminishing value not prime cost) and figure there must be an excel forumula to do this...

Playing around with the help on the forumula's built into Excel none of them look quite right. DB, SLN etc. None of them seem to be based on percentage rates (is that a USA/Aust tax law difference I wonder)

As an example:
My dishwasher was installed Feb 2000. Cost of \$1650. Claimed by others \$577. Diminshing Value percentage is 20% So for the part year rented 01/02 it had \$76 to claim.
02/03 \$199 03/04 \$159 04/05 \$128 05/06 \$102 Residual is \$408 06/07 \$????

Looking at it, it seems like the formula is Previous years figure x (1-DimVal%) or in this case times 80%. Is it really that simple?

Many thanks, and happy Easter!

Last edited:

#### duckster

excel formula

My dishwasher was installed Feb 2000. Cost of \$1650. Claimed by others \$577. Diminshing Value percentage is 20% So for the part year rented 01/02 it had \$76 to claim.
02/03 \$199 03/04 \$159 04/05 \$128 05/06 \$102 Residual is \$408 06/07 \$????
new value 1073 dim dep value 20
(cell e1 make it 20)
(cell b1 make it 1073)
(cell a3 make it 0)
(cell a4 make it =a3+1)
(cell b4 make it =ROUNDUP(C3*E\$1/100,0) )
(cell c4 make it =ROUNDUP((C3)-(C3*E\$1/100),0) )
copy cells a4,b4,c4
paste in cells below this to copy formula down
make (cell c4 =roundup((c3)-(76),0) )
make cell b4 be the value 76 as part depreciation for that year
cell a cell b cell c cell d cell e
row 1 new value 1073 dim dep value 20
row 2 year depreciation value
row 3 0 1073
row 4 1 76 997
row 5 2 200 798
row 6 3 160 639
row 7 4 128 512
row 8 5 103 410
row 9 6 82 328
row 10 7 66 263
row 11 8 53 211
row 12 9 43 169
row 13 10 34 136

depreciation for year one - \$1073 *20% = 214
new value - \$1073 - \$214 = \$859

Looking at it, it seems like the formula is Previous years figure x (1-DimVal%) or in this case times 80%. Is it really that simple?
nope almost
value = previous year value - (previous year value * .20)
depreciation = (previous year value * .20)
This allows diminishing effect .
if partial year you multiply days depreciated / 365
value = previous year value - (previous year value * .20* days depreciated / 365 )
depreciation = (previous year value * .20) * days depreciated / 365

new value 1073 dim dep value 20
year depreciation value
0 1073
1 76 997
2 200 798
3 160 639
4 128 512
5 103 410
6 82 328
7 66 263
8 53 211
9 43 169
10 34 136

figures are slighly different probably due to using roundup(data,0)

Last edited:

#### Luke

Thank you very much Duckster, that's a very helpful and clear post. Much appreciated.... hopefully it helps someone else as well.

Now to crunch a whole heap of figures...

#### WinstonWolfe

Luke, I don't know how you are determining each item's depreciation rate, but there's a few things that determine it which would impact on the design of your spreadsheet.

1.
depreciation is worked out by the formula

Decline in Value = Base Value * Days Held / 365 * 150% / Effective Life
for items acquired post 10/5/06

and

Decline in Value = Base Value * Days Held / 365 * 200% / Effective Life
for items acquired <= 10/5/06 back to ?2001

2.
Effective life is the other variable that will determine the depreciation rate.

3.
Depending on your accounting structure, items under \$300 (or \$1000) can be depreciated 100% in the first financial year. Other items can be thrown into a low value pool and treated the same.

Check the ATO website for the "Guide to depreciating assets" for further details, or hopefully your accountant will inform you of the correct depreciation rate for each item.

This is a sample xls I use to estimate decline in value.

#### depreciator

Crikey Luke, that's hard work. I hope you're not doing that for a bunch of items? Winston mentioned the Low Value Pool. After six years of ownership, it's hard to imagine you have anything with a written-down value greater than \$1,000? That means you can bung everything in the Pool and only have to do one set of calcs on the total Pool value.

(WW, the DV changed to 200% in mid 06 from 150%, not the other way around)

Scott

#### WinstonWolfe

(WW, the DV changed to 200% in mid 06 from 150%, not the other way around)

Scott

thx for picking that typo up Scott......the xls has it the right way around.