SLN Function

Returns a Double specifying the straight-line depreciation of an asset for a single period.

Syntax

SLN(cost, salvage, life)

Parameters

cost - Required. Double specifying initial cost of the asset.
salvage - Required. Double specifying value of the asset at the end of its useful life.
life - Required. Double specifying length of the useful life of the asset.

Return Value

Returns a Double representing the depreciation of an asset for a single period using the straight-line method. The formula is:

SLN = (cost - salvage) / life

Remarks

The SLN function calculates straight-line depreciation, which is the simplest and most commonly used depreciation method. It assumes that the asset depreciates by the same amount each period over its useful life. Key characteristics: - Depreciation is constant for each period - Total depreciation = cost - salvage value - Each period's depreciation = (cost - salvage) / life - All three arguments must be positive - Life represents the number of periods (years, months, etc.) - Units must be consistent (if life is in years, result is yearly depreciation) Straight-line depreciation is used when: - Asset provides uniform benefit over its life - Simple, easy-to-understand method is preferred - Tax regulations require or allow straight-line method - Asset doesn't become obsolete quickly - Usage is relatively constant over time The straight-line method is one of several depreciation methods: - SLN: Straight-line (this function) - constant depreciation - DDB: Double-declining balance - accelerated depreciation - SYD: Sum-of-years digits - accelerated depreciation

Typical Uses

Asset Depreciation: Calculate annual depreciation expense
Financial Planning: Project future asset values
Tax Calculations: Determine tax deductions
Budgeting: Estimate replacement costs
Accounting: Prepare financial statements
Asset Management: Track asset value over time
Cost Analysis: Calculate total ownership cost
Investment Analysis: Evaluate asset investments

Basic Examples

' Example 1: Calculate yearly depreciation for equipment
Dim equipmentCost As Double
Dim salvageValue As Double
Dim usefulLife As Double
Dim yearlyDepreciation As Double
equipmentCost = 50000      ' $50,000 initial cost
salvageValue = 5000        ' $5,000 salvage value
usefulLife = 5             ' 5 years useful life
yearlyDepreciation = SLN(equipmentCost, salvageValue, usefulLife)
' Returns 9000 ($9,000 per year)

' Example 2: Calculate monthly depreciation
Dim cost As Double
Dim salvage As Double
Dim months As Double
Dim monthlyDepreciation As Double
cost = 24000
salvage = 2000
months = 36  ' 3 years = 36 months
monthlyDepreciation = SLN(cost, salvage, months)
' Returns 611.11 (approximately)

' Example 3: Calculate book value after n periods
Dim initialCost As Double
Dim salvage As Double
Dim life As Double
Dim periods As Integer
Dim annualDepreciation As Double
Dim bookValue As Double
initialCost = 100000
salvage = 10000
life = 10
periods = 3  ' After 3 years
annualDepreciation = SLN(initialCost, salvage, life)
bookValue = initialCost - (annualDepreciation * periods)
' bookValue = 73000

' Example 4: Display depreciation schedule
Dim cost As Double
Dim salvage As Double
Dim life As Integer
Dim depreciation As Double
Dim i As Integer
cost = 30000
salvage = 3000
life = 5
depreciation = SLN(cost, salvage, life)
For i = 1 To life
    Debug.Print "Year " & i & ": $" & depreciation
Next i

Common Patterns

Pattern 1: `CalculateBookValue`

Calculate asset book value after specified periods

Function CalculateBookValue(cost As Double, salvage As Double, _
                            life As Double, periodsElapsed As Integer) As Double
    Dim annualDepreciation As Double
    Dim totalDepreciation As Double
    annualDepreciation = SLN(cost, salvage, life)
    totalDepreciation = annualDepreciation * periodsElapsed
    ' Ensure book value doesn't go below salvage value
    If totalDepreciation > (cost - salvage) Then
        CalculateBookValue = salvage
    Else
        CalculateBookValue = cost - totalDepreciation
    End If
End Function

Pattern 2: `GenerateDepreciationSchedule`

Create complete depreciation schedule

