3

I would like to know what formula or tool I need to use to calculate the capital needed for a hypothetical retirement that will start at date X and end at date Y.

Here is an example:

  • Variable1 = retirement start date: Official retirement is 65 year old.
  • Variable2 = retirement end date: Let's say 90 year old.
  • Variable3 = monthly budget: let's say it's $5000.
  • Variable4 = interest on capital: let's say it's 5% per year.

How much money is needed at age 65 to get $5000 per month until age 90 given the fact there is no need for succession?

Chris W. Rea
  • 31,671
  • 16
  • 102
  • 190

4 Answers4

2

$3,679,163.80

I made these assumptions that you did not state:

  • You'll retire in 2042
  • Inflation will average 3.5% per year from 2012 - 2067
  • Your yearly budget of $60,000 will grow at the rate of inflation
  • The yearly budget is withdrawn at the start of the year and the interest is paid at the end of the year, based on the amount left over after the withdrawal

Then using Excel, we find that with a starting point of $3,679,163.80, we can achieve your goal. The formula for Yearly Budget is =G$1*((1.035)^(A3-2012)) and the formula for Money left at year end is =(D4/1.05)+C4 For 2067, enter $0 leftover, and for 2066, enter $397,988.47 leftover.

G$1 is $60,000

G$2 is 0.05

Year  Age  Annual Budget  Money at year end
2041  64         $0.00    $3,679,163.80
2042  65   $168,407.62    $3,686,293.99
2043  66   $174,301.89    $3,687,591.71
2044  67   $180,402.46    $3,682,548.71
2045  68   $186,716.54    $3,670,623.78
2046  69   $193,251.62    $3,651,240.77
2047  70   $200,015.43    $3,623,786.61
2048  71   $207,015.97    $3,587,609.18
2049  72   $214,261.53    $3,542,015.03
2050  73   $221,760.68    $3,486,267.07
2051  74   $229,522.30    $3,419,582.01
2052  75   $237,555.58    $3,341,127.75
2053  76   $245,870.03    $3,250,020.60
2054  77   $254,475.48    $3,145,322.38
2055  78   $263,382.12    $3,026,037.27
2056  79   $272,600.50    $2,891,108.61
2057  80   $282,141.51    $2,739,415.46
2058  81   $292,016.47    $2,569,768.94
2059  82   $302,237.04    $2,380,908.49
2060  83   $312,815.34    $2,171,497.81
2061  84   $323,763.88    $1,940,120.63
2062  85   $335,095.61    $1,685,276.27
2063  86   $346,823.96    $1,405,374.93
2064  87   $358,962.80    $1,098,732.74
2065  88   $371,526.49      $763,566.56
2066  89   $384,529.92      $397,988.47
2067  90   $397,988.47            $0.00
Richard Krajunus
  • 352
  • 2
  • 11
2

The usual, but controversial, answer to this question is a 4% withdrawal rate.

This means a net worth of ($5000 * 12 months ) / 4%, i.e., $1,500,000

If you want to play with the numbers, based on historical data, you can use the FIRECalc simulator.

mouviciel
  • 803
  • 8
  • 8
1

This is a present value calculation, which excel or any financial calculator can handle. N = 300 (months) %i = 5/12 or .05/12 depending on the program/calculator PMT = $5000 (the monthly payment) FV = 0 (you want to end at zero balance)

This calculates a PV (present value of $855,300) Chad had it right, but used a calculator that didn't offer the PV function, so he guessed and changed numbers til the answer was clear.

user379 makes a good point, but why start inflation calculations at 65, and not now? You look like you're in your 30's, so there's 30 years of inflation, and $60K/yr in today's value will need to be closer to $150K/yr, given about 30 years of 3% inflation.

JTP - Apologise to Monica
  • 172,273
  • 34
  • 296
  • 560
0

You can get the number you want pretty easily by plugging some numbers in to a mortgage calculator like this one.

Set the PMI and property tax to 0 and set your term to the duration you need. Then plug in numbers until your payment amount equals your monthly budget. Think of yourself as the bank in this process.

I started at 1mil over 25 years and that was over my budget so went down to 800k which was under just a bit, and found that you need about 850k based on your numbers.