Please use Excel to answer the following questions and show all your work including formulas/calculations. When solving problems in a spreadsheet, make sure to format the output very carefully in order to ensure that it is legible and presentable. (Remember that the work you turn in reflects you. Even if your work is correct, if it looks unprofessional it is unlikely to get the full attention of its intended audience.) Be sure to turn in a printout of the answers (carefully labeled) and the formulas (the spreadsheet with your work or print the formulas by pressing Ctrl + 3. Financial Planning: Ryan Daniels, age 32, graduated 5 years ago with an MBA and is currently employed as a manager in a fairly successful local food manufacturer. His current annual salary of $85,000 has increased at an average rate of 5% per year and is projected to increase at that rate for the foreseeable future. His firm has had a voluntary retirement savings program in place, whereby employees are allowed to contribute up to 7% of their gross annual salary (up to the legal maximum of $12,500 per year) and the company matches every dollar the employee contributes. Unfortunately, like many young people who start on their first job, Ryan has not yet taken advantage of the retirement savings program. He chose instead to buy a fancy sports car, rent an expensive apartment and consume most of his income. Ryan is currently planning a wedding, and with wedding and family plans on the horizon, he has realized that he needs to start planning. He figures that he has two major expenses coming down the road: His wedding and the down payment on a house. He estimates that the wedding, which is in 12 months, will cost about $15,000 in today's dollars. Furthermore, he plans to move into a $250,000 house (in today's terms) after 5 years and he will need a 20% down payment. He realizes that his cost estimates are in current terms and need to be adjusted for inflation. Inflation is estimated to be 3% per year for the following 5 years. What was Ryan's starting salary? How much could he have contributed to the voluntary savings plan in his first year of employment? Had Ryan taken advantage of the company's voluntary retirement plan up to the maximum each year, how much money would he have accumulated in his account assuming a nominal rate of return of 7%? If Ryan starts his retirement savings plan from January of next year by contributing the maximum allowable amount into the firm's voluntary retirement savings program, how much money will he have accumulated for retirement, assuming he retires at age 65? Assume that the rate of return on the account is 7% compounded monthly and that the maximum allowed amount does not change. How much would Ryan have to save each month starting from the end of the next month in order to accumulate enough money for his wedding expenses assuming that his investment fund is expected to yield a rate of return on 7% per year? If Ryan starts saving immediately for the 20% down payment on his house, how much additional money will he have to save each month? Assume an investment rate of return of 7% per year