Sub GenerateDepreciationSchedule(cost As Double, salvage As Double, _
                                 life As Integer)
    Dim depreciation As Double
    Dim bookValue As Double
    Dim i As Integer
    depreciation = SLN(cost, salvage, life)
    bookValue = cost
    Debug.Print "Year" & vbTab & "Depreciation" & vbTab & "Book Value"
    Debug.Print "0" & vbTab & "0" & vbTab & bookValue
    For i = 1 To life
        bookValue = bookValue - depreciation
        Debug.Print i & vbTab & depreciation & vbTab & bookValue
    Next i
End Sub

Pattern 3: `CompareDepreciationMethods`

Compare straight-line with other methods

Sub CompareDepreciationMethods(cost As Double, salvage As Double, _
                               life As Integer, year As Integer)
    Dim slnDepreciation As Double
    Dim ddbDepreciation As Double
    slnDepreciation = SLN(cost, salvage, life)
    ddbDepreciation = DDB(cost, salvage, life, year)
    Debug.Print "Year " & year
    Debug.Print "Straight-Line: $" & Format(slnDepreciation, "#,##0.00")
    Debug.Print "Double-Declining: $" & Format(ddbDepreciation, "#,##0.00")
End Sub

Pattern 4: `CalculateTotalDepreciation`

Calculate total depreciation over asset life

Function CalculateTotalDepreciation(cost As Double, salvage As Double) As Double
    CalculateTotalDepreciation = cost - salvage
End Function

Pattern 5: `ValidateDepreciationInputs`

Validate inputs before calculating depreciation

Function ValidateDepreciationInputs(cost As Double, salvage As Double, _
                                    life As Double) As Boolean
    ValidateDepreciationInputs = False
    If cost <= 0 Then
        MsgBox "Cost must be positive", vbExclamation
        Exit Function
    End If
    If salvage < 0 Then
        MsgBox "Salvage value cannot be negative", vbExclamation
        Exit Function
    End If
    If salvage >= cost Then
        MsgBox "Salvage value must be less than cost", vbExclamation
        Exit Function
    End If
    If life <= 0 Then
        MsgBox "Life must be positive", vbExclamation
        Exit Function
    End If
    ValidateDepreciationInputs = True
End Function

Pattern 6: `CalculateMonthlyDepreciation`

Convert annual to monthly depreciation

Function CalculateMonthlyDepreciation(cost As Double, salvage As Double, _
                                      yearsLife As Double) As Double
    Dim annualDepreciation As Double
    annualDepreciation = SLN(cost, salvage, yearsLife)
    CalculateMonthlyDepreciation = annualDepreciation / 12
End Function

Pattern 7: `CalculateDepreciationRate`

Calculate depreciation rate as percentage

Function CalculateDepreciationRate(cost As Double, salvage As Double, _
                                   life As Double) As Double
    Dim annualDepreciation As Double
    annualDepreciation = SLN(cost, salvage, life)
    CalculateDepreciationRate = (annualDepreciation / cost) * 100
End Function

Pattern 8: `CalculateReplacementYear`

Determine when asset should be replaced

Function CalculateReplacementYear(cost As Double, salvage As Double, _
                                  life As Double, _
                                  minimumValue As Double) As Integer
    Dim depreciation As Double
    Dim bookValue As Double
    Dim year As Integer
    depreciation = SLN(cost, salvage, life)
    bookValue = cost
    For year = 1 To life
        bookValue = bookValue - depreciation
        If bookValue <= minimumValue Then
            CalculateReplacementYear = year
            Exit Function
        End If
    Next year
    CalculateReplacementYear = life
End Function

Pattern 9: `CalculateAccumulatedDepreciation`

Calculate accumulated depreciation at specific period

Function CalculateAccumulatedDepreciation(cost As Double, salvage As Double, _
                                          life As Double, _
                                          period As Integer) As Double
    Dim annualDepreciation As Double
    annualDepreciation = SLN(cost, salvage, life)
    If period >= life Then
        CalculateAccumulatedDepreciation = cost - salvage
    Else
        CalculateAccumulatedDepreciation = annualDepreciation * period
    End If
End Function

Pattern 10: `FormatDepreciationReport`

Format depreciation information for display

