How To Solve Wharton Business and Financial Modeling Capstone Week 3

The Minimum Variance and Optimal Risky Portfolio

Disclaimer: This article is published for the purpose of providing students with knowledge that is not provided in the course module and not to breach the Coursera Honor Code. As the final course of the specialization, it misses some of the key concepts that are required to pass the quiz. This article will only guide you on how to find the key variables that are necessary to pass the quiz, rather than provide a direct answer.

For Questions 1 to 4, you have two stocks, which are MSFT, i.e., Microsoft, and WFC, i.e., Wells Fargo & Co. We have to invest 100% of our money while generating the maximum return.

In this exercise, we have to find the weights of the two stocks first by minimum variance and then by maximum Sharpe ratio and plot the efficient frontier using the portfolio characteristics for each weight.

Question 1-4

Before you start solving Q 1-4, you need to find
1) Variance
2) Standard Deviation
3) Covariance
4) Daily Stock Return
5) Individual Stock Expected Return
6) Portfolio Variance
7) Portfolio SD
8) Expected Portfolio Ret
9) Sharpe Ratio

To Find the Variance, standard deviation and covariance of stock returns the formulas are (=VAR.P), (=SQRT) of variance and (=COVARIANCE.P) respectively

Daily Stock Return

To calculate daily stock Return you need to take closing price – previous closing and divide it by the previous closing price. As displayed in above image and don’t forget to add brackets () and follow PEMDAS rule

Expected Stock Return

To calculate the expected stock return, you need to create an additional column of probability, as shown in the above image. To get the expected return of both stocks, you need to find the sum product of Daily average of stock and probability, i.e. sum product (Stock daily return, Probability) and take probability as 0.05

Portfolio Variance

This is the most important part of the whole exercise and a tricky one, In module few articles are given, if you read carefully there are two formulas to find portfolio variance one using covariance and other is using correlation, both gives same answer.

But many students and myself get confused and interchange the formulas to find the variance that is because in module there isn’t much video lecture for this just few articles which is not enough to grasp the full concept of portfolio variance

Here are the two formulas of Portfolio variance of two stock.

Portfolio variance= w₁²σ₁² + w₂²σ₂² + 2w₁w₂Cov_1,2

• w₁ = the portfolio weight of the first asset
• w₂ = the portfolio weight of the second asset
• σ₁ = the standard deviation of the first asset
• σ₂ = the standard deviation of the second asset
• Cov_1,2 = the co-variance of the two assets, which can thus be expressed as p_(1,2)σ₁σ₂, where p_(1,2) is the correlation co-efficient between the two assets

This formula used Covariance of the two stocks and in the end we did not include the standard deviation of the two stocks. Now let’s see the other formula using Correlation

Portfolio variance=w₁²σ₁² + w₂²σ₂² + 2w₁w₂σ₁σ₂Cor_1,2

If you pay close attention to the formula, you will find that the standard deviation is also being used in the final section of the formula. In the article of the Investopedia at top the Portfolio variance formula is given using Covariance but on the next section the example is given using the Correlation. Which makes everything really confusing for a newcomer.

Portfolio Standard Deviation

Simply use the SQRT formula on the portfolio variance or you can simply wrap the portfolio variance in it. It is only used to find the Sharpe ratio. Other than that, the variance is enough for most of the calculations.

Expected Portfolio Return

To get the expected portfolio return, you need to find the Sum product of the Expected stock Return and Weights.
=Sumproduct (Exp Ret, Weights)

Sharpe Ratio

The formula for Sharpe Ration is simple, i.e. (Expected Portfolio Return minus risk-ffree Rate) / Portfolio Standard Deviation. Note in this module that the risk-free rate is zero.

NOTE: In questions 1 and 2, you need to find the minimum variance, which is the minimum portfolio variance, and only in question 3 will you use a solver. you will get different weights when finding minimum variance and maximum Sharpe ratio.

If you do everything correctly, your excel sheet will look like the above image. Note the weights are incorrect, so the value of Portfolio variance, SD, Expected portfolio return, stock return, and sharpe ratio are different as they use weights in their formula. You need to use solver to get the correct answer.

Question 5-10

For questions 5 to 10 you have to calculate the above statistics again for all the 10 securities, in addition to that you need to find the Stock mean, Portfolio mean and covariance matrix.

Stock Average

Simply calculate the Average of the daily return of all the stock using the =average(daily return) formula for all the 10 stocks

Portfolio mean

The Portfolio mean is just the sum product of the average of the 10 stocks of their weights. =SUMPRODUCT (Average, Weights)

Covariance Matrix

This is the trickiest part of the whole quiz, and this is not even taught in the whole module, students have to learn this by themselves, I personally watched a lot of YouTube videos to learn this concept. Please read carefully.

For this segment you cannot find the covariance using the formula, because the formula can only be used to find covariance between two variables only. to find the covariance between all the 10 stocks you need to create a covariance matrix. To create a covariance matrix, follow the steps below

Step 1

Go to the Data tab > Data analysis > Select Covariance > select the daily return of all the securities and tick on labels option

After you have done this, you can see the covariance of all the stocks, and it will look like this:

Step 2

You can see a stair pattern in which each covariance is displayed for each security, but this is not the complete matrix, you further need to fill those empty cells, so that it can be used to find the portfolio variance without it the formula will return an #value error.

In cell B2 or below the BIDU you need to select the whole row from BIDU to XOM and use =transpose function and select the column below the BIDU row. As shown in the image below.

After entering the formula hit Ctrl + Shift + Enter without hitting control shift enter you will get #Value! error most people do this mistake.

Step 3

After repeating above step for all the empty cells, the covariance matrix is complete and ready to use in the portfolio variance formula.

Portfolio Variance

The formula for calculating the portfolio variance is completely different from the formula which is used in two stock portfolio in excel.

In two-stock portfolio, we used to write the complete formula but for more than 10 securities, you need to use this formula.

=MMULT(MMULT(TRANSPOSE(Weights),Cov Matrix),Weights)

Note: The MMULT function multiplies the row so if you see the weights in vertical format, so that’s why I used transpose function and wrap the MMULT function again in MMULT formula to multiply it by weights again.

Here is the YOUTUBE VIDEO link from which I have learned to use the MMULT formula.

After writing the formula, hit Ctrl + Shift + Enter to get the answer. If done correctly, then your excel sheet will look something like this:

In the above images, the weights are incorrect, and you need to use solver to find the optimal weights of the stocks to get the correct answers and solve the quiz. The purpose of the above article is to guide the students, as there isn’t any information provided in the course itself.