Use a Sensitivity Analysis to Adjust Discount Rate in NPV

Information presented below pertains to the cash flows of three mutually exclusive investment proposals and is also included in the file linked here.

Proposal A Proposal B Proposal C

Initial investment $$100,000$ $$100,000$ $$100,000$

Cash flow from operations

Year $160,00025,000110,000$

Year $240,00040,000-$

Year $335,00070,000-$

Disinvestment $---$

Life $($years$)331$

Discount rate $($for all proposals$)20\%$

a$.$ Prepare a table in Excel to compute the net present value for each proposal, A$,$ B and C$.$ In your formulas, reference the discount rate used for all of your calculations to a single cell. Hint: By referencing all of your calculations to a single cell, you can easily develop a sensitivity analysis by changing the discount rate in that cell and the output $($net present value$)$ will update automatically.

Note: Round your answers to the nearest whole dollar. Use a negative sign to indicate a cash outflow.

Proposal A Proposal B Proposal C

Initial investment Answer

Answer

Answer

$1$ Answer

Answer

Answer

$2$ Answer

Answer

Answer

$3$ Answer

Answer

Answer

Net present value

$0$

$0$

$0$

b$.$ Prepare a new table listing the discount rate from $5\%$ to $20\%$ along with the net present value for each proposal, A$,$ B$,$ and C$.$ Hint: Using the table from part a$,$ enter each percentage. Take the results and copy and paste values into the new table.

Note: Round your answers to the nearest whole dollar.

Net Present Value

Discount Rate Proposal A Proposal B Proposal C

$5\%$ Answer

Answer

Answer

$6\%$ Answer

Answer

Answer

$7\%$ Answer

Answer

Answer

$8\%$ Answer

Answer

Answer

$9\%$ Answer

Answer

Answer

$10\%$ Answer

Answer

Answer

$11\%$ Answer

Answer

Answer

$12\%$ Answer

Answer

Answer

$13\%$ Answer

Answer

Answer

$14\%$ Answer

Answer

Answer

$15\%$ Answer

Answer

Answer

$16\%$ Answer

Answer

Answer

$17\%$ Answer

Answer

Answer

$18\%$ Answer

Answer

Answer

$19\%$ Answer

Answer

Answer

$20\%$ Answer

Answer

Answer

c$.$ Prepare a line chart showing the net present value of proposals A$,$ B$,$ and C with discount rates ranging from $5\%$ to $20\%.$

d$.$ Using the chart in part c$,$ answer the following questions.

$1.$ Which proposal has the highest net present value for each interest rate in your chart?

Answer

$2.$ Which proposal has the lowest net present value for each interest rate in your chart?

Answer

until around Answer

where Answer

has a lower net present value.

$3.$ Using only the chart and no additional calculations, what is the approximate internal rate of return for each proposal?

Proposal A: Between Answer

and Answer

Proposal B: Approximately Answer

Proposal C: Approximately Answer

$4.$ Does the net present value increase or decrease as the interest rate changes? Why?

The net present value Answer

as the interest rate increases. As the net cash inflows are discounted at a higher rate of interest, the present value of those cash flows Answer

$,$ which causes the net present value to Answer

$.$

$5.$ How does this sensitivity analysis take into account the risk of the investment? Should each proposal be discounted at the same interest rate?

A proposal perceived as having a higher interest rate should be discounted at a Answer

interest rate. This means that each proposal should only be discounted at the same rate if the perceived risk level is Answer

$.$

$6.$ What's the highest discount rate that generates a net present value for all proposals that's not a negative amount?

Answer

$7.$ Based on the net present value, which proposal would you recommend, assuming that you must choose one?

Answer