Depreciation in Excel: Methods and Calculations Explained

Every business owns assets – machinery, vehicles, computers, furniture – and these assets don’t hold their value forever. As they age or get used, their worth decreases. This decrease in value is what accountants call depreciation, and recording it accurately is a fundamental part of sound financial management. Excel makes this process significantly easier with built-in financial functions that can calculate depreciation automatically, period by period. Whether you’re building a fixed asset register or a full depreciation schedule, understanding how to use Excel’s depreciation tools is a practical skill with real-world accounting impact.

What is depreciation and why does it matter? 🔗

Depreciation is the systematic allocation of an asset’s cost over its useful life. When a business purchases a long-term asset – say, a delivery truck for $40,000 – it doesn’t record the full $40,000 as an expense in the first year. Instead, it spreads that cost across the years the truck is expected to be in use. This matches the expense to the revenue the asset helps generate, following the matching principle in accounting.

From a business perspective, depreciation serves several important purposes. It reduces taxable income by recording an annual expense, helps companies plan for asset replacement, and ensures that financial statements reflect a more accurate picture of a company’s net worth. Without depreciation, a business might look artificially profitable while its physical assets quietly wear out.

Three key values define every depreciation calculation:

• Cost: The original purchase price of the asset.
• Salvage value: The estimated residual value of the asset at the end of its useful life (what it can be sold for as scrap or resale).
• Useful life: The number of periods – typically years – over which the asset will be depreciated.

Excel’s built-in depreciation functions – including SLN, DB, DDB, and SYD – all use these three inputs as their foundation. The method you choose determines how the depreciation expense is distributed across those periods.

The straight-line method and the SLN function 🔗

The straight-line method is the simplest and most widely used depreciation approach. It assumes an asset loses an equal amount of value in every period throughout its useful life. The annual depreciation is constant from year one to the final year.

The formula behind it is straightforward:

Annual Depreciation = (Cost − Salvage Value) ÷ Useful Life

In Excel, the SLN function performs this calculation automatically. Its syntax is:

=SLN(cost, salvage, life)

For example, if a machine costs $10,000, has a salvage value of $1,000, and a useful life of 10 years, the SLN function calculates:

=SLN(10000, 1000, 10) → $900 per year

This means $900 is recorded as depreciation expense every year for 10 years, reducing the asset’s book value from $10,000 down to $1,000 at the end of its life. Because the amount never changes, this method is easy to budget for and straightforward to audit.

When to use straight-line depreciation 🔗

Straight-line depreciation works best for assets that wear out evenly over time – furniture, buildings, and long-term equipment that doesn’t rapidly become obsolete. It’s also the preferred method when simplicity and consistency in financial reporting are the priority. According to the Journal of Accountancy, the SLN function is one of the most commonly taught Excel tools in introductory accounting courses because of how directly it maps to fundamental accounting concepts.

The declining balance method and the DB function 🔗

The declining balance method takes a different approach. Instead of spreading depreciation evenly, it front-loads the expense – higher depreciation in early years, lower in later years. This reflects the economic reality that many assets, particularly technology and machinery, lose value more rapidly when they’re new.

Rather than applying depreciation to the original cost each year, the declining balance method applies a fixed rate to the asset’s remaining book value (cost minus accumulated depreciation). This means the depreciation amount shrinks each year, even though the rate stays the same.

Excel’s DB function handles this calculation. Its syntax is:

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

The additional period argument tells Excel which specific year you’re calculating for. The optional month argument accounts for partial first years (e.g., if an asset is purchased mid-year with only 9 months remaining, you’d enter 9).

Using the same $10,000 asset with a $1,000 salvage value over 10 years, the DB function first calculates a fixed rate:

Fixed Rate = 1 − (Salvage ÷ Cost)^(1 ÷ Life)
= 1 − (1000 ÷ 10000)^(1 ÷ 10) ≈ 0.206 (20.6%)

This rate is then applied each year to the remaining book value:

• Year 1: $10,000 × 20.6% = $2,060
• Year 2: ($10,000 − $2,060) × 20.6% = $1,635.64
• Year 3: Continuing to decline…

The depreciation expense decreases every year, and by year 10, the book value approaches the $1,000 salvage value. In Excel, the formulas for years 1 through 10 would look like:

=DB($B$1, $B$2, $B$3, A8) – where A8 contains the period number, and the dollar signs lock the cost, salvage, and life cell references so they don’t shift when you copy the formula down.

Declining balance vs. straight-line: which fits your asset? 🔗

The choice between these two methods depends on the nature of the asset and business policy. Accelerated methods like declining balance are often better suited to assets like computers, vehicles, or production equipment that become outdated or wear out faster in early years. Straight-line suits assets with consistent usefulness, like office furniture or commercial buildings. It’s also worth noting that tax regulations in many jurisdictions specify which method is permissible – so always verify with applicable standards before deciding.

Building a depreciation schedule in Excel: step-by-step 🔗

A depreciation schedule is a structured table that shows the depreciation expense, accumulated depreciation, and remaining book value for each period of an asset’s life. Setting one up in Excel is straightforward once you understand the functions. Here’s how to build one from scratch using both the SLN and DB methods.

Step 1: Set up your asset data 🔗

Start by entering your asset details in a clearly labeled reference area. In cells B1 through B3, enter the following:

