Copy of Lagusay_Smart_Zutter_Problem_5

.xlsx

School

Northern Alberta Institute of Technology *

*We aren’t endorsed by this school

Course

FNCE-331

Subject

Finance

Date

Apr 3, 2024

Type

xlsx

Pages

Uploaded by JusticeOryx2071

Spreadsheet Exercise: Problem 5.35 To Do Create a spreadsheet to model and analyze the distribution of two secu a. Estimate the beta values for INTC and PG based on the data from t b. Using the CAPM, determine the required rates of return for both IN c. Solution a. Estimate the beta values for INTC and PG based on the data from t Katie plans to form a portfolio consisting of two securities, Intel (INTC amount that she invests in each stock. Katie’s professor suggests that s r j = r f Katie measures r f using the current long-term Treasury bond return of 3 that figure to measure r m . Complete the steps below using cell references to given data or previou down a column, an absolute cell reference or a mixed cell reference m type in numerical data into a cell or function. Instead, make a referenc cases, unless otherwise directed, use the earliest appearance of the dat Katie has decided that the portfolio will be distributed between INT and betas of the portfolio. Determine the weighted average required -20.0% -15.0% -10.0% -5.0% 0.0% 5.0% 10.0% 15. -10.0% -5.0% 0.0% 5.0% 10.0% 15.0% 20.0% 25.0% f(x) = − 0.119451918168832 x + 0.007157233989827 f(x) = − 0.161770919471419 x + 0.011431814805318 Stock Return, %.

Beta for INTC -0.185 Beta for PG -0.1195 b. Using the CAPM, determine the required rates of return for both IN Risk-free rate 3% Expected market return 11% Required rate of return for INTC 2% Required rate of return for PG 2.0% c. Weight of INTC in the portfolio 60% Weight of PG in the portfolio 40% Weighted average required rate of return 2% Katie has decided that the portfolio will be distributed between INT and betas of the portfolio. Determine the weighted average required war where war = weighted average required rate of retur w i = weight of security i in the portfolio r i = required return of security i in the portfol w j = weight of security j in the portfolio r j = required return of security j in the portfol wab where wab = weighted average beta for the portfolio w i = weight of security i in the portfolio b i = beta for security i w j = weight of security j in the portfolio b j = beta for security j -15.0% Column D Linear (Column D) Column E Market Return, %

Weighted average beta -0.159 2/1/2017 -1.7% 3/1/2017 0.4% 4/1/2017 0.2% 5/1/2017 -0.1% 6/1/2017 -5.9% 7/1/2017 5.1% 8/1/2017 -1.1% 9/1/2017 9.4% 10/1/2017 19.5% 11/1/2017 -1.4% 12/1/2017 3.6% 1/1/2018 4.3% 2/1/2018 2.4% 3/1/2018 6.4% 4/1/2018 -0.9% 5/1/2018 6.9% 6/1/2018 -9.4% 7/1/2018 -3.2% 8/1/2018 0.7% 9/1/2018 -1.8% 10/1/2018 -0.9% 11/1/2018 5.2% 12/1/2018 -4.2% 1/1/2019 0.4% 2/1/2019 9.1% Requirements Stock Return, % Intel (INTC)

1 2 Note: Compare calculated beta value with that obtained in the equa 3 Note: Compare calculated beta value with that obtained in the equa 4 5 6 7 9 Save the workbook. Close the workbook and then exit Excel. Subm In cells C15:E29 , insert a Scatter Chart to estimate betas for Intel ( Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Selecting Data Series Then in Select Data Source window, delete any series created autom Add new series for the security market line using cells F50:F74 for Add new series for the stocks from your portfolio using cells F50:F Trendline Options Select any point of the security market line and right click on it. Ch name. Choose Series1. Select any point of the trendline and right click on it. Choose Form Select any point of the security market line and right click on it. Ch name. Choose Series2. Select any point of the trendline and right click on it. Choose Form Edit Chart Elements Add axis titles. Replace Axis Title for the horizontal axis with Mar In cell D31 , by using cell references and the function SLOPE , calc In cell D32 , by using cell references and the function SLOPE , calc In cell D38 , by using cell references, calculate the required rate of r In cell D39 , by using cell references, calculate the required rate of r In cell D45 , by using cell references, calculate the weighted averag In cell D46 , by using cell references, calculate the weighted averag

urities in the portfolio: the table far below using the graphic method and Excel SLOPE function. NTC and PG. the table far below using the graphic method and Excel SLOPE function. C) and Procter& Gamble (PG), and she wonders how the portfolio’s return will depend she use the capital asset pricing model to define the required returns for the two compan + [b j × (r m - r f )] 3%. Katie determines that the average return on the S&P 500 Index over the last severa us calculations. In some cases, a simple cell reference is all you need. To copy/paste a may be preferred. If a specific Excel function is to be used, the directions will specify the ce to the cell in which the data is found. Make your computations only in the blue cells ta in your formulas, usually the Given Data section . TC and PG in a 60% and 40% split, respectively. Hence, a weighted average can be cal d rate of return and the weighted average beta for the portfolio. .0% 20.0% 25.0%

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version