I recently applied for a financial analyst position at a Real Estate Firm. and I received an assignment to complete within $2$ days to qualify for the next process. Model Assignment:

Ridge Business Center

Ridge Business Center is a warehouse$/$office property in Charlotte. The property includes a well$-$maintained building totalling $50,000$ sq ft in leasable area.

The property has been offered for sale by the current owner at asking price of $$5$ Million. As a Financial Analyst of XYZ Investors, you are expected to identify if the property will

be a good acquisition for the company. An investment must generate a minimum of $15\%$ IRR for XYZ Investors.

Please build a financial model using the assumptions below to provide an answer to the Management. Required sheets have been provided.

Investment Assumptions

Acquisition Date $01-$Jan$-21$

Holding Period : $5$ yearstime for which the property is owned$)$

Sale Date: last day of $60$th month

Terminal Cap Rate : $7\mathrm{.}5\%($ This rate is used to determine Sale Price by the following formula: Sale Price$=5$th Year NOI$/$Terminal Cap Rate$)$

Cost of Sale: $2\%(2\%$ of Sale Earnings go to broker$/$lawyer$)$

Loan Assumptions

Loan Start Date$01-$Jan$-21$

Term of Loan$5$ years

LTV : $75\%*$ of Price

Interest Rate$4\mathrm{.}0\%$

Loan Fees$1\%$

Amortization Period$25$ years$*$ Calculate monthly loan payments, you will need an amortization schedule

Rent Assumptions

$1)$ Assume rents increase every year on first day of the calendar year

$2)$ Rents shown are Annual rents on a per square feet basis. Convert them to monthly rents and actual $ as and when required.

$3)$ NNN leases are triple net leases, tenant has to pay their proportionate share of CAM, taxes and insurance over and above the rent

$4)$ Consider Suite $1006$ as vacant for the entire duration of $5$ years

Rent Roll $($ As of $31$ July, $2020)$

SuiteLeased AreaStatusLease Start DateLease End DateAnnual Rent PSFRent Increase DateRent IncreaseLease Type

$($sq$.$ ft$)($mm$/$dd$/$yy$)($mm$/$dd$/$yy$)($as of $31$ July, $20)($mm$/$dd$/$yy$)$Annual $(\%)$

$100110,000$Occupied$03-01-1803-31-28$$$12\mathrm{.}1501-01-213\%$NNN

$100210,000$Occupied$06-01-1906-30-29$$$11\mathrm{.}5001-01-213\%$NNN

$100315,000$Occupied$01-01-2001-31-30$$$10\mathrm{.}7501-01-213\%$NNN

$10045,000$Occupied$03-01-1803-31-28$$$1201-01-215\%$NNN

$100510,000$Occupied$06-01-1906-30-29$$$1201-01-215\%$NNN

$10066,000$Vacant

Total$56,000$ $$10\mathrm{.}32$

Expense Assumptions

$1)$ All expenses are paid by landlord

$2)$ All expenses grow $2\%$ annually

Expense Assumptions

Expense TypeAnnual Expense $($$$)$

CAM $$25,000$

Taxes$$75,000$

Insurance$$25,000$

Utilities$$20,000$

Repairs$$45,000$

Total$$1,90,000$

Capital Expenditure

YearAnnual Amount

$($$ psf$)$

$2021$$$2\mathrm{.}00$

$2022$$$1\mathrm{.}25$

$2023$$$1\mathrm{.}00$

$2024$$$1\mathrm{.}00$

$2025$$$1\mathrm{.}00$

Please refer to the excel sheet for the case study $$ Ridge Business Center. Use functions of Excel wherever necessary, no macros should be used for the case study. Calculate Levered IRR on the investment using the data provided.