Question

Use an Excel spreadsheet to solve these problems. Also refer to the documents about investment criteria.

1. Find the Internal Rate of Return for the following three investments. Year Inv. 1 Inv. 2 Inv. 3 0 $(1,500) $(2,500) $(3,500) 1 $ 800 $ 1,100 $ 1,200 2 $ 700 $ 1,000 $ 1,200 3 $ 600 $ 900 $ 1,200 4 $ 500 $ 800 $ 1,200 2. Now find the net present value of each of these investments using the following costs of capital:
a. 16%
b. 18%
c. 14%
d. The IRR for Investment 1 e. The IRR for Investment
2. The IRR for Investment 3
3. Describe the relationship between the net present values and costs of capital for each of the investments, as well as between the costs of capital used and the IRR for each investment. For problems 4 through 8, use the Profitability Index equation, PI = Sum of the PV of cash flows divided by the initial investment.
4. An investment cost $9,000 and the sum of the pv of its cash flows is $11.750. What is the investment’s Profitability Index?
5. Another investment that cost $9,000 has a Profitability Index of 0.85. What is the sum of the pvs of this investment’s cash flows?
6. An investment has a Profitability Index of 1.00. If the sum of the pvs of this investment is $10,250, how much did it cost?
7. An investment cost $16,000 and has a Profitability Index of 1.10. What is the net present value for this investment? 8. Investment A cost $500,000 and has a Profitability Index of 1.25, while Investment B, which cost $1 million, has a Profitability Index of only 1.1
8. If you chose Investment A, however, how much would you be missing out on in terms of net present value?
9. An investment has two Internal Rates of Return, 15% and 28%. Describe how you would determine the range of costs of capital that would return a positive net present value for this investment. 10. Investment A and B cost the same, but Investment B’s IRR is 24% while Investment A’s is 18%. At a cost of capital of 15%, however, the net present value of the two projects is the same. What range of costs of capital would cause you to:
a. Choose Investment A
b. Choose Investment B 11.
You are choosing between two investments, X and Y, which cost the same but have different cash flows in each of their 5-year lives. All you know about these cas flows is the difference between X’s and Y’s cash flows in each of those 5 years. Your cost of capital is 16%. You only have enough funding for one of the investments, so how would you determine which one to choose?

Answer 1

Use Excel's IRR function to find the Internal Rate of Return and NPV function to calculate Net Present Values. The Profitability Index is calculated by the PV of cash flows divided by the initial investment, aiding in investment comparison and selection.

Step-by-step explanation:

To calculate the Internal Rate of Return (IRR) for the three investments using Excel, you can use the IRR function. Input each investment’s cash flows in a separate column and apply the function to that column. For calculating the net present value (NPV), use the NPV function with the respective cost of capital for each investment and add the initial investment manually since it’s not included by the function itself. The relationship between NPV and cost of capital is inverse; higher costs of capital will result in lower NPVs. To determine which investment to choose based on NPVs calculated using the given cost of capitals or the IRRs, select the one with the highest NPV.

For the Profitability Index (PI) questions, the PI is found by dividing the present value of future cash flows by the initial investment. To find specific values such as the total present value or initial investment when you have the other variables, manipulate the PI formula accordingly. For example, to find the sum of PVs from a given PI and investment cost or to calculate how much potential NPV is lost when choosing one investment over another, rearrange the formula to solve for the desired variable.

To deal with multiple IRRs and determine the range of costs of capital yielding a positive NPV, utilize graphical representation or iteration methods in Excel. Choose between two practically similar investments based on their IRRs and NPVs by evaluating the cost of capital in relation to these metrics, selecting the one that maximizes NPV at the existing cost of capital.