AI2152 - Quantitative Methods Applied to Real Estate

Av: Björn Andrén & Ulf Nilsson

Formler som använts under tidigare tentor

Annuity

Variance

Present Value

Future Value of Cash today

IRR (Internal Rate of Return)

Price Elasticity (Midpoint method)

The result indicates a 1% change in price will change the quantity by x %.

Multipel R, R-Square, Adjusted R-Square

Quantity Theory of Money

Tenta 2021 October

Question 1. Calculate the present value of Annuity cashflows and a Bonus payment

Your investment pays you annual cash flows of $25 000 for the next 40 years. Year 40, your investment also pays you $500 000 (in addition to the $25 000). As usual all payments occur at the end of each year. Your required rate of return (the discount rate) is 5.5%. The present value of the cash flow is ___

Combine two formulas

Annuity

Present Value of future payment

	Given	Acronym
Annual Cashflow	25 000	CF
Bonus Payment Year 40	500 000	CFt
Time Period Years	40	T
Rate of Return	5,5%	r

Question 2 - Calculate the constant cashflow growth in perpetuity

You assume that your new investment will generate annual cash flow forever (in perpetuity). Next year (year one) your investment will pay you $5 million. Thereafter, you assume that the yearly payments grow with an incredible (according to you) constant annual growth rate in perpetuity. Your required rate of return (the discount rate) is 12%. The present value of the growing cash flow is $62 500 000. Then the constant annual growth rate must be_____.

	Given	Acronym
Year One CF1	$5 000 000	CF1
Required Rate of Return (Discount Rate)	12%	r
Present Value of the growing Cashflow	$62 500 000	PV
Constant growth rate	(?)%	g

Present Value of growing cashflow in perpetuity

(It’s the last part of the formula we need, since they want us to calculate g)

🧠

PV = CF1/(r-g) → g = -((CF1/PV)-r)

✅

g = 4%

‣

Answer

Question 3 - Expected IRR, Risk analysis, Variance, Standard Deviation

A property can be purchased for 12 000 000 today (USD). A real estate analyst who likes risk analysis is analyzing the expected IRR, and the risk measured as the standard deviation, of the real estate investment by projecting five different scenarios as follows: Severe recession: NOI will be 800 000 the first year, and then decrease 4.5 percent per year until year six. The property will sell for 9 000 000 in year six. The probability for this scenario is 10 percent. Moderate recession: NOI will be 800 000 the first year, and then decrease 2.5 percent per year until year six. The property will sell for 10 000 000 in year six. The probability for this scenario is 15 percent. Baseline forecast: NOI will be level 800 000 per year for the next six years. The property will sell for 13 000 000 in year six. The probability for this scenario is 40 percent. Moderate expansion: NOI will be 800 000 the first year, and then increase by 2.0 percent per year until year six. The property will sell for 14 000 000 in year six. The probability for this scenario is 30 percent. Strong boom expansion: NOI will be 800 000 the first year, and then increase 4.0 percent per year until year six. The property will sell for 15 000 000 in year six. The probability for this scenario is 5 percent.

Given from the question

1️⃣ Future Value Cashflow

To calculate NOI for year 1→6

2️⃣ Calculate IRR for all scenarios📗

=IRR((NOI Year Zero:NOIY6))

Obs → NOI Year zero måste vara negativt för att IRR formeln ska funka i excel

3️⃣  Calculate Mean IRR (Expected IRR) 📗

The sum of all scenarios based on their probability (Scenario probability)*(Case IRR)

=SUMPRODUCT(Scenario Prob(1->5);IRR(1->5))

4️⃣ Calculate Variance for each Case📗

=Scenario Prob.*((IRR for case)-(expected IRR))^2

5️⃣ Calculate Variance of Scenarios 📗

=sum(all case Variances) --> Variance

6️⃣ Standard Deviation 📗

=SQRT(Variance)

5️⃣ Variance	0,0628%
6️⃣ Standard Deviation	2,506% ✅

