Hello,

I need to solve this problem in excel, but am having a hard time calculating the yield on cost calculation. Any help is greatly appreciated!

Prepare a 5-year cash flow model based on the following information. Note that because your companys strategy is to hold developments long term, there will not be a reversion at the end of the 5-year period as is typical for commercial real estate underwriting. As such, IRR is not a relevant metric. Instead, calculate yield on cost, debt yield, and cash on cash.

Overview: Your company is competing to win a lease with Tenant A. The intent is to construct a 100,000 sf 4-story office building in a development park that will be 75% pre-leased to and occupied by Tenant A and 25% leased by a future (speculative) Tenant B that will occupy and begin paying rent 6 months after completion of construction.

Site: 4 acres priced at $20.00/sf to the land

Construction Costs: $200/sf hard cost and $15/sf soft cost. Costs of land, TIs, LCs, Development Fee and Construction Loan Interest should be imputed and added to the development budget. All other costs assumed to be included in soft & hard costs.

Tenant Improvements: $50/sqft

Leasing Commission: 6.0% of gross rents for up to 10-year term

Development fee: 4%

Schedule: 6 months to design & permit and 12 months to construct

Lease Term: 12 years with 2.0% annual rent bumps for Tenant A and 10 years with 2.5% annual rent bumps for Tenant B

Rent Rate: Assume 8.5% yield on levered cost in determining the rent rate for Tenant A and 9.0% yield on levered cost in determining the rent rate for Tenant B

Operating Expenses: $15.00/sf/year (all expenses included taxes, insurance, and CAM)

Construction Loan: 65% LTC, 3-year interest only term at L+300. 1% origination fee, open prepayment at any time. Loan is last money in and is refinanced by the Permanent Loan upon achieving 100% occupancy

Permanent Loan: 70% LTV based on 7% cap rate value. 10-year term at 4.5% fixed interest on 25-year amortization. 1% penalty (on outstanding balance) for prepayment in first 5 years