Chapter 1: Introduction

A main factor that plays a big part in the price of a stock or shares is the economy, the economy was doing really well up until 2008 when the recession started, this caused share prices to drop dramatically and businesses having to close down. Customers began to spend less, this affected most companies as it caused a downturn in the economy. Investment levels dropped and businesses did not perform to the same standard. Investment became riskier as banks started to collapse, some even going bankrupt causing businesses to close down too. The recession in the past years (which is still ongoing) has led to fewer people investing and share prices decreasing. Due to this risk management has become important when investing.

1.1 Equity

Stocks are also known as equities or shares. An equity is when a stock or share is bought either privately or within a company, this can sometimes be risky because if the company does well in terms of valuation of the equity then the stock or shares bought would have been a good investment to the buyer and profit could be made, but if the company does badly, then the net worth of the stock or shares held is less than what its initial value was and a loss could be made through the returns generated. Risk measures need to be considered when investing in equities to avoid potential losses.

1.2 Aim of the project

The aim of this project is to gain an understanding of the Monte Carlo simulation method (Value-at-Risk measure) and to create a user-friendly piece of software that will allow investors to determine the potential risk a chosen stock has. Geometric Brownian motion will be used to simulate the behaviour of stock prices using historical price data; the data used will be three years worth of daily share prices collected from DataStream. The user friendly software will be created to allow trouble-free navigation throughout the process of finding out how risky a stock can be. The software will also be designed to alert users of any errors that have been made through VBA coding and functions available.

Chapter 2: Background

A simulation model will be used in this project in order to generate an estimation of a future stock price. However, there are many simulation models used within the financial sector in order to determine potential future stock prices. Two simulation models will be discussed and researched throughout this chapter in order to gain an understanding of the ways future stock prices could be estimated.

2.1 Symmetric Garch Model

Garch stands for general autoregressive conditional heteroscedasticity (GARCH). They are a set of models where the returns of a stock are assumed to be generated by a stochastic process or with a time varying volatility. There are whole ranges of different Garch models, only certain models are used in practice. The symmetric Garch model gives a time dependant value of volatility and can be defined using the following formula. [3]

Where ? > 0, ?ï¿½ ? 0.

? is called the Garch constant

? is the Garch error coefficient and ï¿½ is the Garch lag coefficient.

ï¿½ is an indication of how long it takes for the periods of volatility to die out.

Where ? is an indication of how sensitive the volatility is to the movements in the market.

The above constants have to be estimated using historical data.

?t is normally distributed with mean 0 and standard deviation ?t, since ?t is dependent on the returns of the stock at time t, ?t is said to be conditional on the set of data available. [3]

The distribution of ?t will change at each time step and therefore needs to be recalculated for each time step for input into the above model. ?t is then estimated and the stock price can then be calculated using the following formula.

2.2 Geometric Brownian Motion (GBM)

Key facts:

> Geometric Brownian Motion was originally used by Robert Brown to describe the movement of particles.

> Geometric Brownian Motion is used to simulate the future price of stocks based on a stochastic model. It is one of the most popular stochastic processes being used in the financial sector.

> Geometric Brownian Motion is not 100% accurate though it does give an approximation of future performance on stocks/shares.

> Geometric Brownian Motion defines changes of a stock price in continuous time. Shown below.

A few of the variables in this equation are easy to identify and understand below indicates each variable.

is the annual expected volatility of returns for the stock whose current price is represented by S.

t represents time.

ï¿½ is defined as the “annual expected rate of return for the stock,” or “drift”.

z is a random variable.

dt represents the very small changes in time (continuous time).

dz represents very small changes in z.

> Geometric Brownian Motion can also forecast stock prices based on a discrete time version of the equation above. This model simplifies how the random walk is incorporated. Shown below.

dS has been replace by the term

Which reflects the change in the price S of the stock between time t, and the next time increment, . Note that because the Wiener process is a type of Markov process, the price of the stock at time t is the only relevant value for forecasting the price of the stock at its next value at time (). [2]

dt, an infinitesimally small change in time, is replaced by a larger increment of time change called “delta t” or .

To define a stock price at a given time increment the discrete formula on the previous page will need to be re arranged to look like this:

> Geometric Brownian motion can also be used to give an estimation of a future stock price by calculating the stock price at some point in time. Shown below.

is the price of the stock today.

Is the annual expected volatility of returns for the stock.

Is the annual expected rate of return for the stock.

The time increment

