Author Topic: Need some help with my FIRE Excel sheet calculations  (Read 2587 times)

Linea_Norway

  • Walrus Stache
  • *******
  • Posts: 8569
  • Location: Norway
Need some help with my FIRE Excel sheet calculations
« on: December 17, 2018, 10:48:44 AM »
Hi.

I have a FIRE Excel sheet with calculations for the future FIREl years. Today DH asked some critical questions about it and I hope you can help to answer that.

In the Excel sheet I have our investments in today's money. From that I take out a safe withdrawl rate, 4%. I have not included inflation (let's say that is 3%), because that should be baked into the 4%. So our investments should be making 7% growth per year, of which 3% is inflation, but that is not in the Excel sheet. So we are only using todays money.

For our expenses I have used a fixed sum, let's say 50K per year. I have used this same sum for each future year. As inflation is not included in the investments, I thought the expenses didn't have to be included in the expenses either. Is this correct? Or should I let the expenses grow with the yearly inflation rate? And then let the investment be as they are in todays money without inflation?

My DH also thinks that my tax calculations might be off. I calculate tax over 4% of our investments, while we in real life should be taking out 7%, including inflation. That gives a much higher tax rate on tax on stock profit.

I thought the whole idea about the perfectly simple math of early retirement contained only calculations without inflation.
Is that wrong, or should I put in inflation overall?

As we plan to FIRE next year, we are nervous to get it right.

Novik

  • Pencil Stache
  • ****
  • Posts: 973
  • Age: 30
  • Location: Ottawa, ON, Canada
Re: Need some help with my FIRE Excel sheet calculations
« Reply #1 on: December 17, 2018, 11:18:42 AM »
Sounds like there's 3 main questions here:
  • Assuming inflation is not included in investment returns, should inflation be included in expense?
  • How should taxation on investment returns be calculated? 4% or the after inflation 7%.
  • Should inflation be included everywhere?

For #1, the important thing is to be consistent and include inflation in NEITHER returns nor expenses, or BOTH. Generally neither is simpler and relies on fewer assumptions, so I'd recommend that.  Of course, depending on your personal situation, you may vary/increase your annual expense figure for other reasons.

So for #3, that makes the answer no. It can be, but you don't have to.

