Confused about my inaccurate FI spreadsheet

[moustacheverte]

I'm using a spreadsheet to track my progress and estimate our FIRE date. But it seems to have issues, because if I plug those numbers into FIREcalc, it easier succeeds 95+% on some months or 40% only on others.

I think one of the bugs is how I calculate the required capital, and the retirement date.

For the required capital, I take our mandatory monthly expenses (rent, bills, food...) and multiply it by 12 for yearly expenses. I average over the years. Then I take this yearly amount and multiply it by 25 to get the amount required. I think there might be a problem here because inflation and compound interest isn't accounted for here. Should it be, or am I doing it right?

For the retirement date, I divide the yearly expected return over 10 years (7.20%) by the number of payments per year (12). For the payment amount, I average the month to month market value of my portfolio: this means that monthly amounts I pay in are mixed with market value appreciation). I use today's market value as the portfolio's value.
I'm plugging all these in the NPER function of Google's Spreadsheets.

My spreadsheet says 7.57 years left, but FIREcalc says 40% success rate with these numbers today. I last tweaked the spreadsheet two months ago and thought it was ok because I was getting over 90% success rate.

What am I missing?

[arebelspy]

I'm using a spreadsheet to track my progress and estimate our FIRE date. But it seems to have issues, because if I plug those numbers into FIREcalc, it easier succeeds 95+% on some months or 40% only on others.

I think one of the bugs is how I calculate the required capital, and the retirement date.

For the required capital, I take our mandatory monthly expenses (rent, bills, food...) and multiply it by 12 for yearly expenses. I average over the years. Then I take this yearly amount and multiply it by 25 to get the amount required. I think there might be a problem here because inflation and compound interest isn't accounted for here. Should it be, or am I doing it right?

You're good.  Keep it all in real dollars.

For the retirement date, I divide the yearly expected return over 10 years (7.20%) by the number of payments per year (12). For the payment amount, I average the month to month market value of my portfolio: this means that monthly amounts I pay in are mixed with market value appreciation). I use today's market value as the portfolio's value.
I'm plugging all these in the NPER function of Google's Spreadsheets.

I don't quite follow what you're doing here, but why not just put into cFIREsim (the much better version of FIRECalc) the amount you currently have saved, put in the retirement year you're planning, savings you're accumulating in the meantime, and run your scenario to see the success percentage.  Too low, adjust up the ER date, and run again.  Repeat until it's a success percent you're happy with.  If it's already at 100, drop the retirement year and see what the percent survival chance is. 

In other words, why use the spreadsheet to determine a time to FIRE, and then run cFIREsim based on that possibly faulty assumption?

www.networthify.com can also give you a rough time to FI calculation.

[Metric Mouse]

I'm using a spreadsheet to track my progress and estimate our FIRE date. But it seems to have issues, because if I plug those numbers into FIREcalc, it easier succeeds 95+% on some months or 40% only on others.

I think one of the bugs is how I calculate the required capital, and the retirement date.

For the required capital, I take our mandatory monthly expenses (rent, bills, food...) and multiply it by 12 for yearly expenses. I average over the years. Then I take this yearly amount and multiply it by 25 to get the amount required. I think there might be a problem here because inflation and compound interest isn't accounted for here. Should it be, or am I doing it right?

For the retirement date, I divide the yearly expected return over 10 years (7.20%) by the number of payments per year (12). For the payment amount, I average the month to month market value of my portfolio: this means that monthly amounts I pay in are mixed with market value appreciation). I use today's market value as the portfolio's value.
I'm plugging all these in the NPER function of Google's Spreadsheets.

My spreadsheet says 7.57 years left, but FIREcalc says 40% success rate with these numbers today. I last tweaked the spreadsheet two months ago and thought it was ok because I was getting over 90% success rate.

What am I missing?

You're all over the map. Could you use some (fake) numbers to show us your work? Seems like you're getting the wrong answer, and going about it the wrong way.

[SnackDog]

Just tell us your current annual living cost, current annual savings amount, and current savings.

E.g.  $30K, $50K, $200K. 

Then, we  see you need 25x30k to retire = $750k, which is $550k more than you have.  You could save that much outright in about ten years, but with 7.2% returns you'll get there in about seven.

NPER(7.2%,-50,-200,750)=6.8 years.

There is no monte carlo or probability involved - this is deterministic.  If the market does better you will get there sooner. If it does poorly, it will take longer. 

The success rate you get from the simulators is the success on living a specified number of years on your specified savings and withdrawals without running out of money.  At 4% SWR most simulators will give you 90% chance of the money lasting 30 years.

[arebelspy]

The success rate you get from the simulators is the success on living a specified number of years on your specified savings and withdrawals without running out of money.  At 4% SWR most simulators will give you 90% chance of the money lasting 30 years.

If you're not ER'd yet, they also give you a probability based on retiring in the future based on historical returns, so you don't have to pick the deterministic assumption you referenced.