Function FormatDepreciationReport(cost As Double, salvage As Double, _
                                  life As Double, year As Integer) As String
    Dim depreciation As Double
    Dim accumulated As Double
    Dim bookValue As Double
    Dim report As String
    depreciation = SLN(cost, salvage, life)
    accumulated = depreciation * year
    bookValue = cost - accumulated
    report = "Depreciation Report - Year " & year & vbCrLf
    report = report & "Initial Cost: $" & Format(cost, "#,##0.00") & vbCrLf
    report = report & "Annual Depreciation: $" & Format(depreciation, "#,##0.00") & vbCrLf
    report = report & "Accumulated Depreciation: $" & Format(accumulated, "#,##0.00") & vbCrLf
    report = report & "Book Value: $" & Format(bookValue, "#,##0.00") & vbCrLf
    FormatDepreciationReport = report
End Function

Advanced Usage

Example 1: `AssetDepreciationTracker` Class

Track depreciation for multiple assets

' Class: AssetDepreciationTracker
Private Type AssetInfo
    AssetName As String
    Cost As Double
    Salvage As Double
    Life As Double
    PurchaseDate As Date
    AnnualDepreciation As Double
End Type
Private m_assets() As AssetInfo
Private m_assetCount As Integer
Private Sub Class_Initialize()
    m_assetCount = 0
    ReDim m_assets(0 To 9)
End Sub
Public Sub AddAsset(assetName As String, cost As Double, _
                    salvage As Double, life As Double, _
                    purchaseDate As Date)
    If m_assetCount > UBound(m_assets) Then
        ReDim Preserve m_assets(0 To m_assetCount * 2)
    End If
    With m_assets(m_assetCount)
        .AssetName = assetName
        .Cost = cost
        .Salvage = salvage
        .Life = life
        .PurchaseDate = purchaseDate
        .AnnualDepreciation = SLN(cost, salvage, life)
    End With
    m_assetCount = m_assetCount + 1
End Sub
Public Function GetAssetBookValue(assetIndex As Integer, _
                                  asOfDate As Date) As Double
    Dim yearsElapsed As Double
    Dim totalDepreciation As Double
    Dim bookValue As Double
    If assetIndex < 0 Or assetIndex >= m_assetCount Then
        GetAssetBookValue = 0
        Exit Function
    End If
    With m_assets(assetIndex)
        yearsElapsed = DateDiff("d", .PurchaseDate, asOfDate) / 365.25
        If yearsElapsed >= .Life Then
            GetAssetBookValue = .Salvage
        Else
            totalDepreciation = .AnnualDepreciation * yearsElapsed
            bookValue = .Cost - totalDepreciation
            If bookValue < .Salvage Then
                GetAssetBookValue = .Salvage
            Else
                GetAssetBookValue = bookValue
            End If
        End If
    End With
End Function
Public Function GetTotalBookValue(asOfDate As Date) As Double
    Dim i As Integer
    Dim total As Double
    total = 0
    For i = 0 To m_assetCount - 1
        total = total + GetAssetBookValue(i, asOfDate)
    Next i
    GetTotalBookValue = total
End Function
Public Function GetAnnualDepreciationExpense() As Double
    Dim i As Integer
    Dim total As Double
    total = 0
    For i = 0 To m_assetCount - 1
        total = total + m_assets(i).AnnualDepreciation
    Next i
    GetAnnualDepreciationExpense = total
End Function
Public Function GetAssetCount() As Integer
    GetAssetCount = m_assetCount
End Function

Example 2: `DepreciationScheduleGenerator` Module

Generate detailed depreciation schedules

' Module: DepreciationScheduleGenerator
Public Function GenerateScheduleArray(cost As Double, salvage As Double, _
                                      life As Integer) As Variant
    Dim schedule() As Variant
    Dim depreciation As Double
    Dim bookValue As Double
    Dim accumulated As Double
    Dim i As Integer
    ReDim schedule(0 To life, 0 To 4)
    ' Headers
    schedule(0, 0) = "Year"
    schedule(0, 1) = "Beginning Value"
    schedule(0, 2) = "Depreciation"
    schedule(0, 3) = "Accumulated"
    schedule(0, 4) = "Ending Value"
    depreciation = SLN(cost, salvage, life)
    bookValue = cost
    accumulated = 0
    For i = 1 To life
        schedule(i, 0) = i
        schedule(i, 1) = bookValue
        schedule(i, 2) = depreciation
        accumulated = accumulated + depreciation
        schedule(i, 3) = accumulated
        bookValue = cost - accumulated
        schedule(i, 4) = bookValue
    Next i
    GenerateScheduleArray = schedule
