Question: You are working for an Investment Management Company and have been asked to produce a report on a client's portfolio.

PART A

You are working for an Investment Management Company and have been asked to produce a report on a client's portfolio. This client currently invests in 4 assets (A,B,C and D) and their current portfolio of £100,000 is split evenly between each of these assets (25% in A, 25% in B, 25% in C and 25% in D). The only information you have is some historical data on these assets presented in the Raw Data sheet, and no dividends are earned on these assets. You believe that the historical performance of these assets is a good indicator of future performance.  Using the Mean-Variance framework you have been asked to advise this client on how they could improve the risk and return characteristics their portfolio. Your write up should not assume that the client already understands the calculations and methods you will use but is interesting in understanding your analysis. (NOTE THIS SPREADSHEET IS SET TO MANUAL CALCULATE YOU WILL NEED TO PRESS F9 TO RECALCULATE IT).

PART B

You are working for an Insurance Company that plans to expand its Small Business Cyber Insurance to 30,0000 policies. This is a new line of business and the company has very limited claims data from its first year of selling this new type of policy. Data for the frequency and severity of claims on the 2141 policies that were sold in the previous year is provided. The initial premium charged on these policies was £180 per policy per year. The costs are assumed to be zero to simplify the analysis. This coursework should be answered using the information provided on the  "Question 1" spreadsheet. This spreadsheet already contains all the VBA Functions from the course that are required to complete the coursework, see the "ExcelFunctions" Powerpoint Presentation for details of these custom functions built into this spreadsheet. The formula you will need to complete Question 7 are found on the "Formula" Powerpoint Presentation.  NOTE THIS SPREADSHEET IS SET TO MANUAL CALCULATE SO YOU WILL HAVE TO PRESS F9 TO RECALCULATE THE SPREADSHEET AFTER A CHANGE.

Write up Part A Guide

Some points you might like to include in your write up are:

1. The average, variance and covariance of returns – discuss how you calculated these inputs – For example you should say you used the AVERAGE, VAR and COVARIANCE.S and relate these to their formula from the class notes. What do these statistics measure, why are they important in the Mean-Variance model?

2. Display the covariance matrix, correlation matrix and expected returns vector in your presentation – you should comment on the statistics, noting high average returns, high volatilities (or standard deviations) and positive and negative correlations. Try to interpret the statistics from an investment perspective.

3. Discuss the idea of deriving the average and variance of the return of the portfolio from the covariance matrix, expected return vector and weight vector. Talk about the formula for expected returns -that returns are a linear combination of the assets. Discuss the quadratic formula used for deriving the risk of the portfolio from the covariance matrix. Relate the equations in the class notes to the matrix equations to your Excel formula.

4. Discuss the idea of efficient portfolios. Why are we only interested in the range between the minimum risk and maximum return portfolio? How did you calculate the points in between the minimum risk and maximum return portfolios? What are the constraints you use to calculate the optimal portfolio, what are the reason for these constraints?

5. Display your efficient frontier as a graph and the table of weights that make up the portfolio. Comment on its shape.

6. Discuss the composition of the portfolios on your efficient frontier and how they relate to the input statistics – covariances, variances, correlations, and expected returns. For example, why does the minimum risk portfolio contain that combination of assets – can you explain it in terms of the correlations and variances? Why does the maximum return portfolio contain the asset(s)? Try to relate what is happening as you move along the frontier from the minimum risk portfolio to the maximum return portfolio to the input statistics – why are those portfolios selected by solver?

7. Is the client’s current portfolio inefficient – how inefficient is it? Discuss how the client could improve both the risk and return on their current portfolio by selecting a portfolio on the efficient frontier.

8. Discuss the normal distribution and how it could be used to make more sense of the risk on the portfolio by calculating a worst-case scenario. Do you think this is a reasonable assumption to make, perhaps discuss the central limit theorem? Perhaps calculate the Worst-Case scenario at the 1% confidence interval and compare it to the 5% level, why is the loss larger?

Write up guide for Part B.

The general structure of your report should be as follows: the inputs for your calculation (statistics), the methods you used to derive your answer (you might want to copy and paste the code from the functions you use and discuss how they work), the results from your model (or calculation) and finally try to interpret your results. You SHOULD NOT paste 10000s of simulated values into your write up. Graphs and diagrams are a great way to present ideas and concepts. You should not limit the content of your write up to this guide. Extra marks will be awarded for additional research, especially if this is used to make further calculations.

Breaking this down into the sections:

Question 1: Discuss the Poisson Distribution and its use in the frequency severity model. Comment on how well you think the Poisson distribution matches the Frequency Data given. Additional marks will be awarded for using further statistical tests to assess how well the Poisson Distribution fits the dataset.

Question 2: Discuss the concept of the Empirical CDF and then discuss how you fit the Pareto and Gamma distribution to the dataset. You should display the graphs and comment on which distribution you decide provides the best fit. Additional marks will be awarded for using further statistical tests to assess how well these severity distributions fit the dataset.

Question 3: Discuss the frequency severity model and the idea of simulation or generating random variables from distributions. Discuss how you will use the Poisson distribution for the frequency and how all it requires in the average frequency for the underwriting portfolio. Then discuss how the Frequency Distribution is combined with your selected Severity Distribution to simulate the Aggregate Loss. The default simulation size is 1000 however you may wish to run a larger simulation if you want.

Question 4: Discuss how you can estimate the aggregate loss distribution using the output from your simulation and produce a graph of the result – ie the Empirical CDF. You should discuss why estimating the distribution of aggregate losses is important for this insurance company. Discuss how you could estimate the PML from this sorted list and the probability of observing a loss greater than a given value. Why are these calculations of interest to the insurer?

Question 5: Discuss how you can also use the simulation of aggregate claims to estimate the distributions of profits, and again graph your results. Discuss how you calculate the probability of making a loss, the VaR and how this could be used to calculate the Underwriting Risk SCR. You should also comment on whether you think the policies have been correctly priced, and possibly suggest a minimum price that the insurer should charge (See Appendix Lecture 5 for some discussion relating to this).

Question 6: Discuss how you could combine the SCRs using the Risk Aggregation Formula from Lecture 5. You might want to discuss the diversification effect – ie why the Diversified SCR is less than the sum of the individual SCRs. You should comment on the level of New Capital Required after the Cyber Insurance Line is added to the existing underwriting portfolio (Motor and Property) and why this is substantially lower than you might expect.

Question 7: Discuss how you can derive the mean and variance of the aggregate claim using formula rather than a Monte Carlo Simulation. Discuss the justification for assuming that aggregate claims are Normally Distributed – the Central Limit Theorem. Finally, you should try to compare your answers using the Normal Approximation and your Monte Carlo Simulation and see if there is any difference, and if possible, explain the reason for any difference or similarity in the results. Do you think the difference is just due to the sampling noise of the Monte Carlo Simulation, or is there a fundamental difference between the two? In your opinion, which of the two methods is more appropriate to calculate the Underwriting Risk in this case?