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.
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
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% &
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
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
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
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?