Portfolio Optimisation
* Assumptions
Risk-Free Rate 1.60%
* Historical Fund Returns
Period Mid Cap
Period 1 6%
Period 2 9%
Period 3 3%
Period 4 38%
Period 5 3%
Period 6 36%
Period 7 46%
Mean Returns 20%
* Covariance Matrix
Fund Mid Cap
Mid Cap 0.035918
Small Cap 0.032206
Contrarian Fund 0.034707
Select Dividend Fund 0.029671
Equity Insights Fund 0.035750
Value Fund 0.016856
Special Situations Fund 0.045115
Property Fund 0.009182
* Portfolio Construction
Fund % Allocation
Mid Cap 20.0%
Small Cap 20.0%
Contrarian Fund 10.0%
Select Dividend Fund 10.0%
Equity Insights Fund 10.0%
Value Fund 10.0%
Special Situations Fund 10.0%
Property Fund 10.0%
Total 100.0%
Portfolio Metrics Mid Cap
% Allocation in Portfolio 20.0%
Expected Portfolio Return 0.3%
Variances within Portfolio 0.0062
Portfolio Variance 1.6%
* Sharpe Ratio Calculation
Historical Portfolio Returns
Variance
Standard Deviation (Volatility)
Sharpe Ratio
Historical Benchmark (S&P 500) Returns
Period S&P 500
Period 1 14%
Period 2 1%
Period 3 12%
Period 4 22%
Period 5 -4%
Period 6 31%
Period 7 18%
Average Return 13.5%
Variance
Standard Deviation (Volatility)
Sharpe Ratio
Step 7) In this step, we will use SOLVER within Excel to optimise the weightings of t
calculated the Sharpe Ratio of the Benchmark by this step. Therefore, our goal is to
To use SOLVER, go to Data -> Solver (if this is not enabled, see the grey text box be
You will then set the "By Changing Variable Cells" field to the column of weights high
You can then click Solve, and SOLVER will maximise the Sharpe Ratio of the Portfo
Check the weightings in orange above and you will see that they should have chang
Link for SOLVER HELP: https://support.microsoft.com/en-us/office/load-the-sol
in-excel-612926fc-d53b-46b4-872c-e24772f078ca
Link for SOLVER HELP: https://support.microsoft.com/en-us/office/load-the-sol
in-excel-612926fc-d53b-46b4-872c-e24772f078ca
10-Year US Treasury Yield
Small Cap Contrarian Fund Select Dividend Fund
2% 17% 9%
-4% -13% 3%
13% 8% 19%
15% 5% 17%
-5% -4% -2%
33% 43% 30%
33% 28% 4%
12% 12% 11%
Small Cap Contrarian Fund Select Dividend Fund
0.025513 0.022150 0.007058
0.022711 0.023559 0.006413
0.021853 0.020862 0.005036
0.024854 0.021421 0.009993
0.034353 0.033164 0.011460
0.014394 0.012455 (0.002571)
0.033055 0.027526 0.001981
0.005542 0.005479 0.005948
Note: In this section, we have t
SOLVER function later in Step
return.
The values to be changed via SOLVER
The first is highlighted in the or
timebeing, but are "the values t
the bottom - this will be the 'con
Constraint in SOLVER
Small Cap Contrarian Fund Select Dividend Fund
20.0% 10.0% 10.0%
0.0046 0.0021 0.0006
1.6%
12.8%
Step 5) In this step, you will calculate
-0.13 Sharpe Ratio = (Portfolio Return - Ris
Mean Deviation Squared Deviations
0% 4.90306122448972E-06
-12% 0.0146133559183673 Note: In this section, we are calcula
-2% 0.000236720204081633
8% 0.0069913487755102 For more information on single fund
-18% 0.0318571502040816
18% 0.0324771887755102
5% 0.00237308163265306
1.5%
12.1%
Step 6) In this step, you will calculate
0.01 Sharpe Ratio = (Benchmark Return -
n Excel to optimise the weightings of the Fund to maximise the Sharpe Ratio for the Portfolio. For
by this step. Therefore, our goal is to maximise the Sharpe Ratio of the Portfolio, which will in ess
s not enabled, see the grey text box below). Next, set the Objective as the Portfolio's Sharpe Ratio
ells" field to the column of weights highlighted in orange above (C35:C42). Lastly, you will need to
aximise the Sharpe Ratio of the Portfolio by optimising the weightings of the funds. It will also ensu
u will see that they should have changed!
osoft.com/en-us/office/load-the-solver-add-in-
8ca
osoft.com/en-us/office/load-the-solver-add-in-
8ca
Equity Insights Fund Value Fund Special Situations Fund
16% 3% 33%
-1% -6% 5%
11% 27% 32%
24% 10% -8%
-6% -16% 15%
26% 21% -16%
18% 4% 40%
13% 6% 14%
Equity Insights Fund Value Fund Special Situations Fund
0.017000 0.007422 (0.011928)
0.017867 0.006566 (0.006989)
0.017534 0.004137 (0.010446)
0.019935 0.016832 (0.000515)
0.022949 0.020631 0.006093
0.004719 0.000126 0.011854
0.016994 0.002583 0.043735
0.005960 0.003022 0.008509
e: In this section, we have two important components which will be used in the
LVER function later in Step 7, when we optimise the portfolio weights for risk-adjusted
rn.
first is highlighted in the orange cells - these are placeholder weights for the
ebeing, but are "the values to be changed by SOLVER". The second is the total row at
bottom - this will be the 'constraint used in the SOLVER calculation".
Equity Insights Fund Value Fund Special Situations Fund
10.0% 10.0% 10.0%
0.0016 0.0008 0.0002
this step, you will calculate the Sharpe Ratio for the Portfolio using the formula:
atio = (Portfolio Return - Risk-Free Rate) / (Standard Deviation of Portfolio Returns)
this section, we are calculating the inputs required to determine the Sharpe Ratio for the Benchm
e information on single fund (asset) variance and standard deviation, refer to the reading guide.
this step, you will calculate the Sharpe Ratio for the Benchmark using the formula:
atio = (Benchmark Return - Risk-Free Rate) / (Standard Deviation of Benchmark Return)
e Ratio for the Portfolio. For more information on the Sharpe Ratio, refer to the reading guide prov
e Portfolio, which will in essence maximise the risk-adjusted return relative to the S&P 500.
the Portfolio's Sharpe Ratio cell (E59) and set to "Max".
42). Lastly, you will need to add a constraint for the total sum of all weights (C43) to equal 1 (i.e. 1
of the funds. It will also ensure to adhere to the constraint that all weightings must sum to 100%.
Property Fund
18%
-3%
-1%
11%
-7%
22% Step 1) Calculate the average (mean) returns for each of th
-6% yellow cells.
5%
Property Fund
0.005083
Step 2) We need to calculate the co-variance of each of the
0.008574
0.008532
Use the COVARIANCE.S formula within the yellow cells to
0.007611
0.007682 The Mid-Cap row has been completed as an example. Be c
(0.004709) the rest of the table!
(0.007829)
0.002369
in the
isk-adjusted
the
e total row at
Step 3) In this step, you are required to complete the formu
by its mean return, we will use the SUMPRODUCT formula
Property Fund
10.0% As mentioned in the Reading Guide, the variances for each
(i) each fund's weighting %; (ii) the product of each fund's c
The portfolio variance is then simply the sum of the varianc
by its mean return, we will use the SUMPRODUCT formula
As mentioned in the Reading Guide, the variances for each
(i) each fund's weighting %; (ii) the product of each fund's c
0.0004 The portfolio variance is then simply the sum of the varianc
ormula:
o Returns)
pe Ratio for the Benchmark.
to the reading guide.
e formula:
chmark Return)
o the reading guide provided. We have
ve to the S&P 500.
s (C43) to equal 1 (i.e. 100%).
gs must sum to 100%.
rns for each of the Funds using the AVERAGE formula within the
nce of each of the funds (see the Reading Guide for more detail).
he yellow cells to calculate the co-variance between each of the funds.
an example. Be careful that the right data is selected when you fill out
omplete the formula for expected return. Instead of manually multiplying each weight
RODUCT formula by selecting the two rows (Mean Returns, % Allocation).
ariances for each fund have then been calculated using:
ct of each fund's co-variance matrix column and the portfolio's weightings
um of the variances of each component (i.e. the funds).
RODUCT formula by selecting the two rows (Mean Returns, % Allocation).
ariances for each fund have then been calculated using:
ct of each fund's co-variance matrix column and the portfolio's weightings
um of the variances of each component (i.e. the funds).
each weight
).
).