✅

Standard Deviation = 2,506%, Expected IRR = 7,173%

‣

Answer

Question 4 - Expected NPV based on 5 Scenarios

If the required rate of return (the discount rate) is 10 % for each of the five scenarios in question 10, then the expected NPV is____.

	Given	Acronym
Required rate of Return (Discount Rate)	10%	i (r otherwise)
Time Period	6 Years	t = 0→6
NOI Year 0 → Year 6	(See table above)	Rt

1️⃣ Calculate Net Present Value 📗

Net present Value = NOIY0 + (NOIY1/((1+r)^(1)) … (NOIY6/((1+r)^(6))

2️⃣ Calculate Expected Mean NPV 📗

For scenario analysis you get Expected Mean NPV based on the probability of each scenario.

=SUMPRODUCT((Scenario Prob(1->5);NPV(1->5))

=SUMPRODUCT((Prob1:Prob5;NPV1:NPV5)

✅

Expected Mean NPV = -1 425 292 USD

‣

Answer

Question 5 - Price Elasticity of rent price and apartment suppy

Assume that an apartment rents for $950 per month and at that price the landlord rents out 12 000. When the price increases to $1 000 per month, the landlord supplies 15 000 units into the market. What is the correct interpretation of the price elasticity of supply? Use the midpoint method for elasticity.

	Before 1	After 2
Quantity	12 000 (q1)	15 000 (q2)
Price	950 (p1)	1000 (p2)

Formula Price Elasticity (Midpoint)

✅

E = (0,22/0,05)% → E = 4,33%

The result indicates a 1% change in price will change the quantity by x %.

🧠

The answers are tricky E = 4,33% It’s positive, hence a decrease in price (-1%) will result in a (-4,33%) decrease in supply.

Answers

A) A 1% decrease in the rent (price) will result in a 4.33% decrease in the quantity supplied. ✅ B) A 10% decrease in the rent (price) will result in a 4.33% decrease in the quantity supplied. C) A 4.33% rise in rent (price) will result in increase in quantity supplied of 1 %. D) A 4.33% rise in rent (price) will result in increase in quantity supplied of 10 %. E) None of the above (A, B, C, D) is close to be correct.

Questions 6, 7 & 8

Q7 - Maximum arithmetic mean quarterly return

The HPI data shows quarterly international nominal house price index series (levels, not percentage returns). For each country compute the quarterly simple returns. The country that has the highest (maximum) arithmetic mean quarterly return is________.

Q9 - Find Maximum and Minimum in a huge set of data

The lowest (minimum) and highest (maximum) simple return observed among all countries and quarters is _________.

1️⃣ Find Max and Min Value 📗

=max(All Monthly return cells for all countries)

=min(All Monthly return cells for all countries)

Q8 - Find the highest pairwise correlation coefficient of quarterly returns

The highest (maximum) pairwise correlation coefficient of quarterly returns is between?

1️⃣  Create a correlation chart for the monthly returns 📗

Tryck med musen 🐁 högst upp i excel menyn → Data → Data analysis → Correlation

2️⃣ “Färglägg” bladet 🎨 

Tryck med musen 🐁 Home → Conditional Formatting → Color Scales → Välj en skala

Färgskalan gör att du enkelt kan se vilka värden som är högst och minst Du kan ta bort ettorna, men om du är färgblind är denna metod inge bra ☹️ (Kör max funktion och ctrl-f:a ) 

Question 9 & 10

Publicly listed Real Estate Investment Trusts (REITs) have been a great investment for many years. The monthly historical REITs data is downloaded from the Monthly Index Values & Returns. We now focus on the index for All Equity Reits, Total Return and Index columns. See the yellow areas in column W and X.

Q9 - Compute hypothetical Returns on monthly historical REITs data

Suppose that you invested $10 000 in January 1998. What was the value of that investment in September 2021?

1️⃣  Add column next to Return column

2️⃣  Find Row that represents returns from Jan 1998

→ Row 323 in my excelfile

