8 Ways to Calculate Depreciation in Excel (2023)

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. Hesecond sectioncovers 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 videoshereyhereya workbook with examplesuse 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 followexcel fileas long as your worksheet looks similar to 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 Sheethereand 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 tabexcel 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 therelated 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.