how to build a portfolio in a nutshell

 Hey all! I was sorta bored this winter and luckily I saw my old college stuff lying around. So, I constructed Hypothetical Portfolio Analysis model with and without a Risk-free asset. Believe me, I had to work a lot to get the variance and SD right to analyse the risk as information in the material is either asymmetric or logical and sometimes appeared uncorrelated. Hence, I took the onus to recreate the Portfolio model, i.e., building it from the scratch!

VIEW MANUAL PORTFOLIO MODEL here

VIEW EXCEL PORTFOLIO OPTIMISATION MODEL here

Step 1: Take a workbook and save it. Enter headings appropriately like in the below screenshots. 

A, B, C & D are four company stocks daily price changes calculated using the formula '(New-Old)/Old=% Change'. 

The Market Index column is used to for calculating Beta (Compare stocks with market data) using Excel's '=Slope' function. Alternatively, you can regress the data between the stock and Market Index.

The following Columns G-J are used to calculate 'Daily Stock Price Change- Their Mean (Averages)'. 

Then, products from Columns L-Q are used to find the correlation between the two stock (The same has been repeated with Risk Free Asset- RFA in Columns X-AC with D value as zero).

Table 1

Table 2

Great care was taken to formulate Variance and Standard Deviation. To demystify, Variance S13-V13 are daily variances, and sum of daily data. Daily Standard deviation was derived from Daily Variances. I have included the screenshots with formulas for your convenience.

Table 3

Table 4

You might not like the special effects, your free to download excel at anytime (Chuckles).

Table 5

Table 6

Now that the main part of data arrangement is over, let us work on the variables to define Risk & Return below.

Column B16-20: Define the weights as per your investment decisions. 

Column C16-20: Define the Mean '=Average()' of individual stocks.

Column D16-20: Define the Annualised daily Variance for stocks by using the formula '=256*S13, =256*T13, =256*U13, =256*V13' . (Please note that you have to work on 365 days data and hence use '=256*Sigma Daily Variance'. Change it to '=52*Sigma Daily Variance' for annualised weekly data)

Column E16-20: Define the Standard Deviation '=SQRT(D17), =SQRT(D18), =SQRT(D19), =SQRT(D20)'.

Column F16-20: Is for Weighted Standard Deviation which I did not use the result anywhere in the model and defined it purely for educational purpose as ' '=B17*E17'.

Column G16-20: Define Correlation Coefficient '=L13/(SQRT(S15*T15))' which is later used to construct a Covariance Matrix or importantly, to derive Portfolio's Variance.

Column H16-20: Define Historical Returns, average of returns, which is the same as Mean here '=C17'.  

Column I16-20: Define Expectational Returns under casual three conditions (Recession, Normal & Boom). I have taken the same return for all three probabilities just to be modest and calculated it as '=(0.5*H17)+(0.25*H17)+(0.5*H17)'.

Column J16-20: Define the Volatility (which is also called as Standard Deviation) on returns '=(1-B17)*E17'.

Till here we succeeded in modelling for Risk measured at Standard Deviation for individual stocks.

Column K16-20: Lets do Beta returns analysis for which we need to find Beta using Slope function in Excel =SLOPE(B2:B12,F2:F12) or do Regression Analysis from historical trends. 

Column L16-20: Now that we have Beta, lets get the Target Returns on individual stocks '=G46+(K17)*(H17-G46)'

Column M16-20: Ex-Post or Ex-Ante results are for the current year. In Simple words, what we actually gained this year from our individual stocks. Define it by using '=(B12-B2+0.01)/B2'.

Column S- Column V: In cell S13, daily Variance formula '=(SUM(S3:S12)/10)-(C17)^2' and Standard Deviation of it is '=SQRT(S13)'. Cell S16 uses '=SUM(S3:S12)' formula and the result is used to calculate Correlation Coefficient.

This is a modification to remember for Table 5.

Similarly, 

Table 7

Table 8

Table 9

Now you are clear on how to calculate within the worksheet.

Till now, we have processed data only for a normal portfolio. Now, We will change some variables and remove Stock D and introduce Risk Free Asset (RFA) into its place. 

Table 10

Table 11

Copy relevant data depicted in Table 7 from the above to table 11 and introduce I24.  Leaving Columns A, B, C, D, E25-29 the same, we have to enter '0' in C28 Cell because Risk-Free Asset does not have any daily variations. You will be observing that Correlation Coefficient in H25-29 has '0' values because Risk-Free asset has no correlation with other assets. 

From here on, we have to separate Expected returns & Variance between Risky assets and Risk-Free Asset.

