Depreciation Expense in a corporate model can be one of the trickiest issues from a mechanical and theoretical perspective. When new assets built have a different growth profile than the historic existing assets, it is easy to make errors in the modelling of depreciation expense. This page includes discussion and exercises on how to efficiently and accurately model depreciation expense in different types of models. The first issue addressed is computing depreciation on future capital expenditures when the depreciation rate is not the same over the lifetime of assets. This problem sometimes results in one of those diagonal matricies that you may have seen. I think it is much better to solve this depreciation problem with a user defined function. For corporate models, a second problem is when the depreciation rate is not constant for new assets, you should account for the vinatge depreciaton of each capital expenditure. You can use the depreciation on net plant, but this does not work if growth rates change. If you are more price, one of the most challenging aspects of corporate modelling is the representing retirements of existing assets because this requires gleaning historic information from financials.

A second problem with depreciation is the theory of depreciation as it applies to companies with lumpy assets. If assets age, the return on equity appears to be much higher after the asset ages. The return on equity is highest just before the next asset is added. The same problem occurs in terms of the P/E ratio. Just after a lumpy asset is completed, the income is low and the future cash flow is high. This implies a high P/E ratio. When the asset is old, the value goes down because of the pending capital expenditure. In addition the income is high. This makes the P/E low. Economic depreciation can resolve this issue. I am not suggesting to re-cast financial statements with economic depreciation but I do think that you should think about the theory. The second lesson set therefore works through economic depreciation.


Depreciation Lesson Set 1: Depreciation Vintage in Project and Corporate Finance Model

A classic problem in modelling is that when you are modelling future captial expenditures and the depreciation rate is not constant, you have to make your model remeber the timing of the capital expenditures in order to model the the prospecitve 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.

Videos associated with Depreciation Lesson Set 1: 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 fucntion that first works throught 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 maintanied to kepp track of the depreciation rate that is a function of the age and the depreciation amount that must sum accross different vintages.



Files Associated with Lesson Set 1: Depreciation Expense from Continuing Capital Expenditures and Changing Depreciation Rates






Depreciation Lesson Set 2: Projected Retirements and Gross Plant


This lesson set begins by working through a file with a bunch of mistakes as a case study. One of the big mistakes is to use gross plant in computing depreciation expense on the basis of gross assets without considering retirements. The correct depreciation should account for retirements on both new assets and existing assets.

Videos Associated with Lesson Set 2: Modelling Retirements for Existing Plant



This file below demonstrates how to simulate retirements on existing assets so that you can model the depreciation on existing assets separately from new assets. The growth rate in retirements can be established from the accumulated depreciation and the base can be established from the amount of retirements necessary to make the balance of accumulated depreciation equal the gross plant by the end of plant life. To do this I set up a sepate little page and then use the SOLVER function.

An effective way to deal with depreciation, is to first separate depreciation into depreciation related to new assets and existing assets. Depreciation on new assets can be computed using gross plant and reflecting retirements on the new assets. If the depreciation rate is not straight line, you can use a vintage depreciation function. The problem with existing depreciation is computing implied retirements. This can be accomplished by using the solver tool as shown in the videos and files below. The videos and files demonstrate how to make functions and the problems that can arise from incorrectly modelling depreciation.






This file includes functions to make a diagonal matrix that includes the effect of retirements and different types of depreciation.

Depreciation Lesson Set 3: Economic Depreciation Theory



This lesson set begins by working through a file with a bunch of mistakes as a case study. One of the big mistakes is to use gross plant in computing depreciation expense on the basis of gross assets without considering retirements. The correct depreciation should account for retirements on both new assets and existing assets.


Subject

Excel Exercise File

Video

Chapter Reference

Page Reference











Depreciation Errors from applying gross rates and net rates

Depreciation Exercise 1: Errors in Methods





21

Creating Depreciation Vintage Matrix without a function

Depreciation Exercise 2: Vintage Matrix

https://www.youtube.com/watch?v=-gBle2ERzGY



25

Creating Depreciation Vintage Matrix with Function

Depreciation Exercise 3: Matrix Matrix with Function







Creating Historic Growth Function

Depreciation Exercise 4: Historic Growth Function







Creating Base Retirement Function

Depreciation Exercise 5: Base Retirement Function







Implementing Depreciation Functions in Corporate Models

Depreciation Exercise 6: Depreciation Functions in Corporate Models







Implementing Depreciation in Functions in Project Finance

Depreciation Exercise 7: Depreciation Functions in Project Models







Projecting Deferred Taxes

Depreciation Exercise 8: Deferred Taxes







Projecting Deferred Taxes with Changing Growth

Depreciation Exercise 9: Deferred Taxes and Changing Growth







Comparison of Methods

Depreciation Exercise 10: Comprehensive Exrcise

https://www.youtube.com/watch?v=secSWQYuTiI





Computing Depreciation with Different Methods

Depreciation Exercise 10: Comprehensive Exrcise

https://www.youtube.com/watch?v=4gPt_D5N6KY





Depreciation Expense Calculations



https://www.youtube.com/watch?v=u9O9XanLQhY





Theoretical Depreciation Expenses









Example of Deprecaition Expense Errors









Depreciation Function with Changing Rate

Corporate and Project Model

https://www.youtube.com/watch?v=cZXwgLHqbTg





Existing Depreciation and Retirments

Kitty Hawk Model

https://www.youtube.com/watch?v=tyLTTmZWljY





Debt and Depreciation in Corporate Model

Kalitta Corporate Model

https://www.youtube.com/watch?v=tyLTTmZWljY





Existing Depreciation with Solver

Existing Depreciation

https://www.youtube.com/watch?v=XmkTCI8TkGw





Recasting Existing Depreciation with Economic Depreciation

Economic Depreciation with Function







Economic Depreciation on New Assets

Economic Depreciation on New Assets






…..
……………………………………………………………………………………………………………
….
……………………………………………………………………………………………………..

………………………………………………………………………………………..

………………………………
….
…………………………..







Depreciation Base Retirements and Goal Seek.JPGDepreciation Exercise 1.JPGDepreciation Exercise 2.JPGDepreciation Exercise 3.JPGDepreciation Exercise 4.JPGDepreciation Project Finance.JPGDepreciation Vintage.JPGDepreciation Excerpt.JPG