I'm looking for a FORMULA that will answer this question:  "How much
should I save for retirement each year if I know:
  -> The exact amount of my retirement goal
  -> The amount I am starting with today
  -> The number of years until I retire
  -> The (constant) earnings rate of my retirement investments during
my saving years
  AND (here is the tricky part)
  -> I expect to increase my contributions each year by a fixed percent.

I don't want a spreadsheet solution... I can do that myself.  I am
looking for a FORMULA that can be put into Excel and will return a
value given these inputs.

EXAMPLE:
  -> At retirement, I wish to have $3,000,000
  -> I have $100,000 currently
  -> I will retire in 30 years
  -> My investments will earn 9%
  -> Each year, I will increase my annual contribution by 3%
From this spreadsheet - http://www.eurasianmissions.org/example.xls -
I know the first year (current year) contribution should be $8496.  I
found that using Excel's Goal Seek tool.

Thanks!

Hello kurt1234!
Let's define the following variables:

F = Retirement goal ($3,000,000 in the example)
I = Initial funds ($100,000 in the example)
r = Annual increase of your contribution (3% in the example)
i = Earnings rate of your investment (9% in the example)
N = Number of years until retirement (30 in the example)

Since the formula is somehow lengthy, I will define some of its terms
as variables. So let's also call:

x = (1+r)^N - (1+i)^N
w = (r-i)/(1+i)

The formula that will give you the annual contribution is the following one:

Contribution = (F - I*(1+i)^30) * w / x

I've edited your Excel file in order to include this formula so you
can check it. Of course, it gives the exact same answer for the
example parameters ($8,496.40 annual contribution). You can find it
at:

http://www.angelfire.com/alt/elmarto/googleanswers/591078.xls
[Right click on the link and choose Save As...]


I hope this helps! If you have any questions regarding my answer,
please don't  hesitate to request a clarification before rating it.
Otherwise I await your rating and final comments.

Best wishes!
elmarto

---

Clarification of Answer by elmarto-ga on 10 Nov 2005 03:58 PST

Hello kurt!
I'm sorry about that, I had never had that problem before.

We are not allowed to have direct contact with Google Answers users.
I've edited the main page at Angelfire so you can download the file
from there. Try going to:

http://www.angelfire.com/alt/elmarto/

and download the file from there. Please let me know if you still have
trouble downloading the file.

Best wishes!
elmarto

---

Request for Answer Clarification by kurt1234-ga on 10 Nov 2005 07:06 PST

Thanks, Elmarto!

Minor point, but for any bystanders following along, there is a typo
in the formula presented.  I believe it should read:

Contribution = (F - I*(1+i)^N) * w / x

---

Clarification of Answer by elmarto-ga on 10 Nov 2005 07:35 PST

Hello kurt!
Sorry for the typo! You're completely right. I was thinking of the
example when I wrote that 30. Thanks for pointing it out, and thanks
again for the rating and nice comments!

Best regards,
elmarto

Elmarto, you are one smart dude!  Thanks for the timely, perfect answer!