Section 1: Problem Statement

John Smith graduated 5 years ago, with a Business degree and an emphasis in Finance. John is currently employed as a Sr. Financial Analyst in the Corporate Finance department of a multinational corporation. He has progressed well in his career, with the ultimate goal of becoming the companys CFO. Johns current salary of $78,000 has increased at an average rate of 5% per year, with routine merit raises, and he expects to continue doing so. Johns firm, ABC Corporation, has a defined contribution plan (401k) plan in place. Employees are allowed to contribute up to 10% of their gross annual salary (up to a maximum of $10,000 per year) and the firm will match 50% of the employees contribution. Unfortunately, John has not yet taken Professor Money Mans advice to Save, Start Young, and Pay Yourself First. Instead, John has enjoyed his post-college, nice-salary life by leasing a new car, renting an apartment and going out to Players every weekend. Now that he has wedding plans on the horizon, John has come to the realization (with help from his fiance, Jane Doe) that its time to start saving while hes still young! John expects that the lovebirds two largest future expenses will be the cost of a wedding (short-term), then later the down payment on a house (medium-term). The couple plans to spend $10,000 of their own money on the wedding in twelve months. They also hope to purchase a $400,000 house in 5 years. Janes parents have promised to match their 10% down payment, but only if they manage to save it within 5 years. Talk about motivation to save! Both future spouses agree that John will automate his savings by setting up monthly contributions to his 401k, wedding and house accounts.

Section 2: Calculations Needed

Your assignment should begin with a Data section, documenting all of the numerical assumptions provided in both sections of this assignment. Your calculations will require the use of a spreadsheet and a financial calculator. Please provide your detailed calculations, step-by-step, including calculator functions, in order to receive maximum credit.

1) If John Smith had fully taken advantage of his employers 401k plan and company match, what would be his current 401k balance based on his historical (last five years) salary and contributions? Assume that John made the maximum contribution every year, with a 6% annual return compounded annually. Second part, what would be his current 401k balance, had he taken more risk and achieved an annual return of 10%?