Homework answers / question archive / Project 1: Risk-return Tradeoff Due: 3/25/2023 11:59 PM Note: Please submit your work individually to the BeachBoard Dropbox – this is an individual assignment

Project 1: Risk-return Tradeoff Due: 3/25/2023 11:59 PM Note: Please submit your work individually to the BeachBoard Dropbox – this is an individual assignment

Finance

Share With

Project 1: Risk-return Tradeoff Due: 3/25/2023 11:59 PM Note: Please submit your work individually to the BeachBoard Dropbox – this is an individual assignment. Instructions: 1. Download the spreadsheet “Project1-Data_v2.xlsx” from the BeachBoard Class. The spreadsheet contains monthly return data for five asset classes: Small Stocks (SS), Large Stocks (LS), Long-Term U.S. Government Bonds(LT), Intermediate-Term U.S. Government Bonds (IT), and 30-Day U.S. Treasury Bills (TB) from 1925 to 2020. 2. Plot the below graph from our lecture slides “L2 Brief History of Risk and Return”: a) The graph shows how much $1 would be worth if you followed the buy-and-hold investment strategy from 1925 to 2000 for the major asset classes. The plot ends in 2000, but you are given data for extra 20 years – until 2020. Your task is to plot the above graph while extending the period to the year 2020 (i.e., plot the above graph to include extra 20 years) for the five asset classes in the dataset. b) In the spreadsheet, I have already computed the values for SS (small stocks) in Column I (cells filled with green). Examine how I created the column and do the same for the rest of the asset classes. c) After you compute the dollar values, plot them into one graph. d) Note that you must convert the y-axis into a logarithmic scale1 . First, click the y-axis of the graph, then select the “Format Axis” menu. The screenshot below shows how you can do this. 1 A logarithmic scale (or log scale) allows us to show data over a wide range of values in a compact manner. Page 2 of 2 3. For all five asset classes, compute over the entire 1926-2020 period: a) Arithmetic average returns (description of expected returns … use the Excel function AVERAGE) b) Geometric average returns (description of past returns … use the Excel function GEOMEAN). Hint: Please see Columns M and O of the spreadsheet “Project1- Data_compute_geomean.xlsx”. c) Standard deviations of returns (use the Excel function STDEV.S) 4. (OPTIONAL) Over the entire 1926-2020 period, compute the covariances, ???(?, ?), using the Excel function COVARIANCE.S for all possible pairs of asset classes. In other words, fill in the covariances in the table below: • Note that this table is called the variance-covariance matrix. • Also, note that ???(?, ?) = ???(?, ?), and ???(?, ?) = ???(?, ?) • ??? denotes covariance, and ??? denotes variance.