**Por Kelly L. Williams, CPA, Ph.D.**

Related

{:the rest}

{time to}

{/if} {@if cond="{isCGMA}"}

## {article title}

{:the rest}

## {article title}

{/e} {/.}

MATTERS

- Technology
- Microsoft Excel

qCan you show me how to calculate depreciation in Excel using different depreciation methods?

**ONE.**There are many ways to calculate depreciation in Excel, and many of the depreciation methods already have abuilt-emfunction included in the software. The table below includes all of them.built-emExcel depreciation methods included in Excel 365 along with formula for calculationunits-of-Productiondevaluation.

These eight amortization methods are discussed in two sections, each accompanied by a video. HeFirst sessionExplaincorrectly-line,Addition-of-years'digits,decreasing-balance, yougood-decreasing-balancedevaluation. He**second section**covers remaining depreciationmethods.

For simplicity and brevity, the amortization methods presented in this article use only the necessary arguments. Several of the smoothing functions include optional arguments to allow for more complex events, such aspartial-depreciation year.

You can access the two attached videos**here**y**here**y**a workbook with examples**use different depreciation methods.

Please note that the following content is based on Microsoft Excel 365 for desktop. Other versions of Excel may work differently.

SECTION 1

## 4 damping functions and an example

The four smoothing functions discussed in this section have three required arguments in common, as follows:

**cost**— initial cost of the asset;**to save**— salvage value of the asset (that is, the book value of the asset after its full depreciation).y**ZOE**— useful life of the asset (ie how long it is estimated that the asset will be used in operations).

These three arguments are the only ones used bySLNfunction that calculatescorrectly-linedevaluation.

HeSYDthe function calculates theAddition-of-years'digital damping and adds a fourth mandatory argument,com. pension is=SYD(cost, ransom, life, for)betraycomis defined as the depreciation calculation period. The unit used for the period must be the same as that used for the lifetime. for example. years, months,etc.

Hedata basethe function is used to calculate the constantdecreasing-balancedepreciation and contains five arguments:cost,to save,ZOE,period, youmes. The first four arguments are required and the last one is optional.Periodis required and represents the depreciation calculation period. The same ascominside itSYDoperation, the unit used for the period must be the same as that used for life; for example. years, months, etc. The optional argument,mes, refers to number of months in the first year. If left blank, Excel will assume there were 12 months in the firstagain.

HeDDBfunction is used to calculategood-decreasing-balancedepreciation (or some other factor ofdecreasing-balancedepreciation) and contains five arguments. The first four (cost,to save,ZOE, youperiod) the same ones used in are also mandatorydata basemode. The fifth argument,factor, is optional and defines multiply the depreciation rate by which factor. If left blank, Excel will assume the factor is 2: thecorrectly-linedepreciation rate times two, that isgood-decreasing-balance amortization.

## Example of depreciation with the first four functions

Let's look at an example using the four amortization methods described so far. Let's say our company has an asset with an original cost of $50,000, a salvage value of $10,000, and a useful life of five years and 3,000 units, as shown in the screenshot below. Our job is to create a depreciation schedule for the asset using the four types of depreciation.

Let's create the formula forcorrectly-linedepreciation in cell C8 (do this on the first tab of the Excel workbook if you're following along). we have to define itcost,to save, youZOEarguments forSLNmode. Hecostappears in cell C2 (50,000).to saveappears in cell C3 (10,000). It isZOE, for this formula, is life in time periods and is listed in cell C4 in years (5). Because we are going to copy the formula so that it calculates thecorrectly-linedepreciation for all periods, let's include absolute references (POSTSCRIPT) in front of the columns and rows of those cells. For this example, the formula we want to enter in cell C8 is= SLN ($ C$ 2, $ C$ 3, $ C$ 4). Drag this formula down to fill cells C9 untilC12.

