Assumptions:1.You have $5,000 of gross income (before taxes) available to invest in year one and we will label this "Gross to Contribute". This amount will grow each year at a fixedrate that we will label "Contrib Growth Rate"

2.All gains each year in the regular investment account will be realized, and thus taxable.By realizing the gains each year this will mean that there is no outstanding taxable capital gain at the end of the investment period. (this assumption keeps the tax situation manageable without adding too much complexity to our calculations)

3.Each year's gain will be made up of some part Short Term (ST), and the rest Long Term (LT).Since LT and ST gains are taxed at different rates the spreadsheet is designed to handle this.Make sure you handle these taxes properly in your calculations.

4.You will be investing for 30 years and make your annual contribution at the end of each year.

5.Inside each of the three types of accounts you will be invested in identical items, therefore the rate of return for each account will be the same.

6.You are starting today with a $0 balance

Goal:Properly calculate the final AFTER TAX balance for each of the three accounts using the assumptions supplied

Instructions (read through completely before starting):

1.You are to populate all the green cells (cell ranges F3:S32 and C21:C23) on the worksheet labeled "Comparison" with the proper equations and/or cell references to answer the question "In which account will you have to most after tax dollars available in 30 years"

2.For the Investment Account:A. Your contributions (Additions) will go inAFTER TAX since there is no tax deductibility of investment accountcontributions.B. Your Gain each year will be the Rate ofReturn applied to the Beginning BalanceC. The Ending Balance each year needs to reflectthe Gain, but NET of the TAXES.Reference the proper ST and LT rates and weights from the assumptions.D. Bottom line - The ending balance each yearwill be the Beg Balance PLUS the after tax gain PLUS the additions for thatyear.E. Beginning Balance each year is simply theEnding Balance for year before.F. Since we are assuming all gains are realizedeach year (and taxed) the ending balance after 30 years will have no additionaltaxes due at that time.G. Note:in a more realistic example we would have un-realized gains at the endthat would still be subject to taxes.Weare not modeling that situation here.

3.For the Traditional IRA Account:A. Your contributions (Additions) will go in PRETAX as we are assuming the contributions are deductibleB.Your Gain each year will be the Rate ofReturn applied to the Beginning BalanceC.Since gains are not subject to taxes while inside the IRA, the End Balance will simply be the Beg Balance + Gain + AdditionsD.Remember that since the IRA contributions and gains have never been taxed, upon distribution at the end the entire balance will be subject to Income (ST) taxes at the ST tax rate.

4. For the Roth IRA Account:A.Your contributions (Additions) will go in AFTER TAX since there is no tax deductibility of Roth IRA contributions.B.Your Gain each year will be the Rate of Return applied to the Beginning BalanceC. Since gains are not subject to taxes whileinside the Roth IRA, the End Balance will simply be the Beg Balance + Gain +AdditionsD.Since taxes were paid when the contributions were made, all qualified distributions for the Roth IRA will be Tax FREE at the end

5. In the "Net After Tax Value at End" section fill the 3 cells (C21:C23) with the proper references/calculations to show the true after tax value of thethree accounts using the "Retiremetn Tax Assumptions" along the leftside of the worksheet.Once this iscomplete you will be able to alter the input assumptions and see the affect ithas on the comparative values of the three accounts.

6. Please leavethe assumptions in cells C3:C18 as I have them pre-populated.Iencourage you to play with those input assumptions to see their affect, butreset them to the defaults

Tips to assist you:

1. The ST tax rate is the same as the Income tax rate so when figuring out your net contribution on an AFTER TAX basis (for the Roth and Investment Acct) you will use the ST tax rate to arrive at your contribution amount.

2. Your contributions each year will grow by the Contribution Growth Rate which is currently set at 3%, but you should reference that cell so that you could change that assumption if you wanted and your Excel sheet would still function.

3. You are beginningyear 1 with a ZERO balance in each account then making the contribution at the end of each year.

4. Realized gains(profits) in investment accounts are taxed on either a short term (ST) or long term (LT) basis depending on how long you have held (owned) the investment. In a typical investment account you will have some gains that occur on assets that you have held less than a year (ST) and are thus subject to the ST tax rate, and you will have gains on other assets that you have held for over a year and are then subject to the LT tax rate. This is why the problem gives you the ST and LT rates as well as how much of the gain each year is considered ST and LT.

