Spreadsheet Goodness
This is part 1 of a 3-part series.
Download the spreadsheet as:
Knowing how to evaluate investments across across tax structures is an important core skill to have. Perhaps you need to run projections whether you should even start a small business. Or maybe you need to decide what retirement plans you will make available to your employees. In the past, I usually created one-off spreadsheets to model specific situations. However, I've been working on a unified format that let's you model everything from 401Ks to variable annuities. I think I finally fixed the last bugs so I'm ready to release this spreadsheet to the public.
Projecting 401K Contributions
Let's start simple -- suppose you plan to contribute $100 a month to your 401K for the next 10 years and then contribute nothing for the next 10 after that. How much money will you have when you retire in 20 years assuming annualized return of 10%? So let's head over to the Tax-Deductible section and fill out the following numbers like so. We'll make the simple assumption your tax rate will be fixed at 25% federal and 5% state.
And we have the answer. About $54.5K before taxes are applied. If you took it out all at once fully taxed at a single marginal rate -- $38K.
Historical Returns
You might say 10% every year is not accurate. In fact, you think 1970 is coming back with a vengeance. So we edit the % column with the actual S&P500 numbers from 1970-1989.
The nominal returns end up being even better ($88K/$61.5K) because contributions during 1970-1979 bought a lot of shares at discounts before the 80's. (What the real returns were is a story for another topic.)
401K versus Roth IRA
As you know, Roth IRAs are fully tax free (for now). That sounds way better than a 401K or Traditional IRA right? Let's head over to the Tax-Free section and give it a try. Key point: the monthly contribution is not $100 but $100*(1-tax) because you must pay tax on it first -- hence we put $70 as the monthly contribution.
Amazing, the $38,196 number matches the 401K number perfectly. You did not think the IRS was actually going to give you more money right? This is why people say fund one or the other depending on what your current tax rate is and what you think your future one will be. Better yet, fund both if possible to diversify for taxes.
HSA
Do you qualify to fund a Health Savings Account? When used for qualified medical expenses, it's the only thing available where you do not pay tax on either contribution or withdrawal. So we enter the pre-tax amount of $100 in the Tax-Free section.
No surprise, 30% higher than both the 401K and Roth IRA number. If you have an HSA option, make it your number one priority. Since the IRS is matching your funds by your tax bracket, that may even beat a 401K with matching. (Do both though!) Some employers will even fund part/all of a HSA which would make it truly the best thing since sliced-bread. And as far the limited use, everybody encounters medical bills in life -- you will be able to take the money out tax-free sooner or later.
To be continued in part 2 (taxable accounts) and part 3 (variable annuities, variable universal life) ...
This is part 1 of a 3-part series.
Download the spreadsheet as:
- OpenDocument: InvestmentTaxSummary.ods
- Excel: InvestmentTaxSummary.xls
Knowing how to evaluate investments across across tax structures is an important core skill to have. Perhaps you need to run projections whether you should even start a small business. Or maybe you need to decide what retirement plans you will make available to your employees. In the past, I usually created one-off spreadsheets to model specific situations. However, I've been working on a unified format that let's you model everything from 401Ks to variable annuities. I think I finally fixed the last bugs so I'm ready to release this spreadsheet to the public.
Projecting 401K Contributions
Let's start simple -- suppose you plan to contribute $100 a month to your 401K for the next 10 years and then contribute nothing for the next 10 after that. How much money will you have when you retire in 20 years assuming annualized return of 10%? So let's head over to the Tax-Deductible section and fill out the following numbers like so. We'll make the simple assumption your tax rate will be fixed at 25% federal and 5% state.
And we have the answer. About $54.5K before taxes are applied. If you took it out all at once fully taxed at a single marginal rate -- $38K.
Historical Returns
You might say 10% every year is not accurate. In fact, you think 1970 is coming back with a vengeance. So we edit the % column with the actual S&P500 numbers from 1970-1989.
The nominal returns end up being even better ($88K/$61.5K) because contributions during 1970-1979 bought a lot of shares at discounts before the 80's. (What the real returns were is a story for another topic.)
401K versus Roth IRA
As you know, Roth IRAs are fully tax free (for now). That sounds way better than a 401K or Traditional IRA right? Let's head over to the Tax-Free section and give it a try. Key point: the monthly contribution is not $100 but $100*(1-tax) because you must pay tax on it first -- hence we put $70 as the monthly contribution.
Amazing, the $38,196 number matches the 401K number perfectly. You did not think the IRS was actually going to give you more money right? This is why people say fund one or the other depending on what your current tax rate is and what you think your future one will be. Better yet, fund both if possible to diversify for taxes.
HSA
Do you qualify to fund a Health Savings Account? When used for qualified medical expenses, it's the only thing available where you do not pay tax on either contribution or withdrawal. So we enter the pre-tax amount of $100 in the Tax-Free section.
No surprise, 30% higher than both the 401K and Roth IRA number. If you have an HSA option, make it your number one priority. Since the IRS is matching your funds by your tax bracket, that may even beat a 401K with matching. (Do both though!) Some employers will even fund part/all of a HSA which would make it truly the best thing since sliced-bread. And as far the limited use, everybody encounters medical bills in life -- you will be able to take the money out tax-free sooner or later.
To be continued in part 2 (taxable accounts) and part 3 (variable annuities, variable universal life) ...
Subscribe
Leave a comment