A deficiency of most spreadsheet programs is the difficulty in calculating straight line depreciation for a row or column of capital expenditures.

Attached is a function written in Visual Basic for Excel models that calculates depreciation over a variable time period for a row or column of capital expenditure.

The function input is the number of periods over which to depreciate the array, the proportion of depreciation to apply in the period of acquisition, and the row or column of capital expenditures.

To incorporate the function, you need to copy the module to the spreadsheet and reference it as per the example (note that you need to enable macros for the function to work). The function logic can be viewed by pressing ALT-F11, and is copied in a Acrobat file for reference below.

Click to download Excel file with Macro attached Click to view syntax and logic (Acrobat Reader required)

Feel free to copy and use the function, but acknowledgement and comments would be appreciated.