XYZ Ltd is planning to invest in a project which costs in Year 0 a capital investment of Rs 80 crores. It has made following projections. Its expected sales & EBITDA forecast is as follows:
Year Sales EBITDA %
1 Rs 10 cr 0%
2 Rs 20 cr 10%
3 Rs 50 cr 15%
4 Rs 100cr 20%
5 Rs 120cr 22%
6 Rs 150cr 22%
7 Rs 150cr 22%
Its annual depreciation is Rs 2 cr, Its annual interest costs are Rs 1 cr, tax rate is 34% , Working capital requirements are 15% of sales. Annual capital expenditure requirements after initial capital investment in Year 0 are Year 1 (Rs 5 cr), Year 2 (Rs 3 cr) and there after Rs 2 crores annually.
In year 7 business will have a terminal value of Rs 200 crores i.e. in 7th year at the end of project, the project residual things ie assets and working capital etc can be sold for Rs 200 crores.
For XYZ Ltd cost of capital or hurdle rate assume Debt, Equity 1:1, pre tax cost of debt is 14%, cost of equity is 25%.
Find NPV, IRR and Payback period and give your conclusion if XYZ Ltd should do the project.
Note: Please show all the calculations & formulas in excel sheet.