Inflation on investments/expenses is relatively straightforward, but for taxes (#2) things get trickier to understand, though I suspect you're still okay. In Canada, the tax brackets get adjusted for inflation every year, so what you pay as a % on 50k today, would be the same % on 50k+inflation in the future. If you have the same or a similar setup, then you can ignore inflation for tax purposes as well.


Does that make sense?  I'm not sure on the details of how #2 works though, so hoping someone already FIRE can chime in there more.

Linea_Norway

  • Walrus Stache
  • *******
  • Posts: 8569
  • Location: Norway
Re: Need some help with my FIRE Excel sheet calculations
« Reply #2 on: December 17, 2018, 12:51:31 PM »
I have started to change my excel sheet to include inflation all over. It is a hell of a job and I need to change a lot. I need to redo a lot of calculations.
The new sheet will be saved under a different name, so nothing is spoiled. But I'm not sure whether the whole job is necessary.

To implement only higher taxes over the 7% requires me to do all the calculations anyway.

Edit: I have put it aside for a while. I need to recalculate everything and it is too much work so spend my whole evening on.
« Last Edit: December 17, 2018, 01:02:04 PM by Linda_Norway »

Novik

  • Pencil Stache
  • ****
  • Posts: 973
  • Age: 30
  • Location: Ottawa, ON, Canada
Re: Need some help with my FIRE Excel sheet calculations
« Reply #3 on: December 17, 2018, 01:21:18 PM »
Why are you trying to account for higher taxes over the 7%?   This all works out with inflation ignored/assumed, so no need to recalculate anything.

Example:
Expenses: 10k   Investments: 250k   (so 4% withdrawal)   
Tax rate of 2% on anything up to 10k, and 5% above 10k -> you pay 200$ in taxes.

Inflation is 10% in one year.

Expenses: 11k   Investements = 250 * 1.14 (inflation+4%) - 10k withdrawal = 275k.    4% of 275k is 11k, so all good there.
Now, assuming the tax brackets are also adjusted, the tax rate is 2% on everything up to 11k, and 5% above that -> you pay 220$ in taxes (which is 10% higher than 200$ last year, aka exactly inflation adjusted).

Ta-da!  No need to worry about inflation.
So the only thing you need to do is confirm that tax brackets get inflation adjusted in Norway.

Linea_Norway

  • Walrus Stache
  • *******
  • Posts: 8569
  • Location: Norway
Re: Need some help with my FIRE Excel sheet calculations
« Reply #4 on: December 17, 2018, 01:56:59 PM »
It is not about a tax bracket. Our money is in a stock account where you can take out the original amount you put in tax free. Over all the profit the stocks make when sold, we need to pay 32%! taxes, only when we take it out. When taking money out of the account, you first reduce the original amount with your withdrawl.

The original amount is expected to grow with inflation, but all the inflation correction will be taxed when we take the money out. The original tax free amountbwill not change, while the expenses will grow with inflation rate each year. When the original amount in the stock account is used up, we will start using the profit only and pay 32% taxes over everything we take out. That means the first x number of years those taxes are zero, while in the years after, the whole amount is taxable.

DH thinks I should calculate exactly when the year starts where we need to pay full tax.

RyanAtTanagra

  • Handlebar Stache
  • *****
  • Posts: 1316
  • Location: Sierra Mountains
Re: Need some help with my FIRE Excel sheet calculations
« Reply #5 on: December 17, 2018, 02:54:59 PM »
Quick correction: Stock market returns 10-11% (assumed), of which 3% is lost to inflation.  7% return already factors the inflation in, so you don't have to subtract it again.

You don't have to account for inflation in your numbers, as long as you're consistent about it: 4% of your stash in todays numbers, against todays expenses.  Both those numbers will increase in the future (presumably), but they'll increase together (also presumably).  It's all an estimation, but you can think of todays numbers into the future.  I do, it's easier.  I just know in reality things are going to go up (both my income/withdrawals, and my expenses).

You're also not taking out 7%, to account for the 3% inflation.  If you had a $1mil stash with $40k cost of living, you take out $40k, not $70k.  You leave the extra $30k invested to grow against the inflation so it's there next year (and 50 years from now).

Novik

  • Pencil Stache
  • ****
  • Posts: 973
  • Age: 30
  • Location: Ottawa, ON, Canada
Re: Need some help with my FIRE Excel sheet calculations
« Reply #6 on: December 17, 2018, 06:56:30 PM »
Thanks for the extra info on your specific tax issues - it clears up a lot. This isn't really a question about inflation in FIRE calcs as it is about withdrawal strategies and calcs around an account with specific rules. And so you're right, it is a lot more complicated.

Some possible solutions that occur to me, in order of increasing complexity:
- assumed everything has 32% tax on it.
- calculate:  "currentContributions / currentYearlyExpenses = # years you can make tax free withdrawals" and assume 32% tax every year after that   (this will over-estimate how many years you get, for the inflation reasons you mention)
- solve for the numbers of years in "currentContributions = SUM(yearlyExpenses + yE*1.03 + yeE*1.03*1.03 etc)"  where 3% is the rate of inflation, and then apply 32% every year after that.  This math is tricky but you could do it manually pretty easy in Excel, and you only have to do it once to get a good estimate. It pretty much answers your DH's question too.  **

You could go beyond those, but you'd have pretty limited extra useful information but a whole pile of effort.

**if this isn't clear, and you give me some numbers, I can work it out in excel and share.

secondcor521

  • Walrus Stache
  • *******
  • Posts: 5503
  • Age: 54
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: Need some help with my FIRE Excel sheet calculations
« Reply #7 on: December 17, 2018, 07:10:49 PM »
Stick your numbers into cfiresim.com or firecalc.com.  They've already done the math.  They have bells and whistles your spreadsheet probably doesn't.  And they consider historic variability in returns and inflation, which you probably don't.

Since you mentioned taxes, both websites mentioned assume that you will include taxes as a part of your expenses.  I don't know how it is where you live, but where I am, taxes since FIRE are, for all practical purposes, zero.

Linea_Norway

  • Walrus Stache
  • *******
  • Posts: 8569
  • Location: Norway
Re: Need some help with my FIRE Excel sheet calculations
« Reply #8 on: December 18, 2018, 12:58:46 AM »
Stick your numbers into cfiresim.com or firecalc.com.  They've already done the math.  They have bells and whistles your spreadsheet probably doesn't.  And they consider historic variability in returns and inflation, which you probably don't.

Since you mentioned taxes, both websites mentioned assume that you will include taxes as a part of your expenses.  I don't know how it is where you live, but where I am, taxes since FIRE are, for all practical purposes, zero.

I don't live in the US, but in Norway. I find our taxes a bit complicated to put in someone else's calculator.
But I'll have a look there as well to see what comes out.

Linea_Norway

  • Walrus Stache
  • *******
  • Posts: 8569
  • Location: Norway
Re: Need some help with my FIRE Excel sheet calculations
« Reply #9 on: December 18, 2018, 01:00:12 AM »
Thanks for the extra info on your specific tax issues - it clears up a lot. This isn't really a question about inflation in FIRE calcs as it is about withdrawal strategies and calcs around an account with specific rules. And so you're right, it is a lot more complicated.

Some possible solutions that occur to me, in order of increasing complexity:
- assumed everything has 32% tax on it.
- calculate:  "currentContributions / currentYearlyExpenses = # years you can make tax free withdrawals" and assume 32% tax every year after that   (this will over-estimate how many years you get, for the inflation reasons you mention)
- solve for the numbers of years in "currentContributions = SUM(yearlyExpenses + yE*1.03 + yeE*1.03*1.03 etc)"  where 3% is the rate of inflation, and then apply 32% every year after that.  This math is tricky but you could do it manually pretty easy in Excel, and you only have to do it once to get a good estimate. It pretty much answers your DH's question too.  **

You could go beyond those, but you'd have pretty limited extra useful information but a whole pile of effort.

**if this isn't clear, and you give me some numbers, I can work it out in excel and share.

I am currently working on a simple new version that does what you suggest and will share it when I get there. Then hopefully you can improve it.

Linea_Norway

  • Walrus Stache
  • *******
  • Posts: 8569
  • Location: Norway
Re: Need some help with my FIRE Excel sheet calculations
« Reply #10 on: December 18, 2018, 01:38:12 AM »
Here is my new very simple FIRE Exel, only including tax on selling stock with profit. As mentioned above, I need to pay stock tax over the whole amount that the stash has grown more than the original I put in, at the moment of taking it out of the account.

We can discuss afterwards which value I should use for stock market growth. For the moment I use 4%.

Is it really as simple as this? Mind that the word HVIS in the function in column E means IF.

I used the inflation only to calculate when the original amount of stash is gone and when I need to start paying taxes over the whole amount to be taken out. Otherwise, I use the 4% for growth and subtract the expenses from it in today's money.

If this basic principle is good, then I can continue to put in the additional tax rules, tax free foot and possible income flows from post-FIRE work.
« Last Edit: December 18, 2018, 01:40:27 AM by Linda_Norway »

Novik

  • Pencil Stache
  • ****
  • Posts: 973
  • Age: 30
  • Location: Ottawa, ON, Canada
Re: Need some help with my FIRE Excel sheet calculations
« Reply #11 on: December 18, 2018, 08:32:38 AM »
I think it really is that simple!  Because my original idea for calculating how many years of tax free withdrawals did not include stock market growth (for simplicity), I ran a parallel calc in the same sheet (attached for your interest) and got basically the same answer*.  Yours is of course better because it allows you to change stock market growth, so I would continue from there for sure.


* technically I found that you should be accounting for some tax in the 12th year (2031) because ~100k will be withdrawn from non tax free amount. But that's not a big deal in the scheme of things. If you wanted to be extra conservative you could change "DX>0" to "DX>CX" and get 32% tax as soon as you withdrew anything from the non-tax free portion in a year.


One other question - this 32% withdrawal tax seems very punitive. I'd be interested in learning more about the account/policy/law that results in this, out of sheer curiosity.  Is it like an American 401(k) where it's an early withdrawal penalty, or is it for everyone?

Linea_Norway

  • Walrus Stache
  • *******
  • Posts: 8569
  • Location: Norway
Re: Need some help with my FIRE Excel sheet calculations
« Reply #12 on: December 18, 2018, 12:52:23 PM »

One other question - this 32% withdrawal tax seems very punitive. I'd be interested in learning more about the account/policy/law that results in this, out of sheer curiosity.  Is it like an American 401(k) where it's an early withdrawal penalty, or is it for everyone?

It is not an early withdrawl penalty. It is the normal tax you have to pay over stock profit for everyone. In 2018 it is 29% and next year it is almost 32%.
This whole type of account with delayed profit tax is new since a year or two. You may sell stocks as you please and keep it in inside the account or buy other stocks, but you don't start paying profit tax until you take it out of the account. So you can use the profit for years for making more profit by buying new stocks. It is a good new tax thingy. Everyone was allowed to put their stocks from other funds into this account tax free. But the tax in taking out is insane. It would have been the same tax for selling stock from normal funds. The account is for after income tax money.

This type of account was introduced, because our retirement system are not as good as they were for the old generation. Many people need to save up a big sum for themselves.

There is also another fund, particularly for retirement saving, where you can add money to before income tax (income tax and profit tax must be paid at take out) but you can take it out from age 62 or 67 and must be spread over 20 years. I don't have such a fund, because I think we can live of the age pensions we have built up by now. We only live of a fraction of our incomes. I will get this confirmed early in 2019.
« Last Edit: December 18, 2018, 10:58:16 PM by Linda_Norway »

Novik

  • Pencil Stache
  • ****
  • Posts: 973
  • Age: 30
  • Location: Ottawa, ON, Canada
Re: Need some help with my FIRE Excel sheet calculations
« Reply #13 on: December 18, 2018, 01:20:30 PM »
Thanks for explanation!

 

Wow, a phone plan for fifteen bucks!