The formula forAddition-of-years'The digit stripping is created in cell D8. The arguments to be defined arecost,to save,ZOE, youcom. again, thecostappears in cell C2.to saveappears in cell C3.ZOE, under time periods, is listed in cell C4. It iscomappears in cell A8. Let's duplicate this formula as we did abovecorrectly-linedepreciation, so again we'll include absolute references (POSTSCRIPT) in front of columns and rows of cells, except the cell describing the argumentcom. The reason is that as we drag the formula down we want the period cell to move down to the next period and so on. For this example, the formula in cell D8 is=ON($C$2,$C$3,$C$4,A8). Drag this formula down to fill cells D9D12.

The formula fordecreasing-balancedamping is created in cell E8.Cost,to save, youZOEare defined and located as in the previous two methods, whileperiodappears in cell A8. as we did withAddition-of-years'digits, include absolute references in front of columns and rows of cells, except for the cell the argument describesperiod. The arguments are exactly the same for declining balance and forAddition-of-years'digits for this example. The formula in cell E8 is= DB ($ C$ 2, $ C$ 3, $ C$ 4, A8). Drag this formula down to fill cells E9 untilQ12.

The formula forgood-decreasing-balancedepreciation is created in cell F8. The arguments for this example are defined in the same way as for the declining balance andAddition-of-years'digits The formula in cell F8 is=DDB($C$2,$C$3,$C$4,A8). Drag this formula down to fill cells F9 untilF12.

if you follow**excel file**as long as your worksheet looks similar to the screenshot below.

The total amount of depreciation assumed over the useful life of the asset must equal the depreciable cost (cost minus residual value). However, accelerated depreciation methods sometimes do not depreciate to exact depreciable cost and must be "capped". Of the depreciation methods described above, the accelerated depreciation methods are decreasing andgood-decreasingbalance. You can manually adjust depreciation expense to equal depreciable cost, or you can add additional formulas to ensure that total depreciation equals depreciable cost. If you are interested, these additional formulas are included in the**excel book**and produce the results shown in the screenshot below.

SECTION 2

## 4 more amortization methods and 2 examples

The unit-of-production method of depreciation does not have a built-in Excel function, but it is included here because it is a widely used depreciation method and can be calculated using Excel. the guy is=((cost − recovery) / useful life in units) * units produced in the period. The first two arguments are the same as in Section 1, with the other arguments defined as follows.

**service life in units**— the number of units that the asset is estimated to produce over its entire useful life;y**production units of this period**— the number of units this asset producedperiod.

HeVDBthe function calculatesgood-decreasing-balancedepreciation (or some other factor ofdecreasing-balancedepreciation) for any period, including partial periods. This function takes seven arguments:cost,to save,ZOE,first period,end_period,factor, youdo not change. The first five arguments are required and the last two are optional. The first three arguments are defined as they were in Section 1. The other arguments are as follows:

**first period**— period from which depreciation is calculated (the unit used for the period must be the same as the unit used for life, eg years, months, etc.);**end_period**— period to complete the depreciation calculation (the unit used for the period must be the same as the unit used for life, eg years, months, etc.);**factor**— by which factor to multiply the depreciation rate (this argument is optional; if left blank, Excel will assume the factor is 2,correctly-linedepreciation rate times two, that isgood-decreasing-balancedevaluation);y**do not change**— value to specify whether to togglecorrectly-linedepreciation when this estimate is greater than the estimate fordecreasing-balancedepreciation (this argument is optional if left blank or value isFALSE, Excel will change. if the price isTRUE, Excel will not change).

Let's look at an example using the two depreciation methods described so far. As in the previous example, let's say our company has an asset with an original cost of $50,000, a salvage value of $10,000, and a useful life of five years and 3,000 units. This time we will create a depreciation schedule for the asset using the two types of depreciation shown in the screenshot below. To continue in Excel, go to Sheet**here**and go to the second tab.

