I’m working on a finance spreadsheet and need an explanation to help me learn.
At the beginning of this class I asked you to follow
- a stock,
- a bond mutual fund,
- and the Vanguard Total Stock Market Index Fund Admiral Shares (Ticker VTSAX),
And put in a spreadsheet, record the prices, dividends on every business day for these three assets beginning Monday April 5th and ending May 21th, 2021.
Using these daily data calculate the following:
- The daily returns of these three assets.
- The average return and standard deviation for these three assets.
- The correlations between these three assets.
- What is the future value for each of these assets at the end of the day on May 21st if you had $100 invested in each asset on April 5th?
- What is the FV and daily returns of a portfolio that placed 70% in VTSAX and 30% in your bond mutual fund. What is the average return and standard deviation of this portfolio?
- What is the FV and daily returns of a portfolio that placed 70% in your stock and 30% in your bond mutual fund. What is the average return and standard deviation of this portfolio?
- What is the beta of your stock and the bond fund? Use the VTSAX as your proxy for the market.
- What is the expected return on the stock for the company you are following? Use reasonable estimates of the variables that enter the CAPM.
- What is the WACC for the company you chose to follow?
Deliverable: Provide me with a well-organized spreadsheet that puts the answers to all of the questions above in one place (e.g. the first sheet in the upper left corner), while allowing me to see where the calculations, and data used to make them, are in your spreadsheet. Part of your score will reflect the clarity presentation of your results in the spreadsheet.