? from the normal distribution with mean 0 and standard deviation 1.

> Geometric Brownian motion formula using Ito’s lemma will be used in the software to estimate future stock prices.

(Equations taken from Claire Lines lecture notes, 2011 – MA3997 ‘GBM and Simulation’)

reflects the change in the price S of the stock between time t (), and the next time increment ().

is the time increment.

is a random number from the normal distribution with mean 0 and standard deviation of 1.

is the annual expected rate of return for the stock.

is the annual expected volatility of returns for the stock.

2.3 Stochastic process

A stochastic process is based on successive draws of a random variable from a defined probability distribution. The values of the random variable cannot be known for certain at the start of the process, the value can only be characterised using the tools of probability such as defining the type of distribution and the key statistics describing the distribution. [2]

2.4 Markov process

A Markov process is a particular kind of stochastic process. One of its properties is that only the current value of the dependant variable (stock price) is relevant for predicting its next value. In other words, the stock price path leading to its current value or price history has absolutely no influence or effect on what the next stock price will be in a Markov process. This process does not use the historical stock prices as they are not useful. [2]

2.5 Wiener process

A wiener process is a specific type of Markov process where the random variable z is drawn from a normal distribution with a mean of 0 and a standard deviation of 1. [2]

2.6 Random number (z)

There are a number of ways in which to generate random numbers. Below will explain how to generate random numbers in Microsoft excel.

