To please be done in excel: Your company is considering whether to invest in a n
ID: 2618260 • Letter: T
Question
To please be done in excel:
Your company is considering whether to invest in a new machine that costs $6m but will save the company $2.5m per year for the three years of its expected life. It will require an additional investment in inventory of $200 000. The marginal tax rate for companies 30% and depreciation for tax purposes is calculated on a straight line basis over three years. The machinery has an expected salvage value of $500 000. To keep the calculations simple, you decide to ignore inflation and use a real discount rate of 10%. Show that the machine project's NPV, IRR and Payback Periods are $83 621, 10.74% and 2.51 years respectively.
Explanation / Answer
Please note: I think that the above given answers for NPV and IRR are incorrect except for payback period,so I have calculated the same and arrived at different answers which have been presented below:
Initial investment=machine+inventory=$(60,00,000+2,00,000)=$62,00,000
Pre-tax savings=$25,00,000
Tax rate=30%
Post tax savings=$25,00,000(1-0.3)=$17,50,000
depreciation on machine=$60,00,000/3=$20,00,000
tax shield on depreciation=$6,00,000
Expected salvage value=$5,00,000
Expected post tax salvage value=$5,00,000(1-0.3)=$3,50,000
Investment in inventory will be recovered at end of third year so it is an inflow.
Accordingly the cash flows for each period are as follows:
Year 0=-$(60,00,000+2,00,000)=-$62,00,000
Year 1 and 2=$(17,50,000+6,00,000)=$23,50,000
Year 3=$(17,50,000+6,00,000+3,50,000+2,00,000)=$29,00,000
Accordingly, NPV, IRR and Payback Period are calculated as follows:
Notes:
Payback=2+(0+15,00,000)/(14,00,000+15,00,000)
=2+15,00,000/29,00,000
=2+0.517
=2.517 years
1 2 3 4=2*3 5 year cash flow($) df @ 10%($) pv cumulative cash flow($) 0 -6200000 1.0000 -6200000 -6200000 1 2350000 0.9091 2136364 -3850000 2 2350000 0.8264 1942149 -1500000 3 2900000 0.7513 2178813 1400000 NPV 57325 IRR 10.51% payback 2.517Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.