header

Finance Lab

Regular Savings (Part 1)

Objectives

Saving on a Monthly Basis

All dollar amounts should be formatted using the comma format.

1. Download and open the workbook SavingsLab01.xlsx. Remind me how.

A married couple decides to save $50 at the end of each month in an account earning 3% compounded monthly. Your goal is to determine how much they will have in their account after four years and how much interest they will have earned.

2. Enter you name in cell B1.

3. Enter appropriate formulas for periodic rate (in cell B6) and number of periods (in B8).

4. In cell B10, enter the formula for the future value of an ordinary annuity (using cell references):

Did you get $2,546.56? If not, find and correct your mistake.

5. In cell B11 enter the formula for the total interest earned:

I = A - Mn

You should get a value of $146.56.

6. In cell B13, enter an Excel formula that will calculate the annual percentage yield on this investment. Format the answer as a percent to three decimal places (you should get 3.042%).

7. Select cells B10:B11 and B13. Make the background light gray, change the font to bold, and outline the cells.

What-If Analysis

1. What do you think would happen to the final account balance and the total interest earned if the nominal rate were doubled to 6.00%? Write your estimate of the new values down somewhere like this:

A=
I=

2. Change the value in cell B4 to 6.00%. Were your estimates fairly accurate?

3. There are some advantages to starting a savings account early in life and committing yourself to making monthly payments into the account. Suppose a married couple saves $50 a month at 3% compounded monthly for 40 years instead of just four years. How much would they have in their account at the end and how much interest would they earn? Write down what you think these values will be:

A=
I=

4. Use your Excel model to calculate the actual values. How did your estimates compare to the actual values?

5. Suppose the interest rate doubles to 6.00%. Write down your estimates of the new future value and the interest earned:

A=
I=

6. Use your model to calculate the actual values. How close were your estimates?

7. Now suppose this couple decides that they will start saving for retirement 20 years from now but will save twice as much per month. That is, they will save $100 a month for 20 years at 6% compounded monthly. Write down your estimates of the new future value and the interest earned:

A=
I=

8. Use your model to calculate the actual values. How close were your estimates? Were you surprised?

9. How much would this couple have to save per month for the last 20 years before retirement in order to have the same future value as they would get by saving $50 a month for 40 years? (The interest rate remains at 6% compounded monthly.) You'll need to use the Goal Seek tool.

Savings Account Schedule

1. Reset your model to $50 a month at 3% compounded monthly for 40 years.

2. Following the steps below, prepare a savings account schedule that shows the status of the couples' account after each payment.

a. Highlight the two cells below the heading "End of Period" and drag the fill handle down until the last number in the column is the number of periods. Release the mouse button.

b. Recall that fictitious period zero is included to make our graphs look nicer. The new balance at the end of period zero is zero dollars because there is no money in the account yet. Remember that the first deposit will not be made until the end of the first month. Enter zero in the new balance column of period zero. The remaining cells should be left blank.

c. The initial balance for each period starting with period one is the new balance at the end of the previous period. Enter an appropriate formula for the initial balance for period 1. Don't forget to use a cell reference rather than a number.

d. The interest earned each period is always the initial balance times the periodic rate and should be rounded to the nearest cent. Enter the correct interest formula for period one. Be careful about relative and absolute cell references.

e. Starting with period one, the payment for each period is the value given in cell B3. Enter the expression for the payment for period one. Be sure to use the appropriate type of cell reference (relative or absolute).

f. The new balance is the sum of the initial balance, the periodic interest, and the periodic payment. Enter the corresponding formula for period one.

g. Copy cells B17:E17 down one row and verify that you get the correct results. If you do not, then correct the formulas in row 17 and repeat this step until you do.

h. Copy the formulas in B17:E17 down to the bottom of the table. Was the final balance in the account $46,303.01? If not, correct your work until you get the correct value.

i. Format the financial values in the body of the table using the comma format.

3. Create a line chart showing how the account balance grew during the term of the account. (Remember that charts illustrating how the balance changes over time always use the new balance column.) Use appropriate titles. Format the vertical axis as currency to zero decimal places.

4. Create a line chart showing how the amount of periodic interest changed during the term of the account. Use appropriate titles. Format the vertical axis as currency to zero decimal places.

Practice Problems

There is a worksheet for each practice problem. For each problem fill in the given data and enter appropriate formulas for the values you are asked to find. Format the cells containing your answers by using a light gray background, bold font, and an outline around the cell(s).

For each problem, generate a table showing the growth of the balance in the account and illustrate this growth with a line chart. Save your workbook after completing each problem.

Problem #1

A man wants to save $15,000 in five years in order to buy a new car. His savings account pays 4% interest compounded quarterly. How much should he invest at the end of each quarter in order to reach his goal? How much interest will he earn? What is the annual percentage yield on this investment?

Notes:

  1. For this model, periodic payment is one of the output values and the future value is one of the input values.
  2. This problem is an example of a sinking fund where periodic investments are made to reach a specific goal.
  3. The quarterly payment should be rounded to the nearest penny using Excel's ROUND function because you will be using this value repeatedly in the table that gives the period by period details.

Problem #2

A woman invests $1000 at the end of each year into an account paying 6% compounded annually. How much will she have at the end of 35 years? How much interest will she earn? What is the annual percentage yield on this investment?

Problem #3

Central University has decided to set up an account in order to update their computer equipment every three years. The account pays 9% compounded semiannually. The college estimates it will cost $300,000 to modernize their equipment. How much should they invest at the end of each half year? How much interest will they make? What is the annual percentage yield on this investment?

You can check your work by looking at my completed workbook: SavingsLab01Answers.xlsx.