Gojar Punjab
Budget: $30 – $250 USD
On the designated spreadsheet, list the name of each of the 10 stocks that you will be following this
semester and the ticker.
• From Yahoo, pull the month ending prices for two of your stocks for the past five years. When on
the main homepage for each stock in Yahoo, you’ll see a “Historical Prices” tab on the left side. Use
that and run the monthly prices for the period 12/31/16-12/31/21. For the sake of this calculation,
we can just use prices and ignore the dividends for simplicity. (Note: I was noticing when I did this,
it was running it as of the first business day of the next month, so if that is the case, use the “open”
price for the first business day of the next month; otherwise, if it is the correct month end date, use
the “close” price. Keep in mind that markets are not open on weekends or holidays, so you may see
some days being skipped). There is a tab on the bottom that coverts the prices to a spreadsheet
(“Download to Spreadsheet”) and you can use that to copy onto the spreadsheet that I have sent
you. Please note the name of the stock you are using for each column.
• On the spreadsheet provided, use those month ending prices to calculate a monthly performance
percentage.
• Use the beginning period price (price as of 12/31/16) and the end of the period price (12/31/21) to
calculate a simple holding period return for your two stocks.
• Calculate the arithmetic average annual return for the five-year period for the S&P 500 and the two
profiled stocks.
• Link the monthly percentages to calculate a cumulative performance number for the five-year
period. Use the “compound return” equation that we covered in class. Calculate this return for
each of the S&P Index based on the data provided and the two stocks you have selected.
• Use the cumulative compound return to calculate the “compound annualized return” for the five-
year period, per the equation that we covered in class. Calculate this return for each of the S&P
Index based on the data provided and the two stocks you have selected.
• Then using those performance percentages, calculate the “beta” per the equation that we discussed
in class. Calculate a beta for each of the S&P 500 Index and the two stocks that you have selected.
Rows have been provided for you to calculate the necessary “Covariance” and “Variance” (there are
Excel functions that calculate this for you) that will be needed for this final beta calculation.
• Assuming a risk free rate of 3% and expected market return of 8%, calculate your risk premium and
required rate of return for each of the two stocks
semester and the ticker.
• From Yahoo, pull the month ending prices for two of your stocks for the past five years. When on
the main homepage for each stock in Yahoo, you’ll see a “Historical Prices” tab on the left side. Use
that and run the monthly prices for the period 12/31/16-12/31/21. For the sake of this calculation,
we can just use prices and ignore the dividends for simplicity. (Note: I was noticing when I did this,
it was running it as of the first business day of the next month, so if that is the case, use the “open”
price for the first business day of the next month; otherwise, if it is the correct month end date, use
the “close” price. Keep in mind that markets are not open on weekends or holidays, so you may see
some days being skipped). There is a tab on the bottom that coverts the prices to a spreadsheet
(“Download to Spreadsheet”) and you can use that to copy onto the spreadsheet that I have sent
you. Please note the name of the stock you are using for each column.
• On the spreadsheet provided, use those month ending prices to calculate a monthly performance
percentage.
• Use the beginning period price (price as of 12/31/16) and the end of the period price (12/31/21) to
calculate a simple holding period return for your two stocks.
• Calculate the arithmetic average annual return for the five-year period for the S&P 500 and the two
profiled stocks.
• Link the monthly percentages to calculate a cumulative performance number for the five-year
period. Use the “compound return” equation that we covered in class. Calculate this return for
each of the S&P Index based on the data provided and the two stocks you have selected.
• Use the cumulative compound return to calculate the “compound annualized return” for the five-
year period, per the equation that we covered in class. Calculate this return for each of the S&P
Index based on the data provided and the two stocks you have selected.
• Then using those performance percentages, calculate the “beta” per the equation that we discussed
in class. Calculate a beta for each of the S&P 500 Index and the two stocks that you have selected.
Rows have been provided for you to calculate the necessary “Covariance” and “Variance” (there are
Excel functions that calculate this for you) that will be needed for this final beta calculation.
• Assuming a risk free rate of 3% and expected market return of 8%, calculate your risk premium and
required rate of return for each of the two stocks