Excel Workshop II Assignment - Roth vs. Traditional IRA

EXCEL WORKSHOP

PART II

You are a staff accountant at Anderson & Paul, a CPA firm located in downtown San Diego. A long-time client, Brady Patterson, has contacted you with a request. Brady is 52 years old and is currently in the 32% tax bracket. He is beginning to think about saving for retirement and has some questions. His employer does not offer a retirement plan so he is considering an IRA. However, he is confused about the differences between a Roth IRA and a Traditional IRA and needs your advice. He wants you to prepare an Excel spreadsheet that he can use to see the difference in a Traditional versus a Roth IRA.

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 not one 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: assume 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)

Prepare the model so that the above input variables automatically update the worksheet. This will allow Brady to test various assumptions and see the results of contributing to a Roth or a Traditional IRA.

The model should be based on a contribution only in the first year. Dont build the model to have contributions every 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. Professional appearance is important so dont overlook this part of the assignment.

The second page of your Excel model should be a graph showing the account balance at each year-end for the Roth and Traditional IRAs. The final year data point should be the net of tax amount of the distribution.

As you know, a contribution to a traditional IRA typically provides a deduction for the taxpayer while a contribution to a Roth IRA is after-tax. Therefore, the contribution amount in full will be the starting point for the Traditional IRA table. However, since a Roth IRA contribution is taxed, the net of tax amount of the contribution will be the starting point for the Roth IRA table.

Check figure: If you have 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:

This assignment counts as a quiz. There are three parts to this assignment.

1. The working Excel model. I will test the model. The properly working model and graph will count as 80% of your quiz grade.
2. 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? Use your model to make this determination. This will count as 10% of your quiz grade.
3. Your graph showing the account balance at each year-end for the Roth and Traditional IRAs. This will count as 10% of your quiz grade

Upload a single Excel file with your observation about choosing between a Roth versus a Traditional IRA, the model worksheet, and the charts. Save all three as a single file and then upload via Canvas, prior to the deadline.