Hello,

I'm trying to create a retirement planning spreadsheet but I need help with one of the calculations please.

Here is an example:

Bob is currently 36 years old and wants to retire at 65. He expects to live to 95 and would like to know based on his current savings how much the shortfall is for his goals and what he needs to save from now until retirement. He would like to have $35,000 (today's dollars) per year for retirement..

(This is not a school problem, I'm creating this problem because it is easier than trying to explain what I want alone)

I have figured out the following:
Years until retirement: 29
Years income needed: 30

$35,000 today inflates to $62,154.56 in 29 years.

Total cost of retirement: This is where I'm stuck.

I'm trying to figure out what the total cost of retirement will be while inflating the income needed by 2% per year and investing the captial at a rate of 5.5% per year.

I can't just subtract the inflation can I? I don't think the numbers will work correctly. Is there a way to do this WITHOUT populating a table. I would like to just use a formula to do it.