Question

home / study / business / finance / finance questions and answers / margaritaville hotel properties is opening a new beach resort in tybee island, ga at a cost ... Question: Margaritaville Hotel Properties is opening a new beach resort in Tybee Island, GA at a cost of $2... Margaritaville Hotel Properties is opening a new beach resort in Tybee Island, GA at a cost of $250 Million in year 0. The hotel is expected to operate for 20 years and at the end, be sold for approximately $500 Million in year 20. As an investment the hotel expected to earn $27 Million per year (including $20 Million in year 20). With a discount rate of 8% and reinvestment rate of 8%, analyze the projects feasibility using: Payback  Discounted Payback  NPV  IRR  Profitability Index  MIRR It turns out, you forgot that the franchise company that licenses the Margaritaville name will require the hotel owners to renovate the hotel property in year 10. This will result in significant room closures and a significant capital investment. Therefore, cash flows in that year 10 are expected to be -$5 Million. Using MIRR, what did you get for a rate of return?

Answer 1

Part a)

Payback

The payback period for the project is calculated as below:

Year	Cash Flow	Cumulative Cash Flow
0	-250	-250
1	27	-223
2	27	-196
3	27	-169
4	27	-142
5	27	-115
6	27	-88
7	27	-61
8	27	-34
9	27	-7
10	27	20
11	27	47
12	27	74
13	27	101
14	27	128
15	27
16	27
17	27
18	27
19	27
20	520

As can be seen from the above table that cumulative cash flows turn from negative to positive between Year 9 and Year 10. Therefore, the payback period will lie between these 2 years. The formula for calculating payback period is derived as below:

Payback Period = Years Upto which Partial Recovery is Made + Balance Amount/Cash Flow of the Year in Which Full Recovery is Made = 9 + 7/27 = 9.26 Years

The project should be selected based on the payback period of 9.26 year as it indicates that the initial investment is recovered within the life of the project.

_____

Discounted Payback Period

The value of discounted payback period is arrived as below:

Year	Cash Flow [A]	Present Value Factor (8%) [B]	Discounted Cash Flows [A*B]	Cumulative Discounted Cash Flows
0	-250	1.0000	-250.00	-250.00
1	27	0.9259	25.00	-225.00
2	27	0.8573	23.15	-201.85
3	27	0.7938	21.43	-180.42
4	27	0.7350	19.85	-160.57
5	27	0.6806	18.38	-142.20
6	27	0.6302	17.01	-125.18
7	27	0.5835	15.75	-109.43
8	27	0.5403	14.59	-94.84
9	27	0.5002	13.51	-81.33
10	27	0.4632	12.51	-68.83
11	27	0.4289	11.58	-57.25
12	27	0.3971	10.72	-46.53
13	27	0.3677	9.93	-36.60
14	27	0.3405	9.19	-27.41
15	27	0.3152	8.51	-18.89
16	27	0.2919	7.88	-11.01
17	27	0.2703	7.30	-3.72
18	27	0.2502	6.76	3.04
19	27	0.2317	6.26
20	520	0.2145	111.57

As can be seen from the above table that discounted cumulative cash flows turn from negative to positive between Year 17 and Year 18. Therefore, the discounted payback period will lie between these 2 years. The formula for calculating discounted payback period is derived as below:

Discounted Payback Period = Years Upto which Partial Recovery is Made + Balance Amount/Discounted Cash Flow of the Year in Which Full Recovery is Made = 17 + 3.72/6.76 = 17.55 Years

The project should be selected based on the discounted payback period of 17.55 year as it indicates that the initial investment is recovered within the life of the project.

_____

NPV

The NPV can be calculated with the use of following formula:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+Discount Rate)^1 + Cash Flow Year 2/(1+Discount Rate)^2 + Cash Flow Year 3/(1+Discount Rate)^3 + Cash Flow Year 4/(1+Discount Rate)^4 + Cash Flow Year 5/(1+Discount Rate)^5 + Cash Flow Year 6 /(1+Discount Rate)^6 + Cash Flow Year 7/(1+Discount Rate)^7 + Cash Flow Year 8/(1+Discount Rate)^8 + Cash Flow Year 9/(1+Discount Rate)^9 + Cash Flow Year 10/(1+Discount Rate)^10 + Cash Flow Year 11/(1+Discount Rate)^11 + Cash Flow Year 12/(1+Discount Rate)^12 + Cash Flow Year 13/(1+Discount Rate)^13 + Cash Flow Year 14/(1+Discount Rate)^14 + Cash Flow Year 15/(1+Discount Rate)^15 + Cash Flow Year 15/(1+Discount Rate)^15 + Cash Flow Year 16/(1+Discount Rate)^16 + Cash Flow Year 17/(1+Discount Rate)^17 + Cash Flow Year 18/(1+Discount Rate)^18 + Cash Flow Year 19/(1+Discount Rate)^19 + Cash Flow Year 20/(1+Discount Rate)^20

Substituting values in the above formula, we get,

