Imagine you are working as an investment analyst for ABC Company, you are given 100,000$ to allocate
Question:
Imagine you are working as an investment analyst for ABC Company, you are given 100,000$ to allocate for 5 different investment opportunities. You may select any combination of investments if the sum of initial cash outlays are equal to $100,000. Use NPV analysis in Excel to determine which out of these 5 Investments (or combination of) will be the most profitable.
Provide an answer to the following three questions in an Excel workbook.
1) If this $100,000 of capital is financed in the way shown below; calculate the WACC for this amount of capital. (corporate tax rate of 20%).
Long term Loan | Short term Loan | Mortgage Loan | Equity |
15,000$ @ 5% | 5,000$ @ 8.5% | 60,000$ @ 3% | 20,000$ @ 7% |
2) Find out with single investment or combination of investments yields the highest NPV. Use Excel to calculate each --> You will be graded on your use of formulas in excel.
3) For each Investment option calculate the IRR (Internal Rate of Return).
Option 1
A $25,000 initial investment which provides cash flows in the following way:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
8,000$ | 9,000$ | 11,000$ | $12,000 | $8,500 |
Option 2
An annuity which provides payments of 7000$ at the end of year for 15 years. The initial investment is $75,000.
Option 3
A $50,000 initial investment that provides cash flows in the following way:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 |
2,000 | $4,000 | $8,000 | $10,000 | $12,000 | $14,000 | $16,000 |
Option 4
A $50,000 initial investment that provides cash flows in the following way:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 |
25,000 | $4,000 | $8,000 | $10,000 | $12,000 | $1,000 | $1,000 |
Option 5
A $100,000 Invesment which provides 5000$ per year for the first 5 years and then 10,000$ per year for an additional 9 years.
Please read the following case study carefully.
Imagine you are working as an investment analyst for ABC Company, you are given 100,000$ to allocate for 5 different investment opportunities. You may select any combination of investments if the sum of initial cash outlays are equal to $100,000. Use NPV analysis in Excel to determine which out of these 5 Investments (or combination of) will be the most profitable.
Provide an answer to the following three questions in an Excel workbook.
1) If this $100,000 of capital is financed in the way shown below; calculate the WACC for this amount of capital. (corporate tax rate of 20%).
Long term Loan | Short term Loan | Mortgage Loan | Equity |
15,000$ @ 5% | 5,000$ @ 8.5% | 60,000$ @ 3% | 20,000$ @ 7% |
2) Find out with single investment or combination of investments yields the highest NPV. Use Excel to calculate each --> You will be graded on your use of formulas in excel.
3) For each Investment option calculate the IRR (Internal Rate of Return).
Option 1
A $25,000 initial investment which provides cash flows in the following way:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
8,000$ | 9,000$ | 11,000$ | $12,000 | $8,500 |
Option 2
An annuity which provides payments of 7000$ at the end of year for 15 years. The initial investment is $75,000.
Option 3
A $50,000 initial investment that provides cash flows in the following way:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 |
2,000 | $4,000 | $8,000 | $10,000 | $12,000 | $14,000 | $16,000 |
Option 4
A $50,000 initial investment that provides cash flows in the following way:
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 |
25,000 | $4,000 | $8,000 | $10,000 | $12,000 | $1,000 | $1,000 |
Option 5
A $100,000 Invesment which provides 5000$ per year for the first 5 years and then 10,000$ per year for an additional 9 years.
Fundamentals of corporate finance
ISBN: 978-0470876442
2nd Edition
Authors: Robert Parrino, David S. Kidwell, Thomas W. Bates