This is a step-by-step tutorial for tracing the efficient frontier using the Solver add-in in Excel. We also explain the impact of short-selling when constructing efficient portfolios.
You can download the Excel spreadsheet used in this tutorial here:
https://www.initialreturn.com/efficient-frontier-calculator/
Formulas used in the cells highlighted as green are as follows:
Cell N12 (Portfolio return): =SUMPRODUCT(P2:P11,N2:N11)
The first array is the investment weights and the second one is average realized returns.
Cell N13 (Portfolio risk): =SQRT(SUM(Q13:Z13))
The square root is taken to obtain the standard deviation (volatility) of portfolio returns. Without it, the result would be the variance of portfolio returns.
The formula in Q13 is =Q12*SUMPRODUCT($P$2:$P$11,Q2:Q11), where Q12 is set equal to the investment weight of the first stock in cell P2. In the SUMPRODUCT function, the first array is the investment weights, and the second one is the first column of the variance-covariance matrix.
Then, cells R13, S13, ..., Z13 are defined similarly. For example:
The formula in R13 is =R12*SUMPRODUCT($P$2:$P$11,R2:R11) where R12 is set equal to the investment weight of the second stock in cell P3. In the SUMPRODUCT function, the first array is the investment weights, and the second one is the second column of the variance-covariance matrix.
0:00 Intro
0:17 Step 1: Prepare the dataset
1:04 Step 2: Compute average return and volatility
2:30 Step 3: Construct the variance-covariance matrix
4:35 Step 4: Solver add-in
5:12 Step 5: Trace the efficient frontier
Enjoying our content?
Click the link below to subscribe to our channel:
https://www.youtube.com/channel/UCshd-qjVDVwSfYi6P7cgAXA/?sub_confirmation=1
You can also follow us on X (Twitter):
https://x.com/initial_return
And, visit our website for free courses, tutorials, and more:
https://www.initialreturn.com/
#tutorial #portfoliomanagement #investing