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.
General Issues in Modelling Depreciation
The video below demonstrates how to deal with depreciation and taxes in a leveraged buyout model.
.
.
Vintage Depreciation
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
The function for a depreciation vintage is demonstrated below. You need to make two loops. First loop around each capital expenditure that is input by year. Then, once the loop for the capital expenditure is made, within the loop, make a loop around the lifetime of each asset. The depreciation moves forward from the date at which asset is placed in service. Note in the excerpt below the model year is the first loop and then within the loop is another loop for the vintage. As long as the computed age is positive, the depreciation for the incremented by the cap exp for the period.