This lab concerns situations modeled by a compounding process. A typical example is the way interest accumulates in a savings account. The first problem builds a "compound interest" calculator. You also build a balance sheet to see the effect of each interest payment.
To solve this problem we compute the balance in the account after 1 year and find the interest by subtracting the balance from the original deposit. Steps (a)-(c) below place a "Compound Interest Calculator" in cells A1..B12. Place labels in column A, input data in cells B4..B7, and formulas to calculate results in cells B9..B12. The Total number of periods is denoted "Tot#period". In columns D and E the balance at the end of each quarter is computed. Verify the total obtained in cell E9 is the same as the Balance in cell B11.
- Problem 1. A sample situation:
- Fred Jones places $3000 in a saving account that pays an 8% annual interest rate, compounded quarterly. What is his account balance after two (2) years and how much interest does he make in each of the two years?
a. Enter the following text, numbers and formulas into a clean worksheet. Steps (b) and (c) complete the template.
A B C D E F 1 Problem #1 2 Compound interest problem 3 4 Deposit 3000 Totals per Period 5 Rate/yr 0.08 start =B4 6 Period/yr 4 1 =E5+E5*B$10 7 No.Yrs 2 2 8 3 9 Tot#Period =B6*B7 4 10 rate/period =B5/B6 5 11 final balance 6 12 interest 7 13 8
b. The formula in cell E5 copies the initial deposit from cell B4. The formula in cell E6 adds the previous balance (the cell above) to the interest for the current period. Now Copy cell E6 to cell range E7..E13 to compute the balance at the end of the remaining quarters.
c. Now we want to use the formula that computes the Final balance (after 8 quarters in this example) straightaway. In cell B11 enter the correct formula from the handout or from the text.
CHECK WITH YOUR INSTRUCTOR TO MAKE SURE THIS FORMULA IS CORRECT.The interest earned (cell B12) is now +B11-B4.
Discussion: Compare the Final balance (in cell E13) with the value computed in cell B11. In a textbox explain the difference in the two methods for obtaining this answer (E13 and B11 should be the same). In a textbox answer the questions posed in Problem 1. Save the worksheet and turn in a printout.
Now we want to take advantage of the work we did in Problem 1.
Problem 2. Copy the template (cells A1..E12) in Problem 1 to a new worksheet. In this problem you are modify the template to illustrate the situation where the initial $3000 is placed in the account for four(4) years instead of just two. Keep the interest rate at 8% compounded quarterly. You need to make two changes in the template: first, change the number of years to 4; and second, in column D, you need to list the periods from 1 to 16 and copy the formula in column E to apply to all 16 periods. Construct this new worksheet.
Discussion: In a textbox state the account balance at the end of each year and give the amount of interest earned by the account during each year. Save the worksheet and turn in a print out Problem 2.
In the problems below you only need the Compound Interest template (cells A1..B12). Each calculation should use a separate copy. Give the answer to each question in the form of a sentence.
Problem 3.(a) Suppose you put $1000 in Bank #1, which is offering a savings plan with 6% annual rate, compounded every month. How much interest is earned in one year? State your answer in a sentence supported by data in a template.
(b) A friend has found that Bank #2 is also giving 6% interest, but it compounds every second. How much interest does he earn in one year?
(c) What is the difference in the amount of interest earned at these two banks?
(d) Suppose each bank has total deposits of ten billion dollars. What is the difference in the amount of interest paid by the two banks? [Hint: ten billion is ten million times 1000.] State your answer in sentence form.
Problem 4. At the end of 1st Quarter of 2009 the median price of a single-family home in Charleston/No. Charleston was $184,990. Single-family home prices in Charleston/No. Charleston decreased from the 1st Qtr of 2008 by 8.15%.
(a). Estimate the median price of a single-family home in the 1st Qtr of 2008.
(b). If the median price of a single-family home falls at the same rate for the next 2 years, estimate the median price of a single-family home in the 1st Qtr of 2011.
Explain your strategy for calculating the answer.
Problem 5. The 1975-76 College Catalog lists the total expenses for freshmen in 1975-76 as $2,580 for South Carolina students and $3,230 for Out-of-State students. For 2009-2010 the total expenses for freshmen is $21,729 for South Carolina students and $35,539 for Out-of-State students. Assume that college costs compound at a fixed percentage each year.
(a) Find the Compound Annual Growth Rate for total expenses for SC students since 1975-76 and find the Compound Annual Growth Rate for total expenses for Out-of-State students over the same period. State your answers in sentence form. Use the Excel Solver to find the rate.
(b) Using the Compound Annual Growth Rate found in part (a) estimate the In-state and Out-of-state expenses for students for the year 2012-2013.