Write an excel formula to calculate depreciation by SLM Method if I have "Depreciable…
- 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".
- 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:
-
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)
- Start =
-
Calculate number of days the asset was in use during the year.
-
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:
Cell Description A1 Depreciable Value A2 Year Start Date A3 Year End Date A4 Asset Sale Date (blank if not sold yet) A5 Total 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 rateMAX(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 Dateis 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!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.