Capital Needs Analysis Spreadsheet Explanation

Online Retirement Savings Calculator

The first things to enter are

  • The number of years from today until you expect to retire, the Years TO Retirement, during which time you can save money
  • Then, the number of years of retirement itself,
    the Years IN Retirement, which is the amount of time you will live off of your investments, at least partially
  • How much you currently have invested, your Current Portfolio Amount
  • The rate of inflation
  • And then the amount you expect your investments to return every year; on average, pre tax
{retirement planning parameters}

Then enter what you expect to need from your investments every year once you retire. Use today's dollars, the spreadsheet will adjust for inflation.

  • First enter the amount of money that you expect you will spend. Taxes are an expense to be included in this amount. The best place to start is with your current expenses.
    • If you stop earning wages, you won't pay FICA taxes, you may have paid off loans and mortgages and your kids will probably be independent, which will reduce your expenses.
    • On the other hand, you may be planning to go around the world on a private jet, which will increase your expenses.
       
    Whatever. Make your best estimate. There are lots of rules of thumb that say you can live off 60 or 80% of your pre-retirement income, but you know your own wants and needs best. And don't forget that later in life you may have very high medical expenses.
     
  • Subtracted from that amount will be any outside sources of income. Social Security is inflation adjusted; most corporate pensions are not. Enter whatever outside sources of income you can reasonably expect during retirement.

{expected income requirement}

Given that input, the minimum portfolio size can be calculated. This is the amount of money you need in the bank on the day you retire.

There are four ways of looking at the amount of money you need.

  1. First, you can plan to spend all of your money and have none left over when you die. Philosophically, there's nothing wrong with approach but if any of your assumptions are just a little bit optimistic, you'll find yourself with no money before you die, which is not a good plan.
  2. Option number 2 is to plan to have the same dollar amount in your portfolio at the end of your retirement as when you start. This is a safer bet than Option 1, but inflation will take a toll.
  3. So, Option 3 is the amount that you need in order to end up with the same amount adjusted for inflation.

    The problem with the first three options is that they just rely on the math of Future Value calculations, and they rely on your assumptions working out perfectly. Which isn't the way life is, particularly over 30 or 50 years.
     
  4. Option 4 says, "How big must my portfolio be to allow me to withdraw 4% every year, adjusted for inflation, and not eat into it?"

    This is the highest hurdle but it is the one you should be shooting for. This is the punch line... (See Withdrawal Limits From A Fixed Portfolio )

    The link cites a study which looks at what happens to an investment portfolio when nothing is going in and an annual withdrawal is being made.
    If we assume that a good part of the investments are in the stock market, since that's the only way to beat inflation, then there will be times when the portfolio actually shrinks.

    When the markets go down, the amount of money gets smaller. And at the same time, you have to withdraw money to live.
    Put all of that into a simulator and the answer is that if you have a life expectancy of 30 years, then you really should not plan to withdraw more than 4% a year.

{minimum investment portfolio size}

A question frequently asked is: why do I need a smaller portfolio if I retire early, all other things being equal?

The short answer is that the portfolio of the person who retires early has a chance to build up through compounding. The situation is somewhat analogous to the example given in Retirement Planning: Math Favors the Early Saver.

The withdrawals, growing each year at the rate of inflation, will eventually consume the portfolio, but in the early years it will actually grow; in this example, the portfolio starts to decline in year 39.

The chart below shows, year by year, what happens in four different scenarios.

The variables held constant:

  • Current Portfolio Amount                      $0
  • Inflation Rate                                               3%
  • Investment Return                                    7%
  •  
  • Income Needed from Investments    $100,000

What's varied in the four scenarios is the Years To Retirement and the Years In Retirement

The way the table works is this

  • The amount withdrawn each year is increased by the rate of inflation (3%)
  • The portfolio
    • First, is reduced by the amount withdrawn
    • Then, is increased by the Investment Return (7%)

The  "1. Run the Portfolio Down to Zero" portfolio method is used.

You might reasonably question the rate of inflation since what a 3% inflation rate says is that in 60 years you'll have to spend $572,000 to buy what costs $100,000 today. Unfortunately, 3% is the 80-year average ... some of us can remember $35/oz. gold and $0.25/gal. gasoline.



As the chart makes clear, what we are looking at isn't four different scenarios at all, but four snapshots of a single scenario.

