We have the time interval, the mean daily return and the standard deviation of daily returns as well as our pseudo-random number generation. With this equation, we’re utilizing the three inputs we defined. For more detailed discussion and explanation of the relevant equations, you can see this discussion of lognormal stock price distributions and conversion from normal to lognormal. With that column established, next to it we will enter an equation which may at first seem a little confusing and intimidating. First, we want to take a number of days from zero (to reflect todays price) all the way down to 252 (we want to forecast the price one year out). The standard deviation function is simply called on the same range as the average function is, and the number of trading days/year (somewhat confusingly named, I apologize) cell contains the formula “=1/252”. Not every year has 252 trading days – but this is close to the average number of trading days per year and is a commonly accepted figure. To accurately adjust our returns, we will use the figure of 252 trading days per year.
Remember that an observation in this example is a stock price – stocks do not trade on weekends and holidays. However, using 365 days would mean that there are 365 observations in a year. A common mistake is to utilize 365 days in order to adjust returns to or from an annual basis. We’ve chosen daily returns, which can sometimes confuse. If our chosen time period is monthly for example, then we would need to use 1/12 to find our monthly return. To effectively annualize, we simply divide one by the number of periods in a year. Daily, weekly and monthly are the most popular intervals. Really, you can pick any interval you desire. Stock returns can be measured and forecast based on minute to minute, day to day, month to month, or year to year intervals. At this point we’ll also address the need to adjust the formula we’re going to be using to reflect our chosen interval. Most importantly, we need the mean daily return, and the daily standard deviation.
With this figure calculated for all of our observations, we can gather some summary statistics. We’ll use the LN function to determine the daily return, see below: Our second step is to figure out what a typical return is for the stock, and what the risk (standard deviation) associated with a typical return is. I then edited the list Yahoo! spits out so that I was left only with a column for date and a column for adjusted return, see the set up below: There are numerous methods to automatically retrieve stock data from various online sources but today I just went to Yahoo! Finance and manually downloaded the required data. We’ll use daily returns and we’ll be utilizing the entire history of its returns (it IPOed in 2014). In this case we’re going to look at Opus Bank, ticker OPB. Our first step is to retrieve the stock returns for our chosen ticker/company, using the chosen time frame and interval. Technically speaking all of the methods will use Excel for this series, but first we’re going to start with native Excel functions alone and zero VBA. Secondly, remember that a Monte Carlo simulation requires a set of input parameters (typically mean and standard deviation) to use for generation of random or pseudo random numbers within the model. Let’s run over a few key concepts that will be key to understanding the model we’re about to put together.įirst and arguably most importantly is the assumption that stock prices follow a lognormal distribution (and therefore stock returns are normally distributed. I’m curious – was our price target accurate? A Quick Review Because of that, I’m always interested to see what’s going on with the firm and its price. I admit that this firm is a special interest of mine, as I had the privilege of preparing an equity research report with several other excellent student analyst during my undergraduate studies. Today we’re going to use a fairly basic Monte Carlo simulation to try to forecast the stock price of a small cap bank – Opus Bank, or as I’m going to refer to it, OPB.
MONTE CARLO IN EXCEL AVERAGE AND STANDARD DEVIATION SERIES
In parallel to our ongoing series introducing different Monte Carlo techniques I thought it would be fun to incorporate more case studies and applications.
0 kommentar(er)
