Google Answers Logo
View Question
Q: Calculate Inflation-indexed Payments Given Present and Future Values ( Answered 5 out of 5 stars,   0 Comments )
Subject: Calculate Inflation-indexed Payments Given Present and Future Values
Category: Business and Money > Finance
Asked by: kurt1234-ga
List Price: $10.00
Posted: 09 Nov 2005 10:01 PST
Expires: 09 Dec 2005 10:01 PST
Question ID: 591078
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.

  -> 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 - -
I know the first year (current year) contribution should be $8496.  I
found that using Excel's Goal Seek tool.

Subject: Re: Calculate Inflation-indexed Payments Given Present and Future Values
Answered By: elmarto-ga on 09 Nov 2005 14:41 PST
Rated:5 out of 5 stars
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
[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!

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:

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

Best wishes!

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,
kurt1234-ga rated this answer:5 out of 5 stars
Elmarto, you are one smart dude!  Thanks for the timely, perfect answer!

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy