Company: Royal Bank of Canada Capital Asset Pricing Model (CAPM) Format: PDF version of Excel spreadsheet for CAPM (see link above) Grading Rubric: Your submission will be marked by your peers based on the following categories (equal weighting for each): Data (inclusion and accuracy), Sources (provided and clear), Calculations (properly done), Documentation (formulas and sample calculations provided for the calculations), Graph (properly oriented, labelled, and includes best-fit line) and Presentation. In this section you will calculate the firm's expected rate of return using the capital asset pricing model. You will first need to calculate your company's beta and then use that in the CAPM formula to get the expected rate of return. Data Required: Monthly closing stock prices (in Canadian dollars) for your company for the period January 1, 2020 to December 31, 2023. A good source is Yahoo Finance Canada, as it allows you to search and download the entire period at once. You can specify the date range, choose monthly prices, and download the information Use the Close price rather than the Adjusted Close price. NOTE: Yahoo Finance provides Open-High-Low-Close prices for each month and lists the date as the first trading day of the month. The closing price is from the last trading day of the month. Monthly closing prices for the S&P/TSX Composite Index for the period January 1, 2020 to December 31, 2023. A good source is Yahoo Finance Canada. The company symbol will be ^GSPTSE. You can also click on S&P/TSX to get to the page. Yahoo Finance may be missing some of the data when you download monthly prices. You will need to look at the daily prices to fill in the missing values. The yield on a 3-month Canada Treasury Bill for December 29, 2023. You will find this at the Bank of Canada. From the dropdown menu under "Statistics", choose "interest Rates", then choose "Treasury Bill Yields". Click on "Look up the past ten years of data" for these series. Select your date and choose Treasury Bills, 3 Month, Daily. You will be given the yield as a percentage. Calculations: Calculate each of the monthly returns for your stock over the 4 years from January 2020 to December 2023 (i.e., percentage change in price from month end to month end). Calculate each of the monthly returns for the S&P/TSX Composite Index over the same 4-year period. Create a scatter plot using Excel that shows the returns on your company's stock and the returns on the market index. Each point will represent one month (see Figure 12.2 in your text). Plot the characteristic line on the graph (the trendline). Make sure to label the axes. Calculate the standard deviation of your company's returns, the standard deviation of S&P/TSX returns, and the correlation coefficient of S&P/TSX and company returns. Calculate beta as the slope of the characteristic line on your graph (see a sample spreadsheet in section 12.1 of your text). Using the value of beta that you calculated, the December 29, 2023 yield on a three-month treasury bill for the risk-free rate, and 7 percent as the market risk premium (the average market risk premium over the last 90 years), calculate the expected rate of return based on the capital asset pricing model. Report the data and results for these two sets of calculations on the CAPM Template. Make sure you include sources for your data and show the formulas you used (using variable names) as well as the calculations (using your numbers - note for the return calculation you only need to show one sample calculation). You should show the excel formulas that you used for standard deviation, correlation, and beta. Include a graph of your data and make sure you label the axes. You will have to convert your document to Portable Document Format (PDF) before you submit it to PEAR (a screenshot is not acceptable). This ensures that everyone will be able to access and read it. Make sure you check your file after the conversion - the conversion has the same effect as printing the document and the results are not always what you expect. You may find that you have to go back and adjust your formatting. Make sure you check the formatting before you submit to PEAR. It is your responsibility to make sure that you have properly uploaded the correct file to PEAR. If you are having technical difficulties, you will need to contact CourseLink support. Weighted Average Cost of Capital (WACC) Format: PDF version of Excel spreadsheet for WACC (see link above) In this section you will calculate a return on debt for your firm and use that with the expected returns on equity that you calculated in the DDM and CAPM parts above to calculate a weighted average cost of capital. You will get three different values, one for each method you used to calculate the cost of equity. Data Required: Most recently available annual financial statement information: Long-Term Debt and Number of Shares Outstanding. These can be found at Yahoo Finance Canada, the Toronto Stock Exchange, SEDAR, or on the company's website. Your company's debt rating for Senior Debt. Sometimes this will be listed on the company's website. Some other sites to check are Moody's (moodys.com), Fitch Ratings (fitchratings.com), or S&P Global (standardandpoors.com). These sites may require registration, but they are free. A google search may also turn up the debt rating. The yield on a 10-year Government of Canada Bond for December 29, 2023. You will find this at the Bank of Canada. The closing stock price for your company on the date of the annual report. Calculations: To get the yield on the firm's debt, assume that the credit spread (i.e., the extra yield over the equivalent term government bond) is as given in Ratings, Interest Coverage Ratios, and Default Spread data Link opens in a new window. Add the appropriate spread for your company's debt rating to the yield on 10-year Government of Canada bonds on December 29, 2023. If you cannot find a debt rating for your company, you can assume that it has a BBB rating. Calculate the values of debt, equity, and the firm. Use the value of long-term debt from the most recent statement of financial position for the value of debt. Calculate the value of equity using the number of shares outstanding and the actual price from the date of the most recent annual report. The value of the firm will be the sum of the debt and equity (ignore any preferred stock). Also calculate the proportions of debt and equity to make it easier to check your WACC calculations. Calculate the weighted average cost of capital (WACC) for your firm three ways. Once using the expected rate of return on equity from the constant-growth dividend discount model - historical growth, Once using the expected rate of return on equity from the constant-growth dividend discount model - sustainable growth, and Once using the expected rate of return on equity from the capital asset pricing model (CAPM). Use the book value of long-term debt and the market value of equity and assume your company has a 26 percent corporate tax rate. Report the data and results for these two sets of calculations on the WACC Template. Make sure you include sources for your data and show the formulas you used (using variable names) as well as the calculations (using your numbers). Also make sure that you report the values for expected rate of return on equity that you determined from the previous parts of this project. You will have to convert your document to Portable Document Format (PDF) before you submit it to PEAR (a screenshot is not acceptable). This ensures that everyone will be able to access and read it. Make sure you check your file after the conversion - the conversion has the same effect as printing the document and the results are not always what you expect. You may find that you have to go back and adjust your formatting. Make sure you check the formatting before you submit to PEAR. It is your responsibility to make sure you have properly uploaded the correct file to PEAR. If you are having technical difficulties, you will need to contact CourseLink support. Summary Memo Format: PDF document Grading Rubric: Your memo will be marked by your peers based on the following categories: Content (does it include the 3 rates of return, 3 WACC values, and a recommended return), Purpose (does it explain why this work has been done), Explanation (does it explain why the particular return has been chosen), and Writing (well-organized, clear, free of grammar and spelling errors). Write a one-page (at most) memo to your boss reporting your findings. Include an opening segment that states the problem and purpose of your memo (your boss receives lots of memos and needs to know what this one is about). Make sure you state the name of the company you have analyzed and its industry. Provide the three expected rates of return that you calculated for your company. Provide the three weighted-average costs of capital that you calculated. Provide a recommendation for what discount rate your company should use in evaluating their proposed investment. Provide a brief explanation of why you are recommending that rate. There is no correct answer to what you should recommend for a discount rate. You should, however, provide a good explanation for why you are recommending the rate you have chosen.