Author Topic: Formula for calculating Years to FI?  (Read 8648 times)

gmuk

  • 5 O'Clock Shadow
  • *
  • Posts: 13
Formula for calculating Years to FI?
« on: March 14, 2014, 06:22:42 AM »
Hi folks.
My partner and I are currently making our FIRE planning (yay!), and we're running a few scenarios (i.e. if we buy a house with different house prices, mortgage interest rates, loan terms, vs. not buying a house at all, etc).

In each scenario we have a different savings rate. Do folks have a handy formula (i.e. for putting it into Excel) in which you can calculate "Years to FI" based on things like: Net worth, Savings rate, Expenses, Savings, Growth rate of investment, withdrawal rate, etc. (Like the one used by http://www.networthify.com/calculator/earlyretirement). This would be very useful for our side-by-side comparisons of different scenarios.

P.S.  I know there are several web pages that do this for you, but it would be grand to have the formula those web sites use so that we can play around with it in Exccel (and not have to manually type it in to the web page and then back to Excel).

Thanks!
« Last Edit: March 14, 2014, 06:44:02 AM by gmuk »

freeedom

  • 5 O'Clock Shadow
  • *
  • Posts: 92
Re: Formula for calculating Years to FI?
« Reply #1 on: March 14, 2014, 08:06:41 AM »

gmuk

  • 5 O'Clock Shadow
  • *
  • Posts: 13
Re: Formula for calculating Years to FI?
« Reply #2 on: March 14, 2014, 08:16:28 AM »
Thanks, but do you know the actual formula used by that site? I'd like to plug this calculation into Excel and use it in different scenarios (without having to go to a web page, type in my numbers, then type the result back into Excel).

I hope that makes sense...

destron

  • Bristles
  • ***
  • Posts: 377
  • Age: 45
  • Location: Seattle
    • Mustachian Financial Calculators
Re: Formula for calculating Years to FI?
« Reply #3 on: March 14, 2014, 08:26:55 AM »
Thanks for the pimp, Freeedom.

Gmuk,

It is easiest to calculate in "today's dollars" and subtract out expected inflation for your target.

First you need to calculate the total amount of 'stache you need. For this you must know your yearly expenditures and the rate at which you plan on withdrawing your money (sometimes called the safe withdrawal rate, this is usually thought of as 4% or below).

total 'stache = yearly expenditures / planned withdrawal rate

For instance, if you will spend $24,000/year and withdraw at 4%

$24,000/.04 = $600,000

Then, you want to calculate the future value of a growing annuity (http://en.wikipedia.org/wiki/Time_value_of_money#Future_value_of_a_growing_annuity)

This is a bit more complex, but the formula is

FV(A) = A * ((1 + i)^n-(1+g)^n) /(i - g) (it's easier to see in the wikipedia link)

Where:

A = starting money
FV(A) = future value of money
i = interest rate compound over each period (for instance, per year you might assume 4% interest after inflation)
g = the amount you are contributing per period (for instance, 20,000 per year)
n = the number of payment periods

If you want to know how many years it will take, this means you have to solve for n. That involves using log which may be difficult if it has been a while since  you took math in high school! But, this page has a good explanation of how to put it into a spreadsheet:

http://www.tvmcalcs.com/calculators/excel_tvm_functions/excel_tvm_functions_page1. You probably want to solve for the "number of periods" to get to your target amount of money.

gmuk

  • 5 O'Clock Shadow
  • *
  • Posts: 13
Re: Formula for calculating Years to FI?
« Reply #4 on: March 14, 2014, 08:39:44 AM »
THANK YOU! This is exactly what I was looking for. I'm an engineer, but haven't solved an equation for n in ages. I shall rise to the geeky challenge.

Thanks again!

destron

  • Bristles
  • ***
  • Posts: 377
  • Age: 45
  • Location: Seattle
    • Mustachian Financial Calculators
Re: Formula for calculating Years to FI?
« Reply #5 on: March 14, 2014, 09:11:50 AM »
THANK YOU! This is exactly what I was looking for. I'm an engineer, but haven't solved an equation for n in ages. I shall rise to the geeky challenge.

Thanks again!

Very cool. Remember that, in this case, n is the number of periods. So, if you want to compound monthly (and have a monthly contribution amount), it is the number of months.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11473
Re: Formula for calculating Years to FI?
« Reply #6 on: March 14, 2014, 09:39:17 AM »
...
Then, you want to calculate the future value of a growing annuity (http://en.wikipedia.org/wiki/Time_value_of_money#Future_value_of_a_growing_annuity)

This is a bit more complex, but the formula is

FV(A) = A * ((1 + i)^n-(1+g)^n) /(i - g) (it's easier to see in the wikipedia link)

Where:

A = starting money
FV(A) = future value of money
i = interest rate compound over each period (for instance, per year you might assume 4% interest after inflation)
g = the amount you are contributing per period (for instance, 20,000 per year)
n = the number of payment periods
...

Careful on the formula above.  In most formulas, it doesn't work well when adding or subtracting things with different units, e.g. the (i - g) term with the variables as described above.

From the wikipedia link referenced (and it is a good one):
FV(A) is the value of the annuity at time = n
A is the value of initial payment paid at time 1
i is the interest rate that would be compounded for each period of time
g is the growing rate that would be compounded for each period of time
n is the number of payment periods

Mister Fancypants

  • Bristles
  • ***
  • Posts: 296
  • Age: 47
  • Location: New York
Re: Formula for calculating Years to FI?
« Reply #7 on: March 14, 2014, 10:01:52 AM »
TVM functions are great for even cash flow, like an annuity or mortgage payment.

FV only works when you make the same or no payment every period, so it will not be the best for calculated everything.

You are looking to calculate several things at once, investment/savings return which will definitely be uneven cash flow, for that you probably want to use a modified Dietz formula

http://www.investopedia.com/terms/m/modifieddietzmethod.asp

It allows you to weight returns by when you deposit and withdraw cash over a period of time base on a starting and ending period. This is really good for tracking real investment returns in the past as you know your beginning balance when the year starts and your yearend balance, you have a record of all of your deposit and withdrawal dates, you can get an actual return on investment. The actual investments don’t matter just the cash flow, and you can aggregate many accounts into a single portfolio to get a holistic picture.

 For projections, you have to enter your projections in different scenarios based on you expected savings and withdrawal rates and compare them. So you set your starting balance to your portfolio size and your ending balance of 0, you then calculate how much and when you would withdraw and deposit money based on you FI scenario and keep entering the cash flow for those dates until the rate of return hits your SWR. Adjusting cash flows accordingly. You can than compare multiple SWR and cash flow scenarios

Combined the investment returns from the Dietz with the fixed expenses of the FV formula can allow you fairly project your savings and expenses and more accurately project your FI scenarios.

I have implemented Dietz in worksheets, it’s not just a formula but a data series, if people are interested I could probably create a dummy excel file to upload to the thread (although it isn't really that hard if you are familiar with excel).

Good luck with your FI planning!!

-Mister FancyPants

DoubleDown

  • Handlebar Stache
  • *****
  • Posts: 2075
Re: Formula for calculating Years to FI?
« Reply #8 on: March 14, 2014, 10:33:20 AM »
My approach was to build a spreadsheet with all my investments as rows, and years across the top as columns from now until the next 40-50 years. For each investment, I had the current value, expected annual contributions, expected growth rate, and then used the "FV" (future value) function in Excel to calculate the value for the next year. That future value then becomes the current value for the next year. You can just copy each year's values into the next year's column, and Excel will automatically populate everything in the future years.

Then you just see which year you hit your "crossover" point, the year when you hit enough total net worth that you feel you can retire.

Advantages:

1. Each investment can be tracked individually, using different assumptions (for example, some investments I don't contribute to regularly or in differing amounts, some had different expectations on rates of growth, etc.).

2. You can see how you are making progress year by year, keeping history for the years as they go by (for example, I could compare my expectations to how they really panned out each year). Each year, your planning becomes more certain as you get closer to your goal, and you are relying less on assumptions and more on actual history of how your investments have performed.

3. You can become more refined in your planning, changing things in different years as your circumstances might change.

4. You can do lots of "if/then" analysis, changing various figures and assumptions and seeing the impact of those changes in future years. As an example, I could see what the impact would be of contributing more to an investment, or if lower returns are realized, if inflation is 3% instead of 2%, and so on.

5. This helps with asset allocation, too, because as your net worth grows each year, you can see how each asset is contributing to that overall picture and rebalance as needed.

6. It's a good exercise for lots of other reasons such as financial planning (as mentioned in #4 above), and estate planning. For example, you could include account numbers and contact numbers for each investment. Then when you die one day, your executors/heirs will know exactly what assets you had, their worth, and whom to contact to transfer them according to your wishes.

7. You can use the information in the spreadsheet in reverse to determine how much each asset will provide in retirement using different withdrawal rates, withdrawal periods, and so on. For example, right now I'm living off taxable assets, while my non-taxable assets (401k/IRA) continue to sit and grow. It's helpful to distinguish how those assets individually are being drawn down or continuing to accumulate.

arebelspy

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: Formula for calculating Years to FI?
« Reply #9 on: March 14, 2014, 11:53:15 AM »
Great discussion.  To keep it simpler than the above for those that just want the bottom line...

http://the-military-guide.com/2011/01/03/how-many-years-does-it-take-to-become-financially-independent-2/

In Excel:
=(LN((A3*(1/A4)*A1/A2)+1))/(LN(1+A1))

Where A1 = Compounding rate
A2 = Annual Savings
A3 = Annual Expenses
A4 = SWR (tpyically .04)

This one doesn't take into account current savings, but that's easily done.

The biggest issue is it assumes spending now = spending in the future.  I use methods that allow a different ER spending than current spending (DoubleDown's suggestion should, I believe, allow this).
:)
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

Heart of Tin

  • Stubble
  • **
  • Posts: 203
  • Location: Kansas City
Re: Formula for calculating Years to FI?
« Reply #10 on: March 14, 2014, 03:25:57 PM »
FV(A) = A * ((1 + i)^n-(1+g)^n) /(i - g) (it's easier to see in the wikipedia link)

Where:

A = starting money
FV(A) = future value of money
i = interest rate compound over each period (for instance, per year you might assume 4% interest after inflation)
g = the amount you are contributing per period (for instance, 20,000 per year)
n = the number of payment periods
...
From the wikipedia link referenced (and it is a good one):
FV(A) is the value of the annuity at time = n
A is the value of initial payment paid at time 1
i is the interest rate that would be compounded for each period of time
g is the growing rate that would be compounded for each period of time
n is the number of payment periods

This is the formula for the future value of an annuity immediate where your contributions increase by a percent, g, each period. You might use this to simulate a percent raise every year, but the percent raise would not quite be equal to g unless you base the percent raise on your current savings amount instead of your gross income, effectively giving yourself a bigger and bigger raise each year as a percent of gross assuming that your expenses stay fixed.

What you actually want is the formula for the future value of an annuity immediate, which is

FV = R*((1+i)^n - 1)/i,

where FV is the future value, R is the amount of each contribution, i is the effective growth rate per period, and n is the number of periods. Make sure your periods for n match your periods for i and R. This formula assumes a constant growth rate and the same contribution amount during each period.

Incidentally, this can lead us directly to the formula arebelspy provided through a tiny bit of algebra. Based on our expenses, E, and our SWR we need E/SWR in future value to be FI. Substituting into my above formula we get

E/SWR = R*((1 + i)^n - 1)/i.

Now we just need to solve for n.

E/SWR*i = R*((1 + i)^n-1)
E/SWR*i/R = (1 + i)^n - 1
E/SWR*i/R + 1 = (1 + i)^n
Ln(E/SWR*i/R + 1) = Ln((1 + i)^n)
Ln(E/SWR*i/R + 1) = n*Ln(1+i)
Ln(E/SWR*i/R + 1)/Ln(1 + i) = n,

which is simply arebelspy's formula with my variable names.

Note that n can be any period of time as long as you use the same period to determine the value all other variables. For example, you might use the expenses and contribution amounts from your biweekly paycheck, then use .04/26 = .00154 as the biweekly SWR, and (1.07)^(1/26)-1 = 0.2606% for your biweekly growth rate which would produce a biweekly n that would need to be divided by 26 in order to convert to years to FI. The smaller your contribution period, the smaller your time to FI will be unless you use a negative growth rate.

The biggest issue is it assumes spending now = spending in the future.  I use methods that allow a different ER spending than current spending (DoubleDown's suggestion should, I believe, allow this).
:)

FYI, it is perfectly acceptable to use your expected retirement expense levels instead of your current expense levels in this formula since the only factor affected by the expense variable is the one that gives us our goal for the FV variable which should be based on your expenses in retirement, not your current expenses.

If you want to include your current account balances in this calculation, use the more general formula

(Ln(E/SWR+R/i) - Ln(P + R/i)))/Ln(1 + i) = n,

where P is your current investment account balance.  Or

=(LN(A3/A4 + A2/A1) - LN(A5 + A2/A1))/(LN(1+A1),

in arebelspy's variables with the addition of A5, the current investment account balance. Note that the special case where P = 0 can be simplified to the above formula where current savings levels are not included.

eefool

  • 5 O'Clock Shadow
  • *
  • Posts: 12
Re: Formula for calculating Years to FI?
« Reply #11 on: March 14, 2014, 04:41:17 PM »
If you don't want to start from scratch, I like this spreadsheet that was posted on reddit: http://www.reddit.com/r/financialindependence/comments/1zb4dq/revised_version_of_my_fi_projection_spreadsheet/

I took it and made edits to fit my plans.

arebelspy

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: Formula for calculating Years to FI?
« Reply #12 on: March 14, 2014, 10:50:39 PM »
Well done Heart of Tin, you rock!
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

gmuk

  • 5 O'Clock Shadow
  • *
  • Posts: 13
Re: Formula for calculating Years to FI?
« Reply #13 on: March 16, 2014, 06:12:15 AM »
Thanks everyone! This whole thread has been very useful.

For future reference, after looking around the net, I found this Excel formula that seems to do a similar thing:

NPER(rate, pmt, pv, fv, type)

Rate : is the interest rate per period.

Pmt : is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. (Should be negative)

Pv : is the present value, or the lump-sum amount that a series of future payments is worth right now.  (Should be negative)

Fv : is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type : is the number 0 or 1 and indicates when payments are due.

http://office.microsoft.com/en-gb/excel-help/nper-HP005209198.aspx


Mister Fancypants

  • Bristles
  • ***
  • Posts: 296
  • Age: 47
  • Location: New York
Re: Formula for calculating Years to FI?
« Reply #14 on: March 17, 2014, 07:44:29 AM »
I still have a problem with the one size fits all formula as it assumes a constant rate of savings, a constant rate of expenses over time, and these just not that case.

Look at the last X number of years, your savings rate and expense rate was far from constant, and yes you can normalize over the long run, and normalize how big a nest egg you need you cover that, but that is how the traditional investment community comes up with a much larger than needed nest eggs to support spending in the golden years.

I prefer to look at actual cash flows, and analyze real spending and investing and make more realistic targets in the near term and make logarithmic projects based on those near term projections.

For example I can look at the last 5 years cash flow and analyze my spending and savings and determine how that will project over the next 5 years with pretty fair amount of accuracy knowing how my habits have changed and my projected income/expense profile. I can than look at how my future expense will be beyond those 5 years and project based on the past 5 years of what did happen and the next 5 years. Beyond that I know what my long term income/expenses will be when my mortgage will be gone, college expenses etc... I can add remove the big ticket items, saving for retirement etc. and project future cash flows based on current cash flows much more accurately then just assuming I am going to withdraw x% from some arbitrary unknown nest egg based on some flat expense rate.

I can also constantly redo this analysis and see how accurate I am and refine it, which increases the accuracy, its more work than plugging 4 numbers into a formula, but then again we are all planning for the unknown future running against simulators written by other people and using formulas others have given us and said we will be fine based on academic research saying the money won't run out in x% of scenarios based on past performance which is no indicator of future returns... So I will hedge on the side of the slightly more conservative, not that I am a doomsday end of the world type of guy... I just prefer to be a bit more prepared and will have a much larger nest egg then my SWF would imply is needed.

Not to mention my burn rate and RE spending will probably be on the higher side... I mean look at the handle I've given myself :) (Face punches expected).

arebelspy

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: Formula for calculating Years to FI?
« Reply #15 on: March 17, 2014, 08:08:29 AM »
Look at the last X number of years, your savings rate and expense rate was far from constant

Maybe if you're new.  My expenses and savings rate has been constant for years.

Ditto MMM.

I'd expect the same of any Mustachian who has been Mustachian for years.
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.

nereo

  • Senior Mustachian
  • ********
  • Posts: 17472
  • Location: Just south of Canada
    • Here's how you can support science today:
Re: Formula for calculating Years to FI?
« Reply #16 on: March 17, 2014, 08:46:49 AM »

Maybe if you're new.  My expenses and savings rate has been constant for years.

Ditto MMM.

I'd expect the same of any Mustachian who has been Mustachian for years.
Personally, my expenses essentially constant, but my savings rate has been anything but for the last decade.  For us, our savings is largely driven by our income, and that has fluctuated from ~$28,000 to >$65,000.  Working as contract employees (and now as grad-students on stipends) has created a lot of uncertainty with plotting how much we can save and/or pay down debt.  I'm frequently envious of people who can estimate what they will be earning 1, 2, or even 5 years into the future.  To me that's as unknowable as who will win the superbowl in 2019.

Mister Fancypants

  • Bristles
  • ***
  • Posts: 296
  • Age: 47
  • Location: New York
Re: Formula for calculating Years to FI?
« Reply #17 on: March 17, 2014, 08:52:57 AM »
Look at the last X number of years, your savings rate and expense rate was far from constant

Maybe if you're new.  My expenses and savings rate has been constant for years.

Ditto MMM.

I'd expect the same of any Mustachian who has been Mustachian for years.

I guess my planning is based on my being slightly less Mustachain. Last year alone my home renovation expense was more the double MMM's entire annual budget. I have also multi 6 figure annual income swings over the last 5 years, there is no way my savings rate can remain constant, some years it is astronomically higher than others.

But I will freely admit I am not the typical FIRE planner on the forum so I won't expect my situations to apply to all, but I am sure there are some who do fit more into my planner bucket than the one size fits all as I do run into these people all the time online and off.

arebelspy

  • Administrator
  • Senior Mustachian
  • *****
  • Posts: 28444
  • Age: -997
  • Location: Seattle, WA
Re: Formula for calculating Years to FI?
« Reply #18 on: March 17, 2014, 09:16:44 AM »
To me that's as unknowable as who will win the superbowl in 2019.

The Seahawks.

And every year until then as well.
I am a former teacher who accumulated a bunch of real estate, retired at 29, spent some time traveling the world full time and am now settled with three kids.
If you want to know more about me, this Business Insider profile tells the story pretty well.
I (rarely) blog at AdventuringAlong.com. Check out the Now page to see what I'm up to currently.