Calculate Inflation-indexed Payments Given Present and Future Values
Category: Business and Money > Finance
Asked by: kurt1234-ga
List Price: $10.00
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!
Re: Calculate Inflation-indexed Payments Given Present and Future Values
Answered By: elmarto-ga on 09 Nov 2005 14:41 PST
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
rated this answer:
Elmarto, you are one smart dude! Thanks for the timely, perfect answer!
|There are no comments at this time.|
If you feel that you have found inappropriate content, please let us know by emailing us at firstname.lastname@example.org with the question ID listed above. Thank you.
|Search Google Answers for|