As a general rule, dollar amounts should be rounded (using Excel's ROUND function) to the nearest penny if the calculation involves multiplication, division, or exponentiation and the result will be used in some other calculation. All dollar amounts should be displayed in dollars and cents.
A bank offers a 30-month (30 months is 2.5 years) certificate of deposit paying 1.2% compounded monthly. Set up a mathematical model of this CD assuming that the present value is one of the inputs and the future value is one of the outputs. Use your model to solve the following problems:
1. Suppose a customer invests $5,000.
a. Find the future value, the total interest earned, and the APY. The future value should be $5,152.20.
b. What if the nominal interest rate was lowered to 0.95%? Find the future value, the total interest, and the APY.
c. What if the nominal rate was 1.2% compounded semiannually? Find the future value, the total interest and the APY.
d. What if the nominal rate was 1.2% compounded monthly but the time was increased to 42 months?
2. Suppose the customer's goal is to get back $5,500 in 3 years at an interest rate of 0.75% compounded quarterly.
a. How much does the customer need to invest at the beginning? (Hint: use the Goal Seek tool). You should get $5,377.75.
b. What if the nominal rate was only 0.65%? How much should the customer invest?
3. Go back to the original input values: time is 30 months, the interest rate is 1.2% compounded monthly, and the customer invests $5,000.
a. Create a table showing the month-by-month details for this investment.
b. Use your table to create a line chart illustrating how the balance grows over time.
A U.S. savings bond matures in 7 years and earns 3% compounded semiannually. Go to a new worksheet and set up a mathematical model of this savings bond assuming that the face value is one of the inputs and the purchase price is one of the outputs. Use your model to solve the following problems:
1. Suppose the face value of the savings bond is $500.
a. Find the purchase price, the total interest earned, and the APY. The purchase price should be $405.92.
b. What if the bond earned 3.2% compounded semiannually? Find the purchase price, the total interest, and the APY.
c. What if the bond earned 3.2% compounded monthly and the bond matures in 6 years? Find the purchase price, the total interest, and the APY.
2. Suppose that the bond matures in 7 years, earns 3% compounded semiannually, the face value of the bond must be a multiple of $100, and the customer can afford to invest no more than $1,000.
a. What is the largest savings bond (i.e., highest face value) that the customer can afford to buy? Find the purchase price, the total interest, and the APY for this savings bond.
b. What if the bond earned 3.5% compounded monthly. What is the largest savings bond (i.e., highest face value) that the customer can afford to buy? Find the purchase price, the total interest, and the APY for this savings bond.
3. A customer buys a $1000 savings bond that matures in 6.5 years and pays 2.6% compounded monthly.
a. Create a table showing the month-by-month details for this investment.
b. Use your table to create a line chart illustrating how the balance grows over time.
Some banks offer what they call a "bump-up" or "raise your rate" certificate of deposit (CD). If, subsequent to the time of the purchase, the interest rate offered by the bank for CDs having the same term goes up, the customer can come to the bank and bump up the interest rate on the certificate of deposit that was bought earlier at the lower rate. The increase does not take affect until the end of the current interest period and the customer may take advantage of this bump-up provision only once.
Here is an example. A customer buys a 24-month certificate of deposit paying 0.80% compounded monthly. After 9 months, the bank offers the same CD at 0.95% compounded monthly. The customer doesn't notice the increase until after 10.5 months and then requests the higher interest rate. The bank will apply the old rate for the current (11th) month and apply the higher rate for all of the months thereafter. If the bank offers the same CD at an even higher rate 4 months later, the customer could not request the new rate because the bump-up provision can only be used once.
Go to a new worksheet and implement a model of a bump-up CD. You should already know everything you need to know to create this model. It is just a matter of creatively applying what you know. It is very important that you at least try to figure out how to create the model for yourself or as a group with other students. If you get stuck, here is some Help.
Use your model to solve the following problems.
1. A customer invests $10,000 in a 60-month bump-up CD paying 1.3% compounded quarterly. A year and a half later, the customer notices that the bank is currently paying 1.5% compounded quarterly on 60-month CDs and requests the bank to bump-up the interest rate on the CD that was purchased earlier. Find the future value and the total interest earned. The future value should be $10,745.16.
2. What if the change in the interest rate occurred 3.5 years after the original investment? Find the future value and the total interest.
3. What if the interest offered by the bank dropped to 1.2% after 3 years? Find the the future value and the total interest earned.
4. What if the interest rate changes to 2.7% after 2 years? Find the future value and the total interest.
5. Create a table providing the month-by-month details for this investment and create a line chart illustrating how the balance changes over time. Help.
The model you have created can be used to examine a wide range of different scenarios. The table, however, is correct for only those scenarios in which the number of periods are the same (for both the original rate and the bumped up rate). This is true for all of our tables.
You can look at CompIntLab02Answers.xlsx to check all your answers.