FINM 3008/6016 Portfolio Construction Tutorial #2

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

FINM 3008/6016 Portfolio Construction

Tutorial #2 – Outline

Question 1: Estimation of Risk Measures

On the course Wattle site can be found the file “Tutorial #2 - Analysis File.xlsx”. This file contains year-end total return indices in A$ for five asset classes. It also contains weightings for two portfolios: an investor’s portfolio (‘P’) and a benchmark portfolio (‘B’). The latter represents the average portfolio of the investor’s peer group. You will see that the investor differs from the peer group by holding a largely global equity portfolio, i.e. they have minimal ‘home bias’. The excel file also contains a number of structured worksheets, which you are to complete via filling in the cells that are shaded faint yellow.

You are to use the file provided to estimate various portfolio risk measures, which you will then review. This is the initial learning-by-doing task in a series of exercises that you should find well worth the effort for two reasons. The first is to give you some exposure to spreadsheet-based analysis of portfolios using real data. This will help prepare you for the assignment. The second is that you might be asked to calculate and interpret the risk measures covered in this tutorial in the exams, i.e. it is examinable.

Part (a) – Analysis Based on (Unadjusted) Historical Returns

Analysis: Calculate yearly returns for both portfolios (P and B), using the ‘SUMPRODUCT’ function. (Note: This implicitly assumes yearly rebalancing, i.e. portfolio is reset so that same weightings apply in every yearly period.) Then estimate the difference in returns between the two portfolios (P – B). (Note: This reflects on peer risk.) Estimate the following measures for all return series, i.e. five asset classes, and three portfolio return series:

Return measures:

1. Arithmetic mean (simple average of yearly returns)

2. Geometric mean (compound return over the entire period)

Risk measures:

3. Standard deviation

4. Probability of loss, i.e. probability of a negative return

5. 90% confidence lower bound

6. Average loss

Other distributional measures:

7. Skewness (‘SKEW’ function)

8. Excess kurtosis (‘KURT’ function)

9. Serial correlation (‘CORREL’ function, comparing period t+1,t+x with period t,t+x-1)

Hints: 

– The first workshop provides some guidance on the excel functions and calculation of excess returns

– Two routes to estimating the geometric mean are either via creating a portfolio total return index by chaining (1+R_t)’s; or calculating the average of ln(1+R_t)’s, taking the exponential, and subtracting 1.

– Measures 4 and 5 can be estimated either directly from the data, or by using parametric methods (assuming a normal distribution is OK). Try both.

Part (b) – Bootstrap Analysis

Analysis: You are now going to apply bootstrap methods. These can be useful practically when return data has a distribution that is difficult to parameterize, or for generating a longer time series than you have available by continually redrawing from the available data (which of course manifests its limitation of drawing from a defined pool of expected outcomes). You will be shown a method of performing the analysis within an Excel spreadsheet. (For future reference, software packages exist that can do simulations within Excel. Also, more powerful programs may be required for complex problems.)  

The objective is to estimate return and risk measures for the distribution of:

– portfolio value at the end of year 5 (per $1 invested at year 0)

– total portfolio return at the end of year 5

– compound return over 5 years

You are to simulate 1000 possible values for both the investor and benchmark portfolios at the end of year 5 and associated 5-year total returns by drawing from the distribution of mean-adjusted historical returns. This can be done in the ‘Part (b) - Bootstrap’ worksheet as follows:

· Column B contains the observation numbers (1 to 38); Column C contains the probability of each observation (i.e. 1/38 each); Columns D and E are designed to link to the historical adjusted return series for the investor and benchmark portfolios respectively in years 1 through 38.

· Use Excel Add-in Analysis Toolpack (you might need to install it if it’s not visible under the Data tab in your Excel) to generate 5 variables * 1000 random numbers, “Distribution” set to “Discrete”, “Value and Probability Input Range” set to be the range of $B$5:$C$42, setting the output range to cell G5. This function will randomly generate numbers 1 through 38. (Hint: Check that the numbers are OK, e.g. mean should be around 19.5, there should be no zeros. If it looks like a bad draw, make another. You want to construct a fair representation of the data.)

· A 5*1000 matrix of random draws of ln(1+R) for the investor’s portfolio P is now placed in columns M-Q using the VLOOKUP function. The following expression can be put into the first cell of the matrix (M5), and copied to the other cells: “=LN(1+VLOOKUP(G5,$B$5:$E$42,3,false))”

· The total portfolio value at t=5 per $1 invested in t=0 can be included in column R by summing columns M-Q, and taking the exponential. For example: cell R5: “=EXP(SUM(M5:Q5))”.

· The total portfolio return at t=5 can be calculated as cell S5 “=R5-1”, or “=EXP(SUM(M5:Q5))-1”.

· Estimate compound per annum returns and place in column T. One possible formula: Value_t=5^(1/5) – 1. Important: this annualization of portfolio total return over 5 years tends to smooth out the true fluctuation of investor’s wealth over the investment horizon.

· Repeat the process for the benchmark portfolio B in columns V-Z. Note that the VLOOKUP expression needs to be changed to reference column 4 rather than 3. For instance, the first cell of the matrix (V1) should read: “=LN(1+VLOOKUP(G5,$B$5:$E$42,4, false))

· The relative performance of investor’s portfolio and the benchmark can now be estimated in columns AE and AF. Formulas are provided in row 4. The relative performance in columns AG and AH are based on compound returns over 5 years for each iteration. The standard deviation of these returns is NOT the tracking error for investor’s portfolio over the investment horizon. The standard deviation of column AF is the tracking error for 5 years portfolio returns, which is annualized in the summary table for comparison and reporting purpose (cell P11 in worksheet ‘SUMMARY’).

· The output from worksheet ‘Part (b) - Bootstrap’ feeds into ‘SUMMARY’. The section of “Based on Yearly Returns” and “Total Portfolio Return in Year 5” demonstrates how risk measures might change when investment horizon is extend from one year to five years. The horizon effect is one of the major themes in quantitative portfolio analysis.

Part (c) – Discussion

(i) What does the output tell you about the risks that an investor is taking with their portfolio?

(ii) How does shifting the time horizon to 5 years under the bootstrap influence the risk measures?

(iii) Do you see issues that might call into question the integrity of the risk analysis? (Hint: Examine statistics for each asset class, and think about the inputs. Also give some thought about the implicit assumption that is being made by drawing data from individual periods and then accumulating under the bootstrap.)

(iv) What doesn’t the analysis tell you that might really matter?

Question 2: Eliminate Risk?

Should investors attempt to eliminate risk, at least as far as possible?