You will notice the time line information on pages 1 & 2 match the time line information on pages 3 & 4. Seek to align these time lines when reviewing the print job.

The spreadsheet is designed, starting on the left, with the time line information, tax related information, the investment portfolios (non-qualified and qualified) column, followed by savings contributions, and the earned income section. These make up the “Accumulation Phase.” To the right of the accumulation phase is the distribution phase. This phase begins with another time line section, some additional tax info, followed by all investment portfolio (non-qualified and qualified) withdrawals and fixed income sources that will exist in retirement. In the third column from the right in the distribution phase section you will see the “Actual Income” from all sources column (which totals all income sources in the distribution phase – including any existing earned income) to the right of that column you will find the Target Net and Gross Income columns. Once the user indicates the target net income for all sources and the expected percentage of income to be paid in taxes, the model determines the gross income needed to achieve the target net income, and then seeks to satisfy the target gross income by first considering the fixed income and qualified portfolio sources, and then it calculates the Non-qualified portfolio withdrawal as a residual amount needed to bring the income level up to the gross income target. You will also notice that the data entry that governs each column’s data is generally above in that respective column.

Accumulation Section

Data Entry:

Ord. Inc. Tax Rate : Enter the appropriate ordinary income tax rate.

Dividend Tax Rate : Enter the appropriate dividend tax rate.

Cap Gain Tax Rate : Enter the appropriate capital gain tax rate.

Ordinary Income Earnings : Enter the percentage of the non-qualified portfolio earnings that is likely to be taxed at the ordinary tax rate.

Dividends Earnings : Enter the percentage of the non-qualified portfolio earning that is taxed at the dividend tax rate.

Capital Gains : Enter the percentage of the non-qualified portfolio earning that is taxed at the Capital Gains tax rate.

Tax Free : The worksheet then calculates any remaining amount that will be considered tax-free.

Earnings Non-Qualified

Initial Gross Earnings : Enter expected gross investment earning assumption

Net of tax Earnings : The worksheet then calculates the net-of-tax earnings based on the tax information you have entered in the section above.

Periodic Rate Step Down: The worksheet allows you to indicate a periodic rate step down to simulate an increasingly more conservative asset allocation in the portfolio. Indicate the periodic rate reduction if any.

Step Down Freq. (Yrs.): Indicate the frequency that the earnings rate reduction should apply.

Minimum Rate Allowed : Enter a rate assumption floor. This stops the rate reduction at the specified rate.

Beginning Investment Balances : Enter all assets currently allocated for retirement purposes for person 1 and person 2 in taxable accounts.

Earnings Qualified

Initial Gross Earnings : Enter expected gross investment earning assumption

Net of tax Earnings : The worksheet uses the gross amount for earnings in the qualified account since these funds grow tax deferred.

Periodic Rate Step Down : The worksheet allows you to indicate a periodic rate step down to simulate an assumed ever increasingly more conservative asset allocation in the portfolio. Indicate the periodic rate reduction.

Step Down Freq. (Yrs.) : Indicate the frequency that the earnings rate reduction should apply.

Minimum Rate Allowed : Enter a rate assumption floor. This stops the rate reduction at the specified rate.

Beginning Investment Balances : Enter all assets currently allocated for retirement purposes for person 1 and person 2.

Qualified Savings Percentages

Savings Phase Contribution Percentages : Enter the percentage of income that each person is contributing to retirement savings, as well as any employer matching percentage.

Investment Deposit Frequency Pull-down Menu : Indicate the frequency of investment contributions.

Non-Qualified Savings

Misc. Savings or Inheritance : This column provides the opportunity to illustrate either a lump sum inheritance (or other windfall like the sale of a business) or an annual contributions to the non-qualified portfolio made over a series of years. To include an expected inheritance you can show the dollar amount and input the beginning and ending years to be the same year. This will add that single number to the portfolio in that specified year. You have the option of inflating that number if that is appropriate. To show an annual contribution over a series of years, simply input the annual dollar value in the amount cell and indicate the beginning and ending year of the contributions. If it is appropriate include the inflation factor.

Income Summary :

Savings Phase

Income Savings Phase : Enter the annual income for person 1 and person 2.

Current Age : Enter the current age for each person.

Current Year : Enter current year.

