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:

• CAPEX

• Opening Book Value

• Depreciation

• Closing Book Value

• Revenue from sale of asset

• Expense from disposal costs

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

After consulting with several CFOs and professors of Finance, we decided to deviate from Excel's depreciation functions in the following ways:

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.

4. In the disposal period, the salvage value is added depreciation expense to reduce book value to zero. 

5. The salvage value is assumed to be the selling price of the asset and added to revenue.

6. Disposal costs are included as an option which can be netted with the assumed selling price to determine net profit or loss from sale of assets.

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. 

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

• SumDepreciateλ( )
  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λ(InitialValues, InServiceDates, LifeInYears, Timeline, [SalvageValues], [DisposalDates], [DisposalCosts], [Methods], [Factor])

InitialValues
(Required) The purchase price of each asset to depreciate

InServiceDates
(Required) The dates from which to start depreciating each asset.

LifeInYears
(Required) The number of years with which to depreciate each asset

Timeline
(Required) The model's timeline or a row of period start dates.

SalvageValues
(Optional) The book values of each asset after fully depreciated. The default is no value (0).

DisposalDates
(Optional) The dates on which the asset is removed from the books when salvage value is greater than nothing. The default is at end of life.

DisposalCosts
(Optional) The estimated costs for disposing each assets. The default is none.

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.

LabelDepreciateλ( )

Adds row labels to the result of Depreciateλ()

LabelDepreciateλ( AssetNames)

AssetNames
(Required) The names of each asset being depreciated