In: Finance
Silvana Zhang of Sajjad Jafri & Geopeng Li Limited is considering purchasing a new widget making machine. She would like to know the maximum price she should pay for the new machine.
1. The new machine will replace the existing machine, which has a current market value of $1,000,000.
2. New machine will reduce before tax operating cost by $300,000 per year for 15 years.
3. The old machine is expected to last for another 10 years and will have a salvage value of $100,000 at the end of 10 years.
4. The new machine is expected to last for 15 years and will have a salvage value of $200,000. CCA rate is 20%, tax rate is 30%, and the required rate of return is 15 %
Based on the given data, pls find below workings, steps and Answer;
- Step 1, the NPV is calculated using the investment amount of $ 1 as Cost of New Machine; The current market value of old machine $ 1000000 is considered as an opportunity benefit in the calculations;
-Step 2, Once the NPV is calculated, using GOAL SEEK option in Excel the value of New Machine is determined by reducing the value of NPV to 0; This is considered as the maximum value of investment in to cost of new machine. Maximum cost of New Machine could be $ 2314870
As you may observe, the NPV is Zero , with the cost of new machine at $ 2314870
Computation of NPV:
Computation of Net Present Value (NPV) based on the Discounted Cash flows; The Discounting factor is computed based on the formula: For year 0, the discounting factor is 1; For Year 1, it is computed as = Year 0 factor /(1+discounting factor%) ; Year 2 = Year 1 factor/(1+discounting factor %) and so on;
Next, the cashflows need to be multiplied with the respective years' discounting factor, to arrive at the discounting cash flows;
The total of all the discounted cash flows is equal to its respective Project NPV of the Cash Flows;
Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Terminal -1 Currency in $ Project Cost of New Machine Salvage Value - New Machine Book Value Salvage Value - Old Machine 2,00,000 0 10,00,000 3,00,000 3,00,000 0 0 Gross Profit Depreciation EBIT Tax @ 30% Net Income 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 3,00,000 0 3,00,000 -90,000 2,10,000 2,00,000 -60,000 1,40,000 9,99,999 1 Free Cash Flows (Net Income + Depreciation) Discounting Factor Discounted Cash Flow Cumulative Discounted Cash Flow Discounting Factor Net Present Value 2,10,000 0.8696 1,82,609 11,82,608 2,10,000 0.7561 1,58,790 13,41,398 2,10,000 0.6575 1,38,078 14,79,476 2,10,000 0.5718 1,20,068 15,99,545 2,10,000 0.4972 1,04,407 17,03,952 2,10,000 0.4323 90,789 17,94,741 2,10,000 0.3759 78,947 18,73,687 2,10,000 0.3269 68,649 19,42,337 2,10,000 0.2843 59,695 20,02,032 2,10,000 2,10,000 2,10,000 2,10,000 2,10,000 2,10,000 1,40,000 0.2472 0.2149 0.1869 0.1625 0.1413 0.1229 0.1069 51,909 45,138 39,251 34,131 29,679 25,808 14,961 20,53,941 20,99,079 21,38,329 21,72,460 22,02,139 22,27,947 22,42,908 9,99,999 9,99,999 15% 22,42,908 10% 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # Depreciation Applied CCA 30% Class 10 for depreciating the asset #: Net Value of Asset GOAL SEEK: NPV Cost of New Machine 0 -26,72,304
Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Terminal -26,72,304 Currency in $ Project Cost of New Machine Salvage Value - New Machine Book Value Salvage Value - Old Machine 2,00,000 1,05,776 10,00,000 Gross Profit Depreciation EBIT Tax @ 30% Net Income 3,00,000 -2,67,230 32,770 -9,831 22,939 3,00,000 -4,81,015 -1,81,015 54,304 -1,26,710 3,00,000 -3,84,812 -84,812 25,444 -59,368 3,00,000 -3,07,849 -7,849 2,355 -5,495 3,00,000 -2,46,280 53,720 -16,1161 37,604 3,00,000 -1,97,024 1,02,976 -30,893 72,083 3,00,000 -1,57,619 1,42,381 -42,714 99,667 3,00,000 -1,26,095 1,73,905 -52,171 1,21,733 3,00,000 -1,00,876 1,99,124 -59,737 1,39,387 3,00,000 -80,701 2,19,299 -65,790 1,53,509 3,00,000 -64,561 2,35,439 -70,632 1,64,808 3,00,000 -51,649 2,48,351 -74,505 1,73,846 3,00,000 -41,319 2,58,681 -77,604 1,81,077 3,00,000 -33,055 2,66,945 -80,0831 1,86,861 3,00,000 -26,444 2,73,556 -82,067 1,91,489 94,224 -28,267 65,957 Free Cash Flows (Net Income + Depreciation) Discounting Factor Discounted Cash Flow Cumulative Discounted Cash Flow Discounting Factor Net Present Value -16,72,304 1 -16,72,304 -16,72,304 15% 2,90,169 0.8696 2,52,321 -14,19,983 3,54,304 0.7561 2,67,905 -11,52,078 3,25,444 0.6575 2,13,984 -9,38,094 3,02,355 0.5718 1,72,872 -7,65,221 2,83,884 0.4972 1,41,140 -6,24,081 2,69,107 0.4323 1,16,342 -5,07,738 2,57,286 0.3759 96,723 -4,11,015 2,47,829 0.3269 81,016 -3,30,000 2,40,263 0.2843 68,298 -2,61,702 2,34,210 0.2472 57,893 -2,03,809 2,29,368 0.2149 49,301 -1,54,508 2,25,495 0.1869 42,147 -1,12,361 2,22,396 0.1625 36,146 -76,215 2,19,917 0.1413 31,081 -45,135 2,17,933 0.1229 26,783 -18,352 1,71,733 0.1069 18,352 0 0 10% 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # 20% on # Depreciation Applied CCA 30% Class 10 for depreciating the asset #: Net Value of Asset GOAL SEEK: NPV Cost of New Machine 0 -26,72,304