Question

OpenSeas, Inc. is evaluating the purchase of a new cruise ship. The ship would cost $499 million, but would operate for 20 years. OpenSeas expects annual cash flows from operating the ship to be $69.1 million (at the end of each year) and its cost of capital is 12.0% a. Prepare an NPV profile of the purchase using discount rates of 2.0%, 11.5% and 17.0%. b. Identify the IRR on a graph. c. Is the purchase attractive based on these estimates? d. How far off could OpenSeas' cost of capital estimate be before your purchase decision would change? (NOTE: Subtract the discount rate from the actual IRR. Use Excel to compute the actual IRR.)

Answer 1

NPV at 12% of cost of capital is calculated below:

Year	CF	Discount Factor		Discounted CF
0	$ -499.00	1/(1+0.125449584244341)^0=	1	1*-499=	$ -499.00
1	$      69.10	1/(1+0.125449584244341)^1=	0.888533804	0.888533803734468*69.1=	$      61.40
2	$      69.10	1/(1+0.125449584244341)^2=	0.78949232	0.789492320378842*69.1=	$      54.55
3	$      69.10	1/(1+0.125449584244341)^3=	0.701490614	0.701490614445364*69.1=	$      48.47
4	$      69.10	1/(1+0.125449584244341)^4=	0.623298124	0.623298123937168*69.1=	$      43.07
5	$      69.10	1/(1+0.125449584244341)^5=	0.553821453	0.55382145292245*69.1=	$      38.27
6	$      69.10	1/(1+0.125449584244341)^6=	0.492089082	0.492089082154934*69.1=	$      34.00
7	$      69.10	1/(1+0.125449584244341)^7=	0.437237784	0.437237783943326*69.1=	$      30.21
8	$      69.10	1/(1+0.125449584244341)^8=	0.388500551	0.388500551303593*69.1=	$      26.85
9	$      69.10	1/(1+0.125449584244341)^9=	0.345195873	0.345195872602719*69.1=	$      23.85
10	$      69.10	1/(1+0.125449584244341)^10=	0.306718202	0.306718201717133*69.1=	$      21.19
11	$      69.10	1/(1+0.125449584244341)^11=	0.27252949	0.27252949044632*69.1=	$      18.83
12	$      69.10	1/(1+0.125449584244341)^12=	0.242151665	0.242151664776085*69.1=	$      16.73
13	$      69.10	1/(1+0.125449584244341)^13=	0.21515994	0.215159939784129*69.1=	$      14.87
14	$      69.10	1/(1+0.125449584244341)^14=	0.19117688	0.191176879707671*69.1=	$      13.21
15	$      69.10	1/(1+0.125449584244341)^15=	0.16986712	0.169867120112744*69.1=	$      11.74
16	$      69.10	1/(1+0.125449584244341)^16=	0.150932678	0.150932678363196*69.1=	$      10.43
17	$      69.10	1/(1+0.125449584244341)^17=	0.134108787	0.134108786813881*69.1=	$        9.27
18	$      69.10	1/(1+0.125449584244341)^18=	0.11916019	0.119160190461953*69.1=	$        8.23
19	$      69.10	1/(1+0.125449584244341)^19=	0.105877857	0.105877857284883*69.1=	$        7.32
20	$      69.10	1/(1+0.125449584244341)^20=	0.094076055	0.094076055264592*69.1=	$        6.50
				NPV = Sum of all Discounted CF	$        0.00

Similarly we calculate the NPV at the required rates:

Cost of capital	NPV
2.00%	630.88
11.50%	33.75
17.00%	-110.12

NPV Profile graph is below:

NPV Profile 0 -100.00% 3.00% 6.00% 1 9.00% 12.00% 15.00% 18.00% -200

Part b IRR is where the NPV = 0 so where the NPV profile intersects the x-axis

The IRR is a little over 12% and using the excel's goal seek function we can say it is 12.54% rounded to 2 decimal places

Year	CF	Discount Factor		Discounted CF
0	$ -499.00	1/(1+0.125449898071482)^0=	1	1*-499=	$ -499.00
1	$      69.10	1/(1+0.125449898071482)^1=	0.888533556	0.888533555970419*69.1=	$      61.40
2	$      69.10	1/(1+0.125449898071482)^2=	0.78949188	0.789491880085438*69.1=	$      54.55
3	$      69.10	1/(1+0.125449898071482)^3=	0.701490028	0.701490027622086*69.1=	$      48.47
4	$      69.10	1/(1+0.125449898071482)^4=	0.623297429	0.623297428720839*69.1=	$      43.07
5	$      69.10	1/(1+0.125449898071482)^5=	0.553820681	0.553820680768546*69.1=	$      38.27
6	$      69.10	1/(1+0.125449898071482)^6=	0.492088259	0.492088258853235*69.1=	$      34.00
7	$      69.10	1/(1+0.125449898071482)^7=	0.43723693	0.437236930490157*69.1=	$      30.21
8	$      69.10	1/(1+0.125449898071482)^8=	0.388499685	0.38849968465001*69.1=	$      26.85
9	$      69.10	1/(1+0.125449898071482)^9=	0.345195006	0.34519500629546*69.1=	$      23.85
10	$      69.10	1/(1+0.125449898071482)^10=	0.306717346	0.306717346446936*69.1=	$      21.19
11	$      69.10	1/(1+0.125449898071482)^11=	0.272528655	0.272528654516307*69.1=	$      18.83
12	$      69.10	1/(1+0.125449898071482)^12=	0.242150855	0.242150854501208*69.1=	$      16.73
13	$      69.10	1/(1+0.125449898071482)^13=	0.21515916	0.215159159831234*69.1=	$      14.87
14	$      69.10	1/(1+0.125449898071482)^14=	0.191176133	0.191176133384454*69.1=	$      13.21
15	$      69.10	1/(1+0.125449898071482)^15=	0.16986641	0.169866409612764*69.1=	$      11.74
16	$      69.10	1/(1+0.125449898071482)^16=	0.150932005	0.150932004973157*69.1=	$      10.43
17	$      69.10	1/(1+0.125449898071482)^17=	0.134108151	0.134108151088544*69.1=	$        9.27
18	$      69.10	1/(1+0.125449898071482)^18=	0.119159592	0.119159592371323*69.1=	$        8.23
19	$      69.10	1/(1+0.125449898071482)^19=	0.105877296	0.105877296337677*69.1=	$        7.32
20	$      69.10	1/(1+0.125449898071482)^20=	0.094075531	0.0940755306114499*69.1=	$        6.50
				NPV = Sum of all Discounted CF	$       -0.00

Part c) Purchase is attractive when the NPV > 0 so that is at 2% and 11.5% but not at 17%

Part d) As IRR is the rate where NPV = 0 so that is how far we will before we can change the decision. So that is 12.54% that is 0.54% from the cost of capital of 12%