{"version":"1.0","provider_name":"Retirement Hangout","provider_url":"https:\/\/retirementhangout.com","author_name":"Hangout Host","author_url":"https:\/\/retirementhangout.com\/index.php\/author\/keith\/","title":"Make Your Own Retirement Spreadsheet - Part II - Retirement Hangout","type":"rich","width":600,"height":338,"html":"<blockquote class=\"wp-embedded-content\" data-secret=\"8NhmiLxzWw\"><a href=\"https:\/\/retirementhangout.com\/index.php\/2020\/01\/06\/make-your-own-retirement-spreadsheet-part-ii\/\">Make Your Own Retirement Spreadsheet &#8212; Part II<\/a><\/blockquote><iframe sandbox=\"allow-scripts\" security=\"restricted\" src=\"https:\/\/retirementhangout.com\/index.php\/2020\/01\/06\/make-your-own-retirement-spreadsheet-part-ii\/embed\/#?secret=8NhmiLxzWw\" width=\"600\" height=\"338\" title=\"&#8220;Make Your Own Retirement Spreadsheet &#8212; Part II&#8221; &#8212; Retirement Hangout\" data-secret=\"8NhmiLxzWw\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\" class=\"wp-embedded-content\"><\/iframe><script type=\"text\/javascript\">\n\/* <![CDATA[ *\/\n\/*! This file is auto-generated *\/\n!function(d,l){\"use strict\";l.querySelector&&d.addEventListener&&\"undefined\"!=typeof URL&&(d.wp=d.wp||{},d.wp.receiveEmbedMessage||(d.wp.receiveEmbedMessage=function(e){var t=e.data;if((t||t.secret||t.message||t.value)&&!\/[^a-zA-Z0-9]\/.test(t.secret)){for(var s,r,n,a=l.querySelectorAll('iframe[data-secret=\"'+t.secret+'\"]'),o=l.querySelectorAll('blockquote[data-secret=\"'+t.secret+'\"]'),c=new RegExp(\"^https?:$\",\"i\"),i=0;i<o.length;i++)o[i].style.display=\"none\";for(i=0;i<a.length;i++)s=a[i],e.source===s.contentWindow&&(s.removeAttribute(\"style\"),\"height\"===t.message?(1e3<(r=parseInt(t.value,10))?r=1e3:~~r<200&&(r=200),s.height=r):\"link\"===t.message&&(r=new URL(s.getAttribute(\"src\")),n=new URL(t.value),c.test(n.protocol))&&n.host===r.host&&l.activeElement===s&&(d.top.location.href=t.value))}},d.addEventListener(\"message\",d.wp.receiveEmbedMessage,!1),l.addEventListener(\"DOMContentLoaded\",function(){for(var e,t,s=l.querySelectorAll(\"iframe.wp-embedded-content\"),r=0;r<s.length;r++)(t=(e=s[r]).getAttribute(\"data-secret\"))||(t=Math.random().toString(36).substring(2,12),e.src+=\"#?secret=\"+t,e.setAttribute(\"data-secret\",t)),e.contentWindow.postMessage({message:\"ready\",secret:t},\"*\")},!1)))}(window,document);\n\/\/# sourceURL=https:\/\/retirementhangout.com\/wp-includes\/js\/wp-embed.min.js\n\/* ]]> *\/\n<\/script>\n","description":"In the previous post, I suggested reasons why you might want to create your own financial planning spreadsheet, and described the nuts and bolts of setting one up. But there&#8217;s still the problem of choosing reasonable inputs and interpreting results in a way that gives you insight into your financial future &#8212; rather than leading you astray. In this post, I go over how to make the most effective use of your spreadsheet plan: how to handle inflation, what you might assume about investment rate of return, how to do sensitivity and what-if analyses, and how to check your results against other tools. Inflation &#8212; What to Do With It There\u2019s little ambiguity about your current savings, income and expenditures \u2013 these are actual numbers that you know or can determine from your records.&nbsp; But what about the future?&nbsp; Should you be projecting numbers that include inflation?&nbsp; If so, what inflation rate should you use?&nbsp; And how should you think about the value of inflated savings 30 years or so in the future?&nbsp; You can do your projection either way: in inflated (or nominal) dollars; or in current (or real) dollars.&nbsp; Nominal dollars represent what you might actually expect to happen, but have the disadvantage that future dollar values are not the same as they are currently.&nbsp; In fact, with modest 3% per year inflation, a dollar in 30 years would buy less than 50 cents would today!&nbsp; Real dollars, on the other hand, net out inflation, so that a dollar in 30 years is worth the same as a dollar today.&nbsp; This makes interpretation of long-term planning results a lot easier, since you don\u2019t have to make a mental adjustment for decreased value.&nbsp; While either approach is perfectly correct, I like to do my planning in real dollars.&nbsp; I think it\u2019s simpler (no need to estimate inflation or build in escalating amounts for things likely to increase with inflation); and easier to interpret. Whichever way you go, make sure you\u2019re being consistent in your assumptions.&nbsp; Social security benefits, for example, are indexed to inflation (the CPI-U).&nbsp; If you are working in real\/current dollars, you can project social security into the future as a level benefit at the same amount as the first year you receive it.&nbsp; If you\u2019re working in nominal dollars, you should show Social Security benefits increasing over time, at some assumed average future inflation rate.&nbsp; Conversely, a level payment (such as a mortage payment) or income stream (say, an unindexed annuity) would work in the opposite way: you should project level amounts in future years under the nominal approach, but decreasing amounts if you\u2019re working in real dollars.&nbsp;&nbsp; By the way, it\u2019s important to understand this distinction whether or not you create your own planning spreadsheet.&nbsp; When you\u2019re looking at savings projections from online financial calculators, you need to understand whether you\u2019re looking at results in inflated or uninflated dollars; there can be a huge difference.&nbsp; Most tools will tell you whether results are inflated or not, or give you the choice \u2013 but sometimes they don\u2019t do either, which renders the software useless, in my opinion. What Rate of Return to Use? A primary function of your planning spreadsheet is to calculate how your savings will grow and compound over time.&nbsp; A critical assumption driving this growth is the rate of return you assume your investments will earn.&nbsp; But what rate of return (ROR) should you use?&nbsp; No one, of course, knows what returns stocks and bonds will bring in the future.&nbsp; Fortunately, over long periods of time (decades), the gyrations of the markets smooth out and returns become somewhat more predictable.&nbsp; Over the last hundred years, stocks have returned about 10%, on average, annually.&nbsp; Inflation has averaged 3%, so real returns have been about 7%.&nbsp; Bonds have returned about 5.5%, or 2.5% net of inflation (with much less volatility!).&nbsp; (Ibbotson )&nbsp; Most investors will have a mixed stock and bond portfolio, which over the same time period would have returned about 8% nominal, or 5% real.&nbsp; (Vanguard) So, should you just assume 5% real returns on your investments?&nbsp; If you are 30 years old, looking forward to many decades of working, saving, and investing, this would not be a bad assumption.&nbsp; However, for those in their 50s or beyond, a more conservative ROR assumption probably makes more sense, for the following reasons:&nbsp; Many experts believe returns over the next decade will be below historical averages, given the low interest rate on bonds and the high valuation (compared to history) of the stock market.&nbsp; (Waggoner, Benz, Perianan.) Most people won\u2019t achieve pure market returns because they will keep some savings in cash (say, for a down payment or other near-term need), will incur management fees, or will pull investments in or out of the market at the wrong times in a counterproductive response to stock ups and downs.&nbsp; Studies have found that individual investors underperform the markets, although there is some disagreement about how big this gap really is. (Hallan, Roberts, Sommer.) Good retirement planning should be conservative so as to minimize unpleasant surprises.&nbsp; Most people would like to know that there\u2019s a high probability that their money will last their lifetimes, so it makes sense to model something more conservative than average returns. The risk of poor or negative returns is very real; according to Vanguard, a 50% stock\/50% bond portfolio would have lost money in 18 out of 93 years since 1926.&nbsp; In fact, the stock market has gone whole decades without any gain at all (most recently in the 2000s).&nbsp;&nbsp; Retirees especially need to protect themselves against sequence of returns risk \u2013 a string of bad returns early in retirement that depletes their portfolios so much that recovery is impossible even if\/when good times return. For all these reasons, it&#8217;s prudent to use a conservative real rate of return below 5% &#8212; say, 4% or even 3% (remember, this is still a 7% or 6% nominal return) &#8212; to model long-term investment returns in your planning, assuming you have a mixed stock\/bond portfolio.&nbsp; It\u2019s also very important to do sensitivity analysis to see how things would turn out under different rates of return, and to compare your results with online calculators that run probabilistic analysis . More Tips Keep it simple.&nbsp; Make your planning spreadsheet as simple as you can, while still providing the detail that you need.&nbsp; Keep the excruciating details of your annual budget and your million and one savings and investment accounts somewhere else; all you need for your planning spreadsheet are your bottom-line annual spending, income, and savings\/investments amounts.&nbsp; A bit of granularity is useful; it\u2019s helpful to break savings out into at least retirement (tax-advantaged) and non-retirement (taxable) buckets.&nbsp; Many people will also want to break out college savings.&nbsp; But resist the urge to add too many line items, bells and whistles; you will find that your simple, elegant spreadsheet can quickly become complex, unwieldy and confusing. Display important outputs.&nbsp; Do you envy the cool graphs and displays that the online calculators have?&nbsp; Create your own!&nbsp; A simple approach is to hide columns so that you\u2019re only looking at periodic, milestone results, as shown here: Or you can build a summary table, such as the one displayed below, that allows you to see the essential results in your spreadsheet without taking ten minutes to scroll from one side to the other.&nbsp; You can also create colorful charts that show information of interest \u2013 your total savings by year, your cash flow, or whatever else interests you. In the chart below, total liquid savings\/investments are shown over time for the same apocryphal couple used as an example above and in the previous post: &nbsp; Now your spreadsheet is starting to look a lot like those glitzy online software tools! Update your spreadsheet regularly.&nbsp; You will need to maintain your spreadsheet.&nbsp; This can be as simple as sitting down once a year (New Year&#8217;s is a good time) and replacing the first column of estimated numbers with your current, updated savings and investments.&nbsp; Don\u2019t erase the future or the past, just change the one column that is the year just past. At the same time, it\u2019s a good idea to review whether any of your other assumptions need updating. What about taxes?&nbsp; There are a number of different ways to handle income and property taxes.&nbsp; In my own version of this spreadsheet, I have different rates of return for tax-advantaged and taxable accounts, because I think it\u2019s helpful to see the difference.&nbsp; In the income\/expenditure section, you can (a) simply lump taxes in with other expenditures, (b) give them their own line item(s), or (c) try to have the spreadsheet calculate what your taxes are likely to be, based on your taxable income.&nbsp; (I tried this and eventually gave up \u2013 taxes are difficult to estimate based on just a few numbers.)&nbsp; Beware negative compounding!&nbsp; Be careful about what happens if any of your savings\/investment buckets go negative.&nbsp; If you\u2019re not careful, your spreadsheet will continue merrily compounding negative returns until the cows home (or at least until age 95).&nbsp; In real life, of course, you would stop spending from the account that\u2019s run dry and switch your withdrawals to a different account.&nbsp; One way to avoid this problem is to construct if\/then formulas that stop the losses if an account falls to zero and switch future withdrawals to a different account.&nbsp; Sensitivity Analysis and \u201cWhat-if\u201d Scenarios Now for the fun part!&nbsp; One of the great things about having your own planning spreadsheet is that you can do sensitivity analysis to find out which assumptions have the most impact on your plan.&nbsp; Perhaps the most important variable to test in this way is your assumed rate of return.&nbsp; For example, let&#8217;s compare long-term savings projections for our example couple using an optimistic 5% real rate of return (about average for a 50 to 60% stock portfolio historically): &#8230; with a similar projection using a more pessimistic\/conservative 3% rate of return: Note the dramatic impact of a seemingly small difference in the rate of return. If your retirement plan looks good at 5% or 4% real return, but falls apart at 3%, that\u2019s important to know.&nbsp; Once you&#8217;re aware of the risk, you can explore ways to create greater income certainty, such as deferring Social Security or purchasing retirement credits in a pension plan.&nbsp; \u201cWhat-if\u201d scenarios are similar, but reflect choices you may be considering. Suppose you\u2019re planning to retire at 65, but you\u2019d secretly like to know whether you could retire earlier (a not uncommon question!).&nbsp; With your planning spreadsheet, you could examine alternative scenarios in which you retire at 62, 60, even 55.&nbsp;&nbsp; Or you might wonder how taking a one-year unpaid sabbatical would affect your retirement plans; go ahead and model a year without income.&nbsp; Many people have retirement dreams that involve big splurges &#8212; buying a beach home or sailboat or RV, or taking a year to do the Grand Tour.&nbsp; You can examine these scenarios, too.&nbsp; In fact, you can use your spreadsheet to examine almost any plan or hare-brained scheme you can dream up.&nbsp; &nbsp;&nbsp; Below, we look at how it would work out if the example couple we have been following thus far retired at 60 instead of 65: Ouch! Although they start out with a nest egg of almost $700,000, they run out of money in their mid-80s under our assumptions. (It&#8217;s actually even worse than it looks, since I cut the chart off at zero savings and don&#8217;t show them going deep into the red.) Looking at the critical decade from age 60 to 70 shows what&#8217;s happening in more detail: Spreadsheet Excerpt, 4% Real ROR, Retire at 60 By 59, the eve of retirement, the couple has acquired a respectable nest egg of almost $700,000. Unfortunately, they need to spend $400,000 of it over the next five years until pensions start to kick in, and their savings are all the...","thumbnail_url":"http:\/\/retirementhangout.com\/wp-content\/uploads\/2019\/12\/Turing-machine-2.jpg"}