Question 1: [Cash Flow Analysis] Pat is the type to plan everything in advance,

Question 1: [Cash Flow Analysis]
Pat is the type to plan everything in advance, and she is already starting to plan for retire-
ment. She plans to work for 40 years, and then retire for the following 30 years. She expects
to spend $120,000 in your first year of retirement, with a 4% growth rate. She will increase
her working-years savings rate by 7.5% per year. While working, she expects her account
will have a 6.75% return, and during retirement it will have a 4.75% return. After the 30th
year of retirement, she wants to have $600,000 in her account for safety net and bequest
reasons (It is fine if the amount is not exactly $600,000 due to rounding error). To solve the
problem, determine how much Pat needs to save in year 1 to accomplish her retirement goals.
——————————————————————————————————————————–
Question 2: [Regression Exercise]
Use monthly total returns (File: return data.xlsx) for the S&P 500 index, Xcel Energy
(XEL), NVIDIA (NVDA), Bank of America (BAC), and Tesco PLC (TSCO) from July 2012
through June 2017.
A) Create an XY (Scatter) plot to show the relationship between the returns on each
individual stock and the S&P 500 (SPY). Estimate the slope of a regression equation
of this data.
B) How to interprete the coefficients you produced? What is the economic intuition
behind those numbers?
C) Add a linear trend line to each chart, and place the equation and R2 on each chart.
How much of the variability in each stock’s returns can be explained by variability in
the S&P 500?
D) Repeat part a) and c) with an equally weighted portfolio that includes all of these
stocks. Use the arithmetic average of the monthly returns of each stock as the expected
return in your portfolio. Do you see any improvements in the R-square?
E) Change the sample period to 2020.1.2 – 2020.12.31 (which is under the impact of COVID pandemic). Produce the time series plot of daily stock return of each individual stock above and your equally weighted portfolio.
F) Re-estimate your portfolio beta, βp, using daily data for the new sample period of2020.1.2 – 2020.12.31 (Let’s assume the risk free rate Rf is 0.1%)†.
(I encourage you to use Excel to work on this exercise. We will work on this type of
problem using Python later. If you prefer using languages or softwares, turn in the code and
output)
——————————————————————————————————————————————-
Please answer the questions above and make sure to read each part carefully, if you could use excel for some of the questions that require it i would highly appreciate it.