Live for 60 Years From Today
Retire Retire in Retire in Retire in
Today 20 Years 30 Years 40 Years
Withdrawal Portfolio
Today   100,000 2,403,024
1 103,000 2,464,236
2 106,090 2,526,522
3 109,273 2,589,862
4 112,551 2,654,231
5 115,927 2,719,598
6 119,405 2,785,927
7 122,987 2,853,179
8 126,677 2,921,305
9 130,477 2,990,251
10 134,392 3,059,958
11 138,423 3,130,356
12 142,576 3,201,368
13 146,853 3,272,908
14 151,259 3,344,878
15 155,797 3,417,172
16 160,471 3,489,672
17 165,285 3,562,245
18 170,243 3,634,748
19 175,351 3,707,020 Withdrawal Portfolio
20 180,611 3,778,886 180,611 3,778,886
21 186,029 3,850,154 186,029 3,850,154
22 191,610 3,920,614 191,610 3,920,614
23 197,359 3,990,033 197,359 3,990,033
24 203,279 4,058,162 203,279 4,058,162
25 209,378 4,124,724 209,378 4,124,724
26 215,659 4,189,421 215,659 4,189,421
27 222,129 4,251,925 222,129 4,251,925
28 228,793 4,311,882 228,793 4,311,882
29 235,657 4,368,905 235,657 4,368,905 Withdrawal Portfolio
30 242,726 4,422,576 242,726 4,422,576 242,726 4,422,576
31 250,008 4,472,440 250,008 4,472,440 250,008 4,472,440
32 257,508 4,518,002 257,508 4,518,002 257,508 4,518,002
33 265,234 4,558,728 265,234 4,558,728 265,234 4,558,728
34 273,191 4,594,039 273,191 4,594,039 273,191 4,594,039
35 281,386 4,623,308 281,386 4,623,308 281,386 4,623,308
36 289,828 4,645,856 289,828 4,645,856 289,828 4,645,856
37 298,523 4,660,950 298,523 4,660,950 298,523 4,660,950
38 307,478 4,667,798 307,478 4,667,798 307,478 4,667,798
39 316,703 4,665,542 316,703 4,665,542 316,703 4,665,542 Withdrawal Portfolio
40 326,204 4,653,258 326,204 4,653,258 326,204 4,653,258 326,204 4,653,258
41 335,990 4,629,948 335,990 4,629,948 335,990 4,629,948 335,990 4,629,948
42 346,070 4,594,535 346,070 4,594,535 346,070 4,594,535 346,070 4,594,535
43 356,452 4,545,858 356,452 4,545,858 356,452 4,545,858 356,452 4,545,858
44 367,145 4,482,665 367,145 4,482,665 367,145 4,482,665 367,145 4,482,665
45 378,160 4,403,606 378,160 4,403,606 378,160 4,403,606 378,160 4,403,606
46 389,504 4,307,227 389,504 4,307,227 389,504 4,307,227 389,504 4,307,227
47 401,190 4,191,964 401,190 4,191,964 401,190 4,191,964 401,190 4,191,964
48 413,225 4,056,128 413,225 4,056,128 413,225 4,056,128 413,225 4,056,128
49 425,622 3,897,906 425,622 3,897,906 425,622 3,897,906 425,622 3,897,906
50 438,391 3,715,344 438,391 3,715,344 438,391 3,715,344 438,391 3,715,344
51 451,542 3,506,340 451,542 3,506,340 451,542 3,506,340 451,542 3,506,340
52 465,089 3,268,634 465,089 3,268,634 465,089 3,268,634 465,089 3,268,634
53 479,041 2,999,794 479,041 2,999,794 479,041 2,999,794 479,041 2,999,794
54 493,412 2,697,205 493,412 2,697,205 493,412 2,697,205 493,412 2,697,205
55 508,215 2,358,058 508,215 2,358,058 508,215 2,358,058 508,215 2,358,058
56 523,461 1,979,332 523,461 1,979,332 523,461 1,979,332 523,461 1,979,332
57 539,165 1,557,782 539,165 1,557,782 539,165 1,557,782 539,165 1,557,782
58 555,340 1,089,920 555,340 1,089,920 555,340 1,089,920 555,340 1,089,920
59 572,000 572,000 572,000 572,000 572,000 572,000 572,000 572,000
60 -   0 -   0 -   0 -   0