NPV = -250 + 27/(1+8%)^1 + 27/(1+8%)^2 + 27/(1+8%)^3 + 27/(1+8%)^4 + 27/(1+8%)^5 + 27/(1+8%)^6 + 27/(1+8%)^7 + 27/(1+8%)^8 + 27/(1+8%)^9 + 27/(1+8%)^10 + 27/(1+8%)^11 + 27/(1+8%)^12 + 27/(1+8%)^13 + 27/(1+8%)^14 + 27/(1+8%)^15 + 27/(1+8%)^16 + 27/(1+8%)^17 + 27/(1+8%)^18 + 27/(1+8%)^19 + 520/(1+8%)^20 = $120.86

The project should be selected as it generates a positive NPV of $120.86.

_____

IRR

IRR is the minimum rate of return acceptable from a project. It can be calculated with the use of IRR function/formula of EXCEL/Financial Calculator. The basic formula for calculating IRR is given as below:

NPV = 0 = Cash Flow Year 0 + Cash Flow Year 1/(1+IRR)^1 + Cash Flow Year 2/(1+IRR)^2 + Cash Flow Year 3/(1+IRR)^3 + Cash Flow Year 4/(1+IRR)^4 + Cash Flow Year 5/(1+IRR)^5 + Cash Flow Year 6 /(1+IRR)^6 + Cash Flow Year 7/(1+IRR)^7 + Cash Flow Year 8/(1+IRR)^8 + Cash Flow Year 9/(1+IRR)^9 + Cash Flow Year 10/(1+IRR)^10 + Cash Flow Year 11/(1+IRR)^11 + Cash Flow Year 12/(1+IRR)^12 + Cash Flow Year 13/(1+IRR)^13 + Cash Flow Year 14/(1+IRR)^14 + Cash Flow Year 15/(1+IRR)^15 + Cash Flow Year 15/(1+IRR)^15 + Cash Flow Year 16/(1+IRR)^16 + Cash Flow Year 17/(1+IRR)^17 + Cash Flow Year 18/(1+IRR)^18 + Cash Flow Year 19/(1+IRR)^19 + Cash Flow Year 20/(1+IRR)^20

IRR is calculated with the use of EXCEL as below:

where IRR = IRR(B2:B22) = 12.13%

The project should be selected as it provides IRR of 12.13% which is greater than the cost of capital of 8%.

_____

Profitability Index

The value of profitability index is determined as below:

Profitability Index = Present Value of Cash Inflows/Initial Investment

where

Present Value of Cash Inflows = Cash Flow Year 1/(1+Discount Rate)^1 + Cash Flow Year 2/(1+Discount Rate)^2 + Cash Flow Year 3/(1+Discount Rate)^3 + Cash Flow Year 4/(1+Discount Rate)^4 + Cash Flow Year 5/(1+Discount Rate)^5 + Cash Flow Year 6 /(1+Discount Rate)^6 + Cash Flow Year 7/(1+Discount Rate)^7 + Cash Flow Year 8/(1+Discount Rate)^8 + Cash Flow Year 9/(1+Discount Rate)^9 + Cash Flow Year 10/(1+Discount Rate)^10 + Cash Flow Year 11/(1+Discount Rate)^11 + Cash Flow Year 12/(1+Discount Rate)^12 + Cash Flow Year 13/(1+Discount Rate)^13 + Cash Flow Year 14/(1+Discount Rate)^14 + Cash Flow Year 15/(1+Discount Rate)^15 + Cash Flow Year 15/(1+Discount Rate)^15 + Cash Flow Year 16/(1+Discount Rate)^16 + Cash Flow Year 17/(1+Discount Rate)^17 + Cash Flow Year 18/(1+Discount Rate)^18 + Cash Flow Year 19/(1+Discount Rate)^19 + Cash Flow Year 20/(1+Discount Rate)^20

Substituting values in the above formula, we get,

Present Value of Cash Inflows = 27/(1+8%)^1 + 27/(1+8%)^2 + 27/(1+8%)^3 + 27/(1+8%)^4 + 27/(1+8%)^5 + 27/(1+8%)^6 + 27/(1+8%)^7 + 27/(1+8%)^8 + 27/(1+8%)^9 + 27/(1+8%)^10 + 27/(1+8%)^11 + 27/(1+8%)^12 + 27/(1+8%)^13 + 27/(1+8%)^14 + 27/(1+8%)^15 + 27/(1+8%)^16 + 27/(1+8%)^17 + 27/(1+8%)^18 + 27/(1+8%)^19 + 520/(1+8%)^20 = $370.86

Now, we can calculate profitability index as follows:

Profitability Index = 370.86/250 = 1.48

The project should be selected as the value of profitability index is greater than 1.

_____

MIRR

The value of MIRR is calculated with the use of EXCEL as below:

where MIRR = MIRR(B2:B22,8%,8%) = 10.15%

The project should be selected as the value of MIRR is greater than the cost of capital of 8%.

_____

Part b)

The revised MIRR is determined as below:

where MIRR = MIRR(B2:B22,8%,8%) = 9.91%

WIth the use of MIRR, the rate of return will be 9.91% or 10%.