Depreciation calculation – Maximize the benefit

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






NPV, IRR and their variants

Net Present Value (NPV), as the name suggest it is the sum of present value of outflow  and inflow cash discounted at a rate. Lets assume some Cashflow (CF) and find out the NPV using discount rate formula


At a discount rate of 8%, Present Value (PV) factor is calculated and multiplied with CF. The the discounted CF gives the NPV. The same can be achieved by using NPV formula as seen in the screen shot.

Internal Rate of Return (IRR) is the rate at which NPV is zero. If we separately find out the sum of NPV of outflow and inflow using a rate, we will get a value. Using Goal seek function of excel, we set the target value of sum to zero by changing the rate cell, we will get IRR in the rate cell. The same result can be achieved by using IRR function.


Now that we have learned the basics lets move forward. The NPV and IRR function assume the CF to happen annually, which may not be the case. For that we have functions like XNPV and XIRR at our disposal. Lets assume some random dates and find out the XNPV and XIRR.


We can observe the difference between two types of function.

People working in Project finance related assignment know that IRR is an optimistic number because the intermediate CF are reinvested at IRR, which may not represent the realty and hence making IRR a highly optimistic or inflated rate of return. To counter this we use Modified IRR (MIRR). MIRR considers both the cost of the investment and the interest received on reinvestment of cash.

Finance rate is the rate at which cash outflow is funded, usually cost of capital. Reinvestment rate is the rate at which intermediate CF are assumed invested, usually a mutually agreed conservative rate. Therefore MIRR represents a relatively fairer rate of return on the investment. Lets again assume some numbers and verify this.


We can see that MIRR is lower than IRR, when a conservative reinvestment rate is used. MIRR equals IRR when FR & RR equals IRR, which cannot be true in most cases.

But wait, again MIRR assumes annual cashflow, what if CF are not annual. And there is no direct function for it!

This requires a deeper understanding of how MIRR is calculated. Lets recalculate the MIRR in above example.


As you can see in above screen shot that outflow and inflow are separated. For outflow the NPV is found using Finance rate. For Inflow the future value is found using formula at time period 5. The MIRR is calculated using IRR function with NPV of outflow and total future value of inflow.

We have to use above logic to calculate MIRR with non periodic CF, lets call it Non Perodic MIRR (NP MIRR).

We calculate the present value of outflow using XNPV and future value of inflow using yearfrac function and formula. After getting the value we simply calculate the XIRR of CF, which gives us the NP MIRR. We can see that MIRR and NP MIRR are different.

Link to the file :


Caution : Averages & Percentages

Averages and percentages should be used with caution. Both numbers are technically correct but should be used viewed closely with the underlying data to get a better perspective.

The Problem with Averages is the Outliners. The outliners can throw the average number completely out of its sense and you may end up making wrong decision.

Imagine someone 6 ft tall who do not know swimming wants to cross a river. He will not dare to cross a river with an average depth of 7 ft of the fear of drowning .


However if he looks closely at the depth of river at different points he will finds that at many points the the depth is 5 ft only where he can keep his head above water or atleast nose above water. So whats stopping him to cross the river : few outliners. At only three point out of ten the depth is more than his height. Few outliners have increased the average depth hence deterred our man to cross the river.

The problem with Percentage is Base effect. When the base the of numbers you are comparing is different, the percentage change should be looked in right context.

Imagine two companies A & B with different sales figure in year 1 & 2.


Company A sales increase by 20% compared to 10% of Company B. One might be prompt to think that A did better than B. However if you look closely the absolute increase in Sales of B is 5 times (100/20) than that of A. This distortion is because of base effect, the year 1 sales of A is just 10% of B. Because the base is small for A, a small increase may look bigger in terms of percentage.

DCF Valuation

In the financial modeling series we have covered term loan schedule, depreciation schedule, tax schedule and an integrated financial model.This is probably the last link in the financial modeling series.

Here we have valued the company using the most widely used valuation technique called Discounted Cash flow (DCF) valuation method. We arrive at Free cash flow of firm (FCFF), discount it by Weighted average cost of capital (WACC) to present value to find the Enterprise value (EV) and then subtract net debt to get the Equity Value of the company.


I follow Prof. Damodaran of Stern NYU for valuation related queries.

link to file


Tax Schedule

In the financial modeling series we covered term loan schedule followed by depreciation schedule and then financial model. We did not cover tax schedule in detail. So here it is, it talks about the current tax, minimum alternative tax (MAT) , deferred tax and MAT credit.

The tax modeling is complex element of financial model and it will require practice on your part.

The changes done in earlier financial model are highlighted in green color


the link to file :

Here is the link I referred for completing the tax schedule, it explains the concept in detail