• B1: Cost → 10000
• B2: Salvage Value → 1000
• B3: Useful Life (years) → 10

Label these clearly (e.g., “Asset Cost”, “Salvage Value”, “Useful Life” in column A). Using a dedicated input section and cell references – rather than typing numbers directly into formulas – keeps your schedule flexible and easy to update.

Step 2: Build the schedule columns 🔗

Starting from row 7, create the following column headers:

• Column A: Year (1 through 10)
• Column B: SLN Depreciation
• Column C: DB Depreciation
• Column D: Accumulated Depreciation (SLN)
• Column E: Book Value (SLN)
• Column F: Book Value (DB)

Step 3: Enter the SLN formula 🔗

In cell B8, enter the SLN function using absolute references for the asset data:

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

Because SLN returns the same value every period, you can copy this formula down from B8 to B17 without any changes – the result will be $900 in each row. As the Journal of Accountancy explains, copying the formula is as simple as dragging the fill handle (the small black square in the cell’s bottom-right corner) down the column.

Step 4: Enter the DB formula 🔗

In cell C8, enter the DB function with the period argument pointing to column A:

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

The reference to A8 (the year number) is left without absolute dollar signs so it updates automatically as you copy the formula down. Drag this formula from C8 to C17. Each row will show a different, declining depreciation value – higher in year 1, lower in year 10.

Step 5: Calculate accumulated depreciation and book value 🔗

For the SLN accumulated depreciation in D8, enter:

=B8 (just the first year’s depreciation)

For D9 onward:

=D8 + B9

This running total adds each year’s depreciation to the previous cumulative amount. You can also use =SUM($B$8:B8) and drag it down – a cleaner approach that calculates the running total automatically.

For book value under SLN in E8:

=$B$1 - D8

This subtracts accumulated depreciation from the original cost. Copy this down through E17. By year 10, the result should equal the salvage value of $1,000.

For the DB book value in F8, you can use a similar approach:

=$B$1 - SUM($C$8:C8)

This dynamically calculates the remaining book value by subtracting total DB depreciation to date from the original cost.

Step 6: Verify your results 🔗

A well-built depreciation schedule should pass two checks. Under SLN, the book value in the final year should exactly equal the salvage value ($1,000). Under DB, the asset depreciates toward – but may not precisely reach – the salvage value due to the nature of the fixed-rate calculation. If your totals are off, double-check that absolute references ($B$1, $B$2, $B$3) are correctly placed and that period numbers in column A run from 1 to 10 without gaps.

Other Excel depreciation functions worth knowing 🔗

Beyond SLN and DB, Excel offers additional depreciation functions that handle more specific scenarios. The DDB (Double Declining Balance) function applies twice the straight-line rate to the remaining book value each year – making it even more front-loaded than the standard DB method. Its syntax is =DDB(cost, salvage, life, period), and it’s commonly used for assets that depreciate very quickly, like computers or vehicles.

The VDB (Variable Declining Balance) function is the most flexible option. It uses double-declining balance by default but automatically switches to straight-line depreciation in the year that straight-line yields a higher expense – ensuring the asset is fully depreciated to its salvage value without any shortfall. This built-in intelligence makes VDB particularly useful for real-world depreciation schedules. Finally, SYD (Sum of Years’ Digits) is another accelerated method that uses a declining fraction based on the sum of all years in the asset’s life.

According to Excel’s financial function library, all of these functions are accessible through the Formulas tab under Financial, making them easy to find even if you don’t remember the exact syntax.

Practical tips for accurate depreciation in Excel 🔗

A few habits will keep your depreciation schedules clean and error-free. Always use absolute cell references (with $ signs) for your cost, salvage, and life values so they don’t accidentally shift when you copy formulas. Use named ranges – for example, naming cell B1 “Cost” – to make your formulas more readable: =SLN(Cost, Salvage, Life) is far easier to audit than =SLN($B$1,$B$2,$B$3). And always include a sum row at the bottom of your depreciation column to verify that total depreciation equals Cost minus Salvage Value – a quick sanity check that catches formula errors before they make it into a report.

For businesses managing multiple assets, consider building a separate input table for each asset and using Excel Tables (Insert → Table) to keep data structured. This makes it easy to add new assets and have formulas update automatically, a technique widely recommended in Excel-based financial modeling workflows.

What do you think? When a business buys a new piece of technology that loses value quickly, does it make more financial sense to use an accelerated depreciation method like DB from the start – or is the consistency of straight-line depreciation more valuable for long-term planning? And how might the choice of depreciation method affect the way investors or lenders perceive a company’s financial statements?

References

1. https://www.journalofaccountancy.com/issues/2021/may/how-to-calculate-depreciation-in-excel/
2. https://support.microsoft.com/en-us/office/sln-function-cdb666e5-c1c6-40a7-806a-e695edc2f1c8
3. https://corporatefinanceinstitute.com/resources/excel/straight-line-sln-function-depreciation/
4. https://www.journalofaccountancy.com/newsletters/extra-credit/calculate-depreciation-using-excel-sln-function/
5. https://www.excel-easy.com/examples/depreciation.html
6. https://www.myexcelonline.com/blog/excel-depreciation-formula/
7. https://www.vertex42.com/ExcelTemplates/depreciation-schedule.html
8. https://www.dummies.com/article/technology/software/microsoft-products/excel/how-to-use-depreciation-functions-in-excel-2016-139506/