EXCEL WORKSHOP

PART II

Based on your discussion with Brady, the Excel model should cover a ten-year period.Brady would like to be able to change the following items in the model:

1.Year analysis begins

2.Amount of the IRA contribution (Note:only one contribution in this analysis -notone per year)

3.Tax rate at date of contribution (Note:assume the contribution is made on January 1 of the initial year)

4.Annual growth rate (Note:constant growth throughout the analysis period)

5.Tax rate at date of withdrawal (Note:assume the entire account balance is withdrawn at the end of the tenth year)

The model should be based on a contribution only in the first year.

The contribution should grow at the annual growth rate over a ten-year period.At the end of the ten years the entire account balance is distributed to the account holder.The model should provide the net of tax amount the account holder will receive on distribution after the ten-year period and show the account balance each year.

Prepare one table showing the Roth IRA and one showing the Traditional IRA.Both tables and the input variable section should easily fit on a single page if set to print in landscape mode.Remember, a professional presentation includes proper titles and headings, is centered on the page when printed, and is a reasonable size on the page.Test the appearance of the worksheet by using the print preview function in Excel.

model should be a graph showing the account balance at each yearend for the Roth and Traditional IRAs.The final year data point should be the net of tax amount of the distribution.

prepared the worksheet correctly, the net of tax distribution amount will be the same for the traditional and Roth IRA if the tax rate on contribution and the tax rate on distribution are the same.

Assignment:

1.Your observation of what the model tells you about a Roth versus a Traditional IRA.Can you make a generalization about when it is best to use one over the other?

2.The working Excel model.