In Cell G29, input '=SUM(G25:G27)'. This will give your Risky Assets (A,B & C's) Expected Returns.

In Cell G30, Input '0' as there is no return or else use the Risk free rate of return + Risk Premium receivable. Either ways is fine because we will define this portfolios returns through a formula and this does not impact our analysis. 

In Cell G31, input '=(B25*D25)+(B26*D26)+(B27*D27)+(B28*D28)+(2*H25*B25*B26*D25*D26)+(2*H26*B25*B27*D25*D27)+(2*0)+(2*H28*B26*B27*D26*D27)+(2*0)+(2*0)'. Looks funny? I did it for fun as well while there are other formulas in my second downloadable Excel workbook above. This formula will calculate Variance for Risky assets only excluding coefficients associated with RFA. You can see from above formula that '(2*0)+(2*0)' as Zero. For theoretical analysis, refer to Portfolio Management books.

Now, all the tough data inputs is over and lets start analysing the results.

Cell G33: Input '=SUMPRODUCT(B17:B20,I17:I20)'. This gives the Expected Portfolio Returns based on Expectational data of a normal portfolio. 

Cell G34: Input '=SUMPRODUCT(B17:B20,H17:H20)'. This gives Expected Portfolio Returns based on Historical data of a normal portfolio.

Cell G35: Input '=(B17*M17)+(B18*M18)+B19*M19+(B20*M20)'. This will give us this year's returns based on Ex-Post calculations.

Cell G36: Input '=(B17^2*D17^2)+(B18^2*D18^2)+(B19^2*D19^2)+(B20^2*D20^2)+(2*G17*B17*B18*D17*D18)+(2*G18*B17*B19*D17*D19)+(2*G19*B17*B20*D17*D20)+(2*G20*B18*B19*D18*D19)+(2*G21*B18*B20*D18*D20)+(2*G22*B19*B20*D19*D20)'. This will give the normal portfolio's Variance. It is lengthy, and there are other shortcut formulas in Excel. This is just for educational purposes. 

Cell G37: Input '=SQRT(G36)'. This gives the risk measure known as Standard Deviation a.k.a., Volatility of a normal portfolio. 

There ends our normal portfolio's Risk analysis. Lets resume with Risk Free Asset inclusive portfolio.

Table 12:

Table 13

All the tables are now complete and to continue...

Cell G38: Input '=(1-B20)^2*G31'. This will give us the Variance of portfolio after the Risk-Free asset has replaced the fourth stock D.

Cell G39: Input '=SQRT(G38)'. This will give us the SD of the Risk-Free asset included portfolio. 1 means Good.

Cell 40: Input '=(B28*G46)+(1-B28)*G29'. This will give us the Expected Return on this portfolio separating the RFA returns as '0' and considers only Risky assets (A,B,C's) returns. If your Portfolio manager says OK, Input Risk free rate 8% into Cell G30 and sum up the results.

Cell 41: Input '=(1-B28)*G39'. This will give us how much risk is involved to achieve the expected return. It's 1 here meaning perfect.

Cell 42: We cannot use a formula here because we need to equate Target returns. 

If you need target return on portfolio as 7% because I already have 4.7% as given return, I will equate

7% = W(f) * r(f) + (1 - W(f)) * Expected return on portfolio. 

7% = W(f) * 8 + (1 - W(f)) * 4.7

7% = 8W(f) + 4.7 - 4.7W(f)

-3.3W(f) = 2.3

W(f) = -2.3/3.3 = 70%

So, investor can borrow at 8% and invest 70% on his portfolio.  

Cell 43: Input '=1-(0.005/G39)'. This will minimise the risk to your desired risk. Supposing, if you want to minimise the risk to 0.003%, replace '0.005' in the numerator with '0.003'. The result will tell us, say '58%' in our problem, that percentage must be invested in the Risk-Free asset.

Cell 44: Input '=(G40-G46)/G41'. This indicates that the market will demand that much percent of Risk Premium for every 1% change in Standard Deviation (Risk).

Cell 45: Input '=(G40-0.08)/G41'. This derives the Sharpe's ratio and when it is higher than the market index, it indicates that portfolios returns are doing better than the market.

Cell 47: Input '=SUMPRODUCT(B25:B28,I25:I28)'. This will give the Expected Return on the portfolio based on the given Beta per stock. If you include the Risk free return 8%, the total return is 10% and if you keep RFA as '0' in Cell I28, the total portfolio return is 6%.

Finally, we have done Portfolio construction successfully with 2 known methods (Standard Deviation & Beta)

Note: You can add an Efficient Frontier curve to the normal portfolio. Once you add Risk free rate, it becomes CML and the curve becomes a straight line.

Next, I failed to deriving Portfolio Variance from Covariance Matrix manually. Importantly, Optimisation can be done in Excel Solver using the same constraints (Find it in my other Excel download).

I am sure this hypothesis is correct because here,

Hypothesis = 1 Standard Deviation.

Time flew by while I was modelling this for fun.

Note: You can change variance to daily, monthly or annual variance and results will vary accordingly.