Release 1.3 Beta
IRA Withdrawal Calculator and Roth Analyzer.

This page was updated on May 21, 1998.

The IRA Withdrawal Calculator and Roth Analyzer is a Microsoft Excel 4.0 spreadsheet that allows you to determine the effect of early IRA withdrawals and the size of the optimal transfer amount to a Roth IRA. The Analyzer allows you to input various investment returns and inflation rates. It also allows you the vary your asset allocation as you get older. The Analyzer assumes no change in Federal tax rates over a 70 year "Look-Ahead" period.

The Analyzer assumes that living expenses and tax payments are withdrawn in the following order. From your taxable account first, your ordinary IRA second, and your Roth IRA last. It also assumes that you would not want to withdraw money from an IRA that would be subject to the 10% penalty tax on early withdrawals. If this situation occurs, the spreadsheet warns you that your current living expense withdrawal makes you subject to the 10% penalty tax. You can correct this by reducing your living expenses or by increasing your investment returns.

The Analyzer includes five charts that allow you to view your financial and tax position over time. The first is a "30 Year Look-Ahead" that plots your Regular IRA, Roth IRA, Taxable account, and Net Worth vs. your age. This chart clearly illustrates your position during the early retirement years. The second chart shows your Marginal Tax Rate vs. Age. It is the most important criteria for determining if a Roth IRA makes sense for you. The third chart compares your annual withdrawals for living expenses to the maximim "safe" withdrawal rate. (See, "What is the maximum "safe" withdrawal rate in retirement ?") The fourth chart compares IRA Withdrawals to your living expenses adjusted for inflation. It also shows your annual Federal income tax liability. Finally, the fifth chart is a "70 Year Look-Ahead" depicting your IRA balances and net worth for the next 70 years. This graph shows if and when you'll outlive your nest egg based on the assumptions input.

Release 1.3 Beta (May 21, 1998) of the Roth Analyzer is a fairly large (324k bytes) .zip file which expands to a 933k byte Microsoft Excel 4.0 Workbook file.

.

The Input and Results section of the spreadsheet is shown below along with a listing of the required input data. Users may want to print out this page for reference while working with the Excel spreadsheet.

This spreadsheet was originally designed for Lotus 1-2-3. In order to run it on Excel you must make the following adjustments to your Microsoft Excel program.

You must start with at least 2 years worth of living expenses in your taxable account for the Roth Analyzer to work properly. The program withdraws sufficent funds from your IRA accounts in order to maintain the 2 year living expense minimum in the taxable account.

Input Parameters and Results

Note: All fields that appear in blue on the spreadsheet require user input.

Retiree Age on 12/31 of last year- Enter your age on December 31 of the previous year.

Current Year- Enter current year.

Important: Press CNTL - L to load correct IRS Life Expectancy Table.- Also, if you change the retiree age, make sure you press CNTL - L again to load the new table.

IRA (Pre-59-1/2) Early Withdrawal- This field allows you to choose one of two methods for making withdrawals from an IRA prior to age 59-1/2 without paying the 10% penalty tax. For more information on early withdrawals see: Can I withdraw money from my IRA before age 59-1/2 ? If you do not need to make an early withdrawal, enter "0" (zero).

Amort. Method Interest Rate- Enter 80% to 120% of the "Long-Term Applicable Federal Rate" See: Table of Long-Term Applicable Federal Rates. for current interest rates.

Pre 59-1/2 Penalty Tax?- No input required. A warning appears in this cell if your living expenses and investment return requires a withdrawal prior to age 59-1/2 subject to the 10% penalty tax. Reducing your annual living expenses (input below) will correct this situation.

Income & Expenses

Wage & Salary Income- Enter this year's before tax annual income. Subsequent years' income is indexed for inflation.

Age Wage & Salary Income Ends- Enter the age you plan to stop earning wage and salary income.

Social Security Income- Enter your expected Social Security income in today's dollars. Subsequent years' Social Security benefit payments are indexed for inflation.

Age Social Security Income Begins- Enter the age you plan to start drawing Social Security.

Pension Income (per year)- Enter your expected Pension income in today's dollars. Subsequent years' pension benefit payments are not indexed for inflation. (Note: If you are fortunate to have a pension that grows with inflation (e.g. military or some government pensions), enter "0" here. Instead, add that amount to the social security benefit you entered above so that it receives the inflation adjustment.)

Age Pension Income Begins- Enter the age you plan to start drawing your pension.

Maximum "Safe" Withdrawal- Enter the percent of assets for your maximum "safe withdrawal rate. (See, "What is the maximum "safe" withdrawal rate in retirement ?")

Living Expenses (per Year)- Enter your after tax living expenses. Subsequent years' living expenses are indexed for inflation.

Calculate Annual Expenses as- Enter "1" to have your annual expenses calculated as the maximum "safe" withdrawal (percent of assets). Enter a "2" to have your annual living expenses calculated as the annual amount input above, adjusted for inflation. It's best to enter a "2", then check the third chart (Annual Withdrawal) to make sure your living expenses are below the safe maximum.

