Loan Project
Manager67
sha611_hungerford_base_LoanRecomendation.xlsxInstructions
| SHA611: Financial Analysis of Hotel Investments | |
| School of Hotel Administration, Cornell University | |
| Hungerford Hotel Case Study | |
| Answer Key for Base Case | |
| Instructions: | |
| Use this workbook to obtain answers to the questions in the Hungerford Hotel Case Study for the base case. | |
| To submit this assignment, please refer to the instructions in the course. | |
| Copyright © 2016 eCornell. All rights reserved. All other copyrights, trademarks, trade names, and logos are the sole property of their respective owners. |
AssumptionsBase Case
| Hungerford Hotel, Grandville, USA | |||||||||
| Investment Analysis Assumptions - Hungerford Hotel | |||||||||
| Property Description | |||||||||
| A 200-room Hungerford Hotel to be built in Grandville, NC. The 8 story hotel will open in 18 months. | |||||||||
| Amenities will include a restaurant, indoor pool, fitness facility, business center, and 2000 square feet of meeting space. | |||||||||
| ''Turn-Key' Development Costs | $42,000,000 | which equals | $210,000 | per key | |||||
| Overall Assumptions | |||||||||
| Desired Holding Period | 10 | years | |||||||
| Annual Increase in Cash Flows after 5th year | 3.00% | ||||||||
| Cash Flow Multiple for Hotel Real Estate | 12.5 | times | equal to a | 8.00% | cap. rate | ||||
| Selling Expenses | 3% | of Gross Sales Price | |||||||
| Desired Total Property Discount Rate (Hurdle Rate) | 11.0% | ||||||||
| Equity Investment Parameters | |||||||||
| Desired Equity After-Tax Discount Rate (Hurdle Rate) | 14.0% | ||||||||
| Lending Parameters | |||||||||
| Loan to Value Ratio | 65% | ||||||||
| Mortgage Amount | $27,300,000 | ||||||||
| Interest Rate | 5.15% | fixed | |||||||
| Interest "Kicker" (extra interest after stabilization) | 0.00% | of Total Revenues beginning in the fourth year of operations | |||||||
| Amortization Term | 25 | years, monthly payments | |||||||
| Annual Debt Service (Monthly times 12) | $1,943,856 | per month | |||||||
| Tax Environment | |||||||||
| Ordinary Income Tax Rate | 39% | of annual taxable income | |||||||
| Depreciation Recapture Tax Rate | 25% | of depreciation taken over holding period, paid at sale | |||||||
| Capital Gains Tax Rate | 15% | of capital gains, paid at sale | |||||||
| Depreciation Parameters - Initial Investment | |||||||||
| Depreciable Basis of Initial Investment | $35,000,000 | ||||||||
| Average Depreciable Life | 25 | years | |||||||
| Depreciation Method | Straight Line | ||||||||
| Depreciation Parameters - Replacement Reserves | |||||||||
| Depreciable Basis Reserves | Assume funds are spent each year | ||||||||
| Average Depreciable Life | 7 | years | |||||||
| Depreciation Method | Straight Line | ||||||||
| Management Contract Valuation Parameters | |||||||||
| Desired Overall IRR on Management Fee Flows | 12% | ||||||||
| Margin on Management Fees | 50% | ||||||||
| Cash Flow Multiple for Hotel Management Fees | 4 | times | |||||||
| Amortization Table | |||||||||
| Year | B. Bal | Interest | Principal | E. Bal | |||||
| 1 | $27,300,000 | $1,393,070 | $550,786 | $26,749,214 | |||||
| 2 | $26,749,214 | $1,364,025 | $579,831 | $26,169,383 | |||||
| 3 | $26,169,383 | $1,333,449 | $610,407 | $25,558,976 | |||||
| 4 | $25,558,976 | $1,301,260 | $642,596 | $24,916,380 | |||||
| 5 | $24,916,380 | $1,267,374 | $676,482 | $24,239,898 | |||||
| 6 | $24,239,898 | $1,231,701 | $712,155 | $23,527,743 | |||||
| 7 | $23,527,743 | $1,194,147 | $749,709 | $22,778,033 | |||||
| 8 | $22,778,033 | $1,154,612 | $789,244 | $21,988,789 | |||||
| 9 | $21,988,789 | $1,112,993 | $830,863 | $21,157,926 | |||||
| 10 | $21,157,926 | $1,069,179 | $874,677 | $20,283,249 | |||||
| 11 | $20,283,249 | $1,023,054 | $920,802 | $19,362,447 | |||||
| 12 | $19,362,447 | $974,497 | $969,359 | $18,393,089 | |||||
| 13 | $18,393,089 | $923,380 | $1,020,476 | $17,372,613 | |||||
| 14 | $17,372,613 | $869,567 | $1,074,289 | $16,298,324 | |||||
| 15 | $16,298,324 | $812,916 | $1,130,939 | $15,167,384 | |||||
| 16 | $15,167,384 | $753,278 | $1,190,578 | $13,976,807 | |||||
| 17 | $13,976,807 | $690,496 | $1,253,360 | $12,723,446 | |||||
| 18 | $12,723,446 | $624,402 | $1,319,454 | $11,403,992 | |||||
| 19 | $11,403,992 | $554,823 | $1,389,033 | $10,014,959 | |||||
| 20 | $10,014,959 | $481,575 | $1,462,281 | $8,552,678 | |||||
| 21 | $8,552,678 | $404,464 | $1,539,392 | $7,013,286 | |||||
| 22 | $7,013,286 | $323,287 | $1,620,569 | $5,392,717 | |||||
| 23 | $5,392,717 | $237,829 | $1,706,027 | $3,686,690 | |||||
| 24 | $3,686,690 | $147,865 | $1,795,991 | $1,890,699 | |||||
| 25 | $1,890,699 | $53,157 | $1,890,699 | $0 |
Assumptions Optional Case Case
| Hungerford Hotel, Grandville, USA | |||||||||
| Investment Analysis Assumptions - Hungerford Hotel | |||||||||
| Property Description | |||||||||
| A 200-room Hungerford Hotel to be built in Grandville, NC. The 8 story hotel will open in 18 months. | |||||||||
| Amenities will include a restaurant, indoor pool, fitness facility, business center, and 2000 square feet of meeting space. | |||||||||
| ''Turn-Key' Development Costs | $42,000,000 | which equals | $210,000 | per key | |||||
| Overall Assumptions | |||||||||
| Desired Holding Period | 10 | years | |||||||
| Annual Increase in Cash Flows after 5th year | 3.00% | ||||||||
| Cash Flow Multiple for Hotel Real Estate | 12.5 | times | equal to a | 8.00% | cap. rate | ||||
| Selling Expenses | 3% | of Gross Sales Price | |||||||
| Desired Total Property Discount Rate (Hurdle Rate) | 11.0% | ||||||||
| Equity Investment Parameters | |||||||||
| Desired Equity After-Tax Discount Rate (Hurdle Rate) | 15.0% | ||||||||
| Lending Parameters | |||||||||
| Loan to Value Ratio | 70% | ||||||||
| Mortgage Amount | $29,400,000 | ||||||||
| Interest Rate | 5.25% | fixed | |||||||
| Interest "Kicker" (extra interest after stabilization) | 1.50% | of Total Revenues beginning in the fourth year of operations | |||||||
| Amortization Term | 25 | years, monthly payments | |||||||
| Annual Debt Service (Monthly times 12) | $2,114,146 | per month | |||||||
| Tax Environment | |||||||||
| Ordinary Income Tax Rate | 39% | of annual taxable income | |||||||
| Depreciation Recapture Tax Rate | 25% | of depreciation taken over holding period, paid at sale | |||||||
| Capital Gains Tax Rate | 15% | of capital gains, paid at sale | |||||||
| Depreciation Parameters - Initial Investment | |||||||||
| Depreciable Basis of Initial Investment | $35,000,000 | ||||||||
| Average Depreciable Life | 25 | years | |||||||
| Depreciation Method | Straight Line | ||||||||
| Depreciation Parameters - Replacement Reserves | |||||||||
| Depreciable Basis Reserves | Assume funds are spent each year | ||||||||
| Average Depreciable Life | 7 | years | |||||||
| Depreciation Method | Straight Line | ||||||||
| Management Contract Valuation Parameters | |||||||||
| Desired Overall IRR on Management Fee Flows | 12% | ||||||||
| Margin on Management Fees | 50% | ||||||||
| Cash Flow Multiple for Hotel Management Fees | 4 | times | |||||||
| Amortization Table | |||||||||
| Year | B. Bal | Interest | Principal | E. Bal | |||||
| 1 | $29,400,000 | $1,529,567 | $584,579 | $28,815,421 | |||||
| 2 | $28,815,421 | $1,498,127 | $616,019 | $28,199,402 | |||||
| 3 | $28,199,402 | $1,464,996 | $649,150 | $27,550,252 | |||||
| 4 | $27,550,252 | $1,430,084 | $684,062 | $26,866,189 | |||||
| 5 | $26,866,189 | $1,393,293 | $720,852 | $26,145,337 | |||||
| 6 | $26,145,337 | $1,354,525 | $759,621 | $25,385,716 | |||||
| 7 | $25,385,716 | $1,313,671 | $800,475 | $24,585,241 | |||||
| 8 | $24,585,241 | $1,270,620 | $843,526 | $23,741,714 | |||||
| 9 | $23,741,714 | $1,225,253 | $888,893 | $22,852,822 | |||||
| 10 | $22,852,822 | $1,177,447 | $936,699 | $21,916,123 | |||||
| 11 | $21,916,123 | $1,127,070 | $987,076 | $20,929,047 | |||||
| 12 | $20,929,047 | $1,073,983 | $1,040,163 | $19,888,883 | |||||
| 13 | $19,888,883 | $1,018,041 | $1,096,105 | $18,792,778 | |||||
| 14 | $18,792,778 | $959,090 | $1,155,056 | $17,637,722 | |||||
| 15 | $17,637,722 | $896,969 | $1,217,177 | $16,420,546 | |||||
| 16 | $16,420,546 | $831,507 | $1,282,639 | $15,137,907 | |||||
| 17 | $15,137,907 | $762,524 | $1,351,622 | $13,786,285 | |||||
| 18 | $13,786,285 | $689,832 | $1,424,314 | $12,361,971 | |||||
| 19 | $12,361,971 | $613,229 | $1,500,917 | $10,861,054 | |||||
| 20 | $10,861,054 | $532,507 | $1,581,639 | $9,279,415 | |||||
| 21 | $9,279,415 | $447,444 | $1,666,702 | $7,612,713 | |||||
| 22 | $7,612,713 | $357,805 | $1,756,341 | $5,856,372 | |||||
| 23 | $5,856,372 | $263,346 | $1,850,800 | $4,005,572 | |||||
| 24 | $4,005,572 | $163,806 | $1,950,340 | $2,055,233 | |||||
| 25 | $2,055,233 | $58,913 | $2,055,233 | $0 |
