Question
I need all of this created in a new excel file. I do not know how to complete all of this. 1. Start by downloading
I need all of this created in a new excel file. I do not know how to complete all of this.
1. Start by downloading monthly data for Microsoft and the SP500. Start the data in January of 2014 through the latest month. (Please note the start and end dates.) Delete all but the appropriate data for calculating returns leaving three columns labeled: Date, MSFT and SP500.
2. To the right of those columns, calculate the arithmetic and geometric monthly returns for both MSFT and the SP500. Label appropriately, MSFTA, SP500A, MSFTG, SP500G.
3. Give yourself some space above those columns and calculate the monthly averages and standard deviations for those returns.
4. I want you to create a simple pivot table for the total returns for each year. Put the pivot table in a tab of its own named Pivot. You will need to create a variable in the original data that will allow you to tabulate the returns by year.
5. In the pivot table, put years in the left hand column and calculate the total return for each year. (Note, I did not say average.)
6. Go back to the original data and use it to calculate the beta for MSFT. Put the beta in a new tab called Beta.
7. Below the beta estimate, calculate the expected return for the market using the following data. Label your answer Rm and put it close to the data so I know where to find it.ScenarioprobExp Returnriots0.1-0.33slow growth0.3-0.05medium growth0.50.09high growth0.10.18
8. You know the t-bill rate is .001, so you have all the information needed to use the CAPM to calculate the expected return for MSFT. Please put this calculation to the right of the table we used above.
9. To finish up, lets do a simple time value of money calculation. You should have room below the last two questions to do the PV, FV, Rate, etc. table as I showed you in class. How many years would it take you to get to $5,000,000 if you saved $500 monthly in an investment yielding 7%?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started