Depreciation Schedule

This function provides a complete depreciation schedule for all assets over the model's timeline. For each asset it includes separate rows for:

• Acquisition Cost

• Opening Book Value

• Less Depreciation

• Less Salvage Value

• Closing Book Value

• Net Disposal Sale/(Cost)

• Gain/(Loss)

Depreciation Methodology

We packed several of Excel's depreciation methods into this one function which are:

• SLN Straight Line Depreciation Daily

• SYD Sum of Year's Digits

• DB Declining Balance

• DDB Double Declining Balance

• VDB Variable Declining Balance

• MACRS Modified Accelerated Cost Recovery System

...and then modified them to behave properly in Financial Models:

1. The DB method (Declining Balance) works properly when there is no salvage value.

2. Monthly and quarterly depreciation always totals annual depreciation. 

3. In the last period, any amount over salvage is depreciated.

Depreciateλ() adjusts to our model's timeline, whether that be in years, months, or quarters. It also places the results with respect to the model timeline's start date, and the asset's in-service date so depreciation can start anywhere within the timeline, or even outside the timeline.

Individual Asset, Asset Classes, All Assets at Once

This function accommodates several approaches to asset depreciation.

1. We can depreciate each asset individually using the Depreciateλ() function for each asset.

2. We can aggregate assets by class and use the Depreciateλ() over the aggregated values as long as all assets share the same in-service date, life in years, and depreciation method.

3. We can place assets in a table and process the entire table, and thus all assets, with one Depreciateλ() function in one cell. Each asset can have its own in-service date, life in years, aquisition value, salvage value, disposal costs, and depreciation method. One function, in one cell for all assets over the model's entire timeline.

A Suite of Functions

The depreciation suite includes: 

• Depreciateλ( )
  Produces a dynamic array containing amounts by model period for CAPEX, depreciation, book value, sale-of-asset revenue, and cost-to-dispose. 

• DepreciateLabelsλ( )
  Adds labels to Depreciateλ( )'s results

• DepreciateSumsλ( )
  Totals depreciation, revenue and expense rows

• SumContainsλ( )
  When processing more than one asset, use this to create a single row of period totals for all CAPEX amounts, or all depreciation amounts, all book values, all revenue from sales values, and disposal cost values. These rows can then be added to finanical statements where needed.

Depreciateλ( )

Produces a depreciation schedule dynamic array. The array contains only values, no row labels. To Add row labels use LabelDepreciateλ().

Depreciateλ( Timeline, EndDates?, AssetCosts, InServiceDates, LifeInYears, [SalvageValues], [DisposalDates], [NetDisposals], [Methods], [Factors])

Timeline
(Required) The model's timeline.

EndDates?
(Optional) TRUE if timeline uses end dates, FALSE if start dates. Default = TRUE.

AssetCosts
(Required) Acquisition cost for each asset.

InServiceDates
(Required) When depreciation starts for each asset.

LifeInYears
(Required) Useful life, in years, for each asset.

SalvageValues
(Optional) Residual value at end of life. Default is no value (0).

DisposalDates
(Optional) When asset is removed. The default is at end of life.

NetDisposals
(Optional) The net of revenue from the sale of each assets less estimated costs for disposing of them. The default is the salvage value.

Methods 

(Optional) The desired depreciation method. The default value is straight line depreciation method. We can choose from any of these:

• SLN Straight Line Depreciation Daily

• SYD Sum of Year's Digits

• DB Declining Balance

• DDB Double Declining Balance

• VDB Variable Declining Balance

• MACRS Modified Accelerated Cost Recovery System

Factors
(Optional) The default is 2 and is only applicable to DDB and VDB methods.

DepreciateLabelsλ( )

Adds row labels to the result of Depreciateλ()

LabelDepreciateλ( AssetNames)

AssetNames
(Required) The names of each asset being depreciated