DEAL OF THE CENTURY

can you do the excel model? To calculate the cash flow/income approach related case study

New York City of 1978 is a bad place where crime rates are high, and business have spent the last 10 years fleeing to the surrounding suburban areas. Based on the high levels of crime and business leaving, the New York City office market is not liquid. However recently (1977 and 1978) suburban rents have increased, and the suburban communities have not invested in the necessary infrastructure. As a result, suburban based office workers are having problems getting to work on time because of the massive increase in local traffic. Olympia and York (O&Y) based on their market reconnaissance, believes there might be an opportunity to buy in the New York City office market. However, based on the current market conditions in New York City O&Y needs a very high level of returns to make such a risky bet. O&Y determines that it would need an unlevered internal rate of return of 35% to take on this outsized risk.

On December 31, 1978, O&Y purchased 11 million square feet of Class A office space in Manhattan. O&Y believes the buildings have been severely mismanaged and that by 1989 the portfolio could generate a combined Net Operating Income of $250 Million and that by year end 1988 residual cap rates should pencil out at 7.35%. Currently Annualized Net Operating Income (before debt service) $24.2 million exists as of 1/1/79 with market rents for new tenant leases being $12 psf while existing rents for space occupied averaged $8.50 psf. Additional operating expenses for each new square foot occupied are $2 psf per year and the average landlord-paid build-out will cost $15 psf during the next ten years. 20% of the office space was vacant in the buildings as of January 1, I979. All existing tenant leases expire evenly over the sixty months beginning 1/1/79. New leases and renewal leases are all signed for an average of seven-year terms for the rents listed below. Annualized operating expenses for the properties (including insurance and taxes), but exclusive of variable expenses, for space vacant on January 1, 1979, are $50.6 million per year and are assumed not to increase during the next ten years.

Assume that 75% of existing tenants (now and in the future) renew and 25% move-out but are immediately replaced with new tenants. No build-out cost is spent for renewal tenants’ space. Leasing commissions are 6% of the total of new tenant’s rent payable in the first 5 years of their lease and 2% of a renewal tenants total rent payable during the first 5 years of the renewal tenant’s term. All commissions are payable on the first day of the new/renewal lease.

Calculate the actual 1979-1988 Cash Flow or deficit after capital expenditures but before debt service. Assume that by the end of 1981, the buildings reached 95% occupancy and stayed that way through 1988. Leases signed in each year were at the following rates:

1979……………………$12
1980……………………$15
1981……………………$19
1982……………………$25
1983……………………$28
1984……………………$30
1985……………………$32
1986……………………$34
1987 and after…….$36

Please determine the price (“Investor Value”) O&Y paid for the office portfolio on December 31st, 1978. You will need to utilize the Income Approach with the Yield Capitalization methodology.

Provide a detailed excel model showing your answers to questions 1 and 2

use my template draft attached to complete the answer