Question

	Annual cash flows:
	Year 0	$(104,000,000)
	Year 1	$250,000,000
	Year 2	$(150,000,000)
	Required return	16%
	Output area:
2)	NPV	$42,806.18
	Accept/Reject	Accept
3)	IRR	15.38%
		25.00%
6)	Required return @ Maximum NPV
	Maximum NPV

6. What discount rate results in the maximum NPV for this project? What is that maximum NPV? Write a note to your client (or an old-school boss), who has little knowledge about the “Solver”, explaining what parameters you chose as inputs in the solver and what you asked the solver to do. Be sure to refer to the cell ID (e.g. cell D52) where appropriate so your client/boss can follow what you are talking about.

Answer 1

Discount rate of 20% results in maximum NPV.

We used the Cell C20 to enter the Required return in Excel. The NPV formula was enetered in cell C22. Go to Data tab--> Solver and select maximize function.

Set Objective C22

By changing variable cell C20

Select Solving method GRG Nonlinear

By Solving for it, the Required return is provided and the Maximum NPV is also returned as $166,667

NPV formula=

=NPV(C16,C5:C6)+C4