Portfolio RIsk and Return

1. Convert prices to total return (% change in the price) = (Pt – Pt-1) / Pt-1

2. Remove outliers – sort data and remove anything +/- 20%

3. Calculate historical average and historical risk

X-BAR = Σx/n
Calculate the sum of the total return and divide by the number of observations

• Variance = σ2 = Σ(x – x bar) 2 / (n-1) Fix X-BAR, double click to apply to all dates, get the sum, divide by (n-1)

Risk = σ = √σ = SQRT(Variance) = standard deviation

4. Average Matrix

Excel Options → Add-ins → Go → Select 1st two and last one → Go

Data Analysis → Descriptive Analysis → Select all data without the time → Label in the first row → Select “Summary Statistics” → OK
• This gives you the averages: average matrix
[ xbar, xbar2, xbar3, ….]

5. Covariance Matrix

Data analysis → Covariance → OK → Select all stocks like before → Labels in the 1st row → OK
• This gives you the covariance matrix
• To fill out the matrix: Copy → Paste Special → Transpose
•
6. Generating Scenarios

Data Analysis → Random number generation → # of variables: 6 (the number of companies in the portfolio) → # of random numbers: 10 000 → distribution: uniform

Calculate the sum of each row for the weights, they’ll never = 1

Normalize the weights: divide the weight of each stock by the sum of all the weights; this makes the weights=1
• You cannot fix the sums. So, copy/paste the sums 3 times if you have 3 weights, 5 times if you have 5 weights….

W1 W2 W3 S1 S2 S3 W1 W2 W3 Sum
0.5 0.5 0.4 1.4 1.4 1.4 0.5/1.4 0.5/1.4 0.4/1.4 1

• weight1 / sum1 then drag to the right to apply to all weight
• Select S1, S2, S3, W1, W2, W3, and Sum=1 and double click to apply to all data. o This gives you the new weights
• Select the normalized weights, and sum=1 o Copy → Paste special → Values, number the 10 000 scenarios
•
7. Calculating Average Return for the Portfolio Rp

Rp = (w1)(xbar1)+(w2)(xbar2)….(wn)(xbarn)

Average Matrix: [xbar1, xbar2, …xbar5] 1x5