Create the formula forunits-of-Productiondepreciation in cell C8. The necessary parts arecost,to save,service life in units, youproduction units of this period. Hecostappears in cell C2.to saveappears in cell C3.service life in unitsappears in cell C5. It isproduction units of this periodappears in cell B8. We want all cells to remain fixed exceptproduction units of this period, so we'll include absolute references (POSTSCRIPT) in all cells except this one. The formula in cell C8 is=(($C$2-$C$3)/$C$5)*B8. Drag this formula down to fill cells C9 untilC12.

Create the formula forvariable-decreasing-balancedepreciation in cell D8. The arguments to be defined here arecost,to save,ZOE,first period, youend_period. Hecostyransom valueare the same as in the previous example, whileZOE, under time periods, is listed in cell C4.first periodappears in cell A8. It isend_periodappears in cell A8. Since we want the period to span a full period, we need to include minus 1 in the initial period calculation to reflect a full period offirst periodoneend_period. Include absolute references in front of columns and rows of cells, except cells describing arguments.first periodyend_period. The formula in cell D8 is=VDB($C$2,$C$3,$C$4,A8-1,A8). Drag this formula down to fill cells D9D12.

Your spreadsheet should look like the screenshot below.

the two endingsbuilt-emThe depreciation functions in Excel areAMORDEGRCyAMORLINC. HeAMORDEGRCthe function calculates frenchdecreasing-balancedepreciation and theAMORLINCthe function calculates frenchcorrectly-linedevaluation. Both functions take seven arguments:cost,purchase date,first period,to save,period,bowl, youbase. The first six arguments are required and the last one is optional. The arguments are defined as follows:

**cost**— initial cost of the asset;**purchase date**— date of purchase of the asset;**first period**— expiry date of the firstperiod;**to save**— salvage value of the asset (the book value of the asset after its full depreciation).**period**— period for its calculationdevaluation;**bowl**- depreciation rate;y**base**— base year to use (this argument is optional):- 0 or left: 360 days (US).
- 1—real.
- 3 — 365dias.
- 4 — 360 days (European).

These two functions have the same syntax, butAMORDEGRCcontains a depreciation rate at which depreciation is accelerated based on the useful life of the asset.

Let's look at an example using these last two amortization methods. Let's say our business has an asset with an original cost of $50,000, salvage value of $10,000, purchase date 7/1/2021, first period end 12/31/2021, useful life of five years. , and a 15% depreciation fee, as shown in the screenshot below. Our task is to create another depreciation schedule for the asset using the two French methods. You can follow this example in your fourth tab**excel spreadsheet**.

Create the formula for Frenchdecreasing-balancedepreciation in cell B9. pension is=AMORDEGRC(cost, date_purchase, first_period, redemption, period, price, [base]).Hecostappears in cell C2,purchase dateon cell C4,first periodin cell C5,to saveon cell C3,periodon cell A9 ebowlin cell C6. These last two depreciation methods start the depreciation calculation at period 0. Include absolute references in front of cell columns and rows, except for the cell describing the argument.period. The formula in cell B9 is=AMORDEGRC($C$2,$C$4,$C$5,$C$3,A9,$C$6). Drag this formula down to fill cells B10 untilB14.

the french formulacorrectly-linedamping is generated in cell C9. pension is=AMORDEGRC(cost, date_purchase, first_period, redemption, period, price, [base]).All arguments are defined as for French.decreasing-balancedevaluation. Faceemcell C9 is=AMORLINC($C$2,$C$4,$C$5,$C$3,A9,$C$6). Drag this formula down to fill cells C10 through C14. Your spreadsheet should now look like the screenshot below.

French declining balance is an accelerated method of depreciation and may need to be offset against the full amount of the depreciation expense to equal the depreciation cost. As noted in Section 1, you can manually adjust the depreciation cost, or you can add additional formulas to ensure that total depreciation equals the depreciation cost. These additional formulas are included in the**related excel workbook**.

