Question

​OpenSeas, Inc. is evaluating the purchase of a new cruise ship. The ship will cost $ 495 million and will operate for 20 years. OpenSeas expects annual cash flows from operating the ship to be $ 69.8 million and its cost of capital is 12.1 %. a. Prepare an NPV profile of the purchase. b. Identify the IRR on the graph. c. Should OpenSeas go ahead with the​ purchase? d. How far off could​ OpenSeas's cost of capital estimate be before your purchase decision would​ change? a. Prepare an NPV profile of the purchase. To plot the NPV​ profile, we compute the NPV of the project for various discount rates and plot the curve. The NPV for a discount rate of 2.0 % is ​$ nothing million.

Answer 1

We first chalk out the cash flows and then calculate NPV and IRR using 'NPV' and 'IRR' functions.

NPV = NPV(Discount rate,Cash flow column) - Initial investment

IRR= IRR( Cash flow column)

			NPV	IRR
Year	Cash flows	Discount rate ( Cost of capital)	NPV(Discount rate,$B$5:$B$24)+$B$4	IRR($B$4:$B$24)
0	-495000000	0%	$901,000,000.00	12.84%
1	69800000	1%	$764,579,597.05	12.84%
2	69800000	2%	$646,330,047.45	12.84%
3	69800000	3%	$543,447,745.26	12.84%
4	69800000	4%	$453,604,778.88	12.84%
5	69800000	5%	$374,862,281.91	12.84%
6	69800000	6%	$305,600,501.06	12.84%
7	69800000	7%	$244,462,194.34	12.84%
8	69800000	8%	$190,306,689.04	12.84%
9	69800000	9%	$142,172,487.70	12.84%
10	69800000	10%	$99,246,747.64	12.84%
11	69800000	11%	$60,840,302.59	12.84%
12	69800000	12%	$26,367,164.98	12.84%
13	69800000	13%	($4,672,339.85)	12.84%
14	69800000	14%	($32,705,487.50)	12.84%
15	69800000	15%	($58,098,663.13)	12.84%
16	69800000	16%	($81,166,905.21)	12.84%
17	69800000	17%	($102,181,839.93)	12.84%
18	69800000	18%	($121,378,294.50)	12.84%
19	69800000	19%	($138,959,822.92)	12.84%
20	69800000	20%	($155,103,334.60)	12.84%

a) NPV profile

b) IRR on the NPV profile

c) Yes, Open seas should go ahead with the purchase as the project has a positive NPV and has an IRR greater than the cost of capital.

d) Using the graph, find the discount rate at which NPV=0. This gives us Rate = 12.842%.

Hence OpenSeas's cost of capital could be at a maximum, lesser than 12.84% before your purchase decision would​ change.

e) Using the discount rate and NPV table, The NPV for a discount rate of 2.0 % is ​$646.33 million.