Acct33004 IRA Excel Exercise

There are several ways to save for retirement that also provide current tax savings. Both the 401K account and the traditional individual retirement account, IRA, allow an individual to contribute money to an investment account before any taxes are withheld. In addition to providing for pretax contributions, earnings in these accounts are not taxed until they are withdrawn, typically when you retire. Early withdrawals incur a penalty. Penalty free early withdrawal exceptions are permitted, for instance to purchase a home or in the case of medical emergency. The 401K account is a company sponsored program where employees can contribute a percentage of their earnings, pre-tax. Often to encourage participation the company will match a portion of the employees contribution. If an individual does not work for a company that offers a 401K plan they can establish an IRA account through a bank or brokerage firm and contribute directly to the account. The contributions are then deducted on the individuals tax return. Both types of accounts have limits on the total annual contribution. And for the IRA, the annual contribution is not deductible if the individuals taxable income exceeds a specified value.

You will create an excel model to project the final balance of an IRA account based on parameters input. Verify your model using the test scenarios attached. Submit the spreadsheet with input assumptions you feel best reflect your case, i.e., when do think you will retire, what return do you anticipate on your investments, etc.

Use two tabs - Summary and Detail - for your model. The Summary tab will use the values input to return the computed retirement value of the account. It will also have the yearly contribution limit values. The Detail tab will compute each years base salary and contribution subject to applicable limits. It will combine the account balance from the prior year, investment earning on the prior years balance and the current years contribution to calculate current year (ending) balance. Assume no contribution is made in the year of retirement. Report the account value in the year of retirement, i.e., before any withdrawals are started. Refer to the two test cases for suggested layout for the Summary and Detail tabs

Hints: You may assume everyone will have at least one and no more than 70 Work Years. You may wish to use an If function with the first three contribution columns. I found a VLookup function helpful on the summary tab when deciding which row of the Current Balance column to display as the Retirement Value. Finally I placed a default value of zero in the employer contribution on the summary tab since we are modeling an IRA account. Once you have this working it should be easy to adapt it to a 401K account if your future employer offers this benefit. Below is the first few rows of the detail tab for test scenario 2.

IRA Detail Schedule Career length 29 $ Contributed Current Year Computed Beginning Earning from Current Balance Age Work Year Salalry Limit Adjusted Total-to-date Balance Prior Year $2,450.00 $5,120.50 $8,027.36 $0.00 34 35 1 $49,000.00 $2,450.00 $5,500.00 $2,450.00 2,450.00 2 $50,225.00 $2,511.25 $5,500.00 $2,511.25 $4,961.25 $2,450.00 3 $51,480.63 $2,574.03 $5,00.00 $2,574.03 $7,535.28 $5,120.50 $0.00 159.25 $332.83 IRA Detail Schedule Career length 29 $ Contributed Current Year Computed Beginning Earning from Current Balance Age Work Year Salalry Limit Adjusted Total-to-date Balance Prior Year $2,450.00 $5,120.50 $8,027.36 $0.00 34 35 1 $49,000.00 $2,450.00 $5,500.00 $2,450.00 2,450.00 2 $50,225.00 $2,511.25 $5,500.00 $2,511.25 $4,961.25 $2,450.00 3 $51,480.63 $2,574.03 $5,00.00 $2,574.03 $7,535.28 $5,120.50 $0.00 159.25 $332.83