N(0,1) is a particular case of the normal distribution where the mean, is 0, and the standard deviation, is 1, from the general form of the normal distribution N(.

In order to calculate a normally distributed random number with mean 0 and standard deviation 1 using this alternative method we need to implement the following formula:-

?, is normally distributed random number

Ri is simply a random number generated using the ‘rand()’ function found under ‘math & trig’.

In order to implement this function, 12 random numbers need to be generated using this generator. The values of the 12 figures will be added together using the ‘sum’ function and take 6 from this value. To generate more the row will just need to be copied down to the relevant number of rows needed, for example can be copied down to 1000. This will show 1000 normally distributed random numbers.

2.7 Historical data

The historical data needed has been collected in order for this project to be implemented and one way of creating a software that will allow investors to estimate future stock prices. The historical data is taken from DataStream via FTSE100 and will consist of three years’ worth of closing share prices for a number of various companies. Historical data is being used in order to estimate future stock prices. Stock prices are observed at fixed intervals of time that will be used in this project including daily, weekly and monthly. Volatility can be estimated by the movement of stock prices.

2.8 Risk Measure (V-a-R)

A well-known risk measure is Value at Risk (V-a-R); this was first used by major financial firms in the late 1980’s. This is a widely used risk measure of the risk of loss on a specific portfolio of financial assets and will be used in this project. Three different types of models exist for estimating Value at Risk, these include the variance-covariance (VCV) assuming that risk factor returns are always (jointly) normally distributed and that the change in portfolio value is linearly dependent on all risk factor returns. Historical simulation is the simplest and most transparent method of calculation. This involves running the current portfolio across a set of historical price changes to yield a distribution of changes in portfolio value, and computing a percentile (V-a-R). The benefits of this method are its simplicity to implement, and the fact that it does not assume a normal distribution of asset returns. Lastly, Monte Carlo simulation is where future asset returns are more or less randomly simulated. [4] This is the most complex and difficult method to understand and implement. This model will be explained further on the next page.

2.9 Monte Carlo Simulation (V-a-R)

Monte Carlo simulation method is one approach used to calculate value at risk and the approach that will be constructed within this project and applied to a stock price. The Monte Carlo simulation will predict the worst likely loss of a potential stock given a confidence interval over a specified period of time.

This simulation generates a number of random outcomes and each time it is run a different outcome is generated, these results can be displayed on a histogram showing the historical pattern. For example if 100 trials are conducted each time the results would be different but near enough similar each time it is being calculated.

Diagram 2.1 [5]

The histogram above shows an illustration of the Monte Carlo simulation indicating the 5 possible worst outcomes that could occur in a frequency of 100 random trials. The worst outcomes would be -15% and -20%. The three outcomes that are also shown to be a risk are between -20% and 25%. The V-a-R confidence level here is 95% as 5% is the worst outcomes. Summarising this illustration no more than 15% will be lost in any given month.

Chapter 3: Software Design

3.1 Data

Gathering of data will come from DataStream, the world’s largest financial and statistical database from the FTSE100, which lists the most highly capitalised UK companies in the London Stock Exchange (LSE). Out of the data thirty companies will be chosen which will be implicated within the software and giving the investors a choice to choose from. The data will be daily historical closing prices of the companies and the returns will be generated using this data.

3.2 Description of Software and language

The main user friendly interface will be created in Microsoft Excel with the help of Visual Basic Application (VBA). Microsoft Excel and Visual Basic Application (VBA) is being used to put the project together as both have relevant functions needed, such as ease of coding within VBA, outputting graphs through built in functions such as command buttons with Microsoft Excel, both will calculate volatility and covariance of a stock. The returns and standard deviation for each company will also be calculated using built in functions in Microsoft excel.

Coding in VBA can be time consuming and complicated, problems can also occur but as mentioned built in functions to help with calculations of the data will help minimise the problems. The built in functions in Microsoft excel work well with Visual Basic Applications (VBA) and will be used to calculate the main objectives which are GBM and V-a-R values.

‘Loops’ and ‘for statements’ will be applied in VBA in order for the software to run smoothly and for calculations to be done correctly. Validation will be put in place to reduce errors.

3.3 Initial overview of software

Microsoft Excel and visual basic application (VBA) will be used as this uses Microsoft graphic user interface which is familiar with most users. Both applications work well together and have many built in functions that will make the process of calculations less time consuming and more user friendly to use, as discussed in section 3.2.

Key aims to accomplish:

> With the historical prices of stocks the covariance will be calculated (returns).

> User forms will be created making it user friendly and easy to use, creating option buttons, list boxes, text boxes and command buttons.

> Geometric Brownian motion (Ito’s lemma) will be implemented into the coding. The style of the coding will make the software accept user defined variables such as implementing error coding, then implementing output coding to show the GBM and V-a-R values of the chosen stock from the user.

; The user will be asked to select one stock, once a stock is chosen the mui and sigma of the stock will be calculated as well as its initial stock value. This will be done using a button function in VBA.

; The user will then be able to proceed to select a time step of daily, weekly or monthly.

; The user will proceed to select confidence levels of either 95% or 99%, output a graph and calculate the V-a-R value.

; The user will finally be able to analyse its chosen stock and see whether it is risky or not by the outputs obtained.

Chapter 4: Design implementation

4.1 Improvements from the design stage

During the implementation stage of the software a few changes have been made in order to improve the initial design. The user would have been able to enter in how many simulations they required in user form 3. However, after some consideration it was decided that it would be best to have the user with options to select from, as throughout the software the user is not required to fill in any fields therefore it was best to keep it all option based. This would hopefully give a positive effect on users using the software as no errors could be made inputting data because that would not be an option.

During the implementation of user form 4 called ‘GBM’ the trend line on the graph has been removed as it could add confusion as to what the graph will be illustrating; keeping the graph as a histogram on its own illustrating the frequency of future stock prices would make it more user-friendly and easy to understand.

Validation checks have been put in place in user forms 4 and 5, before the user can proceed to the next user form the command buttons ‘Calculate GBM’ and ‘Calculate V-a-R’ should have been clicked and results displayed in order for the ‘Next’ command button to be enabled and for the user to proceed. This was decided to make sure the user gets all the findings needed and makes use of the whole software.

User form 6 no longer will have a command button called ‘Back’, as once the user has got their GBM findings illustrated in a histogram and their V-a-R calculated and outputted in labelled text boxes, the user would surely be satisfied before proceeding to finish, so therefore the ‘Back’ command button has been removed from user form 6. This user form will allow the user to exit the software or to start again.

Chapter 5: Testing

5.1 Introduction

This chapter will consist of testing different aspects of the software. This will ensure that the software is working accurately and the way it has been programmed to do. More importantly it should output GBM and V-a-R figures correctly and appropriately, this chapter will demonstrate that these have been tested and found to be successful. During the implementation stage validations had been put in place, this chapter will also test to verify that these validations are done correctly and put in the right places; this is to ensure the user will not be misled in any parts of the software.

There will be two test tables in this chapter, one table will consist of the functionality of the software and the other the validations of the software. Functionality of the software will test all command buttons, option buttons and text boxes, making sure that outputs are outputted into the correct places and into the correct text boxes. The validation testing will ensure that all validations are working perfectly and the users input will be managed via error messages if inputs are not done precisely.

The purpose of testing and what the outcome should be and whether it has been successful will be shown in tables throughout this chapter. Each test will be individually illustrated using screen shots from the software while it is being run.