a) Choose 4 different publicly traded stocks. Assume that you had a total of $10,000 to invest (so invest your funds as close to $10,000 as possible) b) Allocate the funds as you see fit between the 4 stocks based on prices 25 months ago C) Go to Yahoo Finance and download the prices on a monthly basis for the last 25 months for each stock. Use the first price as your purchase price. Use the adjusted close prices. d) Calculate the initial value of your investment = No of Shares x Price e) Calculate each of the stocks weight based on initial investment value. So for stock A weight would be = value in $ of A/Total porfolio value in$ f) Calculate monthly returns for each stock. You should have 24 Returns. R= (P2-P1)/P1. Calculate expected monthly return of your stock = the average of your returns and then mutiply by 12 to anualize your returns. Add your results to colunmf g) Calculate the risk of the monthly returns by calculating the std deviation (one value) anualize by multiplying the result by the Square root of 12. (excel function=SQRTI. ).) h) Using your annual expecected returns and portfolio weights, calclate the weighted expected return of your portfolio i) Obain the Betas for each stock in Yahoo Finance - These are available in the first sheet that you see when you get the information for your stock. 1) Assume that the risk free rate is 1.99% (You can actually look for the updated value in Yahoo Finance using the Ticker TNX which will give you the rate for the 10 year treasury bond.) and assume that the market return is 8%. Using that information plus the Betas of each stock, calculate the return of each stoch using the CAPM k) Calculate the weighted expected return of your portfolio using the Expected return calculations that you did Using the CAPM. 1) Calculate the portfolio Beta m) Add your sales price (The most recent price from your analysis) n) Calculate your value by multiplying your number of shares x sales price o) Calculate your final return Stock Porfolio a b d h Portfolio Weights (Based on value) Risk Individual Weighted individual Stocks (Std Dev Portfolio Stock Returns % %) Return k Expected Weighted Return Portfolio Individual Returns Stocks Using Stock Betas using CAPM CAPM m n O Final Return (Individual and Sale Price Sales Value portfolio) Company Name No Shares Purchase Price value(s) Total M N S Company Name Company Name LE G H 1 Add your stock information here. Reference your results to the table in the instructions page 2 3 4 Company Name Company Name 5 6 Montly Expected Return (Return Average) Montly Expected Return (Return Average) 7 Yearly Return = Monthly x 12 Yearly Return Monthly x 12 8 Monithy Std Deviation Monlthy Std Deviation 9 Anualized = Montihy Stdev xSqrt(12) Anualized = Montlhy Stdev x Sqrt(12) 10 Stock Beta Stock Beta 11 12 Adjusted Adjusted 13 Date Close Return Calculation Date Close Return Calculation Montly Expected Return (Return Average) Yearly Return = Monthly x 12 Monlthy Std Deviation Anualized = Montlhy Stdev x Sort(12) Stock Beta Montly Expected Return (Return Average) Yearly Return = Monthly x 12 Monlthy Std Deviation Anualized = Montlhy Stdev xSqrt(12) Stock Beta Adjusted Close Adjusted Close Date Return Calculation Date Return Calculation 14 15 a) Choose 4 different publicly traded stocks. Assume that you had a total of $10,000 to invest (so invest your funds as close to $10,000 as possible) b) Allocate the funds as you see fit between the 4 stocks based on prices 25 months ago C) Go to Yahoo Finance and download the prices on a monthly basis for the last 25 months for each stock. Use the first price as your purchase price. Use the adjusted close prices. d) Calculate the initial value of your investment = No of Shares x Price e) Calculate each of the stocks weight based on initial investment value. So for stock A weight would be = value in $ of A/Total porfolio value in$ f) Calculate monthly returns for each stock. You should have 24 Returns. R= (P2-P1)/P1. Calculate expected monthly return of your stock = the average of your returns and then mutiply by 12 to anualize your returns. Add your results to colunmf g) Calculate the risk of the monthly returns by calculating the std deviation (one value) anualize by multiplying the result by the Square root of 12. (excel function=SQRTI. ).) h) Using your annual expecected returns and portfolio weights, calclate the weighted expected return of your portfolio i) Obain the Betas for each stock in Yahoo Finance - These are available in the first sheet that you see when you get the information for your stock. 1) Assume that the risk free rate is 1.99% (You can actually look for the updated value in Yahoo Finance using the Ticker TNX which will give you the rate for the 10 year treasury bond.) and assume that the market return is 8%. Using that information plus the Betas of each stock, calculate the return of each stoch using the CAPM k) Calculate the weighted expected return of your portfolio using the Expected return calculations that you did Using the CAPM. 1) Calculate the portfolio Beta m) Add your sales price (The most recent price from your analysis) n) Calculate your value by multiplying your number of shares x sales price o) Calculate your final return Stock Porfolio a b d h Portfolio Weights (Based on value) Risk Individual Weighted individual Stocks (Std Dev Portfolio Stock Returns % %) Return k Expected Weighted Return Portfolio Individual Returns Stocks Using Stock Betas using CAPM CAPM m n O Final Return (Individual and Sale Price Sales Value portfolio) Company Name No Shares Purchase Price value(s) Total M N S Company Name Company Name LE G H 1 Add your stock information here. Reference your results to the table in the instructions page 2 3 4 Company Name Company Name 5 6 Montly Expected Return (Return Average) Montly Expected Return (Return Average) 7 Yearly Return = Monthly x 12 Yearly Return Monthly x 12 8 Monithy Std Deviation Monlthy Std Deviation 9 Anualized = Montihy Stdev xSqrt(12) Anualized = Montlhy Stdev x Sqrt(12) 10 Stock Beta Stock Beta 11 12 Adjusted Adjusted 13 Date Close Return Calculation Date Close Return Calculation Montly Expected Return (Return Average) Yearly Return = Monthly x 12 Monlthy Std Deviation Anualized = Montlhy Stdev x Sort(12) Stock Beta Montly Expected Return (Return Average) Yearly Return = Monthly x 12 Monlthy Std Deviation Anualized = Montlhy Stdev xSqrt(12) Stock Beta Adjusted Close Adjusted Close Date Return Calculation Date Return Calculation 14 15