Using Excel (please show all your formulas, calculations and work) Calculate the internal rate of return,...

Transcribed Image Text:

Using Excel (please show all your formulas, calculations and work) Calculate the internal rate of return, net present value, payback period, profitability index and total increase in income after taxes for each Option. Assume a weighted average cost of capital of 10%. Option A Purchase of Race Car Additional income Additional expenses Amortization Increase or decrease in income Additional income Additional expenses Amortization Purchase of 350,000 Plane Increase or decrease in income Option C Additional income Additional expenses Amortization Purchase of 150,000 Boat Increase or decrease in income Beginning Investment 220,000 Option B Will be amortized over a 20 year timeline Year 1 Option D Purchase of Horses Additional income Additional expenses Amortization Beginning Investment Increase or decrease in income Beginning Investment Year 1 Beginning Investment 155,000 30,000 15,000 40,000 -25,000 43,750 13,125 8,750 -21,875 Year 1 21,500 5,625 22,500 -6,650 Year 1 111,250 5,562 Year 2 28,250 77,438 60,000 15,000 66,000 -21,000 Year 2 87,500 13,125 8,750 -65,625 Year 2 38,400 5,625 33,000 -225 Year 2 122,562 5,562 36,100 80,900 Year 3 100,000 15,000 62,000 23,000 Year 3 131,250 13,125 8,750 109,375 Year 3 56,150 5,625 31,500 19,025 Year 3 22,625 8,250 33,550 -19,750 Year 4 165,000 15,000 62,000 88,000 Year 4 196,875 13,125 8,750 175,000 Year 4 112,500 5,625 31,500 75,375 Year 4 28,250 10,940 33,550 -19,240 Year 5 200,000 15,000 62,000 123,000 Year 5 262,500 13,125 8,750 240,625 Year 5 84,375 5,625 31,500 47,250 Year 5 11,500 15,100 33,550 -37,150 Using Excel (please show all your formulas, calculations and work) Calculate the internal rate of return, net present value, payback period, profitability index and total increase in income after taxes for each Option. Assume a weighted average cost of capital of 10%. Option A Purchase of Race Car Additional income Additional expenses Amortization Increase or decrease in income Additional income Additional expenses Amortization Purchase of 350,000 Plane Increase or decrease in income Option C Additional income Additional expenses Amortization Purchase of 150,000 Boat Increase or decrease in income Beginning Investment 220,000 Option B Will be amortized over a 20 year timeline Year 1 Option D Purchase of Horses Additional income Additional expenses Amortization Beginning Investment Increase or decrease in income Beginning Investment Year 1 Beginning Investment 155,000 30,000 15,000 40,000 -25,000 43,750 13,125 8,750 -21,875 Year 1 21,500 5,625 22,500 -6,650 Year 1 111,250 5,562 Year 2 28,250 77,438 60,000 15,000 66,000 -21,000 Year 2 87,500 13,125 8,750 -65,625 Year 2 38,400 5,625 33,000 -225 Year 2 122,562 5,562 36,100 80,900 Year 3 100,000 15,000 62,000 23,000 Year 3 131,250 13,125 8,750 109,375 Year 3 56,150 5,625 31,500 19,025 Year 3 22,625 8,250 33,550 -19,750 Year 4 165,000 15,000 62,000 88,000 Year 4 196,875 13,125 8,750 175,000 Year 4 112,500 5,625 31,500 75,375 Year 4 28,250 10,940 33,550 -19,240 Year 5 200,000 15,000 62,000 123,000 Year 5 262,500 13,125 8,750 240,625 Year 5 84,375 5,625 31,500 47,250 Year 5 11,500 15,100 33,550 -37,150

Answer rating: 100% (QA)

To calculate the internal rate of return IRR net present value NPV payback period profitability inde... View the full answer