In this assignment we are going to compare the end result of saving in either a regular investment account, a traditional IRA or a Roth IRA. We need to make a few assumptions in order to make this a fair comparison and to keep things fairly straight forward Assumptions: 1. You have $5,000 of gross income (before taxes) available to invest in year one and we will label this "Gross to Contribute". This amount will grow each year at a fixed rate that we will label "Contrib Growth Rate" 2. All gains each year in the regular investment account will be realized, and thus taxable. By realizing the gains each year this will mean that there is no outstanding taxable capital gain at the end of the investment period. (this assumption keeps the tax situation manageable without adding too much complexity to our calculations) 3. Each year's gain will be made up of some part Short Term (ST), and the rest Long Term (LT). Since LT and ST gains are taxed at different rates the spreadsheet is designed to handle this. Make sure you handle these taxes properly in your calculations. 4. You will be investing for 30 years and make your annual contribution at the end of each year. 5. Inside each of the three types of accounts you will be invested in identical items, therefore the rate of return for each account will be the same. 6. You are starting today with a $0 balance Goal: Properly calculate the final AFTER TAX balance for each of the three accounts using the assumptions supplied Instructions (read through completely before starting): 1. You are to populate all the green cells (cell ranges F3:S32 and C21:C23) on the worksheet labeled "Comparison" with the proper equations and/or cell references to answer the question "In which account will you have to most after tax dollars available in 30 years" 2. For the Investment Account: A. Your contributions (Additions) will go in AFTER TAX since there is no tax deductibility of investment account contributions. B. Your Gain each year will be the Rate of Return applied to the Beginning Balance C. The Ending Balance each year needs to reflect the Gain, but NET of the TAXES. Reference the proper ST and LT rates and weights from the assumptions. D. Bottom line - The ending balance each year will be the Beg Balance PLUS the after tax gain PLUS the additions for that year. E. Beginning Balance each year is simply the Ending Balance for year before. F. Since we are assuming all gains are realized each year (and taxed) the ending balance after 30 years will have no additional taxes due at that time. G. Note: in a more realistic example we would have un-realized gains at the end that would still be subject to taxes. We are not modeling that situation here. 3. For the Traditional IRA Account: A. Your contributions (Additions) will go in PRE TAX as we are assuming the contributions are deductible B. Your Gain each year will be the Rate of Return applied to the Beginning Balance C. Since gains are not subject to taxes while inside the IRA, the End Balance will simply be the Beg Balance + Gain + Additions D. Remember that since the IRA contributions and gains have never been taxed, upon distribution at the end the entire balance will be subject to Income (ST) taxes at the ST tax rate. 4. For the Roth IRA Account: A. Your contributions (Additions) will go in AFTER TAX since there is no tax deductibility of Roth IRA contributions. B. Your Gain each year will be the Rate of Return applied to the Beginning Balance C. Since gains are not subject to taxes while inside the Roth IRA, the End Balance will simply be the Beg Balance + Gain + Additions D. Since taxes were paid when the contributions were made, all qualified distributions for the Roth IRA will be Tax FREE at the end 5. In the "Net After Tax Value at End" section fill the 3 cells (C21:C23) with the proper references/calculations to show the true after tax value of the three accounts using the "Retiremetn Tax Assumptions" along the left side of the worksheet. Once this is complete you will be able to alter the input assumptions and see the affect it has on the comparative values of the three accounts. 6. When submitting the assignment, please leave the assumptions in cells C3:C18 as I have them pre-populated. I encourage you to play with those input assumptions to see their affect, but reset them to the defaults Tips to assist you: 1. The ST tax rate is the same as the Income tax rate so when figuring out your net contribution on an AFTER TAX basis (for the Roth and Investment Acct) you will use the ST tax rate to arrive at your contribution amount. 2. Your contributions each year will grow by the Contribution Growth Rate which is currently set at 3%, but you should reference that cell so that you could change that assumption if you wanted and your Excel sheet would still function. 3. You are beginning year 1 with a ZERO balance in each account then making the contribution at the end of each year. 4. Realized gains(profits) in investment accounts are taxed on either a short term (ST) or long term (LT) basis depending on how long you have held (owned) the investment. In a typical investment account you will have some gains that occur on assets that you have held less than a year (ST) and are thus subject to the ST tax rate, and you will have gains on other assets that you have held for over a year and are then subject to the LT tax rate. This is why the problem gives you the ST and LT rates as well as how much of the gain each year is considered ST and LT. General Assumptions Rate of Return Gross to Contribute $ Contrib Growth Rate 8% 5,000 3% Current Tax Assumptions ST Tax Rate LT Cap Gains Rate % of Gains LT % of Gains ST 25% 15% 50% 50% Retirement Tax Assumptions ST Tax Rate 25% LT Cap Gains Rate 15% % of Gains LT 50% % of Gains ST 50% Net After Tax Value at End Investment Acct Taditional IRA Roth IRA Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Beg Bal Investment Account Gains Additions End Bal Beg Bal Traditional IRA Gains Additions End Bal Beg Bal Roth IRA Gains Additions End Bal