Basic Monte Carlo Simulation of a Stock Portfolio in Excel

okay I'm going to demonstrate how to make a basic in spreadsheet Monte Carlo simulation of a investment portfolio and here what I'm doing is assuming that I have starting with a hundred thousand dollars and I'm going to invest it in SP 500 where I think I can average 11.2% a year with a standard deviation or volatility of eighteen percent per year okay I'm going to say that I have 30 years till retirement and I'm going to invest in additional ten thousand dollars per year and I want to get an idea of what that will be worth thirty years from now okay so the first thing I'm going to do is I've already set some of this up I'm going to generate a random rate of return for one year from today and I'm going to do that using the norm inverse function all right so we're going to assume that the rate of return follows a normal distribution all right I'm going to randomly get one using the random function okay and then the average of that normal distribution is going to be eleven point two percent with a volatility or standard deviation measurement of 18 percent alright so we can see that okay one possible return is is somewhere over 36 percent if I generate another return okay we have eleven point three percent okay and and so on for several different possible returns a year from now all right the ending balance okay there's a lot of ways to do this I'm going to just take the beginning bounce and I'm going to multiply that by one plus the annual rate of return all right and then I'm going to assume that I make my investment at the end of the year okay all right so we can see here that this is a pretty good example actually lucky if I invest if I have a hundred thousand dollars and I earn a point eight nine percent I'm going to earn eight hundred ninety or even hundred ninety four dollars here and then if I add ten thousand with that right I end up with 110 894 okay all right so that's how I generate one possible ending value all right and then we have thirty years so I'm going to copy this formula down okay so there's a stream of possible returns in 30 years time okay and then the ending balance in the second year it's almost the same as the first ending balance so I am just going to actually copy this I'm not going to do that yet I'm going to absolute reference this I'm going to copy it down then and then change the formula to update the the beginning balance okay so actually the beginning balance in year two is the ending balance in year one okay all right and then after you make that one adjustment we should be able to just copy this formula down for the 30 years all right and let me let me put the the final formulas up there okay so that's how we generate a return and then here's how we figure out what the ending balance is okay and then we just need to sort of collect what the kind of the ending balances at the end of year 30 all right so that's going to be an F 39 and so I want to record that up here okay so one possible outcome we have is is ending with about 3.5 million dollars okay another one is ending with about 4.4 and then there's 1.2 18 million 4.5 3.5 all right so that there there seems to be a big a big range of ending values we could have a and which one do we choose well we're not really sure which one to choose because we don't know what what's going to happen in the future so instead what we're going to do is sort of try to characterize some of the things that could happen by generating many many possible ending values all right so that's what I'm going to do down here so I've set up a table and I'm going to generate a thousand possible ending values and hopefully from that be able to get a reasonable idea of what my ending value will be all right so first thing I'm going to do is reference the ending value that we got from F 39 there okay and then I'm going to select my whole range we can see that I ended a thousand all right and then I'm going to use the the data table tool alright and I'm not going to use it how it is conventionally used or I want to sort of trick it into just pointing at a blank cell so there is no input here okay and then what it will do in turn is just generate a thousand different ending values okay all right and so I format this as currency so we can see it a little better the decimals okay so um there we have a thousand possible ending values all right for our our portfolio okay all right and so then I'm just going to summarize those possible ending values up here getting pealing off some statistics all right so I'll take the average obviously we won't have the average portfolio all right but here is a central tendency number all right so we tend to have 4.3 million dollars when I run this if I run the simulation again all right so generate a new stream of random numbers all right I'm going to have things that are fairly consistent fairly close to that 4.3 that we had initially okay we're going to see if this distribution has any skew to it so I'm going to get the median okay and it probably does have some skew since the lowest value it can have is 0 highest values out there all right so yes we can see from this that the 50th percentile is about 3.2 million and again if I run this simulation a number of times I should get values that are right around there okay and where the the mean is much higher than the median we can see that this distribution is very positively skewed okay all right so still haven't really centered it on okay well what will we have at the end we don't know we don't know how much we're going to have at the end but we can make probability statements about what we'll have at the end I'm probably the best way to do that is with percentiles okay or I'm going to get oops needs percent on here all right several different percentile functions as you can see I'm going to use this one percentile inclusive and what that does is it includes the the zero and hundred percentiles are the the end posts alright if you use the exclusive one it excludes those all right so I think the inclusive is a little more correct for what we're doing here all right so I'm going to get the fifth percentile okay and based on this the probability statement we can make is that there is a 95% chance that we will have somewhere more than nine hundred sixty-four thousand dollars at the end of thirty years if returns continue as they sort of historically have okay so this eleven point two and the eighteen percent standard deviation okay so we can repeat this process with the twenty-fifth percentile or any percentile you're interested in okay all right I'm going to use this again same right here okay and so from this we can say yes we have a 25 percent chance of having somewhere less than one point seven million or one point almost eight million all right any 75 percent chance of having something more than one point seven million okay all right and then we can just sort of see just how volatile how spread out the possible ending values are using the standard deviation function all right I'm going to use this dot ass version all right which tells me I have a sample right but once I have a thousand duration it doesn't really matter too much that we make the correction for for for sample versus population but we'll do it just to make sure we're absolutely correct and then you can see just how volatile this this kind of investing is all right and this actually is not too far off from what you can expect or what has happened in the past investing S&P 500 okay then that's a simple Monte Carlo simulation using just just the know atoms and in just what we have in Excel

Channel: Matt Macarty

Views: 294,407

Rating: 4.9065309 out of 5

Keywords: monte carlo simulation excel, excel monte carlo simulation, monte carlo finance, monte carlo simulation, monte carlo simulations, monte carlo simulation tutorial, investing simulation, Matt Macarty, excel, statistics, simulation, stock market simulation, risk, portfolio theory, data analysis, monte carlo, investment, investing, index function, monte carlo method, stock market simulator, stocks, math, forecasting

Id: Q5Fw2IRMjPQ

Length: 11min 30sec (690 seconds)

Published: Sun Feb 24 2013