06 Jul Due: 6 hoursMid-Term ProjectFRL 383 – Summer 2018Excel spread

Due: 6 hoursMid-Term ProjectFRL 383 – Summer 2018Excel spreadsheets named “383-MTProject-Lastname1-Lastname2” (send only one file; do NOT send additional word files, use e.g. textbox for written answers) due by email by date/time above. Also due by same deadline, by email, evaluation of team member(s), by email, out of 10. Especially if <10, give reason. Evaluation will remain confidential. The evaluation will add another 10 points to your score (will not be revealed). If you do not send this evaluation, you will get -10 points for the evaluation.80 points = 70 points (project) + 10 points (evaluation, will not be revealed).No need to annotate, since I will have electronic copies, but make sure your layout is neat and logically arranged [5 pts]Project: 65 points +5 points for layout1. [5 pts] Download the monthly Case-Shiller house price index for Los Angeles from the St. Louis Fed (FRED II). Choose the version that is not seasonally adjusted, and get all data, from about 1987 through the last available data point.2. [5 pts] Compute the monthly appreciation r tt+1 = (H t+1 – H t)/ H t. Extra points is you can show that the resulting time series is stationary.3. [15 pts] Model the change in appreciation as an Orenstein-Uhlenbeck process (discretized version)Δrt+1 = r tt+1 – r t = κ (ϑ – r t) Δt + σ Δt1/2 εwhere ε is a normally distributed variable with mean 0 and variance 1.Hints: a. Do this in excel, by adding in the Analysis Tool pack from File>Options>Add-Ins. Use OLS regression, with y= r ttt+1 – r t and x = r t Δt, and identify the unknown parameters above (κ, and ϑ) from your linear regression result.b. To compute σ, ask the regression to give you the residuals, find their standard deviation, and then divide the answer by Δt1/2c. Since Δt = (1/12) years (monthly), your parameters will be annual numbers.4. [15 pts] Create a set of 10,000 simulations of a monthly house price for 10 years using the equation and parameters in 3 above. Assume the starting price is $800,000.Hints:a. Since you do not know the appreciation before t=0, “warm up” the data generating process with 1 years’ worth of appreciation before t=0 (you can start the warmup with r=0).b. You can use either VBA, or simulate directly in the spreadsheet. When testing your work, use a smaller time frame (say 1 year) and fewer Monte Carlo simulations (say 100): 10 years’ worth of 10,000 simulations may take a while on your computer.c. ε is a normal random draw with mean 0 and variance 1. To generate such a random draw, use the function norm.inv(rand(),0,1). Rand() generates a uniform random number between 0 and 1 (a random probability), and the function then returns a random draw from the normal distribution with mean 0 and variance 1.d. Since every alteration to your spreadsheet will re-compute the random variable (potentially changing your results slightly), I suggest you switch off the automatic workbook calculation by going to file>options>formulas and selecting “manual.” To re-compute the workbook, you MUST then press “control =” or F9 to update formulae… don’t forget this. Also, best to set back to “automatic” when done with project. Apples may be different.5. [20 pts] Suppose you wanted to buy this home with 10% down, and, to lower mortgage costs, you chose to get unison.com as an equity investor to provide another 10% (for a total of 20% down and 80% mortgage). From your results in 4, create a frequency distribution of Unison’s return, compute their expected return and standard deviation of returns, and estimate the probability that Unison will lose money. Do this for the cases that the homeowner will pay back Unison after 1, 2, …,10 years (i.e., only at yearly anniversaries). 6. [5 pts] Why do you think point.com computes appreciation from a “risk adjusted basis,” that is smaller than the value of the home at origination, rather than the purchase price/valuation at origination?