Question

Caspian Sea Drinks is considering the production of a diet drink. The expansion of the plant and the purchase of the equipment necessary to produce the diet drink will cost $22.00 million. The plant and equipment will be depreciated over 10 years to a book value of $1.00 million, and sold for that amount in year 10. Net working capital will increase by $1.17 million at the beginning of the project and will be recovered at the end. The new diet drink will produce revenues of $8.59 million per year and cost $2.15 million per year over the 10-year life of the project. Marketing estimates 15.00% of the buyers of the diet drink will be people who will switch from the regular drink. The marginal tax rate is 23.00%. The WACC is 13.00%. Find the IRR (internal rate of return).

Can you please show how you find he answer even if it was done in excel. thanks!

Answer 1

Initial Cashflow in year 0, CF0 = cost of plant and equipment + increase in Net working capital

= - ($22 million+ $1.17 million) = - $23.17 million

Depreciation every year = ($21 million -$1 milln)/10 = $2.1 million

Overall Increase in operating income per year = $8.59 million -$2.15 million = $6.44 million

As 15% revenues are from buyers who switched, only 85% of operating income produced shoyld be accounted

So, cashflows per year are calculated as

Net increase in operating income per year =85% of $6.44 million = $5.474 million

Less Depreciation = $2.1 million

Net profit before taxes = $3.374 million

Less :Taxes @23% = $0.77602 million

Profit after tax = $2.59798 million

Add: Depreciation = $2.1 million

Cashflow per year (year 1-10) = $4.69798 million

Additional Cashflow in year 10 = salvage value + recovery of Net working capital

=$1 million + $1.17 million

=$2.17 million

The NPV (in million $) = -23.17+4.69798/0.13*(1-1/1.13^10)+2.17/1.13^10 = $2.96164 million

The IRR(r) is calculated as the discount rate for which NPV of cashflows = 0

-23.17+4.69798/r*(1-1/(1+r)^10)+2.17/(1+r)^10 = 0

Using hit and trial method

Putting r = 0.15 in the above equation, Left hand side of equation = 0.944465

Putting r = 0.17 in the above equation, Left hand side of equation = - 0.83253

Putting r = 0.16 in the above equation, Left hand side of equation = 0.0283

Putting r = 0.161 in the above equation, Left hand side of equation = -0.0602

So, IRR lies between 0.16 and 0.161, Using linear approximation method

IRR = 0.16+(0.0283-0)/(0.0283-(-0.0602))*(0.161-0.16) =0.16032

which is the correct IRR , . So IRR of the project = 16.03%