Developing my personal Retirement Spreedsheet

[danb]

This is a technical post. I am basically looking for someone who can help me add functionally to an excel worksheet.

I've decided that I want to create a custom spreadsheet to help me track my financial situation.Currently I have taken parts of other sheets, tweaked and combined them to display what i want. It's currently far from complete.My Current goal for the sheet is to Help me decide how good moving to Georgia ro future locations is for Financial Independence.

I have some tabs at the bottom that help me see my FIRE for MD VS GA.  However, I have to choose the FI tab at the bottom to see the data.

I want to create a drop down box on my "FI" tab, and choose an expense profile, and have it ingest the expenses from the respective ExpenseX tab. Can anyone help me do this?  I have attached the file. Can anyone teach, or help me modify this?

If you notice anything not accurate, or "unreasonable" I'd like to hear it also.

[deborah]

I needed to change the names of the sheets, but I have fixed up your drop down list, and have added the formula in cell C17 of FI .

[danb]

Awesome, thanks for doing that.  When i toggle between Georgia and Maryland the FI numbers don't change.

I guess i need to update each field to do something like:
if b14 = Maryland, load data from maryland_B14
if b14 = Georgia, load data from georgia_B14

If I could see an example of doing one field dynamically like that I could probably figure out the rest.

[deborah]

See cell C17 in FI  -

=INDIRECT(CONCATENATE(B14,"!A1"),TRUE)

[danb]

Deborah, I appreciate all the help. Thanks so much!

[danb]

PART 2:

So, now that I have the nifty option to switch between expense profiles, I think there is some confusion between the FI tab and "Retirement Planner" tab.

When i set my Georgia Salary to 75000, the FI tab says I can retire in 11.57 years, and the retirement planner tab says, I'll have 1.8M by age 99.

However, If I increase my Georgia Salary from 75000 to 100000, the FI tab says i can retire in 8 year, but the retirement tab says i will run out of money when I'm 75

It seems like I need to tell the retirement planner to base it's numbers off my Withdraw Rate (=FI!C4) instead of percentage of annual income.  Any ideas how to make these more synchronized?

I looked at cell (J45) but i don't understand how it's getting the value. =IFERROR(Calculations!I45,"")???

I appreciate any help you can offer. Here is the latest working spreadsheet.

[deborah]

You have a hidden sheet called calculations, so =IFERROR(Calculations!I45,"") means that you will return the value of I45 in that sheet or null ("").

[danb]

"Retirement Planner", it's where my "Desired Retirement Income" begins.

[danb]

You have a hidden sheet called calculations, so =IFERROR(Calculations!I45,"") means that you will return the value of I45 in that sheet or null ("").

How do I see the Formula for I45? Is this even the right formula for my goals? (being that I want this worksheet to use the 4% withdraw rate, instead of a percentage of my annual income)      

[deborah]

Answer part 1: To see the hidden sheet, right click a sheet tab, and it will give you the "unhide" choice.

[deborah]

Answer Part 2: The yearly spend appears not to be adding inflation properly, and the savings are not adding inflation at all, so the proportion of your pay that you are saving is rapidly decreasing, and is certainly wrong at the time of retirement.

[deborah]

It appears to me that most of this spreadsheet comes from the recent mad fientist post, but where do the other two sheets come from (Retirement planner and Calculations)?

[danb]

Calculations was a hidden sheet that came with Retirement planner. And I don't remember where I got that sheet from.

I think i resolved part2, I made some changes to a formula, hard coded retirement income to be based off withdraw rate instead of %of annual income.

Anyways here is the results if you or anyone else is interested.