View Question
Q: Calculate Inflation-indexed Payments Given Present and Future Values ( Answered ,   0 Comments )
 Question
 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. 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```
 kurt1234-ga rated this answer: `Elmarto, you are one smart dude! Thanks for the timely, perfect answer!`