**About the author**

**Kelly L. Williams, CPA, Ph.D., MBA**, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

**send a question**

Have technical questions for this column? Or, after reading an answer, do you have a better solution? send them tojofatech@aicpa.org. We regret that we cannot respond individually to all submitted questions.

## FAQs

### How depreciation can be calculated in Excel? ›

Depreciation can be calculated on fixed assets in excel by: For straight-line depreciation method: **=SLN (cost, salvage, life)**.

**What are the methods you can use to calculate depreciation? ›**

The four methods for calculating depreciation allowable under GAAP include **straight-line, declining balance, sum-of-the-years' digits, and units of production**. 2. The best method for a business depends on size and industry, accounting needs, and types of assets purchased.

**What is the easiest way to calculate depreciation? ›**

The **straight-line method** is the simplest and most commonly used way to calculate depreciation under generally accepted accounting principles. Subtract the salvage value from the asset's purchase price, then divide that figure by the projected useful life of the asset.

**What is the formula for depreciation straight-line method in Excel? ›**

**=SLN(cost, salvage, life)**

The SLN function uses the following arguments: Cost (required argument) – This is the initial cost of the asset. Salvage (required argument) – The value at the end of the depreciation (sometimes called the salvage value of the asset).

**How to calculate depreciation using declining balance method in Excel? ›**

The Excel DB function returns the depreciation of an asset for a specified period using the fixed-declining balance method. The calculation is based on initial asset cost, salvage value, the number of periods over which the asset is depreciated and, optionally, the number of months in the first year.

**What are the 3 depreciation methods? ›**

**The three methods of depreciation are:**

- Straight Line Method.
- Written Down Value Method.
- Units of production method.

**What are the 2 most popular methods of depreciation? ›**

The most common depreciation methods include: **Straight-line**. Double declining balance.

**Why are there different depreciation methods? ›**

Some businesses choose one method for depreciating all their assets while some use two or more methods. The reason for using different methods **could depend on the useful life of the asset or the company wanting larger deductions early**.

**What is the most straightforward method you can use to calculate depreciation? ›**

**Straight line** is the most straightforward and easiest method for calculating depreciation. It is most useful when an asset's value decreases steadily over time at around the same rate.

**How do I calculate straight lines in Excel? ›**

The equation of a straight line is **y = mx + b**. Once you know the values of m and b, you can calculate any point on the line by plugging the y- or x-value into that equation. You can also use the TREND function. where x and y are sample means; that is, x = AVERAGE(known x's) and y = AVERAGE(known_y's).

### How to calculate depreciation using straight line method and reducing balance method? ›

**Simple – you can use the following formula:**

