Share this content

Accumulated depreciation in Excel

Accumulated depreciation in Excel

Some clients have had old computers for years, and it is time to write off old ones from the accounts. We have date of purchase, current year date year end, and cost. 25% reducing balance method is used. We need accumulated depreciation from date of purchase to current year end date in one formula. Trouble with Excel is that their formulas always ask for useful life of the asset, which we do not have and there seems no formula to calculate what must be a common requirement. Am I missing something, or does someone have the golden formula? Any help appreciated.


Please login or register to join the discussion.

By Anonymous
13th Aug 2009 20:49

why dont you put down £1 value

Thanks (0)
By Anonymous
13th Aug 2009 20:52


Thanks (0)
By Anonymous
13th Aug 2009 22:12

power formula
The net book value at the end of year 1 is 75% of the original cost (OC). The net book value after two years is :

original cost x (0.75 to the power 2) ie OC x 0.75 x 0.75

So if you have had your asset for 6 years the net book value at the end of year 6 is:

original cost x (0.75 to the power 6).

The accumulated depreciation is the difference between this resulting NBV and the original cost. Excel has a POWER formula in its formulae toolbar (or at least Excel 2007 does) to create the formula above.

I hope that helps you.

Thanks (0)
13th Aug 2009 22:23

Helpful Answer (I hope)
Cost * (1-power(1-rate),year(year_end)-year(purchase_date)+1)
where cost, rate year_end and purchase_date are cell references.

I hope this helps.


Thanks (0)
By shurst
14th Aug 2009 08:33

Power of
The upward pointing triangle (Shift+6) also performs a to the power of calculation:

So =2^3 should give the result 8

Thanks (0)
By Anonymous
14th Aug 2009 11:36

Why not use the VDB function?
Use the following criteria:

Start period - 0
End period - age of asset

In Life and Factor you should enter numbers that will give you an effective annual rate of 25%. For instance, 10 year life, 2.5 factor or 20 year life, 5 factor.

It's always worked for me!

For instance - £10k cost, £0 salvage value, 10 year life, Start period 0, end period 6, factor 2.5, result £8,220.

Thanks (0)
By DMGbus
14th Aug 2009 13:40

Reducing balance formula - justification
When I was in training at college it was put to us students that a merit of the Reducing Balance Method was that in the early years of ownership (when an asset had a lower level of repairs) it sufferred the highest depreciation and in later years a lower rate of depreciation applied at a time when repair costs would be higher. This would spread the total costs of the asset more evenly over its economic life. Probably too sensible for the modern world !

A few years later I came across a problem with a haulier who had depreciated his fleet on the straight line basis : some of his lorries had a NEGATIVE book value because they'd been owned more than the requisite number of years of straight line write off and the rate of depreciation had been computed as a global figure on the whole fleet cost. With the straightline basis of depreciation what the haulier should have done (like any fleet operator, regardless of depreciation method) is maintain a plant register and compute depreciatiion individually on each listed asset - proven essential with straight line methodology.

Personally I'm in the "old school" here and will always operate the reducing balance method unless its an asset is something of definite useful economic life when cost divided by known economic life may have some merit.

PS. I don't usually depreciate land and buildings either (another debate altogether!). [Fits in with concept of the spreading the cost of ownership as evenly as possible over period of ownership, ie. don't have 8 years of P&L cost then followed by 1 year of profit on disposal - instead have no P&L cost ever, just have a modest one-off profit in year of disposal].

Getting back on topic - Excel computations, its ever so easy with the reducing balance method.
The closing NBV of an individual asset in any year is the opening net book value multiplied by 0.75 (where a 25% depreciation charge is used). No need to use any Exccel templates - just write your own spreadsheet with this ever-so-easy formula.

Thanks (0)
15th Aug 2009 17:57

accumulated depreciation for reducing balance
Thanks for the replies. The power formula is the best and easiest to do with shift + 6 to enter the power. Works perfectly.

Thanks (0)