|
|
| SAMPLE-2 | Investment Portfolio Spreadsheet |
| | | Index Funds | | Bond Fund | | Money Market Fund |
| Scenario | Probability | Rate of Return | Col B x Col C | Rate of Return | Col B x Col E | Rate of Return | Col B x Col E |
| Mild recession | 0.50 | -15 | -7.5 | 7 | 3.5 | 0.5 | -3.8 |
| Normal growth | 0.50 | 14 | 7.0 | 5 | 2.5 | 1 | 7 |
| Expected or Mean Return: | | SUM: | -0.5 | SUM: | 6.0 | SUM: | 3.3 |
| | | | Stock Fund | | | Bond Fund |
| | | | Deviation | | | | Deviation |
| | | Rate | from | | Column B | Rate | from | | Column B |
| | | of | Expected | Squared | x | of | Expected | Squared | x |
| Scenario | Prob. | Return | Return | Deviation | Column E | Return | Return | Deviation | Column I |
| Mild recession | 0.5 | -15 | -14.5 | 210.25 | 105.125 | 7 | 1 | 1 | 0.5 |
| Normal growth | 0.5 | 14 | 14.5 | 210.25 | 105.125 | 5 | -1 | 1 | 0.5 |
| | | Variance = SUM | | | 210.25 | | | Variance: | 1 |
| | | Standard deviation = SQRT(Variance) | | | 14.5 | | | Std. Dev.: | 1 |
| | | | Portfolio invested 70% in stock fund, 20% in bond fund, 10% MM fund |
| | | | Rate | Column B | Deviation from | | Column B |
| | | | of | x | Expected | Squared | x |
| | Scenario | Probability | Return | Column C | Return | Deviation | Column F |
| | Mild recession | 0.5 | -4.1 | -2.05 | -2.1 | 4.20 | 2.10 |
| | Normal growth | 0.50 | 10.9 | 5.45 | 7.5 | 56.25 | 28.13 |
| | | | Expected return: | 3.40 | | Variance: | 30.23 |
| | | | | | | Standard deviation: | 5.50 |
| | | | Deviation from Mean Return | | Covariance |
| | Scenario | Probability | Stock Fund | Bond Fund | Product of Dev | Col B x Col E |
| | Mild recession | 0.5 | -14.5 | 1 | -14.5 | -7.25 |
| | Normal growth | 0.5 | 14.5 | -1 | -14.5 | 7.25 |
| | | | | Covariance = | SUM: | 0 |
| | Correlation coefficient = Covariance/(StdDev(stocks)*StdDev(bonds)) = | | | | | 0 |
barkersbaseball
finc_340-_spreadsheet_-sample__ii.xls
