please show using excel and answer question 1.1

You are considering replacing a current machine with a new machine that has an 8-year life. The purchase price of the new machine is $635.000 and transportation/installation expenses will be $100,000. This new machine falls into the 5-year MACRS classification for depreciation purposes. At the end of that 8-year life of the new machine, it is expected to have a market value of $260,000. The current equipment has been in use for 7 years and has an expected remaining life of 8 years. Seven years ago, you purchased the equipment for a total of $485,000. This current equipment is being depreciated on a straight-line basis to an expected $110,000 salvage value for accounting purposes. You estimate the market value of the equipment to be $264,000 currently and $127,000 at the end of 8 years. You estimate that inventories will increase by $68,000 and accounts receivable will increase by $75.000 with the purchase of the new equipment. If you continue to operate the old machine, you estimate that you can produce and sell 124,000 units per year at a selling price of $6 per unit. Variable costs associated with running the old machine are $1.93 per unit, and fixed costs associated with the old machine are $118,500 annually. If you switch to the new machine, you anticipate that you can produce and sell 142,500 units at a selling price of S8 per unit. Variable costs associated with running the new machine are estimated to be $2.75 per unit, while estimated fixed costs are $130,425 annually. Your firm is financed with debt and common equity. Debt is comprised of a single issue of 2.000 bonds that are currently trading at a price of $885 each. The bonds were issued exactly two years ago today, each with a par value of $1,000, a coupon rate of 4%, and total maturity of 30 years. Interest coupons are paid on a semi-annual basis. For common equity, there are currently 123,000 shares outstanding, trading at a price of $41.50 each. Dividends are paid on an annual basis, and the last dividend paid was $1.70 per share. Dividends are expected to grow at a rate of 4% for the foreseeable future. You have chosen to use an historical market risk premium estimate of 6%. It is your firm's policy to use the current yield to maturity on 5-year T-bonds as your proxy for the nominal risk-free rate of return. Finally, you believe the estimated levered equity beta of your firm is the exactly the same as that of Caterpillar, Inc. (tic: CAT). You determine that the current market value of your firm's capital structure approximates the target capital structure and that the risk of the project under consideration is comparable to the overall risk level of the firm's current asset mix. Also, if there is more than one way to estimate a variable, it is your firm's policy to use the average of the estimations. Finally, your firm's marginal tax rate is 50%. Depreciation for Tax Purposes: Modified Accelerated Cost Recovery System (MACRS) Ownership Year 5-year 20.0% 32.0% 19.0% 11.5% 11.5% 6.0% Question 1.1 (60 points): Should you replace the old piece of equipment with the new one under consideration? Substantiate your answer using NPV, IRR, and MIRR. You are considering replacing a current machine with a new machine that has an 8-year life. The purchase price of the new machine is $635.000 and transportation/installation expenses will be $100,000. This new machine falls into the 5-year MACRS classification for depreciation purposes. At the end of that 8-year life of the new machine, it is expected to have a market value of $260,000. The current equipment has been in use for 7 years and has an expected remaining life of 8 years. Seven years ago, you purchased the equipment for a total of $485,000. This current equipment is being depreciated on a straight-line basis to an expected $110,000 salvage value for accounting purposes. You estimate the market value of the equipment to be $264,000 currently and $127,000 at the end of 8 years. You estimate that inventories will increase by $68,000 and accounts receivable will increase by $75.000 with the purchase of the new equipment. If you continue to operate the old machine, you estimate that you can produce and sell 124,000 units per year at a selling price of $6 per unit. Variable costs associated with running the old machine are $1.93 per unit, and fixed costs associated with the old machine are $118,500 annually. If you switch to the new machine, you anticipate that you can produce and sell 142,500 units at a selling price of S8 per unit. Variable costs associated with running the new machine are estimated to be $2.75 per unit, while estimated fixed costs are $130,425 annually. Your firm is financed with debt and common equity. Debt is comprised of a single issue of 2.000 bonds that are currently trading at a price of $885 each. The bonds were issued exactly two years ago today, each with a par value of $1,000, a coupon rate of 4%, and total maturity of 30 years. Interest coupons are paid on a semi-annual basis. For common equity, there are currently 123,000 shares outstanding, trading at a price of $41.50 each. Dividends are paid on an annual basis, and the last dividend paid was $1.70 per share. Dividends are expected to grow at a rate of 4% for the foreseeable future. You have chosen to use an historical market risk premium estimate of 6%. It is your firm's policy to use the current yield to maturity on 5-year T-bonds as your proxy for the nominal risk-free rate of return. Finally, you believe the estimated levered equity beta of your firm is the exactly the same as that of Caterpillar, Inc. (tic: CAT). You determine that the current market value of your firm's capital structure approximates the target capital structure and that the risk of the project under consideration is comparable to the overall risk level of the firm's current asset mix. Also, if there is more than one way to estimate a variable, it is your firm's policy to use the average of the estimations. Finally, your firm's marginal tax rate is 50%. Depreciation for Tax Purposes: Modified Accelerated Cost Recovery System (MACRS) Ownership Year 5-year 20.0% 32.0% 19.0% 11.5% 11.5% 6.0% Question 1.1 (60 points): Should you replace the old piece of equipment with the new one under consideration? Substantiate your answer using NPV, IRR, and MIRR