# Average Monthly Return for Stock Assignment | Homework For You

Can you show the work for number 5 and 6 and what it should look like in excel?

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.

This is the whole question:

1. First download into Excel the APPLE (AAPL), Microsoft (MSFT), and S&P 500 Stock Index (^GSPC) monthly price data for the 5-year period from the Feb. 1, 2015 to Jan. 31, 2020 using the instructions outlined above. Using the Open and Close price data for each month (the date given is the first trading day for that month: i.e. the open and close for the month) to calculate the monthly percentage holding period returns for each stock and index where each month’s Open price is the beginning of the month price and each month’s Close price is the end of the month price. You can get rid of the other data provided for each month. In addition, you will need to include dividend payments for each stock to calculate monthly returns for the months that they made a dividend payment (the index doesn’t have dividend payments). For example, September’s monthly return is (Sept Close – Sept. Open + any Sept Div)/Sept Open. You should have 60 monthly returns for each stock and index from Feb. 1, 2015 to Jan. 31, 2020. Helpful Hint: I strongly urge creating a table of the appropriate opening & closing prices and dividends for each stock along with opening and closing prices for the S&P 500 Index on a separate worksheet and then do your calculations on this “fresh” worksheet.

2. What is the average monthly return for each stock and the S&P 500 index over the entire time period? Convert the average monthly return for each stock and index into an APR (annualized rate) by multiplying your monthly average for each stock by 12. Please convert your answers to percentages

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.

3. Calculate the (population) standard deviation of monthly returns for each stock and the index using Excel.

4. Now calculate the beta for each stock using Excel’s Slope or Linest function to use estimate the slope of the linear regression line, which is beta. Use the S&P 500 index as the market portfolio in finding beta.

5. Now use the CAPM/SML Equation to estimate each stock’s required return. Use 10% as the required market return and the recent 1-year Treasury rate of 1% as the risk-free rate. Would you recommend buying APPLE and/or Microsoft if your APR average for each stock from #2 is your expected return? Explain your answer.

6. Construct a graph of the Security Market Line given by the parameters in #5. Point out the SML returns you calculated in #5 on your SML. Also, find and plot APPLE’s and Microsoft’s expected returns from the information given below gathered from their recent Yahoo Finance stock quotes against each stock’s beta on your SML graph. Would you recommend buying APPLE and/or Microsoft based on this information? Explain your answers. Note: you are evaluating each stock individually, not in comparison in one another.

 Stock Apple Microsoft Current Price 292.92 166.27 Estimated Price 1 year from now 332.91 194.19 Annual Expected Dividend (D1) 3.08 2.04

Get Finance homework help today

Calculate the price
Pages (550 words)
\$0.00
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with Homework Writing Services
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
Testimonials
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
Psychology
Outstanding job! Really happy about it. I'm impressed!
Customer 463863, February 24th, 2023
Perfect
Customer 463337, April 18th, 2023
Education
Thank you. Well written
Customer 463647, February 3rd, 2023
SEO
you captured my imagination. good copy writing skills
Customer 452441, June 17th, 2022
Good work,
Customer 462901, April 27th, 2022
English 101
I wish the writer would have picked a more natural topic. Although the work is good, it is obvious that the writer of the material is African American. The fact that I am not African American raises questions.
Customer 462899, April 26th, 2022
Other
Good job.
Customer 459155, May 31st, 2022
project management
thank you
Customer 462327, November 16th, 2022
IT, Web
The new writer was amazing and followed the instructions perfectly! Thank you so much for tackling all requirements on short notice and a HUGE thank you to the support team for remaining in contact with me until a resolution was received.
Customer 452455, January 13th, 2023
Military
Good work.
Customer 456821, May 11th, 2022
Nursing
yes
Customer 452577, January 27th, 2020
Military
good job
Customer 456821, September 1st, 2022
11,595
Customer reviews in total
96%
Current satisfaction rate
3 pages
Average paper length
37%
Customers referred by a friend