Author Topic: Developing my personal Retirement Spreedsheet  (Read 4120 times)

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 39
Developing my personal Retirement Spreedsheet
« on: January 27, 2015, 06:27:25 PM »
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

  • Senior Mustachian
  • ********
  • Posts: 10643
  • Location: Australia or another awesome area
Re: Developing my personal Retirement Spreedsheet
« Reply #1 on: January 27, 2015, 07:17:15 PM »
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

  • 5 O'Clock Shadow
  • *
  • Posts: 39
Re: Developing my personal Retirement Spreedsheet
« Reply #2 on: January 27, 2015, 07:37:48 PM »
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

  • Senior Mustachian
  • ********
  • Posts: 10643
  • Location: Australia or another awesome area
Re: Developing my personal Retirement Spreedsheet
« Reply #3 on: January 27, 2015, 08:21:48 PM »
See cell C17 in FI  -

=INDIRECT(CONCATENATE(B14,"!A1"),TRUE)
« Last Edit: January 27, 2015, 08:25:10 PM by deborah »

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 39
Re: Developing my personal Retirement Spreedsheet
« Reply #4 on: January 27, 2015, 09:13:24 PM »
Deborah, I appreciate all the help. Thanks so much!

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 39
Re: Developing my personal Retirement Spreedsheet
« Reply #5 on: January 27, 2015, 09:56:23 PM »
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

  • Senior Mustachian
  • ********
  • Posts: 10643
  • Location: Australia or another awesome area
Re: Developing my personal Retirement Spreedsheet
« Reply #6 on: January 27, 2015, 10:36:17 PM »
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 ("").
« Last Edit: January 27, 2015, 10:51:03 PM by deborah »

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 39
Re: Developing my personal Retirement Spreedsheet
« Reply #7 on: January 27, 2015, 10:56:30 PM »
"Retirement Planner", it's where my "Desired Retirement Income" begins.

danb

  • 5 O'Clock Shadow
  • *
  • Posts: 39
Re: Developing my personal Retirement Spreedsheet
« Reply #8 on: January 27, 2015, 11:01:08 PM »
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

  • Senior Mustachian
  • ********
  • Posts: 10643
  • Location: Australia or another awesome area
Re: Developing my personal Retirement Spreedsheet
« Reply #9 on: January 27, 2015, 11:47:03 PM »
Answer part 1: To see the hidden sheet, right click a sheet tab, and it will give you the "unhide" choice.

deborah

  • Senior Mustachian
  • ********
  • Posts: 10643
  • Location: Australia or another awesome area
Re: Developing my personal Retirement Spreedsheet
« Reply #10 on: January 28, 2015, 12:36:16 AM »
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

  • Senior Mustachian
  • ********
  • Posts: 10643
  • Location: Australia or another awesome area
Re: Developing my personal Retirement Spreedsheet
« Reply #11 on: January 28, 2015, 05:29:16 AM »
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

  • 5 O'Clock Shadow
  • *
  • Posts: 39
Re: Developing my personal Retirement Spreedsheet
« Reply #12 on: January 28, 2015, 04:08:29 PM »
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.