Today I got a mail from Modeloff for registration of their 2017 championship event, in addition, new past questions were uploaded. This blog refers to one of their past questions asked in 2016 championship event Round 2 Section 4 – Case Study – Maximize the Benefit. In the question, they essentially asked to calculate different depreciation method and recommend the most tax efficient tax method. The time awarded was 36 min. The question served me as a great refresher to different depreciation method calculation, although took much longer time than that was given. I thought sharing the solution would be great.
The depreciation methods in question were Declining balance (DBM), Straight line (SLM), Units of production (UOP), Sum of years (SOY). The excel functions like DB, SLN, SYD serve little purpose here as the calculation is far more complex.
A method for calculation of DBM, UOP & SOY was same as to calculate the yearly depreciation rate >> reverse the rates >> calculate depreciation. The reverse ticker method is used to reduce the calculation load when we have different depreciation rates each year coupled with CAPEX lasting number of years, otherwise, the calculation will be difficult to handle.
All the yearly depreciation rates are calculated first and then reversed using OFFSET function. The reversed rates are then multiplied with CAPEX using SUMPRODUCT.
Have a better understanding of formulas after looking at the worksheet linked. It takes a time to understand and even longer to master. But very helpful in building complex models.
In SLM the depreciation rate is constant for the given years. It is relatively easy to calculate, the challenge here is to remove the assets which are fully depreciated otherwise the depreciation will be inflated. The formula keeps accumulating the depreciation till the assets are fully depreciated, as the years goes beyond the life of asset it starts subtracting the fully depreciated asset depreciation from the accumulated depreciation. The formula again requires practice to master.
The depreciation tax shield is calculated by multiplying the tax rate and total yearly depreciation. NPV of tax shield is calculated using discount rate given. Higher the NPV more preferred the method
Please go through the file linked to have a better understanding of different depreciation method calculation. The question file is also inserted in the worksheet.
Link to the file : Depreciation_Maximize_Benefit