Kick Ass Spreadsheet as Google Doc

[frugalman]

Inspired by MMM's latest post, I created a Google Doc spreadsheet, hit the link below

https://docs.google.com/spreadsheet/ccc?key=0Ajym06WyPAdmdEFyN0s2WDBtNWJZZmRvR2ViSDR1V3c

To personalize this for your own use, after you open the document, click File then Make a Copy and you will have your own spreadsheet that you can customize

[frugalman]

https://docs.google.com/spreadsheet/ccc?key=0Ajym06WyPAdmdEN5ZVg3Ul9Mc19ELVRzandlMkZMT2c#gid=0

So I modified the basic MMM format in the above to include:
HOA (homeowners association)
Home insurance
Credit card interest

Don't forget you can use File Make a Copy and then it will be keyable for you so you can make your own version.

P.S. My stuff is higher than I would want, because we just bought a second home, which will be our retirement home in a very few years..

[gdborton]

Need to request access for the second link that you added.

[frugalman]

Sorry about that, I just opened up the second spreadsheet to global share

[frugalman]

I should also mention that you can click File Download As and select a format, such as an excel spreadsheet format

[arebelspy]

Sweet, thanks.

The years to retirement isn't correct, obviously, as it doesn't take compound interest into account.

[frugalman]

Arebelspy, I realize I didn't have the compound interest figured into the years to retirement (since I didn't have MMM's original spreadsheet in front of me).  If anyone want's to figure out the correct formula, I will update the google doc spreadsheet I posted.

[yolfer]

Arebelspy, I realize I didn't have the compound interest figured into the years to retirement (since I didn't have MMM's original spreadsheet in front of me).  If anyone want's to figure out the correct formula, I will update the google doc spreadsheet I posted.

To do compound interest in Google Spreadsheets, use the fv() function. You can read the documentation for it here: https://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs

For some reason it returns a negative value, so I put a minus-sign at the beginning of the formula to flip it back into a positive number.

It's too complicated to spell out how to use it, so I created a sample Google Spreadsheet that you can play around with to get a feel for how fv() works:

https://docs.google.com/spreadsheet/ccc?key=0Am8qaK8Qf80edFdiVkhjZ084NllNdEE5eGZOQlJlQWc

Let me know if you have trouble accessing it

[gdborton]

Does your function include continual additions (either monthly or yearly)?  I put something together a few days ago that I can share when I get home.

[Erica/NWEdible]

Thanks again for this frugalman, I really appreciate you taking the time to input the numbers. I can direct copy/paste or export from GD but can't do a direct C&P from the html table very cleanly so this helps me a lot. Appreciate your effort!

[frugalman]

I understand the concept of the FV future value function, but it requires that you know the term, the rate, the starting balance and the periodic contributions.  But how to know the term!  The term must be extended until we get to 25 times the indicated annual spend, which represents the accepted safe withdrawal rate of 4 percent from your stash.

So unless someone can help me solve for the term, I'm stymied here.

[arebelspy]

First thing that comes to mind, though it's probably not the optimal way to do it, is an IF statement .. But you'd probably have to split it between to cells so it's not self referential. 

Another way is make a bunch of hidden cells with terms all the way up to 100 years and reference the first one where .04*that cell > annual expenses cell above.

I can't do elegant things in excel, but I can hack together solutions.  ;)

[arebelspy]

Driving to work the simple solution hit me.

Use the time to retirement formula.

=(LN((C3*(1/C4)*C1/C2)+1))/(LN(1+C1))

Just change the cell references in there.

C1 = Compounding Rate
C2 = Savings Rate (as a percent)
C3 = Expenses (as a percent)
C4 = SWR (usually 4%)

[arebelspy]

Here you go, edited the spreadsheet from the OP to include the correct years to retirement.  I just assumed a 4% SWR, but you can change that in the formula or add a row to specify and reference that cell.

Attached.

[frugalman]

Thanks much to Arebelspy for providing the correct formula for "Years to Retirement"!!! Master!!!  I changed the formula so that it would work with a new row I added, Annual safe withdrawal rate, so that users can play with Investment Returns and Safe withdrawal rate also

