Document Sample

BA 303 Spreadsheet #2 Retirement Planning Read the following directions and complete the Excel Spreadsheet. “Boy, this is all so confusing,” said John as he stared at the papers on his desk. If only I had taken my grandfather’s advice, I would not be in such a predicament today. John Anderson, aged 27, graduated five years ago with a degree from the Food Industry Leadership Center at PSU and is currently employed as a middle-level manager of the “Big M”, a successful grocery chain. His current annual salary of $55,000 has increased at an average rate of 5% per year and is projected to increase at least at that rate for the foreseeable future. The firm has had a voluntary retirement savings program in place, whereby, employees can contribute up to 10% of their gross annual salary (up to a maximum of $5,000 per year) and the company will match every dollar that the employee contributes. Unfortunately, excited to be out on his first “real job” that pays a “real salary”, John has not taken advantage of the retirement savings program. He opted instead to spend his money and has not accumulated any savings. However, with wedding plans on the horizon, John is motivated to start saving for the future. He knows that, once married, he will have various large expenses that he would like to plan for in advance. He has decided to build a spreadsheet with approximate costs and timelines involved. John estimates that the two largest expenses in the near term will be the wedding and down payment on a house. He estimates that his portion of the wedding, which will take place in 12 months, will cost about $10,000 in today’s dollars. In five years (four years after getting married) he hopes to purchase a $200,000 house (in today’s dollars). To avoid renter’s insurance, he plans to pay 20% down and get a mortgage for the rest. John knows that these estimates are in today’s dollars and will have to be adjusted for inflation. Moreover, he knows that the best way to make the savings happen is through an automatic payroll deduction – if he doesn’t see the money in his account, he won’t think about spending it. Now, John just needs to figure out how much money to put away each month, given inflation, timelines for his investments, and the salary increases that he anticipates. If only he had listened to his grandfather and started saving when he got his job five years ago. Questions 1. What was John’s starting salary, in the first year of his job? Work backward from John’s salary of $55,000 in 2005 to find his salary in 2000. In addition, calculate John’s Age, and the number of years until he retires to use as necessary in other calculations. 2. Had John taken advantage of the company’s voluntary retirement plan to the maximum, every year for the past five years, how much money would he have contributed to the plan each year? How much would his company contributed and what would the total contribution be? How much would John have accumulated in his retirement account today, assuming annual deposits earn a nominal rate of return of 5% with interest compounded annually? How much at 10% interest? At 15% interest? 3. If John starts his retirement saving plan in January of 2006 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 5%, 10%, or 15% per year in the appropriate columns (and inflation is either 3%, 4%, or 6%), compounded annually (use these figures to calculate the Real Rate of Return on investment before beginning your calculations). 4. John figures he will need approximately $30,000 per year (in current dollars) during his retirement. If inflation is expected to average 3% per year, how many 2043 dollars will John need to match the value of $30,000 today. Assume that John’s savings yield a nominal return of 5% per year, how long will his retirement savings last? Find this answer by calculating the present value of an annuity where the annual contribution is equal to John’s annual spending in 2043 dollars. Set the number of years in your Present Value of Annuity calculation equal to the corresponding cell in the Trial and Error line. They guess different answers in the Trial and error line until the Present Value of Annual Annuity equals John’s Total Savings at Retirement. Repeat these calculations with inflation at 4% and savings yield of 10% and again with inflation of 5% and savings yield of 15%. 5. Assume that John does not have the benefit of a company match. How much must her save per month to make a $10,000 contribution to his retirement at the end of the year. Transform a $10,000 annual retirement contribution into a monthly retirement contribution. How much will John have to save each month to make a $10,000 contribution to his retirement account at the end of the year at the three rates of return/inflation levels listed previously? 6. How much would John 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 of 5% per year? First calculate the cost of the wedding (at least John’s $10,000 share of it) in 2006 dollars. Then calculate how much he must save each month. Repeat these calculations at 10% return and 4% inflation per year and at 15% return and 6% inflation per year 7. If John starts saving immediately for the down payment on his house, how much additional money will he have to save each month? First calculate how much the house will cost in 2010 dollars. Then calculate the 20% down payment and John’s share of the down payment (50%). Finally, calculate his monthly savings in order to make his portion of the down payment. Assume the same investment rates of return and inflation rates presented above (5% & 3%; 10% & 4%; 15% & 6%) 8. Finally, add up the savings required for John’s wedding savings, his retirement savings, and his down payment savings. What is John’s total schedule monthly savings. 9. In your Narrative, comment on the following. a. What information did you start with? b. How did you use this information to analyze John’s financial situation (what formulas were key to your analysis)? c. How do the Interest Rate and Inflation Rate combine to create a Real Rate of Return? d. How did the different Interest and Inflation rates impact John’s savings needs? Which savings plans are significantly impacted by differences in rates? Which savings plans are only modestly impacted by differences in rates? e. What have you learned about the time value of money? When does it have a significant impact? f. How does the time value of money make your own financial planning difficult? How can you use the time value of money to your benefit in your own financial planning?

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 18 |

posted: | 7/25/2011 |

language: | English |

pages: | 3 |

OTHER DOCS BY xiuliliaofz

Feel free to Contact Us with any questions you might have.