End Function
Public Sub ExportScheduleToCSV(cost As Double, salvage As Double, _
                               life As Integer, filePath As String)
    Dim schedule As Variant
    Dim fileNum As Integer
    Dim i As Integer
    Dim j As Integer
    Dim line As String
    schedule = GenerateScheduleArray(cost, salvage, life)
    fileNum = FreeFile
    Open filePath For Output As #fileNum
    For i = 0 To life
        line = ""
        For j = 0 To 4
            If j > 0 Then line = line & ","
            line = line & schedule(i, j)
        Next j
        Print #fileNum, line
    Next i
    Close #fileNum
End Sub
Public Function CalculateQuarterlyDepreciation(cost As Double, salvage As Double, _
                                               yearsLife As Integer) As Double()
    Dim annualDepreciation As Double
    Dim quarterlyDepreciation As Double
    Dim quarters() As Double
    Dim i As Integer
    annualDepreciation = SLN(cost, salvage, yearsLife)
    quarterlyDepreciation = annualDepreciation / 4
    ReDim quarters(1 To yearsLife * 4)
    For i = 1 To yearsLife * 4
        quarters(i) = quarterlyDepreciation
    Next i
    CalculateQuarterlyDepreciation = quarters
End Function

Example 3: `DepreciationComparison` Class

Compare different depreciation methods

' Class: DepreciationComparison
Private m_cost As Double
Private m_salvage As Double
Private m_life As Integer
Public Sub Initialize(cost As Double, salvage As Double, life As Integer)
    m_cost = cost
    m_salvage = salvage
    m_life = life
End Sub
Public Function GetStraightLineDepreciation(year As Integer) As Double
    GetStraightLineDepreciation = SLN(m_cost, m_salvage, m_life)
End Function
Public Function GetDoubleDecliningDepreciation(year As Integer) As Double
    GetDoubleDecliningDepreciation = DDB(m_cost, m_salvage, m_life, year)
End Function
Public Function GetStraightLineBookValue(year As Integer) As Double
    Dim depreciation As Double
    depreciation = SLN(m_cost, m_salvage, m_life)
    GetStraightLineBookValue = m_cost - (depreciation * year)
End Function
Public Function GetTotalDifference(year As Integer) As Double
    Dim slnTotal As Double
    Dim ddbTotal As Double
    Dim i As Integer
    slnTotal = 0
    ddbTotal = 0
    For i = 1 To year
        slnTotal = slnTotal + GetStraightLineDepreciation(i)
        ddbTotal = ddbTotal + GetDoubleDecliningDepreciation(i)
    Next i
    GetTotalDifference = ddbTotal - slnTotal
End Function
Public Function GenerateComparisonReport(maxYears As Integer) As String
    Dim report As String
    Dim i As Integer
    report = "Depreciation Method Comparison" & vbCrLf
    report = report & "Cost: $" & Format(m_cost, "#,##0.00") & vbCrLf
    report = report & "Salvage: $" & Format(m_salvage, "#,##0.00") & vbCrLf
    report = report & "Life: " & m_life & " years" & vbCrLf & vbCrLf
    report = report & "Year" & vbTab & "SLN" & vbTab & "DDB" & vbCrLf
    For i = 1 To maxYears
        report = report & i & vbTab
        report = report & Format(GetStraightLineDepreciation(i), "#,##0") & vbTab
        report = report & Format(GetDoubleDecliningDepreciation(i), "#,##0") & vbCrLf
    Next i
    GenerateComparisonReport = report
End Function

Example 4: `FinancialPlanner` Module

Financial planning with depreciation

' Module: FinancialPlanner
Public Function CalculateReplacementFund(cost As Double, salvage As Double, _
                                        life As Double) As Double
    ' Calculate annual savings needed to replace asset
    Dim replacementCost As Double
    ' Assume replacement cost increases by inflation
    replacementCost = cost * 1.03 ^ life  ' 3% annual inflation
    CalculateReplacementFund = (replacementCost - salvage) / life
