Office/Retail DCF Model Help

[Brad Kellen]

Hi there I'm new to Bigger Pockets so I apologize if this was posted in the incorrect forum. I have been working in the commercial real estate industry for the past few years, specifically in Multi Family. I have decided however I would like to expand my skill set and become more involved with the office/retail side of the business and plan to seek opportunities in the near future. A few years prior in my original search for a job in CRE I had an opportunity with a company specializing in the office/retail space. They had provided a modeling test which I was unable to complete despite my research efforts and schooling, now that I am looking for a job in that space again I was wondering if the forum would be able to provide me assistance in building out the model so I can dissect and learn it. Hope you guys can help, thanks for taking the time. The parameters are below:

ASSUMPTIONS:

Start Date: Jan 1, 2021

Building Size: 500,000 SF

Current Occupancy: 50%

Tenant 1:

50,000 SF at $18.00 PSF NNN with $0.50 annual bumps in January. Expiration Date 12/31/2024. 1 yr to backfill.

Tenant 2:

100,000 SF at $19.00 PSF NNN with $0.50 annual bumps in January. Expiration Date 12/31/2030. 1 yr to backfill.

Tenant 3:

100,000 SF at $20.00 PSF NNN with $0.50 annual bumps in January. Expiration date 12/31/2026. 1 yr to backfill.

Market Leasing Assumptions:

Rent: $20.00 PSF NNN

Annual Steps: $0.50 PSF

Lease up period: 24 months

Vacancy Factor: 5% of Gross Revenue

Free rent: 0 months

TI: $20 PSF. $0 for renewal/backfill

LC: 6%. For sake of simplicity of calculation, assume 0% for renewal/backfill tenants.

Lease term: 10 Years

Storage Income: $15,000/year with 3% annual increases

Operating Expenses: $8.00 PSF annually with 3% annual inflation

Cap Ex reserve: $0.15 PSF annually

Roof replacement cost: Year 3: $200,000

Sale assumptions:

Hold period: 9 Years

Terminal Cap Rate: 10%

Cost of Sale: 4%

Questions:

1. What is the property's Current NOI?
2. Show a cap rate grid from 3.5% - 7% with 0.5% increments on the current NOI
3. What is the property value on a current NOI on a 4.5% Cap?
4. Assuming closing costs of 1% at purchase, what would the total acquisition price be?
5. Assuming an LTV of 65% at 3.5% fixed interest on a 30 Year Amortization what is the debt amount & the equity amount? 1% loan origination fees. 24 month Interest Only.
6. Show annual unlevered return & cash on cash return
   
7. What is the property value on a Y3 proforma NOI on a 10% Cap?