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:

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.

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