Skip to content
The Chat Archive
qwen3-235b-a22b-no-thinking· Alibaba (Qwen) LMArena Human Preference 140K

Write an excel formula to calculate depreciation by SLM Method if I have "Depreciable…

  1. User

    Write an excel formula to calculate depreciation by SLM Method if I have "Depreciable Value" "Year Start Date" " Year End Date" "Asset sale Date"(In some cases), "Total Life of Asset in Days".

  2. Assistant

    To calculate Straight Line Method (SLM) depreciation in Excel using the parameters you've provided:

    Given:

    • Depreciable Value – Initial value to be depreciated
    • Year Start Date
    • Year End Date
    • Asset Sale Date (optional, may be blank if asset is not sold yet)
    • Total Life of Asset in Days

    ✅ Objective:

    Calculate depreciation for a given financial year, taking into account:

    • Whether the asset was held for the full year or part of it
    • If the asset was sold before the end of the year
    • The prorated depreciation based on days

    🧮 Formula Explanation:

    We will use this logic:

    1. Determine the actual period of depreciation for the year:

      • Start = MAX(Year Start Date, Asset Purchase Date)
      • End = MIN(Year End Date, Asset Sale Date or Total Life End Date)
    2. Calculate number of days the asset was in use during the year.

    3. Depreciation = (Depreciable Value / Total Life in Days) * Days in Use During Year


    🔢 Assumptions for Excel Implementation:

    Let’s assume your data is structured as follows in Excel:

    CellDescription
    A1Depreciable Value
    A2Year Start Date
    A3Year End Date
    A4Asset Sale Date (blank if not sold yet)
    A5Total Life of Asset in Days

    Also assume:

    • The purchase date is known and located at cell A6
    • The total life end date = A6 + A5 → i.e., purchase date + total life in days

    📌 Final Excel Formula:

    =IFERROR(
      MAX(0,
        (A1 / A5) *
        MIN(
          A3,
          IF(A4="", A6 + A5, A4),
          A6 + A5
        ) -
        MAX(
          A2,
          A6
        )
      ) + 1,
    0)
    

    But that’s hard to read — here's a better version with line breaks (for understanding):

    =IFERROR(
      MAX(0,
        (A1 / A5) *
        (
          MIN(
            A3,
            IF(A4="", A6 + A5, A4),  // End of asset life or sale date
            A6 + A5                 // Cap at total life end date
          ) -
          MAX(
            A2,
            A6                      // Start of year or purchase date
          ) +
          1                        // Include both start and end dates
        )
      ),
    0)
    

    🧾 How It Works:

    • A1 / A5: Daily depreciation rate
    • MAX(A2, A6): First day of depreciation in the year (the later of year start and purchase date)
    • MIN(A3, IF(A4="", A6+A5, A4), A6+A5): Last day of depreciation in the year (earlier of year end, sale date, or total life end)
    • Difference gives the number of days the asset was in use during the year
    • Multiply daily rate by number of days to get annual depreciation

    📝 Notes:

    • This formula assumes inclusive dates (i.e., depreciation happens on both start and end dates).
    • If Asset Sale Date is blank, the formula assumes the asset continues until the end of its useful life.
    • If the asset has already expired, the formula caps depreciation at the total life limit.

    ✅ Optional Enhancement:

    If you want to calculate accumulated depreciation over multiple years, or generate a depreciation schedule, we can build a table using this formula row-by-row per year.

    Let me know if you'd like help building a full depreciation schedule!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.