- Straight-Line Depreciation = (Purchase Price – Salvage Value) / Useful Life.
- Reducing Balance Depreciation = (Value at Beginning of the Year x Depreciation Rate) / 100.
- Sum of Digits Depreciation = Depreciable Cost x (Remaining Useful Life / Sum of Years' Digits)

**What is linear depreciation straight line method? ›**

Using straight line depreciation, a linear depreciation formula, **the value of an asset is reduced at a steady rate over each period until it reaches its salvage value** — or the amount for which it can be exchanged when it has reached the end of its usefulness.

**How to calculate depreciation using the declining balance method? ›**

The formula for calculating depreciation value using declining balance method is, **Depreciation per annum = (Net Book Value - Residual Value) x % Depreciation Rate** Net Book value is the cost of a fixed asset minus the accumulated (total) depreciation.

**How to calculate depreciation by using reducing balance method? ›**

Suppose that the fixed asset acquisition price is 11,000, the scrap value is 1,000, and the depreciation percentage factor is 30. Using the Reducing balance method, **30 percent of the depreciation base (net book value minus scrap value) is calculated at the end of the previous depreciation period**.

**What is the method of calculating depreciation using reducing balance? ›**

The reducing balance method of depreciation is a method in which **depreciation is calculated at a fixed percentage rate of the book value of the assets**. This results in higher depreciation expenses in the initial years, in line with the higher productivity showcased by the asset.

**What is the most commonly used method of depreciation? ›**

The most common way to calculate depreciation is the straight-line method. The difference between the fixed asset cost and its salvage value is divided by the useful life of that asset in years to get the depreciating value, which is the same for each year of the asset's life.

**Which of the four methods used to measure depreciation is the easiest? ›**

The **straight-line depreciation method** is the easiest to understand and implement. Start by subtracting the asset's salvage value from its cost. Then, divide the remaining amount by the asset's useful life. This gives you the amount of depreciation to recognize for each period.

**What are the three 3 factors that affect the calculation of depreciation? ›**

There are three basic things which are required to charge depreciation viz, **cost, estimated useful life and probable salvage value**; these are the three things which affect the amount of depreciation.

**Which method of calculating depreciation is the best and why? ›**

The **straight-line method** of depreciation is one of the most effective methods of allocating the cost of capital assets. With the straight-line method, assets' values are reduced uniformly in every period until it reaches the salvage value, or the end of an asset's useful life.

**How many types of depreciation are there? ›**

There are **four main methods** used to calculate depreciation: straight-line, units of production, double declining balance and sum of the years' digits.

### What is depreciation and its methods with examples? ›

In accounting terms, depreciation is defined as **the reduction of the recorded cost of a fixed asset in a systematic manner until the value of the asset becomes zero or negligible**. An example of fixed assets are buildings, furniture, office equipment, machinery etc.

**Can you choose depreciation method? ›**

System Id shares that **if the asset is expected to have the same efficiency throughout its expected life, you may want to select the physical deprecation method**. If an asset produces more revenue in its early years, the accelerated depreciation method is better suited for this asset.

**What is depreciation for dummies? ›**

The term depreciation refers to **an accounting method used to allocate the cost of a tangible or physical asset over its useful life**. Depreciation represents how much of an asset's value has been used. It allows companies to earn revenue from the assets they own by paying for them over a certain period of time.

**Which method results in the highest depreciation? ›**

The **declining balance method (DBM)** gives the highest depreciation in the initial year.

**What is the most commonly used depreciation method? ›**

**Straight Line Method**

This is the simplest and most used depreciation method. It is best for smaller businesses that are looking for a simple way to calculate depreciation. With the straight-line method, you are calculating a depreciation amount that is the same year after year for the life of the asset.

**What is the MACRS depreciation method? ›**

The modified accelerated cost recovery system (MACRS) is a depreciation system used for tax purposes in the U.S. MACRS depreciation **allows the capitalized cost of an asset to be recovered over a specified period via annual deductions**. The MACRS system puts fixed assets into classes that have set depreciation periods.

**Which depreciation method is the most accurate? ›**

**Usage-Based Depreciation**

This is the most accurate of the depreciation methods in matching actual usage to the related depreciation expense, but suffers from an inordinate amount of record keeping to track usage levels.

**How do you manually calculate MACRS depreciation? ›**

**To do that, you can use the following MACRS depreciation formulas:**

- 1st Year Depreciation = Cost x (1 / Useful Life) x Depreciation Method x Depreciation Convention.
- Subsequent Years Depreciation = (Cost – Depreciation in Previous Years) x (1 / Recovery Period) x Depreciation Method.

**What is 7 year property for depreciation? ›**

[10] What is class life? Class life is the number of years over which an asset can be depreciated. The tax law has defined a specific class life for each type of asset. Real Property is 39 year property, **office furniture** is 7 year property and autos and trucks are 5 year property.

**What are 5 year depreciable assets? ›**

5-year property: **vehicles, computer equipment, office machinery, cattle, and appliances used in a residential rental property**. 7-year property: office furniture and fixtures. 10-year property: water transportation equipment and some agricultural buildings.