Questions are attached. It is in real estate/finance category……………make sure the answers are accurate……Real Estate Markets and Institutions Assignment Instructions:
To complete this assignment, you will need to answer the 14 questions listed below. All questions
must be answered in the Excel spreadsheet that is provided to you. Moreover, your answers must fit
within the designated space (highlighted in yellow) for each answer within each of the Excel tabs.
1.
a. According to the rule of 72, how many times your money will be doubled over a 36-year
period if it earns a rate of return of 8% per year? Show your work.
b. Using the rule of 72, estimate the value of an initial investment of $100K at the end of a 36year period if it earns a rate of return of 8% per year? Show your work.
c. According to the 4% rule, what the size of your investment portfolio needs to be in order for
you to withdraw an initial annual amount of $250K? Show your work.
d. Referring to part c, what will be your 3rd annual withdrawal amount if the inflation rate during
the 2 years since your initial withdrawal averaged 2% per year? Show your work.
2. Consider the “buy vs. rent” Excel spreadsheet provided to you in tab A2.
a. According to the assumptions made in that spreadsheet, should the average individual buy or
rent? Briefly explain.
b. Using “Goal Seek” alter the “buy vs. rent” Excel spreadsheet so that it shows the minimum
rate of price appreciation the homeowner must receive in order to be better off buying than
renting. Highlight in yellow the cell that includes this price appreciation rate.
3. Consider the levered DCF model provided to you in tab A3 of the Excel spreadsheet.
a. Calculate the maximum price that an investors with the assumptions made in the model should
be willing to pay for that property. Show this maximum price in cell C8.
b. If you expect a general increase in the risk premium for real estate investments over the next 8
years, how would this affect the expected levered return on this property? Briefly Explain.
4. Using the headers in tab A4 of the Excel spreadsheet and the input values included in the
green “box”:
a. Make a monthly amortization schedule for a fixed rate 15-year mortgage. Make sure that any
value that the user (me in this case) changes in the green “box” will be reflected in your
amortization schedule. Your monthly payment calculation should be included in cell J5 and
should also automatically change with any changes to the values in the green “box”.
b. Next to the amortization schedule, use an Excel graph that shows the remaining mortgage
balance overtime. Make sure that your graph is labeled appropriately.
c. Include a vlookup function in cell L7 that will reflect the remaining mortgage balance
associated with the end of the month entered by the user into cell J7. For example, if the user
enters 34 into cell J7, cell L7 will automatically show the remaining mortgage balance after 34
months.
5. Calculate the nominal and real annual rate of housing price appreciation rate for Miami and
for Houston for the requested different time periods using the housing price index data and the
inflation index data provided to you in tab A5 of the Excel spreadsheet. In total, you need to
calculate 16 values that will appear in the 16 yellow cells of that Excel tab.
6. Given your results from the previous problem:
a. What can you say about the magnitude and the volatility of housing price appreciation in the
short, medium and long run?
b. Are the results from question 5 consistent with the theory of price appreciation we discussed
in the beginning of this course? Briefly explain.
7. Consider a REIT that holds high quality office buildings in some of the best locations in the
US. The REIT is currently traded at a price of $64/share and there are 130 million shares
outstanding. Using the information below answer the following questions:
Expected next year total revenue: $750M
Expected next year total expenses (including interest and depreciation): $380M
Expected next year depreciation: $90M
Expected next year interest: $70M
Total debt: $1.6B
Current office CAP in the US: 4.5% to 6.0% depending on quality and location.
a. What is your estimation for a fair market value for a share of the REIT described? Show your
work!
b. What is your estimation for a fair price to pay for a share of the REIT described, if you require
an 7.5% rate of return on an unlevered basis and expect the REIT to increase NOI at an average
rate of 2.5%? Should you buy shares of that REIT? Show your work!
8. PLAM and reversed annuity mortgages
a. Under which economic circumstances a PLAM type loan is greatly needed? Briefly explain.
b. What kind of individuals are most likely to benefit from reversed annuity mortgages?
9. For each of the factors listed below indicate whether the factor, independently, is likely to
cause a particular income producing property to trade for a lower or higher CAP rate compared
with an average property. For this question, no explanation is needed. Indicating “higher”,
“lower” or “irrelevant” for factors a through g is sufficient.
a. Lower volatility in rent prices and occupancy rates.
b. Worse location
c. High inflation environment
d. High risk premium environment
e. Market general higher than normal expected NOI growth
f. Lower construction quality
g. High quality tenants
10. Consider an income producing property that according to your assumptions and estimations
is currently worth $4M on an unlevered basis when a 7.5% required rate of return is applied. One
of the assumptions that you have made when arriving at that estimate is that you will sell the
property in 6 years for a CAP of 8%, which translates to $4.4M at that future point in time.
a. At what price will you sell the property in 6 years if all your assumptions materialized except
that you will sell the property for a CAP of 7% instead of 8%? Show your calculations.
b. All other things equal, by how much the situation described in part a affects the current value
of the property. Show your calculations.
11. What is likely to happen to the average CAP rate in the market under each of the following
conditions. Briefly explain.
a. Rising interest rate environment.
b. Growing perception that real estate is becoming riskier that previously viewed.
c. Expectations that future inflation will increase
12. Real estate market inefficiency:
a. Briefly describe 5 factors that cause the stock market to be more efficient than the real estate
market.
b. Can investors make money in an efficient market? Briefly explain.
c. Do educated and informed investors rather operate in an efficient or inefficient market?
Briefly explain.
13. DCR:
a. Calculate the DCR for an income producing property to be acquired at a price of $7M and a
CAP of 5.5%. The down payment on the property is 30% of the property value and the mortgage
on the remaining balance is a fixed-rate interest only loan at a rate of 4%.
b. What is the meaning of a DCR of 1.40, for example? Please explain.
c. List and briefly explain three different factors that are likely to cause the lender to require a
higher DCR from investors?
14. Four years ago, when you were 24, you graduated from college and landed a good paying
job. At that time you purchased your “starter home” for $200K. Since then, the housing market
in the city where your home is located experienced unusually high rate of price appreciation and
a local real estate agent informed you that if you were to put your home on the market today, you
will be able to sell it for about $350K.
a. Did the recent abnormal housing price appreciation benefited you? Explain.
b. What kind of individuals benefited the most from the recent price appreciation described in
this question? Explain
c. What kind of individuals suffered the most from the recent price appreciation described in this
question? Explain.
a.
Answer:
Show your work:
b.
Answer:
Show your work:
c.
Answer:
Show your work:
d.
Answer:
Show your work:
a.
Answer:
Input Variables
Inflation expectation
Purchase price
Initial monthly rent
Rental growth rate
Property appreciation
Insurance
Maintenance
Expense growth
Marginal tax rate
Property tax
2.00%
$230,000
$1,800
2.00%
2.00%
$3,450
$2,300
2.00%
25.00%
$4,025
Down payment
Loan amount
Interest rate (Fixed)
Mortgage initiation fee
Loan maturity (years)
Mortgage Payment
Holding period (years)
Selling expenses
Invetments opportunity
Option 1: Buy the house
Year
0
1
2
3
$5,750
$4,025
$9,224
$5,865
$4,106
$9,224
$5,982
$4,188
$9,224
$18,999
$19,194
$19,394
$0
$2,603
$2,595
$2,585
$72,000
$16,395
$16,600
$16,809
Out flows
Down payment + Origination
Insurance & Maintenance
Property tax
Mortgage payments
$72,000
Total:
$72,000
In flow
Tax benefits
Overall “out of pocket”
Other
Interest payment
Principal payments
House value
Total equity
$6,388
$6,273
$2,835
$2,951
$230,000 $234,600 $239,292
$69,000 $76,435 $84,078
$6,153
$3,071
$244,078
$91,935
$21,600
$22,473
Cash at time sold
Option 2: Rent the house
Rent expenses
$22,032
Investments portfolio
$72,000
$72,915
$73,681
$74,279
30%
$161,000
4.00%
$3,000
30
$768.64
5
6.50%
8.50%
4
5
$6,102
$4,271
$9,224
$6,224
$4,357
$9,224
$19,597
$19,804
$2,575
$2,564
$17,022
$17,241
$6,028
$5,897
$3,196
$3,326
$248,959 $253,939
$100,013 $108,318
$91,812
$22,922
$23,381
$74,693
$74,903
b.
Answer:
Acquisition Price
Expected 1st Year NOI
CAP
Required Rate of Return
Expected NOI Growth
Terminal NOI Growth
Next Investor Req Premium
$9,500,000
$550,000
5.79%
8.00%
3.00%
2.50%
0.50%
Down Payment
Mortgage Rate
Manutiry
Levered Required Rate
Minumum Required DCR
$ 5,000,000
5.50%
20
11.00%
Terminal CAP
Next buyer Required return
Mortgage Payment (Annual)
6.00%
8.50%
$376,557
Loan Amount
DCR
Miminum DCR satisfied?
Mortgage Balance at Sale
$4,500,000
1.46
Yes
$3,245,363
End of year
0
1
2
3
4
5
6
7
8
NOI
$550,000
$566,500
$583,495
$601,000
$619,030
$637,601
$656,729
$676,431
PV_NOI
REV
$509,259
$485,682
$463,197
$441,753
$421,301
$401,797
$383,195
$365,454 $11,555,690
Amortization schedule:
I
0
1
2
3
4
5
6
P
$247,500
$240,402
$232,913
$225,013
$216,678
$207,885
$129,057
$136,155
$143,644
$151,544
$159,879
$168,672
Ending balance
$4,500,000
$4,370,943
$4,234,788
$4,091,144
$3,939,600
$3,779,721
$3,611,049
1.25
PV_REV
Total PV
$6,243,180
$509,259
$485,682
$463,197
$441,753
$421,301
$401,797
$383,195
$6,608,634
Total PV:
$9,714,819
NPV:
IRR:
$214,819
8.36%
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$198,608
$188,820
$178,495
$167,602
$156,109
$143,984
$131,193
$117,698
$103,461
$88,440
$72,594
$55,876
$38,238
$19,631
$177,949
$187,737
$198,062
$208,955
$220,448
$232,573
$245,364
$258,859
$273,096
$288,117
$303,963
$320,681
$338,319
$356,926
$3,433,100
$3,245,363
$3,047,301
$2,838,346
$2,617,898
$2,385,325
$2,139,961
$1,881,102
$1,608,005
$1,319,889
$1,015,926
$695,245
$356,926
$0
Leveraged
Total CF
-$9,500,000
$550,000
$566,500
$583,495
$601,000
$619,030
$637,601
$656,729
$12,232,120
DS
$376,557
$376,557
$376,557
$376,557
$376,557
$376,557
$376,557
$3,621,920
CF
-$5,000,000
$173,443
$189,943
$206,938
$224,443
$242,473
$261,044
$280,172
$8,610,200
NPV:
IRR:
PV(CF)
-$5,000,000
$156,255
$154,162
$151,311
$147,847
$143,896
$139,565
$134,947
$3,736,194
Month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Beginning balance
Interest
Principal
Ending Balance
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
Interest rate
Original balance
3.50%
$ 400,000
Monthly Payment
Remaining balance after
months:
Year
Q
1975
1975
1975
1975
1976
1976
1976
1976
1977
1977
1977
1977
1978
1978
1978
1978
1979
1979
1979
1979
1980
1980
1980
1980
1981
1981
1981
1981
1982
1982
1982
1982
1983
1983
1983
1983
1984
1984
1984
1984
1985
1985
1985
1985
1986
Miami
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
Houston
Inflation
52.7
53.6
54.6
55.5
34.85
36.04
36.44
37.99
37.12
39.2
40.28
41.91
39.72
43.05
42.34
45.72
48.42
48.39
53.62
56.79
58.6
57.43
63.32
64.88
67.13
66.99
55.28
66.65
69.89
70.22
67.47
70.26
68.96
68.45
68.88
69.11
67.73
69.56
67.03
68.46
68.91
67.76
66.98
66.99
68.6
51.59
58.32
58
57.8
59.94
65.9
64.23
67.66
69.45
74.66
73.44
76.89
78.71
83.32
84.67
85.55
88.07
91.92
90.89
92.8
89.81
96.01
97.58
100.37
106.2
107.95
105.65
107.81
107.81
108.4
107.62
106.86
103.45
103.37
103.95
101.38
97.99
95.83
94.32
92.13
93.16
55.9
56.8
57.6
58.2
59.5
60.7
61.4
62.1
63.4
65.2
66.5
67.7
69.8
72.3
74.6
76.7
80.1
82.7
84.0
86.3
88.5
90.6
93.2
94.0
94.5
97.0
97.9
97.6
97.9
99.5
100.7
101.3
102.6
103.7
105.0
105.3
106.4
107.6
108.3
109.3
108.8
1986
1986
1986
1987
1987
1987
1987
1988
1988
1988
1988
1989
1989
1989
1989
1990
1990
1990
1990
1991
1991
1991
1991
1992
1992
1992
1992
1993
1993
1993
1993
1994
1994
1994
1994
1995
1995
1995
1995
1996
1996
1996
1996
1997
1997
1997
1997
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
70.2
71.17
71.01
68.3
72.51
73.64
74.43
74.52
76.5
77.69
79.09
79.33
80.13
81.66
81.51
83.21
82.82
83.65
83.36
84.58
84.73
85.38
86.96
87.99
87.73
89.04
89.15
90.45
92.76
95.61
96.95
97.65
98.55
98.84
98.97
100
101.5
103.64
105.29
106.61
106.01
105.67
107.19
108.04
107.68
108.08
110.14
94.4
92.61
88.22
88.1
86.56
83.78
81.55
81.87
84.32
85.05
85.59
86.37
88.45
90.37
90.89
91.48
92.53
93.74
93.56
94.46
95.65
96.21
97.19
98.89
98.42
100.18
100.07
100.38
100.87
101.68
102.25
102.25
101.71
101.67
100.26
100
101.67
102.16
102.74
104.24
103.74
103.99
104.63
104.47
106.25
107.3
108.45
109.5
110.2
110.5
112.1
113.5
115.0
115.4
116.5
118.0
119.8
120.5
122.3
124.1
125.0
126.1
128.7
129.9
132.7
133.8
135.0
136.0
137.2
137.9
139.3
140.2
141.3
141.9
143.6
144.4
145.1
145.8
147.2
148.0
149.4
149.7
151.4
152.5
153.2
153.5
155.7
156.7
157.8
158.6
160.0
160.3
161.2
161.3
1998
1998
1998
1998
1999
1999
1999
1999
2000
2000
2000
2000
2001
2001
2001
2001
2002
2002
2002
2002
2003
2003
2003
2003
2004
2004
2004
2004
2005
2005
2005
2005
2006
2006
2006
2006
2007
2007
2007
2007
2008
2008
2008
2008
2009
2009
2009
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
113.35
113.35
113.73
115.91
116.32
115.6
116.9
118.65
121.32
123.09
125.16
128.15
132.2
136.56
140.36
144.98
149.12
154.71
161.09
166.01
170.76
176.21
180.8
190.87
197.66
206.91
220.75
230.2
242.23
258.75
276.27
294.66
309.5
321.9
331.11
337.48
342.38
343.25
337.97
331.22
315.56
291.89
256.05
236.14
226.16
213.32
199.05
110.87
111.99
114.31
115.72
117.28
120.24
123.21
125.78
127.26
129.24
130.99
132.03
136.29
137.76
139.61
140.99
141.7
143.45
145.22
147.36
148.43
149.34
150.23
151.72
153.28
154.66
156.69
157.68
160.22
161.23
163.58
166.15
168.72
171.22
173.67
175.05
177.33
180.2
180.99
182.13
184.22
184.8
185.97
185.6
188.27
186.76
185.45
162.2
163.0
163.6
163.9
165.0
166.2
167.9
168.3
171.2
172.4
173.7
174.0
176.2
178.0
178.3
176.7
178.8
179.9
181.0
180.9
184.2
183.7
185.2
184.3
187.4
189.7
189.9
190.3
193.3
194.5
198.8
196.8
199.8
202.9
202.9
201.8
205.352
208.352
208.490
210.036
213.528
218.815
218.783
210.228
212.709
215.693
215.969
2009
2010
2010
2010
2010
2011
2011
2011
2011
2012
2012
2012
2012
2013
2013
2013
2013
2014
2014
2014
2014
2015
2015
2015
2015
2016
2016
2016
2016
2017
2017
2017
2017
2018
2018
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
200.55
195.61
193.88
196.88
194.03
184.56
180.05
181.61
184.29
180.57
181.79
186.15
193.27
196
205.2
210.22
217.64
222.3
228.56
236.95
242.48
244.42
251.76
260.12
265.12
272.37
277.33
283.61
289
295.67
302.09
306
313.69
319.81
327.43
185.04
183.8
184.21
184.76
184.45
181.18
180.76
182.19
182.73
183.84
186.3
187.69
189.83
191.83
195.45
200.46
205.1
209.17
217
224.51
227.68
230.27
237.35
240.63
241.86
242.67
247.59
250.61
253.22
254.5
262.22
262.74
267.44
269.76
277.07
215.949
217.631
217.965
218.439
219.179
223.467
225.722
226.889
225.672
229.392
229.478
231.407
229.601
232.773
233.504
234.149
233.049
236.293
238.343
238.031
234.812
236.119
238.638
237.945
236.525
239.261
241.038
241.428
241.432
243.524
244.737
245.708
246.524
249.554
251.989
Time Period
Miami Nominal Annual
Price Appreciation Rate
Q2 2007 – Q4 2009 (2.5 years)
Q1 2002 – Q1 2006 (4 years)
Q2 1986 – Q2 1995 (9 years)
Q2 1978 – Q2 2018 (40 years)
Houston Nominal
Annual Price
Appreciation Rate
Miami Real Annual
Price Appreciation
Rate
Houston Real Annual
Price Appreciation
Rate
a.
b.
Current share price
Number of shares outstanding
Expected next year revenue
Expected next year total expenses
(including interest and depreciation)
Expected next year depreciation
Expected next year interest payment
Total Debt
Current range of CAP for office space in
the US (depending on quality and
location)
Required unlevered rate of return
Expectet NOI graowth rate
a. Answer:
Show your work:
b. Answer:
Show your work:
$
$
64.00
130
750
per share
million
Million
$
380
Million
$
$
$
90
70
1.6
Million
Million
Billion
4.50%
7.50%
2.50%
to
6.00%
72
a.
b.
c.
a.
b.
c.
d.
e.
f.
g.
a. Answer:
Show your work:
b. Answer:
Show your work:
a.
b.
c.
a.
b.
c.
a. Answer:
Show your work:
b.
c.
a.
b.
c.
Spring 18
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.