Finance Lab

Regular Savings (Part 2)


All dollar amounts should be formatted using the comma format.

Download and open the workbook SavingsLab02.xlsx. Remind me how.

Problem #1

What single investment at 4.8% compounded monthly will have the same future value as a series of investments of $45 a month for six years into the same account? What is the future value? How much interest would be earned if the single investment were made? How much interest would be earned if $45 were invested each month? (Since these two investments are equivalent, the future values will be the same. However, you should find that the amount of interest earned is quite different.)  What is the annual percentage yield on each of these investments?

Insert a line chart that shows how the balances of both of these investments grow over time (see the image below). To include two columns of data on the same chart, highlight both columns before inserting the line chart (highlight the first column and then, while holding the Ctrl key down, highlight the second column).

Immediately after inserting your line chart, click on the Select Data tool on the Design tab of Chart Tools. Click on "Series 1" in the left window and then click the edit button. You can enter the name of the series in the upper text box or, if you prefer, you can click on the heading on your worksheet that identifies the first series of data. For example, assuming you highlighted the annuity balance column first, you can enter "Annuity" in the text box or you can click on the yellow heading that says "Annuity". Repeat this procedure for the single investment legend name.

Later on, you can use the Legend tool on the Layout tab of the Chart Tools to show the legend at the top of the graph as illustrated here:


Problem #2

A business anticipates that it will need $5,000 in four years to upgrade its office computer system. It is considering two options:

1. Make monthly deposits into a savings account earning 3.6% compounded monthly. What would be the size of the monthly deposit? How much interest would the company earn? What is the APY?

2. Buy a certificate of deposit paying 5% compounded semiannually. How much would this cost? How much interest would be earned? What is the APY?

Generate a line chart for each option illustrating how the balance grows over time.

Problem #3

A young couple sets up a retirement account with the intention of depositing regular payments into the account every month for 40 years. The account earns 3.4% compounded monthly. Initially, they plan on depositing $100 per month. The figure that after 20 years, their financial situation will have improved to the point that they can afford to deposit $200 a month into the account. Set up a mathematical model that will allow them to determine the amount in the account at the end of 40 years and the total interest earned. Help!

Generate a line chart illustrating how the account balance grows over time. Help!

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