How much to invest monthly to reach your goal

What I try to do here is pretty simple. I want to know how much to invest each month during a given period in order to reach a specific goal.

[FREE DOWNLOAD Excel calculation sheet]

Let’s say I am 40 years old and want to retire at age 60. At 60 years old, I expect my capital to grow at an annual rate of 5%. If at this point I want a virtual salary of $50,000 a year for 25 years, how much do I need to invest now?

Invest (accumulation) period: 20 years.
Starting point: $10,000
Monthly inflow: ???
Average annual rate of return: 5%
Withdraw period: 25 years.
Yearly outflow: $50,000

The thing to keep in mind is that money will continue to grow at 5% (or your expected annual return) during the withdrawal period.

* * * Calculation process * * *

1. First, let’s calculate how much capital I need at age 60.

With Excel, I calculate the Present Value (PV excel function) of $50K times 25 at a rate of 5%.
Capital needed at 60 yrs old: PV(rate, period, pmt) = about $700K

– rate = 5%
– period = 25 yrs
– pmt = $50K

How to read this result

If I want to retire at age 60, and be able to withdraw $50K every year, knowing that my capital continues to grow at 5%, I will need about $700K when I reach 60.

Ok, so now I know that I need at least $700K when I’m 60.

2. Next question is: how to get to $700K?

Hypothesis is I’m currently 40 years old. I need to invest yearly (or monthly) for 20 years before reaching 60.

For this I use Excel PMT function.
The yearly contribution is equal to: PMT(rate, period, 0, fv) = about $21K

– rate = 5%
– period = 20 yrs (between 40 and 60)
– FV (future value) = $700K

How to read this result

If I want $700K at age 60, I will need to invest about $21K yearly for 20 years (with an expected annual return of 5%). That’s about $1,780 monthly.

You can use this [free Excel spreadsheet] and modify yellow inputs: expected rate, invest period (ages now and retirement), outflow period and cash.

Now, the big question is: how do I get a 5% return (or any return for that matter)? How do I calculate it? How can we be sure?

Nothing is sure about this return, you need to adapt it with your current asset allocation expected return. 5% is pretty conservative. Average market returns are about 7%-8%, using historical S&P500 data. Five percent seems reasonable.

Of course, you also need to take into account taxes. I recommend you use these calculations in net terms, ie. after tax.