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.

dep4blog

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.

2017-07-27_1150

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

Thanks!

 

 

 

Advertisements

Annual compound interest monthly calculation

SI and CI are easy concept to learn. I m writing about them because someone asked me a question with little twist which I thought worth sharing

In simple interest calculation interest is charged only on the principal whereas in compound interest calculation the interest gets added with the principal and interest is charged on principal plus accumulated interest.

SI = PRT                               >> interest at particular time

SI = P x (1+RT)                    >> interest plus principal at particular time (Total Amount)

CI = P x (1+R/N) NT                   >> Total Amount

P = Principal

R = Annual rate of interest

T = Time

N = Compounding period vis Annual(1), Semi Annual(2), Quarterly(4), Monthly(12), Daily(365)

An Example

Amount = 10000

Annual Interest rate = 12%

Time = 5 yrs

Image

 

Now the twist question, at annual compounding what is the value after  69 months

No. of completed years = 5 (5 x 12 = 60)

No. of months remaining  = 9 (69 – 60)

Convert months into yearly fraction = 9/12 = 0.75

Therefore total no. of years = 5 + 0.75 = 5.75

CI = 10000 x (1 + 12%) 5.75 = 19,186.85

 

one more twist, for quarterly compounding what is the value for 71 months

one quarter has 3 months

No. of completed quarters = 23 (3 x 23 = 69)

No. of months remaining = 2 (71 – 69)

Convert months into quarterly fraction = 2/3 = 0.67

Therefore total no. of quarters = 23 + 0.67 = 23.67

CI = 10000 x (1 + 12%/4) 23.67  = 20,130.62