How to Calculate Declining Balance in Excel Using Formula

We have discussed Declining balance method of depreciating the fixed asset in the past. But in this tutorial, we will focus on how we can do it with easy to use Microsoft Excel formula. 

Excel Formula Declining Balance 

DB( cost, salvage, life, period, [month] )

Where:

cost = cost of the asset

salvage = value of the asset at the end of its life

life = usefullife of the asset

period = the year for which you want to calculate the depreciation

month = the number of months for which you want to calculate the depreciation, if it is left empty, Excel will conisder this value as 12 month be default.

Example

LTI company is involved in manufacturing of auto spare parts. It has variety of fixed assets in its factory. Its accountant is not an experienced one and as such finds difficulty in calculating the depreciation using write down value/ declining balance method. The director of the company requested you to prepare an Excel sheet for the accountant so that he could only enter some values in to the Excel sheet cells and the depreciation gets calculated easily. Following are the details of the fixed asset for which your skills are needed.

Initial cost = 400,000

Salvage value = 20,000

Useful Life = 05 Years

Required

Calculate depreciation expense from year 01 to year 05 using Microsoft Excel.

Solution

We have created the Excel sheet from the data provided above and calculated the depreciation using DB (Declining Balance) formula:

=DB($B$1,$B$2,$B$3,1)

You can see that the depreciation of 180,400/- is getting calculated from year 1 to year 5. But depreciation from year 2 to 5 is wrong because period 1 is getting copied when we copy the formula from cell B7 ans paste it down the cell. But, in order to calculate the right amount of depreciation from year 2 to 5, we need to take advantage of Row formula. So,we will edit the formula as follows:

=DB($B$1,$B$2,$B$3,row(c1)) 

Upon doing so,we will get the depreciation figuresover declining balance method as follows:

reducing balance depreciation formula in excel 

One we put the above formula in B7, the amount comes 180,400. We just need to copy paste this formula to all the cells below B7 to find out the depreciation expense of the remaining years as shown.

How to Calculate Declining Balance in Excel Using Formula

 

 

 

Contents
Financial Ratios Accounting Cycle Accounting Principles Financial Accounting Basics Financial Statements Reporting Bad Debts Current Assets Long-term Assets Voucher System Partnership Depreciation Work Sheet - 10 Column Work Sheet Difference Between Reserve and Fund Accounting for Leases Capital reduction and reconstruction Absorption of Company Amalgamation Accounting for Installment Sales Basis of Recording Profit and Loss Branch Accounting Construction Contracts Revenue Recognition Accounting for Groups Financial Analysis Events After the Balance Sheet Date Deferred Tax Cost Accounting Activity Based Costing (ABC) Throughput Accounting Relevant Cost Break Even Analysis Standard Costing Inventory Management Payroll Accounting Royalty Accounting Statistics Master Budget Salary Income Microsoft Excel Tutorial How to Count Number of Records between Two Dates How to sum values in excel based on a criteria Finding the first login and last logout time of employees How to Print Salary Slips Using VLookup Formula in Excel How to Calculate Net Present Value (NPV) in Excel Income tax calculation excel sheet for salaried individuals How to Calculate Pay Back Period in Excel How to Calculate Declining Balance in Excel Using Formula How to Calculate Double Declining Balance in Excel How to Calculate Number of Payments on A Loan In Excel Using NPER Count the Number of Excel Records That Fall Between Two Dates How to Count Cells between Two Numbers in Excel How to Prepare Exam Result in Excel 3D Sum Multiple Worksheets How to Count Specific Text in Excel Other Topics Share or Stock Valuation Model Financial Management Topics Kinds of Endorsement Letter of Credit, Kinds and Its Advantages/ Utilities Modern Functions of a Commercial Banks Difference Between Secured And Unsecured Loan Excel conditional formatting red if negative green if positive How to Remove Extra Spaces in Excel Result Card or Result Sheet Creation Using Excel