3️⃣  Input 10 000 in that cell

4️⃣  Feb 1998 is computed in picture → Then drag until September 2021

✅

September 2021 → $82 022.

‣

All answers

Q10 - Calculate Geometric Mean on Returns

For the entire time period, December 1971 to September 2021, what is the monthly geometric mean return?

🧠

För använda formeln “=Geomean” behöver alla värden vara positiva - Så hur löser man det?

1️⃣  Lägg till en Column för Geo mean

2️⃣  Första värdet börjar på 100 + r → Dra ned formeln hela vägen ner till september 2021

3️⃣  Använda Geomean formeln

=GEOMEAN(V11:V607)-100

OBS 🚧  Svaret är i % - Vi tar bort 100 för vi la till 100 i steg 1)

✅

Geomean = 0,93 → 0,93%

‣

Answer

Questions 11-13 - Chi-Square stuff

Q11 - Calculate upper-tail critical Value from a Chi-Square Distribution

The upper-tail critical value from a chi-square distribution at the 0.01 level of significance is______.

Q12 - Compute chi-square test statistic based on the collected Data

Referring to the Dune table, value of the chi-square test statistic is ______.

Q13 - Calculate P-Value of Chi-square test, Given chi test statistic

The conclusion for a Chi-square test would be that ______.

Question 14 & 15

Model Selection Criteria

Q14 - Which model should you choose based on the AIC criteria?

Q15 - Which model should you choose based on the RMSE criteria?

Question 16 - Exogeneity vs Endogeneity, how does these affect covariance

In econometric modelling the assumption of strict exogeneity implies that_____.

Picture of Covariance with strict exogeneity in the middle.

🧠

Endogeneity broadly refers to situations in which an explanatory variable is correlated with the error term. Exogeneity is the opposite of endogeneity, which an explanatory term is not correlated with the error term. This means the Covariance would be 0 if it’s strict exogeneity.

← Picture of Covariance with “strict exogeneity” in the middle.

Answers

A) cov(xi, ej) = 0: there is no correlation between the omitted factors (variables) associated with the observation j and the value of the explanatory variable for observation i. ✅ B) cov(xi, ej) ≠ 0: there is positive or negative correlation between the omitted factors (variables) associated with the observation j and the value of the explanatory variable for observation i. C) cov(xi, ej) > 0: there is strict positive correlation between the omitted factors (variables) associated with the observation j and the value of the explanatory variable for observation i D) cov(xi, ej) < 0: there is strict negative correlation between the omitted factors (variables) associated with the observation j and the value of the explanatory variable for observation i E) None of the above (A, B, C, D) is correct.

Question 17, 19, 20 & 21 - Understanding regression

Q17 - Compute R-Square for the model

The R square for this model is closest to _________.

Q18 - Compute the Standard Error for the regression model

The standard error of the regression is closest to _________.

1️⃣  Compute the Residual Error

Residual: 642-409 = 232

Residual Error = 232

2️⃣  Compute SSE/df

df = observations - Regression - 1 df = 2160 - 6

SSE: 232/2154 = 0,107

3️⃣ Compute the standard Error

Standard Error: Sqrt(SSE/df) Sqrt(0,107) = 0,32

✅

Sqrt(0,107) = 0,32

‣

Answer

Q19 - How to compute t-statistic for linear model

The t-statistic for the ln(SQFT) (see ln_sqft) is closest to_________.

Q20 - Confidence interval for a variable in a linear model

The 95% confidence interval for β5 is closest to__________.

Q21 - How to interpret a linear model

Which of the following interpretations of the coefficient of WATERFRONT is most correct? A) A waterfront house in on average 1.35 times more expensive than not waterfront houses. B) A waterfront house in on average 13.5 % more expensive than not waterfront houses. C) A waterfront house in on average 13.5 % more expensive than traditional houses. D) A waterfront house in on average 135 % more expensive than not waterfront houses. E) None of the above (A, B, C, D) is close to be correct.