Problem 1: When to Invest (30 points)

You have just started a new job with one of the world's greatest employers: HDF 322, Inc. Among the incredible benefits that the company offers is a best-in-class 401(k) plan. Several of your new co-workers have learned about your finance knowledge and Excel skills, and have asked you to help them understand the plan and how to use it.

To get started, you have decided to put together a quick analysis to help you and your co-workers decide when it would be best to enroll in the plan: early in your careers or later in your careers. Under the "early plan," assume that you will contribute $7,000 to your 401(k) account at the beginning of your first year of employment. Because you expect to earn a 3% increase in salary each year, you will increase your annual contribution by 3% each year (so your 2nd year's contribution will be $7,210). You will continue in this fashion until you've made a total of 20 deposits. Then, for purposes of the analysis, you assume you will quit your job to do something fabulous and worthwhile that, unfortunately, will pay you nothing. Although you intend to keep your money invested in your 401(k) account, and it keeps earning returns each year, you won't make any more contributions into the account.

Under the "late plan," you will contribute nothing to your 401(k) account during years 1-20. Instead, you will make your first deposit of $7,000 at the beginning of year 21. Thereafter, you will increase each year's contribution by 3%. You will continue in this fashion until you've made a total of 25 deposits (in years 21 through and including year 45).

For both the early and late plans, assume your 401(k) account will earn 7% per year, and that you will receive no matching money from your employer. Also, recognize that the maximum allowable contribution keeps rising every year. Given the circumstances herein, you will never exceed it.

Create two side-by-side schedules in Excel (in the same worksheet/tab). Name one "Early Plan." Name the other "Late Plan." For each schedule, use the five column headings noted in bold type below.

1. Year: Number each row from 1 to 45
3. Beginning Balance
4. Early Plan: For year 1, this will equal zero. For years 2 to 45, it will equal the prior year's Ending Balance.
5. Late Plan: For years 1 to 21, this will remain at zero. For years 22 to 45, it will equal the prior year's Ending Balance.
6. Contribution: The contribution will equal $7,000 in the first year that a contribution is made. Each subsequent contribution will be 3% larger than the previous year's contribution.
7. Early Plan: The first contribution of $7,000 is made in year 1. The contribution will be zero for years 21 to 45.
8. Late Plan: The contribution will be zero for years 1 to 20. The first contribution of $7,000 is made in year 21.
9. Earnings: For planning purposes, assume that you earn your returns at the end of each year. Thus, the earnings amount will equal (Beginning Balance + Contribution) x 7%.
10. Ending Balance: This will equal (Beginning Balance + Contribution + Earnings).
11. When you are finished putting together your spreadsheet, add a text box at the bottom to explain the following to your co-workers:
12. How much total cash must you deposit under each plan?
13. Which plan will provide the highest ending balance (and what are the two ending balances)?
14. When should you and your co-workers start contributing to the company's 401(k) plan?
15. After you present your analysis, one of your co-workers reminds you that the company has offered to match a portion of your contributions. No problem! You estimate that if you each contribute $7,000 to your 401(k) account, the company will contribute an additional $1,400. To show how this matching contribution will impact your ending balances, you change the very first contribution under each plan from $7, 000 to $8,400. Because you already show your contributions growing at a rate of 3% each year, the new schedule will show both your contributions and your company's matching contributions growing at that same 3% each year.
16. By what amount does each 401(k) account balance rise by the end of the 45th year? That is, how much value did the match add to your 401(k) account under each of the two plans?
17. What advice should you offer to your co-workers regarding the company's matching program?
18. Please note: Change your initial contributions back to $7,000 once you've answered the question. Save and submit the version that shows $7,000 as your initial contribution for each plan. Make sure all of your columns are visible on a single page-width before you print.