Calculate reducing balance method percentage formula

Calculate reducing balance method percentage...

Didn't find your answer?

Hi All,

I trust you are all well.  I was really hoping someone would be able to help me with a query that I have for the reducing the balance method of depreciation in Excel.

1.  How would I calculate the percentage to use based on say an asset NBV of £25,000, salvage value of £5,000 and a useful life of 5 years,

2.  How on earth do I go about putting that into an Excel formula.

Maybe someone would be kind enough to assist??

Thanks

Ant

Replies (9)

Please login or register to join the discussion.

avatar
By ElliottRoss
14th Jun 2012 13:51

Is this close enough?

Hi Anthony

Does the percentage 2.6467% get you close enough?

Regards

Elliott

Thanks (0)
By Steve Kesby
14th Jun 2012 14:08

Try DB

Do you want to know the percentage? Or do you just want Excel to work it out.

The formula =DB(25000,5000,5,x) will give you the depreciation for period x (between 1 and 5).

From that you can then calculate the constant reducing balance percentage, which is 27½%.

Thanks (0)
By Anthony.Evans82
14th Jun 2012 19:51

Hi Steve & Elliott,

Hi Steve & Elliott,

 

Steve:  The 27.5% looks right - but where did that calculation come from (sorry to sound really stupid).

I am reading an ACCA text and it doesn't give the calculation - although I did start using the DB function in Excel to calculate it.

 

Thanks

Thanks (0)
By Anthony.Evans82
14th Jun 2012 19:56

Actually, before you waste your time responding to that idiotic question I shall save you the hassle.

 

Maybe you could just check the method in my madness though?

The DB function =DB(25000,5000,5) gave me a dep'n value of £6,875

Meaning the Opening NBV in the next period was £18,125

The percentage calculation = 1-(18125/25000)

Percentage = 27.5%

 

 

Thanks (0)
By Steve Kesby
14th Jun 2012 20:12

I just did...

... £6875/£25000 = 27½%. It works for all the other periods too.

If you get an old enough accounting textbook, there is a complex mathematical formula to derive the rate. But then spreadsheets were invented.

So you could get to it by doing =25000/DB(25000,5000,5,1).

Thanks (0)
Replying to Agutter Accounts:
By Anthony.Evans82
14th Jun 2012 20:14

Ah yes, the formula
I know the one you're referring to.

But I am right in saying you would use the DB function to first derive the 6,875?

I think I may scour youtube for videos on using the DB function to accommodate for specified number of months of the year etc.

Thanks for your help.

Ant

Thanks (0)
avatar
By edhy
18th Jun 2012 08:41

The Formula

The formula is

rate = (salvage / cost) ^ (1 / useful life) -1

i.e. rate = (5000/25000)^(1/5)-1

regards

Zubair Edhy

Thanks (0)
avatar
By favs_taze
18th Oct 2015 20:09

reducing balance method
How do u work the formula for this method?

Thanks (0)
avatar
By MalcolmS
27th Oct 2022 09:19

Replying to the string below, I have the useful life of an asset and want to calculate the depreciation rate under the reducing balance method in Microsoft Excel. How do I do that?

To calculate the depreciation rate under the reducing balance method, when the useful life is known, I can use the following excel formula:
Depreciation Rate = round(1/(cost base/DB(cost base, residual scrap value, useful life in years,1)),2).

How do I get the depreciation rate, when I know the useful life? Any thoughts please?

Thanks (0)