Savings through What Age ?: Enter the age through which each person will make contributions to the retirement savings plan.

Savings through what Year ?: This value is calculated for you.

Inflation Factor : Inter the assumed cost of living factor for each income. This serves to increase the savings contributions proportionately.

Post Savings Phase

This section is for you to illustrate earned income past the time frame during which savings contributions will be made. This may be a continuation of the same income level or a reduced income that occurs into the retirement years. It is assumed to begin the year following the end of the savings phase. This earned income is one of the income sources included when the model is considering all retirement income sources before calculating the portfolio withdrawal needed to meet the target income level. This is the only potential income source during retirement that is not on the “distribution” phase area of the worksheet.

Income Post Savings Phase : Enter any earned income for each person that will follow the actual accumulation phase.

Starting age : This is calculated for you. It is the beginning age of the pose savings income.

Ending Age : Enter the age through which this post saving income is expected to last.

Inflation Factor: Indicate any expected inflation factor that will apply to any post savings phase earning income.

Tax Treatment: Enter the percentage of income that is attributable to each tax category.

Distribution Section:

As stated above, this phase begins with another time line section, followed by all investment portfolio withdrawals and fixed income sources that will exist in retirement. In the third column from the right of the distribution phase you will see the “Actual Income” from all sources column (which totals all income sources in the distribution phase – including any existing earned income). The last 2 columns show the Target Net and Gross Income. Once the user indicates the target net income for all sources and the expected percentage of income to be paid in taxes, the model determines the gross income needed to achieve the target net, and then seeks to satisfy the gross income need by first considering the fixed income and qualified portfolio sources, and then it calculates the Non-qualified portfolio withdrawal as a residual amount needed to bring the income level up to the necessary gross income target.

Data Entry

Assumed Tax Rates (Post Retire)

Ord. Inc. Tax Rate : Enter the appropriate ordinary income tax rate.

Dividend Tax Rate : Enter the appropriate dividend tax rate.

Cap Gain Tax Rate : Enter the appropriate capital gain tax rate.

Layer One Qualified Withdrawals

Annual Amount : Manually Enter the first layer of expected qualified portfolio withdrawals. Some may wish to use this to manually enter enough money to cover “required minimum distributions” from the qualified account. This value can be zero if you do not need this added flexibility.

Starting Year : Enter the beginning year of this withdrawal stream.

Ending Year : Enter the ending year of this withdrawal stream.

Inflation Factor : Enter any expected annual increase in this withdrawal steam.

Layer Two Qualified Withdrawals

Annual Amount : Manually Enter the first layer of expected qualified portfolio withdrawals as stated above. Enter the second layer of qualified portfolio withdrawals. Review additional notes below for further insight.

Additional Notes : The two qualified portfolio withdrawals (layer one and two) were set up as manual entries to allow the user more flexibility on the amount of and timing of those withdrawals. One can be used to indicate RMDs and the other can be used to indicate an additional layer of withdrawals that may occur during another time frame – perhaps later. Once the qualified portfolio withdrawals are manually entered, the model then seeks to satisfy the net of tax target gross income by considering all fixed income sources along with the qualified withdrawals you entered, and then the worksheet calculates the non-qualified portfolio withdrawal as a residual amount needed to bring the income level up to the gross income target.

*** To arrive at the appropriate manual entry for the qualified withdrawal, first determine when you want qualifiedwithdrawals to begin for layer one and two (what Year). Set the first layer of withdrawals to the desired dollar amount (can be zero), then use the second layer to estimate (aiming high in your estimate) what additional dollar amount (in today’s dollars) of the total income requirement might need to come from the qualified account. Enter your (high) estimate in the layer two withdrawal section. Part of the decision on amount you enter is determining whether or not you want some of the income to come from the non-qualified portfolio. Lowering the qualified withdrawal amount will increase the calculated non-qualified withdrawal amount. Once you have made your preliminary entries look at the actual income column relative to the target income (far right columns) to see if your entries have created a surplus income or an income shortfall. If you have aimed high enough in your estimate on the second layer withdrawals to be showing an income surplus, you then should manually adjust the second layer number down until you see no surplus in the far right column. If you are familiar with the “Goal Seek” feature of Excel, it can be used to facilitate this process.