Inflation Rate- Enter your estimate of future inflation in percent per year. (Note: It's very instructive the enter a high inflation rate, say 1% or 2% less than the return on your investments. This would represent the most difficult investment environment you're likely to encounter. If your portfolio can survive this combination and still maintain your inflation adjusted withdrawal for living expenses, you're in good shape!)

Rebalance Taxable Account?- Rebalancing your taxable account may result in taxable stock sales that the Roth Analyzer ignores. Enter a "2" if you do not want your taxable account rebalanced.

Roth IRA

Calculate Roth Rollover as- Enter a 1,2 or 3 to have your Roth IRA funded to the limits of the 15%, 28% or 31% tax bracket, respectively. Enter a "4" if you want to enter your own "% of assets" transferred to a Roth IRA.

% Regular IRA transferred- Enter the percentage of your Regular IRA balance you want to transfer to a Roth IRA.

Amount of Roth rollover- Not an input field.

Use 4-year tax averaging for Roth - you can only use this option for money transferred to a Roth IRA during the 1998 calendar year.

% Net Worth (Today + 20 Years)- Not an input field.

% Net Worth (Today + 40 Years)- Not an input field.

Negative Net Worth at Age- Not an input field. Reveals age you run out of money.

Federal Tax Information

Filing Status- Enter 1 for single, 2 for married.

Personal Exemptions- Enter number claimed.

Itemized Deductions- Overides Standard Deduction. Value is indexed for inflation.

Adjustments to Income- Enter any adjustments to income on your Federal tax return. Value is indexed for inflation.

Capital Gains - Low Bracket- Enter value from 8% to 10% to estimate your tax liability for the low bracket. (Note: Under the 1997 Tax Act there are five separate long term capital gains holding periods. Entering 10% will result in a conservative calculation.

Capital Gains - High Bracket- Enter value from 18% to 20% to estimate your tax liability for the low bracket. (Note: Entering 20% will result in a conservative calculation.

Expected Investment Returns- For each asset class (i.e., cash, bonds, REITs, stocks) enter your return from both capital appreciation and interest & dividends. For money market funds (i.e., cash), all of your return will be interest, so capital appreciation is zero. If you hold your bonds to maturity, capital appreciation will also be zero. REITs and stocks will most likely have both capital appreciation and dividends combining for total return. For example, a reasonable breakdown for stocks would be 8% capital appreciation and 2% dividends for a total return of 10% per year.

Beginning Account Balances- Enter value for each asset class on January 1st for both your IRA and taxable account.

Total Non-Deductible IRA Contributions- Enter the total of all your non-deductible IRA contributions for all your ordinary IRA's

Capital Gains Tax Cost Basis- Enter your cost basis (i.e., the price you paid) for each asset class in your taxable account. It is not necessary to enter the cost basis for your IRA since all gains as taxed as ordinary income.

Asset Allocations - 10-Year Increments- Enter your desired asset allocation for your IRAs and Taxable Account. You may select a different allocation as you get older, if desired.

How do I decide how much to put in a Roth IRA ?

It's easy, once you've entered all the data. Return to the cell labeled "% Regular IRA Transferred" and watch the graph to the left of the cell entitled "Marginal Tax Rate vs. Age". As you increase the percentage of your IRA transferred to a Roth IRA, your tax rate will change. As long as the rate when you're young is lower than the tax rate when your old, a Roth rollover makes sense. When your tax rate is higher when you're young and lower in your old age, you've transferred too much to the Roth IRA. Start at 10% and increase the percentage in 5% increments to see how you fare.

Frequently Asked Questions about the Roth Analyzer

1. Has any "independent authority" confirmed the accuracy of the algorithms used in the Roth Analyzer?

No. The Roth Analyzer has been reviewed by several people and has gone through a number of revisions, but no "independent authority" has conducted a comprehensive review of the software and issued a "Good Housekeeping" seal of approval. This is a free, non-commercial spreadsheet. Users assume the risk of any errors. See, our Legal Disclaimer.

2. How should I interprete the 30 year and 50 year Net Worth calculations "with Regular IRA taxed at the next highest bracket?" This seems confusing.

This is an attempt to place a Roth IRA and Ordinary IRA on more or less equal tax footing. It's very difficult to show the after tax value of an IRA account. You would never withdraw the entire balance of a large Ordinary IRA in a single year because that would place you in the highest bracket. On the other hand, you can withdraw the entire balance of a Roth IRA without tax consequences once you're age 59 1/2 and the money has been in the Roth IRA for 5 years. The middle ground I chose, assuming that you would pay taxes on your Ordinary IRA at the next highest tax bracket, models withdrawing a large IRA balance over 5 or 10 years. This is by no means a completely accurate method. An exact calculation would require; 1) assuming the age at death for the retiree, 2) devising a withdrawal schedule that depletes the IRA before death, 3) calculating the present value of the after tax income stream. This calculation is beyond the scope of this spreadsheet.