[frugalman]

Arebelspy - I'm thinking of adding two new rows to the spreadsheet.

Current savings e.g. $200,000 would shorten the time to retirement
Additional monthly income e.g. MMM's blog income, or my coming social security - this would also shorten the time to retirement.

Can you help adjust the formula to include data in these two new rows?  Thanks much!

[arebelspy]

Yes, that should be doable.  Upload your spreadsheet and lmk what you need.  It'll probably have to wait until tonight.

Anyone else can feel free to take a go at it as well. :)

[frugalman]

I updated the original MMM Kickass spreadsheet, link is below, see if you can figure anything out for
1. Existing savings
2. Other expected retirement monthly income

https://docs.google.com/spreadsheet/ccc?key=0Ajym06WyPAdmdEFyN0s2WDBtNWJZZmRvR2ViSDR1V3c#gid=0

[matchewed]

I'm not sure on the Expected Additional Mo Income side but the Current Savings affecting years to retirement would go something like this - =D40-(D38/D32)

D40 = Years to Retirement
D38 = Existing Savings
D32 = Annual Total

I think that'd be right.

For the Expected Additional Mo Income; are we talking about post retirement income? If so it shouldn't affect your years to retirement.

[lauren_knows]

Seems like we need to engineer something similar to FireCalc that takes in a ton of variables and runs the numbers on achieving FIRE, not just getting through it.

I've done some PHP programming in the past, maybe I should start taking down requirements...  (I need to shut up the Systems Engineer in my brain)

[PaulM12345]

I'm not volunteering to do this (I'm not advanced enough to know how), but I've been thinking it would be cool to have a sort of super-powered mustachian calculator - either an excel spreadsheet or a website, where you can factor in a ton of different aspects of life, and somehow get the type of results that are helpful for people aiming for retirement. I'm thinking something like the NYT housing calculator, but for mustachianism - savings, spending, income, real estate, loans, and so on. You could even calculated a mustache score based on the input! Or it could compare with the average citizen, or the average mustachian, or with the MMM family themselves.

I know a lot of us use some sort of system for ourselves. How hard would it be to combine minds and make an ultimate MMM calculator?

This table might be a good start...

...just a thought! :)

[arebelspy]

I'm not volunteering to do this (I'm not advanced enough to know how), but I've been thinking it would be cool to have a sort of super-powered mustachian calculator - either an excel spreadsheet or a website, where you can factor in a ton of different aspects of life, and somehow get the type of results that are helpful for people aiming for retirement. I'm thinking something like the NYT housing calculator, but for mustachianism - savings, spending, income, real estate, loans, and so on. You could even calculated a mustache score based on the input! Or it could compare with the average citizen, or the average mustachian, or with the MMM family themselves.

I know a lot of us use some sort of system for ourselves. How hard would it be to combine minds and make an ultimate MMM calculator?

This table might be a good start...

...just a thought! :)

Here: http://www.mrmoneymustache.com/2011/12/30/the-ultimate-retirement-calculator-united-states-version/

[PaulM12345]

Ha ha! I thought I had already read all the MMM postings but I must have missed that one. Everything already exists...

I will definitely take a look at that - thanks again!

[frugalman]

One side benefit of working this, and working some other spreadsheets, is that I see I "could" retire right now  (I'm 62) with about a $1,600/mo surplus over basic expenses.

It's a comforting thought, but I just feel I will keep going to my full retirement age of 66 at this point.  It means a surplus of $4,400/mo over basic expenses.  There is never going to be enough money if we get into a decent inflationary environment.  And when you are "old", you can't just make up for it by getting a job!  Not to say that I would eschew the opportunity to do some fun work for a while after I retire.

[CarolynM]

Cool spreadsheet. But 140k after taxes! Jeez, I am no where remotely close to that. My 33k after taxes is quite pitiful looking :(

[grantmeaname]

$33k after taxes makes my $17k before taxes look pitiful... in the big picture, it's not really about the absolute dollar value, but living within your means and learning to appreciate challenges.