Time Value of Money

This is an individual graded assignment.To assure Academic Integrity and a fair game, you may NOT discuss this graded individual assignment with anyone(except Prof. Franke) prior to class.

Directions: Solve the following problem using the data given. You probably will find that usinga spreadsheet program i.e.,Excel(under Formulas click on Financial) or perhaps a financial calculatorwill be extremely useful. Consider designing the spreadsheet to analyze each respective years cash requirements versus cash inflows, and then analyze the shortfall in each year on a present value basis.To ease computations, assume the cash flow shortfall occurs at the end of each calendar year of Bills retirement.

Your client, Bill Smith engages you to assist him in planning for his retirement. Specifically, he wants the following question answered:

In addition to the amounts described below, how much money will I need to invest at the end ofeach of the next three years (i.e., December 2013, 2014 and 2015), invested at 5% (compounded annually), so I may retire at my desired standard of living at the end of three years from now (December 31, 2015)?

Assume today is January 1, 2013 and ignore the impact of income taxes throughout.

In order to answer Bills question, you gather the following information from him:

- Bill desires an income in todays dollars of $75,000 during each year of his retirement, which is assumed to last 16 years.

- One of Bill greatest concerns is inflation. He recently read inflation is expected to average 3.0% annually over the next twenty years.

-Bill will be eligible for Social Security. Assuming Social Security benefits increase 2% per year as they have in the past, Bill would receive $19,500 in the first year of retirement, increasing thereafter at the historical rate.

- Bill has been told by his employer that at December 31, 2015 he will be eligible for a fixed pension of $24,000 per year.

- Bill currently has $15,000 in a savings account earning 1% compounded annually. He states that these funds will be made available to support his retirement savings pool.

In addition to the answer to his question, Bill asks that you provide sufficient detail so he may review your analysis.