FINANCIAL MODELLING ASSIGNMENT 2015/16
Details: You will have to apply the skills you’ve learned during the lecture and tutorials and use excel to answer the assignment question. You must answer all your questions in one document i.e. in word, and provide screenshots or copy and paste all your workings from excel into the word document. You must not provide your answers in essay form. You are assessed on how you are able to use excel and how you are able to decompose the information it provides. Therefore your answers need to be clear and concise. Task Collect ten years (1/1/2005 – 31/12/2014) of daily closing prices for a company of your choice. a. What does the distribution of its daily log returns look like over the ten years? Please think of appropriate method to represent your data. Is assuming that log returns are normally distributed a reasonable assumption to make here? (Excel’s ‘Frequency’ function may be useful here). (40%) b. Using Monte Carlo simulations, with 10,000 runs, simulate the closing prices of the company you have chosen in 130 trading days from the last day in your sample i.e. in half a years’ time (260 trading days in a year). You will have 10,000 different estimated closing prices in half a year’s time. (Remember to calculate the a, µ, and s for the asset). What does the distribution of simulated closing prices look like? Does the assumption that stock prices are lognormally distributed hold? (40%) c. Imagine that on the last trading day (31/12/2014) you brought 100 shares of the firm you have selected. Using the simulations from part b., how much can you potentially loose on this investment in half a year with 1% certainty? (Excel’s ‘percentile’ function will be useful here). (20%) You need to consider carefully with your partner the best way to represent your data. Please state which company you have chosen. For part a. different companies have different return histories, therefore the history of one company is different to the history of another. You are required to go into depth into your analysis of the company’s historical returns, i.e. has any extreme events occurred? For part b. think about what the starting stock price is where you begin your simulation, the value will depend on the company you have chosen, but use the most recent closing price as the start value for the simulations (should be 31/12/2014). Imagine that 31/12/2014 is t =0 or today, and we are simulating into the future. Benninga’s Financial Modelling textbook, may be useful. There are also various excel hints and tips online.