financial modelling topic eight dynamic portfolio simulations Monte Carlo personal financial planning models and using at risk copyright Louganis in this topic we're going to simulate portfolios with multiple periods changing asset allocation and contributions we ran create a personal financial planning model then use at risk and macros to run Monte Carlo simulations will also look at at risk which is a Monte Carlo simulation software money at risk is available of palisades there's a 15 day free trial if you like to follow along so here's our basic model we can copy this but in excel or you can build it yourself we'll start with some assumptions will say our individual has a $55,000 income saving fifteen percent of their income for retirement inflation and salary growth or two and a half percent currently 25 years old have one dollar saved plan to live 30 years after retirement can retire at the age of 65 my capital market assumptions I have correlations between stocks and bonds of 0.3 stocks and cash ups zero in cash and bonds of 0.1 I'll use expected returns of stock bonds and cash of seven and a half three and a half two and a half and volatilities of sixteen eight and two with that I can calculate a covariance matrix by combining the correlations and the standard deviations to calculate covariance matrices I can then simulate this portfolio over time so person starts out with $25,000 their annual contribution it's going to be fifteen percent of their salary and I'm going to use a simple rule for allocating their assets over their life I'm going to assume they use the 120 minus your age rule of thumb for asset allocation so at the age of 25 you take 120 minus 25 our 95 percent of their portfolio and equity I'll put the and bonds and I'll use no cash for this example so at the age of 20 595 5 at the age of 65 you're 55 percent stock 45% bond and I've just hidden some rows here so I could highlight this and unhide it but it's just easier to look at with some hidden rows just copied these formulas down having salary grow at the salary growth rate contribution always being 15% and my out allocation going down every year to stocks and going up every year to bonds I think calculate the annual mean and standard deviation of the portfolio based on these weights so the mean of that portfolio is going to be the weights times the means using matrix operations that would be H the vector weights here times the mean and then my portfolio standard deviation using matrix operations will be the am mult of the row vector of weights times the covariance matrix times a transpose of the weights to the one-half power and you can see your portfolio mean goes down over time and so does a standard deviation my ending portfolio balance would just be my initial portfolio balance plus 1 plus times 1 plus the mean return of the portfolio plus the contribution and assume these are year-end contributions therefore the return is not on the you don't hear you not get a 7.3 percent on the contribution only on the $1 and then I just copy that formula down and I get an expected portfolio value and I just went and took this out to 85 years even though this person is going to retire here in age 65 would expect a portfolio value of 2.05 1 million I just have a vlookup looking in this area for the age 65 and returns the value in the 1 2 3 4 5 6 7 8 column so you can or a 9th column so you can see I'm looking for age 65 so if I do say 2 person retires at 75 it would pick the portfolio value at the age of 75 of 2.8 I'm sorry 3.8 so I'll change that back to 65 years old so here's our basic model right the expected portfolio value is 2.0 5 1 million dollars and then I make a couple other calculations if someone breached retirement portfolio 2.0 5 million and then go out and buy an annuity or create an annuity for 30 years there were years in retirement and assuming that portfolio is invested in cash I'm sorry bonds that has an expected return of three and a half percent I just do a payment calculation that says that that person should be able to create an annual annuity of a hundred and eleven thousand dollars so that would be a at age 65 you have a present value of two million fifty one you're looking for a 30-year payment where a mean return is three point five percent and that you should be able to generate an annual level annuity $111,000 and I make one other calculation that since this is 40 years from now this first payments gonna be over 41 years from now or 40 years from now I calculate the real value of that first payment in other words in current dollars adjusted for inflation so I take the 111 discount of at two and a half percent the inflation rate for 40 years okay so that's like retiring on forty one thousand dollars of today and that's that's all about the general price level increases of two and a half percent so now I have the expected portfolio value of 2.5 million dollars and you'll generate a retirement annuity of 111th expected and on real terms that's 41 thousand dollars so that's my model we're going to calculate a couple key ratios in retirement planning the first ratio is wealth to final your income final yoke retirement wealth at final year income and the second one is income replacement so wealth to income I'll just put it over here we call it wealth to income would be your expected portfolio or your portfolio about value at retirement divided by your final year salary which would be my age 65 salary 147,000 so I'll have an expected wealth to income ratio retirement of thirteen point nine which is very good and I can have an income replacement rate so if I generate a salary or a retirement annuity 111 and I divide that by my final year salary about 147 I'm effectively replacing 75 percent of my salary in retirement so I would just change formattings on those and highlight them a little bit so these are key metrics in retirement planning what is your portfolio value about retirement compared to your final your wealth this time I have 13 times or almost 14 times my final your income and wealth and I'm able to replace 75 percent of my income let's talk about these little bit more the wealth income is an important measure because it's a standardized measure of savings adjusted for income so so in making $50,000 a year would not need or be expected to had the same level of portfolio of savings for retirement that sell making say triple that resource suggested that ratios can be between say five and fifteen word is higher for those that have higher incomes so the more money you make out retirement the more of a multiple you'll need income replacement obviously the important is importance is if you're making say one hundred and forty seven thousand dollars your last year of working and you only generate a portfolio retirement savings our annuity of one hundred eleven thousand you have to live on seventy five percent of your pre-retirement income right now research suggests that only needs to be about thirty to seventy percent the reason why and first time first of all also for the for poorer individuals for lower income the number probably is closer to the lower end of that range and probably closer to the higher end of the range for middle or high income individuals and the question might come up well why do you not need a hundred percent if you want to say keep your standard of living in retirement and there's several reasons one is actually quite simple if I'm saying making $100,000 a pre retirement but I'm saving 15% for retirement I'm actually only consuming 85% of my pre-retirement income therefore I'd only need to replace 85% of my pre-retirement income to maintain my purchasing power or my consumption level so all else equal you subtract your savings rate from a hundred percent and that would tell you I only need to save seventy I only need to replace seventy five percent of my income another reason is we're not including say Social Security or even a pension from a private company these are just portfolio wealth and portfolio replacement now for people that are have lower incomes Social Security can be a large portion or to replace a large portion of their pre retirement incomes maybe up to 30 percent or more or 50 percent and further and for the higher income individuals every place is very little also in retirement sometimes there are situations where you pay less tax either your income is lower and you have lower tax brackets or retirement income is not taxed or you've already paid tax on retirement income and lastly sometimes in retirement you're able to cut expenses especially for the low income maybe low income individuals spend disproportionate amount of their money on say commuting costs and other labor related costs or job related cost once you retire maybe there's go away and for fire higher income individuals maybe that's not a significant so for those reasons you probably need to saved between five and fifteen times your final year income higher if you're more higher income and you need to replace somewhere between thirty and say 70% of your pre-retirement income so now we're going to do is do a portfolio simulation we're going to do that by using a random walk model when I've covered before and the random walk model says the portfolio value is equal to and your new portfolio value is equal to the one plus the expected return plus a random shock we call that a random variable Z which is a standard normal random number times the standard deviation so in this formula I have that my simulated portfolio value is equal to the value before plus one plus the mean so that dollars expected to grow by 7.3 percent the first year plus a random number and we're going to use our excel function norm s inverse R and that's going to generate a random number with a zero mean one standard deviation called a standard normal random number times a standard deviation so this is our random walk model of asset prices on average you will get the mean because this random shock has a mean of zero but in any given period you cannot predict what that random number is going to be or without rain of events going to be and then finally I'm going to add my flank end of your contribution so when I do that and copy this formula down and every time I hit ctrl 9 or function 9 I get a new value so every time I hit an enter I get a new value or if I can go to formulas calculate now and you'll get a new value for the simulated portfolio value so we can see in year 3 I did a recalculate f9 or if I go formula recalculate or calculate now is 16 6 to 2 that'll change a little bit it doesn't change much this year really the shock is only a one dollar portfolio but you can go down to say like my year 65 income here for my said you're 65 portfolio value it's expected to be 205 one but every time I do a formula calculate now I get a new random variable based on 45 random shocks which are the annual shocks to the portfolio and markets let me copy these ratios down here I'm gonna set up a look up here I have a look up here that grabs what's in column 9 and I get a similar look up I'll just change this a little bit looking for age 65 in this area and grabs it what is in column 10 okay so one one seven six is now my age sixty-five random number one 176 so every time I hit f9 I'm gonna get a new random simulate a portfolio value and let me update these formulas here that should be the income or portfolio value over age 65 income changing portfolio value over age 65 income 147 and my replacement rate annuity divided by final year income wait so every time again I hit o f9 I get a simulated wealth to income ratio a simulated replacement rate now what I'd like to do is say copy this value recalculate it say five thousand times and then look at say what the average portfolio value is and maybe what some of the highest and lowest numbers are to give an idea how risky this strategy is so I'm going to grab a function I'm gonna call its import 5000 I'm sorry on the macro and what it does it's a loop from I equal 1 to 5,000 next I so it's going to run the calculation 5000 times its could have put in cells 912 my ninth row 12th column 5000 minus I so I is going to go from 1 to 5000 this turns into a countdown for 4999 to zero then it's going to put in the 11th or 12th row 12th column whatever's in cells 7 10 which happens to be this simulated portfolio value right here that's the 7th row 10th column of the spreadsheet and then since it's 11 plus I as I goes to to its going to put it in a thirteenth row fourteenth row fifteenth row so I'm going to grab that function copy it go to excel I'm gonna hit go to my macro or my VBA area developer Visual Basic I'm going to insert a module and I'm gonna paste it in so I can verify now that's in my excel file in module one it's called symport 5000 and then I'm going to insert an art object and I'll just use that to run my macro put that right there I'm not gonna right-click on that sign macro now every time I hit this shape it's now going to run my macro what it's going to do it's going to take the value in this cell here and paste it over here again and again and again and keep moving it down one row and also do a countdown so here's the countdown going from 5000 to 0 these are the 5000 simulated portfolios based on the random walk model each one of these numbers represents a series of 45 shocks that are independent that affect our portfolio value so when this is done we're going to look and see what the distribution of these look like all done looks like we have 5000 up and let me take a look actually give those a range and a named range I'll call those Sims so I just give a name range of that column right now we're going to look at some of the look at the distribution of that series so let's look at the the mean the 5% largest the 5% smallest portfolio value and then we'll use that to calculate an annuity and real annuity suppose to simulation results analytics and we'll look at the mean portfolio and then we look at the 5% smallest 5% largest and then we'll also look at the retirement annuity and then real annuity so if I just take an average of sims it's just an average of this column down here you can see my average is about 2.0 2 million which is close to what the expected result is and in fact if I did this for say a hundred thousand iterations on my macro I would start to converge towards this value here I can then calculate the smallest value in this by sorting this column and seeing what these say 5% smallest since there are 5,000 observations 5% of 5,000 is 250 so I would sort this from low to high and take the 250 is smallest and then I take the 250th large as black music sells small and large function so I can do this small of the array of sims and I'll look for the smallest 250 value so the smallest 250th value is eight hundred and some thousand dollars and the largest I can use excels large function is four million dollars based on that I can I can just use this met these calculations over here for real rib time annuity and real annuity and hopefully they'll link correctly so it's looking at the number above and calculating a payment and then looking to discount that so what you see is our portfolio it's expected to have a value of two point zero five million although with our limit at five thousand iterations we did get quite as close as I'd like but it'll be usually on average about two point oh five million however the five percent worst-case scenario is looking at these observations would be retiring on a portfolio value of 866 with in gives us an annuity of only in real terms seventeen thousand dollars and the five ten five percent largest gives us an annuity of eighty-one thousand dollars so the simulation allows you to think about how your choice of portfolio weights and asset allocation which then affects the portfolio standard deviation how that affects the risk that you do not achieve your expected portfolio value of about two million dollars so this is the process of Monte Carlo simulation create a model where you just choose some outputs or calculations you're looking to analyze that would be say mean real retirement annuity and real annuity that we then select a variable that we can make a stochastic variable that variable is asset return we'll assume that ass returns are normally distributed will then simulate many different acid random variables asked returning random variables using my normal s inverse ran so every time I hit recalc and actually every time I the macro case a number here an automatic recalc occurs that's why I didn't have to do a recalculate a series of 45 random numbers for the norm s inverse ran and they're all different inside these cells that'll generate a new portfolio value paste it here then I'll as that paste I get a series of 45 new randoms gives me a new portfolio value paste it here and then we evaluate the distribution of that those two variables to help us make decisions maybe we like to get a say for portfolio or more we like maybe you'd like to have more equity so that's simulation one little exercise we could do and practice that using macros is we could try to change our macro to also keep track of these other simulated variables say wealth to income this is portfolio value but what if you want to track wealth to income and income replacement well I'll need to keep track of where everything is that means as every time I hit f9 i get a new wealth income and new income replacement wealth to income is in 7/11 and replacement isn't 811 so I can go 7-eleven 811 let's take a look at my Visual Basic I could add some more code here I can copy this formula so I said 7-eleven and 811 or my values for wealth to income since 7-eleven 811 again my portfolio value is in 710 so one down one over would be I'm sorry one over would be seven eleven and then one down these eight eleven that looks right and then when I put it is in this column here now the portfolio is currently going into column twelve just put them in column thirteen and fourteen all right so it's going to put them in columns there when offsets I equal one we're going to get in column twelve I'm sorry wrote 12 column twelve thirteen or fourteen so if I were to run my macro now it should be taking the values here paste it here here paste it here here paste it here and there it goes I'm going to stop it before it ends by hitting escape twice and hit end nice little exercise to see how to use that macro last I'm going to do we're going to use at risk at risk is a software offered by palisades it allows you to to Monte Carlo simulation using their interface to use at risk we're going to just make a few modifications so for at risk to specify to where to generate a standard normal random variable you don't use norm s inverse R and use risk normal zero-one oh by the way you can't do this unless you've actually launched at risk and see the at risk tab up here so I've replaced norm s inverse ran with normal risk normal 0 1 now what's going to sit in the cells is zero so it's going to be zero time standard deviation so what's going to give you is actually the expected portfolio value I'll copy that down so now I've generate all those random numbers in these cells using at risk and then all I need to do is tell at risk the output that I want to keep track of in other words the output I want to see a graph of and I could highlight say all three of these if I'm gonna keep track of all three of these click on at risk at output cone at risk also you can see that if you go to your model window what you see is I inserted a bunch of risk normal 0 1 so this would be all those years and I created three outputs now if I click on my portfolio value and go to at risk and do 10,000 iterations it's going to generate 10,000 portfolio values based on sets of in this case forty five randoms each time and I'll start the simulation this will give me a graphical display of the histogram of portfolio values shows me my five percent smallest portfolio value eight hundred and sixty thousand foot day ten million notice that ties to what we got doing our homemade simulation and the five percent highest simulation is four point one five million which also roughly ties to what we did here with five thousand you want to see the other statistics we could just click on say real retirement annuity at risk browse results and see say the five percent Louis and five percent highest retirement annuity that's nominal now if we want to play with this we can also look at different intervals say what is the 80% confidence interval 10% in each tail so now you have the 10% lowest 10% highest or you can say what's the probability we retire on $50,000 a year well there's a 7% chance of retiring on less than 50 $50,000 a year and I could drag this over with a 93 percent chance I'll retire on more than $50,000 here so that's using at risk to run a simulation remember when you save a file that has a macro we do a file save as and save it as a macro enabled workbook and then it'll ask you whether you'd like to save your at risk results so in this topic we'll try to review we looked at simulating portfolios with multiple peer it's changing asset allocation contribution create a personal financial planning model used at risk and macros to run Monte Carlo simulations and the last thing I want to show you is using goal seek and at risk and at risk you can also do a goal seek just like you do in Excel kind of like a sovereign under advanced analysis goal seek we could ask say we want to set our simulated income replacement rate to a value of say 0.6 if we want so by changing savings rate so in this case we'd be if we determine that we only need to replace 60% of our income at risk we'll run ten run the simulations trying to figure out how much should you save to target a mean average replacement rate of 60% I can analyze it'll ask me to make us an output and that might take a minute it'll go faster than what you think it'll start off slowly then finish rather quickly and then we'll show you what savings rate you need to have a goal of replacing 70 70 or 60 percent of our income yes and looks like the answer is save 12% and what other thing ours can do is it can run another type of goal seek where you don't just target a mean say you want to have a percentile you want to no more than eight no no 25% chance retiring on 40% for replacing 40% so in this case you're targeting a percentile you're asking how much do we need to save so that our 25% lowest replacement rate is 40 percent not sure how this will go but we'll see what that number is the last number again was 12% see down here is doing 2,000 iterations per second it looks like that answer it is also about 12% all right so that's using at risk to do a goal seekers solver