Also, to the extent that the "actual" income (column AI) generated by your entries exceeds the "target" gross income (column AK) the numbers in the actual income column turn blue and a "surplus" label shows up to the right of the worksheet. If the actual income generated by your entries is too low to meet the target gross income needed, the numbers turn green and a "shortfall" label shows up to the right of the worksheet. If this happens evaluate what adjustments can be made in your data entry to correct the problem. The most likely adjustment needed is in the layer two qualified dollar withdrawal amount ...

There is a column just to the right of the qualified withdrawals that places a "y" when it is likely that minimum distributions are necessary, by virtue of the fact that one of the clients is age 70 or above. (The model does not calculate minimum distributions it merely indicates when they are likely needed just as a precaution).

Starting Year : Enter the beginning year of this withdrawal stream.

Ending Year : Enter the ending year of this withdrawal stream.

Inflation Factor : Enter any expected annual increase in this withdrawal steam.

Tax Treatment : Indicate the expected tax treatment of income coming from the qualified accounts.

Social Security Person 1

Amount : Enter the expected annual Social Security dollar amount in today’s dollars.

Starting Age : Enter the starting age for Social Security payments for person 1. It is anticipated that there will not be an ending age for Social Security.

Inflation Factor : Enter any cost of living factor that will apply to Social Security payments.

Tax Treatment : Indicate the expected tax treatment from this income source.

Social Security Person 2

Amount : Enter the expected annual Social Security dollar amount in today’s dollars.

Starting Age : Enter the starting age for Social Security payments for person 1. It is anticipated that there will not be an ending age for Social Security.

Inflation Factor : This inflation is assumed to coincide with the inflation factor for SS Person 1.

Tax Treatment : Indicate the expected tax treatment from this income source.

Pension for Person 1

Amount : Enter the expected annual pension income for person 1 in today’s dollars.

Starting Age : Enter the expected starting age for pension income for person 1.

Ending Age : Enter the expected ending age for pension income for person 1.

Inflation Factors : Enter the inflation factor that will apply to the LTC annual cost number.

Tax Treatment : Indicate the expected tax treatment from this income source.

Pension for Person 2

Amount : Enter the expected annual pension income for person 2 in today’s dollars.

Starting Age : Enter the expected starting age for pension income for person 2.

Ending Age : Enter the expected ending age for pension income for person 2.

Inflation Factors : Enter the inflation factor that will apply to pension income for person 2.

Tax Treatment : Indicate the expected tax treatment from this income source.

Target Income Retirement

Gross Income Need : No entry needed. This is a calculated value.

Estimated Income % lost to taxes Entry : Prior to addressing this entry, it is advisable to enter the net of tax income target three cells below this one. Once that entry is made you can adequately address this cells entry.

Detail: The user should have by now entered the tax treatment for all income sources in the distribution phase. The worksheet then considers the percentage of the total income coming from each source, the tax treatment of each source for each year and calculates an average percentage of money lost to taxes (looking from the beginning of retirement through the younger persons age 100 and dropping the lowest 10). This value is reported to you in cell AJ22. Using this information (in cell AJ22) as a guide, the user then manually enters the estimated percentage of income lost to taxes in cell AJ21. If the value you enter here differs from the calculated value in the cell AJ22 by 5% in either direction (up or down), the background of cell AJ21 will turn yellow. This is to alert you to when a manual adjustment is needed in AJ21. This manual entry method was used in an effort to avoid a circular reference in the formula structure. This “money lost to taxes” entry allows the model to calculate the gross income needed from all sources to result in the net after tax income needed to meet the net income goals specified.

Net Income Need – Today’s Dollars : Indicate the overall target net income (in today’s dollars) desired from all sources in cell AJ23.

Inflation Factor : Enter the inflation factor that should be applied to the target net income through time.

User Selected Year to Begin Investment Withdrawals : This worksheet will begin calculating investment withdrawals in an effort to meet the target income goal in one of two years. Either the year indicated in this user-selected cell or in the “default year” that is predetermined by the spreadsheet. If you wish to use the default year select the check box. The calculated default year is indicated for you the cell to the right of the check box. The default year is determined by looking at the earliest year that the savings phase is concluded for either person 1 or person 2 (in cells N8 or O18), and then it adds one year to it.

Portfolio Lasts until: This section indicates the ages of person 1 and person 2 and the year through which the portfolio lasts.