Building a Simulation in Excel
Impact of Random Returns
About a month or so ago, I did a post with Khalen Dwyer of Honest Math which provided a retirement simulator. His project grew out of an assignment that I developed for my Investments class when I was teaching. The purpose at the time I created it was that many students were struggling with some of the aspects of Excel. Given how important Excel is in the world of finance (since that time there have been some other powerful tools developed such as Google Sheets), I thought it was important that students leave our finance program with an understanding (or at least having had the opportunity to develop an understanding) of a small piece of using Excel. From my experience, one of the more effective ways to get comfortable using software is not to go through exercises, but instead to dive into a project which would cause you to get stuck. The reason is that we tend to learn more from our mistakes than from just following simple steps. Since Substack recently expanded the power of their platform to allow embedded Excel files, I thought that this would be a great topic for this week’s newsletter. Hopefully, I’ll be taking advantage of this again in the near future.
Warning — Technical Junk Ahead
First, a disclaimer. I am NOT an Excel expert. I know enough to make basic use of Excel. However, I would argue that the number of people who use Excel to even 40% of its functionality is a VERY small subset of the population…even within the finance community. I may get 15-20% of the benefits of Excel (and that could be a generous assumption). It is an extremely powerful tool. With that disclaimer aside, we’re going to get into some basics. If you aren’t interested in learning to use Excel, it probably indicates a higher level of mental health…so stop reading here and go do something more rewarding. On the other hand, if you are interested it probably means you (like myself) have some strange behavioral issues that ensure you will not fit in at most parties.
Here is the Spreadsheet
At the bottom of the column, I’ve put in a video illustrating the concepts discussed in this week’s update. This way you can download the spreadsheet to play around with it if you want. You can also watch the video if that helps. Just remember that none of this is meant as THE way to approach this type of project.
Cell References — Relative vs. Absolute
One of the important aspects of Excel is using cell references. Excel is essentially a giant “what-if” calculator that allows us to change inputs and see what happens to the results. Note that like any calculator, it falls under the garbage-in, garbage-out framework. This means assuming I’m going to earn average annual rates of 25% per year for the next 30 years may look nice, but is about as realistic as me deciding to play in the NBA next year. Granted, if you’ve seen my vertical jump or basketball skills, you’d know the 25% average annual return has a much higher probability of happening. Remember the False Precision update. These tools are approximations, not precision instruments.
One common mistake that I regularly saw students make was to put numbers directly into a formula. While that made it quick to set up a formula, it made it very difficult to take advantage of what Excel can do…what-if analysis. Whenever setting up a formula, you want to use cell references.
Here you can see a basic future value calculation. Note that instead of putting 0.08 into the formula (for 8%), I used the cell reference to make it B1 (column-row) and did the same for the rest of the inputs. I made B3 and B4 negative as that influences the way that Excel “thinks”. The last entry into the formula recognizes that the $1000 per year is coming at the end of each year instead of the beginning of each year.1 By using cell reference, I can easily change any of the inputs and Excel will automatically update my result. At 8%, I will have $36,075.81 at the end of 10 years. At 7%, that will fall to $33,487.96.
However, if I copy the formula to another cell (either dragging it down or across), my formula will no longer work. For example, if I try to copy it down into cell B7 by dragging it down, the cell references will all add a row (so my formula will be looking to B2 instead of B1 for my rate of return). If I try to drag it over to cell C6, my formula will be looking to C1 instead of B1 for my rate of return). That is because these are relative cell references which automatically adjust based on where the formula is. Excel doesn’t read B1 in the original formula. Instead, it reads “go up 5 rows from here”. If I am in C6, 5 rows up is in C1. This can be very useful in some situations, but frustrating in others. To lock the formula into ALWAYS going to B1, we need to make it an absolute cell reference. To do that, simply change B1 to $B$1. The $ tells Excel to always go to that column/row.2
Getting comfortable with relative and absolute cell references is essential to getting comfortable in Excel. This will allow you much greater ease in taking advantage of the “what-if” nature of Excel spreadsheets. There are places for both styles.
Relative Cell Reference — The formula just puts in the cell reference without $ anchors. These allow Excel to automatically update the formula as you copy the formula.
Absolute Cell Reference — The formula includes $ anchors. These tell Excel to go to a specific column/row for the input.
Freezing Panes
The ability to freeze panes allows you (or the spreadsheet user) to see a column and/or row heading. This can be a huge advantage when you have a larger spreadsheet that doesn’t all fit on one screen. Here is a screenshot with freeze panes off and one with it on. Note that the second (with it on) allows me to scroll down and still see the headings. While not shown, I could also scroll over and would still see the first couple rows.
This leads to an essential element of creating a spreadsheet. Often when you are creating a spreadsheet, you want to think of the user. For example, if I’m creating this as a tool to talk to a client about ranges of outcomes, I want to summarize the results in a place that is easy to see instead of having to search for them.
Creating Random Returns
Normally, when generating random numbers, a simple Gaussian (aka “normal”) distribution is used. However, returns are a bit different than many variables in that returns tend to be more lognormally distributed than the traditional bell-shaped normal distribution.
This is because returns on most securities is capped to the downside at -100% (you lose your entire investment). However, the upside is not capped (as owners of Tesla could tell you given that the total return since 3-18-2020 through 11-8-2021 is just a touch over 1500%!!). Therefore, a lognormal return is a better estimate (and remember, these are all just estimates — actual returns also exhibit greater kurtosis than these distributions would model. To generate a random return using a lognormal distribution in Excel you would use the following formula:
=LOGNORM.INV(RAND(),Return,SD)-1
where the return and standard deviation are entered as decimals. Therefore, an 8% return with a 15% standard deviation would be entered as
=LOGNORM.INV(RAND(),0.08,0.15)-1
One issue with random numbers (like these returns) in Excel is that they recalculate automatically whenever you do a calculation. You can turn that off or automatically regenerate the returns by pressing F9 or using the following from the toolbar. We’ll go through this in the video.
Monte Carlo Simulation
A Monte Carlo simulation is essentially just generating random numbers with some set of guidelines to recognize the inherent unpredictability of the future. It doesn’t mean that we don’t have some ideas of what could happen, but it does imply that there is a range of possible outcomes. More complex MC simulations will allow for multiple variables and even interactions across variables. Our simulation is going to be relatively basic and just allow the returns to vary randomly. Instead of saying that we anticipate an 8% return each and every year, we are going to set an expected return and a standard deviation and allow Excel to generate random returns from a lognormal distribution with that expected return and standard deviation. Even if we average an 8% return, the amount we accumulate can be much higher or lower depending on the timing of returns. Low returns early and high returns later are much better during the accumulation phase. Once we retire, it flips so that high returns early and low returns later are much better. This is due to the concept of dollar-weighted returns.
One challenge is figuring out how many simulated trials we should have. More is better (up to a point). Three trials is better than one, but is still too small to appreciate the randomness. Probably somewhere around 1000 would be appropriate. Unfortunately, that introduces the challenge of interpretation as now you have a distributions of outcomes. If you have a chance of retiring with $500,000 or $3,600,000…what does that tell you? If you are looking at retiring with $1,200,000 and find out that there is a 35% chance that your retirement nest egg will last 30 years and you are currently 60, what does that imply? You might be dining on steak and lobster or you might be dining on ramen noodles in your golden years? Our brains like concrete answers better than ranges even when the ranges are likely more accurate.
Let’s Go To The Video
Probably the best way to cover this is with a video. I understand if you don’t want to watch as it will be a bit of a time commitment. That said, I’ll try to keep it relatively short (apologies in advance if my definition of short —which turned out to be about 17 minutes — is longer than yours).
A few quick clarifications if you haven’t done time value of money calculations before in Excel or a financial calculator. First, the rate of return in Excel is entered as a decimal (if you put it in as 8%, Excel makes that adjustment…if you put it in as 8, Excel thinks it is 800%). The default number of periods per year is 1 (we’ll adjust that later). Financial calculators and Excel are always trying to balance cash inflows (positive) and cash outflows (negative). In order to receive (cash inflow = positive) my money in the future, I need to give up (cash outflow = negative) my money over the next 10 years. Be careful with this as making the PV (how much we put away initially) positive and PMT (how much we put away each period) negative will give you a different answer than making both negative. Finally, we need to tell Excel whether the PMTs are coming at the beginning of each period or the end of each period. A 0 tells Excel that they are coming at the end. As this is the standard approach, if you leave it blank, Excel assumes a 0 (ordinary annuity).
For example, $B1 would tell Excel to ALWAYS go to column B, but to go up 5 rows. B$1 would tell Excel to ALWAYS go to row 1, but to stay in the same column. $B$1 tells Excel to ALWAYS go exactly to B1. You can get a breakdown here — http://web.pdx.edu/~stipakb/CellRefs.htm