A classic problem in modelling is that when you are modelling future capital expenditures and the depreciation rate is not constant, you have to make your model remember the timing of the capital expenditures in order to model the prospective depreciation. This can be done with a matrix where you remember the year of the capital expenditure on the vertical side of the table and the year of the model on the top of the table. Then you can compute the age of each capital expenditure vintage and use the LOOKUP function together with the computed age to derive the correct depreciation. A better solution to this is to create a user defined function. This approach and using a much simpler approach when there is straight line depreciation are explained in this lesson set.

The set of videos first address how to compute prospective depreciation in the case of straight line depreciation rates. In this case without growth, the problem is demonstrated to be an easy one. In the case of growth of capital expenditures, the use of the OFFSET function is demonstrated for straight line depreciation. The second file demonstrates how the problem gets messy when there is a changing depreciation rate. The third video explains how do create a function that first works through the capital expenditure array in a loop and then works through the projection model in a second loop. The age of each vintage is computed and a variable with two dimensions is maintained to keep track of the depreciation rate that is a function of the age and the depreciation amount that must sum across different vintages.

Depreciation Expense from Continuing Capital Expenditures and Changing Depreciation Rates