Individual Homework Assignments REVISED Fall XXXXXXXXXX)
2 | P a g e
WEEK TWO
INDIVIDUAL ASSIGNMENTS
Intended Learning Outcomes for these individual exercises
Reinforce understanding of how to properly setup a financial model and reinforce XLS skills.
Recreate the following Back of the Envelope (BOE) setup in XLS. Submit the XLS file.
Answer the following questions by responding below.
What are the financial and non-financial risks exhibited by this investment?
What would you adjust to this model and why?
3 | P a g e
QUICK EVALUATION (Back of the Envelope "BOE'") TO DETERMINE IF FO IS A VIABLE INVESTMENT
Gross Building Size 12,000 sf
Rentable Building Size 10,000 sf
Efficiency Ratio 83.3%
Cost Per SF Total
Purchase Price - BUILDING 141.67 XXXXXXXXXX1,700,000
Closing Costs - Transfe
Recordation Costs 2.83 XXXXXXXXXX34,000
Closing Costs - Title Insurance 1.42 XXXXXXXXXX17,000
Closing Costs - Legal 10,000
Lender 3rd Party Reports (appraisal, hard, soft cost review) 15,000
Total Costs 1,776,000
Total
MedStar $15.00 sf 5,000 sf 75,000
RiseCo $20.00 sf 2,500 sf 50,000
Harford Co Gov $20.00 sf 2,500 sf 50,000
Sub-Total 175,000$
Vacancy Loss 5.00% increase per yr (8,750)$
Effective Gross Income 166,250$
Expenses - non-recoverable $1.00 rentable SF (10,000)
Reserves $0.20 rentable SF (2,000)
Total Cost 1,776,000$
Loan per LTV Ratio 75.00% LTV Ratio
Loan Amount 1,332,000$
Interest Rate 5.00%
Amortization 25 years
Annual Payment (Debt Service) $93,441
Required Equity 444,000$
Year 1 Year 2 Year 3
MedStar 1.00% increase per yr 75,000 XXXXXXXXXX75, XXXXXXXXXX,508
RiseCo 0.00% increase per yr 50,000 XXXXXXXXXX50, XXXXXXXXXX,000
Harford Co Gov 2.00% increase per yr 50,000 XXXXXXXXXX51, XXXXXXXXXX,020
Sub-Total 175,000$ XXXXXXXXXX176,750$ XXXXXXXXXX,528$
Vacancy Loss 5.00% increase per yr (8,750.00) XXXXXXXXXX8, XXXXXXXXXX,926.38)
Effective Gross Income 166,250$ XXXXXXXXXX167,913$ XXXXXXXXXX,601$
Expenses - non-recoverable 2.00% increase per yr (10,000.00) XXXXXXXXXX10, XXXXXXXXXX,404.00)
Reserves 0.00% increase per yr (2,000.00) XXXXXXXXXX2, XXXXXXXXXX,000.00)
Net Operating Income 154,250$ XXXXXXXXXX155,713$ XXXXXXXXXX,197$
Debt Service (93,440.87) XXXXXXXXXX93, XXXXXXXXXX,440.87)
Cash Flow After Debt Service 60,809$ XXXXXXXXXX62,272$ XXXXXXXXXX,756$
Terminal Cap Rate 9.00%
NOI (Year 3) 157,197 - -
Reversion 1,746,635
Less Cost of Sale 5.00% (87,331.74)
Net Reversion 1,659,303$
Less Debt Balance 1,245,319
Net Proceeds 413,984$
Acquisition Yr 1 Yr 2 Yr 3
Investment (444,000)
Cash Flow 60,809 XXXXXXXXXX62, XXXXXXXXXX,756
Net Proceeds 413,984
Project Cash Flow (444,000) XXXXXXXXXX60,809 XXXXXXXXXX62, XXXXXXXXXX,740
Leveraged IRR ????
Operating Income
Loan and Equity Sizing
Introduction of Time to this Analysis
Asset Size
Costs to Close
Return on Equity
Hint…create an IRR formula for the strip shown in
grey above…
4 | P a g e
WEEK TWO
INDIVIDUAL ASSIGNMENTS
Intended Learning Outcomes for these individual exercises
Reinforce understanding of the Time Value of Money (TVM) formulas both conceptually and
mechanically. Many in the industry know the mechanics (using Excel) but do not understand
the concepts and their application and mathematical underpinnings. A careful review of
Chapter 3 in the text and the sample problems in the chapter is important, especially for those
without a deep finance or accounting background.
Formulate a business plan and understand its important elements
Brueggeman Text Chapter 3
For the following questions, create the following cash flow and reversion in Excel.
1. Why should defining a mission statement and business plan precede the evaluation of Freedom
Office (FO) as an investment opportunity? In two paragraphs or less, craft a concise mission
statement and then business plan for FO. Hint: One must start with profiling the investor and
establish goals for targeted returns (both cash-on-cash returns and reversion price), planned holding
period, risk tolerance and overall investment objectives.
2. TVM Principles – Using Excel, provide a specific example in which a real estate investor would
consider using a future value calculation to make a real estate investment decision. Please prepare
an Excel worksheet to illustrate this example similar to the worksheet below. Please paste below
with CTRL~ or submit the Excel worksheet.
3. TVM Principles and Mechanics – Analyze the data below in Excel and then answer the following
questions.
A real estate investment has the following