End Function
Public Function CalculateTaxSavings(cost As Double, salvage As Double, _
                                    life As Double, taxRate As Double) As Double
    ' Calculate annual tax savings from depreciation
    Dim annualDepreciation As Double
    annualDepreciation = SLN(cost, salvage, life)
    CalculateTaxSavings = annualDepreciation * taxRate
End Function
Public Function CalculateNetPresentValue(cost As Double, salvage As Double, _
                                         life As Integer, _
                                         discountRate As Double) As Double
    ' Calculate NPV of depreciation tax shield
    Dim annualDepreciation As Double
    Dim taxShield As Double
    Dim npv As Double
    Dim i As Integer
    Const TAX_RATE As Double = 0.3  ' 30% tax rate
    annualDepreciation = SLN(cost, salvage, life)
    taxShield = annualDepreciation * TAX_RATE
    npv = 0
    For i = 1 To life
        npv = npv + taxShield / ((1 + discountRate) ^ i)
    Next i
    CalculateNetPresentValue = npv
End Function
Public Function ShouldReplaceAsset(currentBookValue As Double, _
                                   replacementCost As Double, _
                                   yearsRemaining As Double, _
                                   expectedSavings As Double) As Boolean
    ' Determine if asset should be replaced now
    Dim replacementBenefit As Double
    replacementBenefit = expectedSavings * yearsRemaining
    ShouldReplaceAsset = replacementBenefit > replacementCost
End Function

Error Handling

The SLN function can generate the following errors: - Error 5 (Invalid procedure call or argument): If life is zero or negative - Error 6 (Overflow): If result exceeds Double range - Error 13 (Type mismatch): Arguments not numeric Always validate inputs before calling SLN:

On Error Resume Next
depreciation = SLN(cost, salvage, life)
If Err.Number <> 0 Then
    MsgBox "Error calculating depreciation: " & Err.Description
End If

Performance Considerations

SLN is a very fast calculation (simple division)
No iterative calculations required
Can be called repeatedly without performance concerns
Consider caching result if used multiple times with same inputs

Best Practices

Validate Inputs: Check cost > salvage, life > 0
Consistent Units: Ensure life matches desired period (years, months)
Handle Edge Cases: Check for zero life, negative values
Document Assumptions: Clearly state depreciation period
Salvage Value: Use realistic salvage value estimates
Format Output: Use Format function for currency display
Complete Schedules: Generate full schedules for planning
Compare Methods: Evaluate if straight-line is most appropriate
Tax Compliance: Verify method meets tax requirements
Regular Review: Update assumptions periodically

Function	Method	Depreciation Pattern	Best For
SLN	Straight-line	Constant each period	Uniform usage assets
DDB	Double-declining balance	Accelerated (higher early)	Tech, vehicles
SYD	Sum-of-years digits	Accelerated (graduated)	Equipment with declining efficiency

Platform Considerations

Available in VB6, VBA (all versions)
Part of financial functions library
Returns Double for precision
Consistent across platforms

Limitations

Assumes constant depreciation (may not reflect reality)
Doesn't account for accelerated wear
No adjustment for partial periods
Salvage value is an estimate
Doesn't consider tax implications directly
Can't handle mid-period asset purchases without adjustment

DDB: Returns depreciation using double-declining balance method
SYD: Returns depreciation using sum-of-years digits method
FV: Calculates future value (inverse concept)
PV: Calculates present value

SLN Function

Syntax

Parameters

Return Value

Remarks

Typical Uses

Basic Examples

Common Patterns

Pattern 1: CalculateBookValue

Pattern 2: GenerateDepreciationSchedule

Pattern 3: CompareDepreciationMethods

Pattern 4: CalculateTotalDepreciation

Pattern 5: ValidateDepreciationInputs

Pattern 6: CalculateMonthlyDepreciation

Pattern 7: CalculateDepreciationRate

Pattern 8: CalculateReplacementYear

Pattern 9: CalculateAccumulatedDepreciation

Pattern 10: FormatDepreciationReport