2

I want to calculate how much money I'll need in retirement. For that I want to calculate how long my money will last. I'm not sure if there is a formula for that. Lets say we have this simple fictional example:

  • I have 100k in investing accounts with 5% / year
  • I need to take out 10k / year
  • How long will this money last?

EDIT: I've changed the 10% to 5%.

  • Best and easiest is to use a spreadsheet. – gnasher729 Oct 27 '18 at 10:35
  • And which formula there? FV? – Gabriel Oct 27 '18 at 10:40
  • @gnasher729: Or an HP-12c calculator or app (many available on the web). Set PV = 100K, FV = 0, 5 g n (to get monthly interest), PMT = 10,000 / 12 (CHS), then solve for n. 167 months, or just under 14 years. – jamesqf Oct 27 '18 at 16:55
2

Using the summation for the present value of an ordinary annuity, with

p = principal
w = withdrawal amount
n = number of periods
m = periodic interest rate

enter image description here

∴ n = -(Log[1 - (m p)/w]/Log[1 + m])

Applying your figures

m = 0.05
p = 100000
w = 10000

n = -(Log[1 - (m p)/w]/Log[1 + m]) = 14.2067

You would be able to withdraw 10k for 14 years.

Note, in an ordinary annuity the cash flow is at the end of each period, so if retirement starts at the beginning of year 1 with 100k, the first payment happens at the end of year 1.

https://www.investopedia.com/retirement/calculating-present-and-future-value-of-annuities/

To calculate the balance in year 14, after the last 10k payment, this formula can be used.

with n = 14

balance = ((1 + m)^n (m p - w) + w)/m = 2006.84
2

Your principal will last indefinitely if your interest rate (or equivalent) is at least 10%. Otherwise, spreadsheets come in very handy for these sorts of questions.

Here are some formulae you can use, assuming your interest comes in at the end of the year as a lump sum and you draw your expenses immediately upon receiving the interest:

First row: Year (Y=2018 or current year), Principal amount (P=100,000), Rate of change (R=1.05 for 5% interest), Amount Drawn (D=10,000), Balance (B=PR-D).

Subsequent rows (referencing the variables in the row before it): Y+1, P’=B, R, D, P’R-D.

Look down to the row where the Balance column is first negative. The first column (year) tells you the year that the funds run out.

Once you’ve set up your spreadsheet, you can change the interest rates or drawings for particular years or year ranges.

Here's a snapshot of the above spreadsheet using the given parameters ($100k principal, 5%pa interest, $10k drawn each year), showing the funds lasting for 14 years and running out in the 15th year:

Spreadsheet

  • Yep, I realized, this was a bad example. I've changed it a bit. – Gabriel Oct 27 '18 at 9:51
  • @Lawrence can you post a pic of your spreadsheet? – Seth Oct 28 '18 at 5:03
  • 1
    @Seth Done. :) – Lawrence Oct 28 '18 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.