There are three sheets in the EXCEL file. |
Complete the problems in each excel sheet, all calculations must be done within the excel file and show your calculation in order to receive credit. |
Must enter your final answers in the designated BOX to receive FULL credit. |
This is an individual project so each student must submit: |
a hard copy of of completed excel work (including graphs) in class by 10/5/2017; |
AND an e-copy of the completed excel file via blackboard by 10/5/2017 at 2:00pm. Checking your e-copy is for potential partial credits. |
1. Consider the table below that summarizes the probability distributions of the two risky funds, a stock fund and a bond fund: | |||||
Stock Fund | Bond Fund | Expected return | Variance | ||
Scenario | Probability | Return | Return | of Stock Fund | of Stock Fund |
Severe recession | 0.05 | -43% | -12% | -0.0215 | 0.00834 |
Mild resession | 0.25 | -17% | 12% | -0.0425 | 0.00406 |
Normal growth | 0.4 | 14% | 9% | 0.056 | 0.00282 |
Boom | 0.3 | 31% | 4% | 0.093 | 0.01413 |
0.085 | 0.02936 | ||||
Calculation of mean return and variance for the stock fund using Excel. | |||||
Show all the calculations in this excel, MUST enter your final answers (in decimal numbers) in the BOX below: | |||||
Keep 4-digit decimal places, e.g., 0.1234. | |||||
Mean (Expected) return is | 0.085 | ||||
Variance is | 0.0294 |
2. Consider the table below that summarizes the probability distributions of the two risky funds, a stock fund and a bond fund: | ||||||
Stock Fund | Bond Fund | Expected value | Expected value | |||
Scenario | Probability | Return | Return | of Stock Fund | of Bond Fund | Covariance |
Severe recession | 0.05 | -43% | -12% | -0.02150 | -0.00600 | 0.00233 |
Mild resession | 0.25 | -17% | 12% | -0.04250 | 0.03000 | -0.00287 |
Normal growth | 0.4 | 14% | 9% | 0.05600 | 0.03600 | 0.00181 |
Boom | 0.3 | 31% | 4% | 0.09300 | 0.01200 | 0.00182 |
0.08500 | 0.07200 | 0.00310 | ||||
Calculate the value of the covariance between the stock and bond funds using Excel. | ||||||
Show all the calculations in this excel, MUST enter your final answers (in decimal) in the BOX below: | ||||||
Keep 5-digit decimal places. | ||||||
Covariance is | 0.0031 |
3. Consider the data below for two risky funds, a stock fund (S) and a bond fund (B): | |||||
T-Bill rate | |||||
10% | 5% | 15% | 6% | 0.3 | 3% |
A. Fill in columns C, D, and E, the expected return ©, volatility (D), and Reward-to-Variability ratio (D) of portfolios consisting of the stock fund and bond fund. | |||||
Enter your answers in decimal numbers, and keep 4-digit decimal places. | |||||
Weights in the | Weights in the | Portfolio | Portfolio | Reward to | |
stock fund | bond fund | expected return | volatility | Variability Ratio | |
-0.6 | 1.6 | ||||
-0.5 | 1.5 | ||||
-0.4 | 1.4 | ||||
-0.3 | 1.3 | ||||
-0.2 | 1.2 | ||||
-0.1 | 1.1 | ||||
0 | 1 | ||||
0.1 | 0.9 | ||||
0.2 | 0.8 | ||||
0.3 | 0.7 | ||||
0.4 | 0.6 | ||||
0.5 | 0.5 | ||||
0.6 | 0.4 | ||||
0.7 | 0.3 | ||||
0.8 | 0.2 | ||||
0.9 | 0.1 | ||||
1 | 0 | ||||
1.1 | -0.1 | ||||
1.2 | -0.2 | ||||
1.3 | -0.3 | ||||
1.4 | -0.4 | ||||
1.5 | -0.5 | ||||
1.6 | -0.6 | ||||
B. Make an excel graph of the efficient frontier using the data in the table above, like the graph in my Topic 4 lecture note. | |||||
Clearly label the X-axis (standard deviation) and Y-axis (expected return) | |||||
C. Using the formula for the tangent portfolio weights in Topic 4, find the proportions (weights) of stock fund and bond fund of the optimal risky portfolio. | |||||
Show all the calculations in this excel, MUST enter your final answers (in %) in the BOX below: | |||||
Keep 2-digit decimal places in %, e.g., 12.15% | |||||
Weight on the stock fund | % | ||||
Weight on t |