How to Calculate Straight-Line Depreciation in Google Sheets Using the SLN Function

Tracking asset depreciation is a fundamental requirement for small business owners, freelancers, and financial analysts. When an enterprise purchases capital equipment—such as laptops, machinery, company vehicles, or office furniture—accounting principles dictate that the cost should be spread across the asset’s useful lifespan rather than written off entirely in the purchase period. The simplest and most widely used depreciation technique is straight-line depreciation, where the asset loses an equal amount of monetary value each fiscal year.

Google Sheets provides a dedicated financial function to automate this exact arithmetic: the SLN function. By supplying the asset’s initial purchase cost, estimated salvage value, and expected useful lifespan, SLN instantly calculates the precise depreciation deduction for each period. In this guide, you will learn the exact syntax of the SLN function, explore practical single-asset and multi-period schedule examples, and discover best practices for handling partial years and salvage adjustments.

Understanding the Syntax of the SLN Function

The SLN function requires three straightforward numerical arguments to compute periodic depreciation:

=SLN(cost, salvage, life)
  • cost: The initial purchase price or acquisition cost of the asset, including any shipping, delivery, or installation fees necessary to make the asset operational.
  • salvage: The estimated residual value of the asset at the conclusion of its useful operational life (often termed scrap value). If the asset is expected to have zero value at retirement, enter 0.
  • life: The total number of periods over which the asset is being depreciated. While this is typically expressed in years, you can also specify quarters or months, provided all financial periods remain standardised.

Mathematically, the SLN function executes the classic straight-line formula:

Depreciation per Period = (Cost - Salvage) / Life

Step-by-Step: Calculating Depreciation for a Single Asset

To demonstrate how SLN operates in a standard workbook, consider a small studio purchasing video production equipment:

  • Initial purchase cost (Cell B2): £4,800
  • Estimated salvage value (Cell B3): £600
  • Useful lifespan in years (Cell B4): 5
  1. Open your spreadsheet in Google Sheets and input the asset parameters into cells B2 through B4.
  2. Select cell B5 where you wish the annual depreciation deduction to appear.
  3. Type the following formula and press Enter:
    =SLN(B2, B3, B4)
  4. Google Sheets instantly returns £840.00.

This result demonstrates that the equipment depreciates by £840 each year. Over five years, the total depreciation equals £4,200 (£840 × 5). When added to the £600 residual salvage value, it reconciles perfectly with the initial £4,800 expenditure.

Building a Complete Multi-Year Depreciation Schedule

While calculating a single annual figure is helpful, accountants and financial managers generally require a multi-year ledger displaying beginning book value, annual depreciation, accumulated depreciation, and ending book value across every year of the asset’s lifecycle.

To construct a dynamic depreciation schedule in Google Sheets, follow these structural steps:

  1. Set up your summary table headers across row 7:
    • Cell A7: Year
    • Cell B7: Beginning Book Value
    • Cell C7: Annual Depreciation
    • Cell D7: Accumulated Depreciation
    • Cell E7: Ending Book Value
  2. Enter the sequential years 1 through 5 in cells A8:A12.
  3. In cell B8, link to the initial purchase cost:
    =B2
  4. In cell C8, calculate the fixed annual depreciation using absolute cell references to allow effortless dragging:
    =SLN($B$2, $B$3, $B$4)
  5. In cell D8, compute the initial year’s accumulated depreciation:
    =C8
  6. In cell E8, subtract the annual depreciation from the beginning book value:
    =B8 - C8
  7. For Year 2 (Row 9), carry forward the previous year’s ending book value by entering the following formula in cell B9:
    =E8
  8. In cell C9, copy down the absolute SLN formula:
    =SLN($B$2, $B$3, $B$4)
  9. In cell D9, calculate cumulative depreciation:
    =D8 + C9
  10. In cell E9, determine the new book value:
    =B9 - C9
  11. Highlight cells B9:E9 and drag the fill handle downward through row 12.

By Year 5, your ending book value in cell E12 will match your salvage value of £600 exactly, ensuring complete audit compliance.

Calculating Monthly and Mid-Year Pro-Rata Depreciation

In real-world business scenarios, companies rarely acquire capital equipment conveniently on the first day of the fiscal year. When an asset is placed into service mid-year, accounting standards require pro-rating the depreciation deduction based on the active months of service.

You can calculate monthly straight-line depreciation simply by expressing the life parameter in months or dividing the annual SLN result by 12:

=SLN(cost, salvage, life_in_years * 12)

Alternatively, if you already have the annual depreciation figure in cell B5 and the asset was placed into service on 1 September (leaving 4 operational months in a calendar financial year ending 31 December), use this pro-rata formula:

=(SLN(B2, B3, B4) / 12) * 4

This yields £280.00 of deductible depreciation for the inaugural partial year, after which full annual allocations proceed normally.

Troubleshooting Common Errors in the SLN Function

When working with financial formulas in Google Sheets, errors occasionally arise due to formatting discrepancies or invalid parameter values:

  • #VALUE! Error: Occurs when non-numeric text characters are included inside the formula arguments. Ensure currency symbols are applied via cell formatting (Format > Number > Currency) rather than typed manually as text strings inside the formula.
  • #NUM! Error: Triggers if the life argument is less than or equal to zero. Assets must possess a positive lifespan. Additionally, an error occurs if the salvage value exceeds the initial purchase cost.
  • Negative Depreciation: If your calculated depreciation produces a negative figure, verify that the salvage value is smaller than the initial cost. If an asset appreciates over time, it cannot be depreciated under standard straight-line guidelines.

Frequently Asked Questions

Can the salvage value in the SLN function be zero?

Yes. If an asset is expected to have no commercial resale value or scrap worth at the end of its operational lifecycle, enter 0 as the salvage argument. For example, =SLN(5000, 0, 5) allocates £1,000 of depreciation per year.

How does SLN differ from the DB and DDB functions in Google Sheets?

The SLN function distributes depreciation uniformly across all periods in equal increments. In contrast, DB (declining balance) and DDB (double-declining balance) are accelerated depreciation functions that allocate substantially larger deductions in the earlier years of an asset’s life and lower deductions toward the end.

Does Google Sheets update the SLN calculation if I revise the purchase cost?

Yes. When cell references (such as B2, B3, and B4) are utilised within the SLN formula, modifying any input value will trigger Google Sheets to recalculate the periodic depreciation and dependent schedules in real time.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.