header

Finance Lab

Compound Interest (Part 1)

Objectives

Excel's ROUND Function

In Excel, formatting a numerical value affects only how the number is displayed. It does not affect the actual value. For example, you can use the Increase Decimal (or Decrease Decimal) tool buttons to control the format of numerical values:

Tools

By using these tools, you can determine the number of decimal places that are displayed. However, the actual value is unchanged.

1. Download and open the workbook CompIntLab01.xlsx. Show me how.

2. Enter your name in cell B1.

3. Enter the value 9.949874 in cell B4 and tap the Enter key.

4. In cell C4, enter the expression: =B4. You should see the same value in both B4 and C4.

5. Select cell B4 and use the Decrease Decimal tool to decrease the number of decimal places to two. Notice that even though the value 9.95 is displayed in the cell, the actual value in that cell is still 9.949874 as shown in the formula bar and in cell C4:

Format Example

6. Play around with the Increase Decimal and Decrease Decimal tools changing the number of decimal places that are displayed in cell B4. Notice that changing the number of decimal places displayed on the screen does not change the actual value shown in the formula bar and in cell C4.

In finance, the final calculated values must actually be rounded off to the nearest cent not merely displayed to the nearest cent. To do this, you will use Excel's ROUND function.

=ROUND(expression, decimal places)

The expression argument is usually a formula that generates the value that is to be rounded off. The second argument, decimal places, specifies the number of decimal places to which the value generated by the expression will be rounded. The number of decimal places is the number of digits to the right of the decimal point. In finance, the dollar amounts are usually rounded to the nearest cent (2 decimal places) or the nearest dollar (0 decimal places).

7. Using the Increase and Decrease Decimal tools, make sure the value in cell B4 is displayed to 2 decimal places (it should read 9.95).

8. Modify the entry in cell B4 to read as follows: =ROUND(9.949874, 2). While the first argument to the ROUND function is usually a formula, it can also be a number as illustrated here. The value displayed in cell B4 should still be 9.95. You should observe that the value in cell C4 also changed to 9.95 indicating that the actual value in cell B4 has changed and not just the format.

9. Use the Increase and Decrease Decimal tools to change the number of decimal places that are displayed in cell B4. As before, these tools still determine how many decimal places are displayed but it took the ROUND function to actually change the value itself.

General Rule for Rounding Dollar Amounts

As a general rule, dollar amounts should be rounded to the nearest penny if the calculation involves multiplication, division, or exponentiation and the result will be used in some other calculation. If the calculation involves only addition and/or subtraction, rounding is not necessary. Also, it is not necessary to round dollar amounts that will not be used in any future calculations (though it never hurts to do so). Whether or not dollar amounts are rounded to the nearest penny, they should always be displayed as dollars and cents (i.e., displayed to two decimal places).

The rationale for this rule is quite simple. Addition and subtraction never add additional decimal places to the result. If you add and/or subtract values that are rounded to two decimal places, the result will also be rounded to two decimal places. On the other hand, multiplication, division, and exponentiation may introduce additional decimal places in the result. Consequently, dollar amounts resulting from calculations involving these three operations should be rounded to the nearest penny.

Modeling a Compound Interest Problem

Our goal is to model a compound interest problem where the output is the future value, the total interest earned, and the APY and the input is the present value, the nominal annual interest rate, the number of compounding periods per year, and the number of years. To get from the input to the output we will need to use the future value, total interest, and APY formulas.

$1,000 is invested for five years in a savings account paying 4% compounded monthly. In the steps that follow, you will be performing the following tasks:

1. Go to the worksheet named "Compound Interest" and enter your name in cell B1.

2. Activate the "Compound Interest" sheet and enter your name in cell B1.

3. Calculate the periodic rate and number of periods:

a. The periodic rate is found by dividing the nominal rate by the number of periods per year. Using cell references for both the nominal rate and the number of periods per year, enter the formula for periodic rate in cell B5.

b. The number of periods is found by multiplying the time in years by the number of periods per year. Using cell references for both the years and the number of periods per year, enter the formula for the number of periods in cell B7.

Strictly speaking, it is not necessary to include cells for the periodic rate and the total number of periods. However, the periodic rate is used in every row in the table showing the period-by-period details and it is somewhat more efficient to perform the calculation only once and refer to the result than it is to recalculate the periodic rate in every row of the table. Also, we'll need to know the number of periods in order to generate the table showing the period-by-period details. Why not let Excel do the calculation for us?

4. Calculate future value, total interest, and APY using formulas:

a. In cell B9, calculate the future value using the compound interest formula for the future value, A=P(1+i)n . In Excel, the exponentiation operator is the caret (^). The correct formula is =B8*(1+B5)^B7. Display the result using the accounting format (in the Number group, use the tool button with the dollar sign on it).

b. In cell B10, enter the interest earned formula (I=A-P) using cell references to replace the variables. Display the result using the accounting format.

c. In cell B12, enter the Excel expression for the annual percentage yield (APY = (1 + i)ppy - 1). Display the result using the percent format rounded to three decimal places.

5. Create a table showing the initial balance, interest earned, and new balance at the end of each period:

a. Highlight the first two cells under the heading "End of Period" (these cells contain the values 0 and 1). Drag the fill handle (the little black square at the lower right) until you have highlighted as many rows as there are periods (60 in this particular problem). Since you highlighted two cells containing consecutive integers, Excel will continue the sequence. As you drag the fill handle down, Excel will show you what the final value in the sequence will be. Keep dragging until you reach 60 and then release the mouse button.

b. Period zero is a fictitious period that is included to make graphs look nicer. The final balance at the end of period zero is the amount of money deposited into the account (the amount of money in the account at the very beginning). In cell D15, enter a formula containing a cell reference that refers to the present value (=B8). Remember, use cell references in formulas whenever possible. If we later change the present value in cell B8, the value in cell D15 will automatically be updated.

c. The initial balance for each period (starting with period 1) is the final balance from the previous period. In cell B16 enter a formula containing the correct cell reference for the new balance at the end of period zero.

d. The interest earned each period (in the Interest column) is always the initial balance times the periodic interest rate. Be careful not to confuse the periodic interest and the periodic interest rate. The periodic interest is measured in dollars and cents and represents the amount of interest earned during the period. The periodic interest rate is expressed as a percent and is used to calculate the periodic interest. Enter the formula for the periodic interest in cell C16 being careful about absolute (periodic rate) and relative references (initial balance). Remember that absolute references require the use of a dollar sign to prevent the column and/or row reference from changing.

e. Because the periodic interest is a calculated value that used multiplication and it will be used in future arithmetic operations it needs to be rounded to the nearest penny (and not just displayed to the nearest penny). Use the ROUND function to round off the periodic interest to the nearest penny (two decimal places).

f. The new balance is the initial balance plus the periodic interest. Enter the appropriate formula in cell D16.

g. Copy the formulas in cells B16 through D16 (B16:D16) down one row and make sure that all values are correct (do the calculations by hand to get the correct results). If they are not, make any corrections in the formulas in row 16 and repeat this step.

h. When the values in row 17 of the worksheet are correct, drag the fill handle down the rest of the table. The final balance after 60 periods should be $1,221.00 confirming the result you got earlier in step 4.

i. Highlight the cells in the interest column of the table (C16:C75). Now look at the very bottom of the Excel window:

Notice that Excel tells you the average of the values in the cells you highlighted, the number of cells, and the sum of the values in those cells. What's important to us is that the sum of the interest payments is $221.00 which confirms the result we got using the formula back up in cell B10.

j. Highlight the body of your table (excluding the first column) and click the comma format button (in the Number group, use the tool button with a comma on it). You may recall that the comma format is the same as the accounting format except it does not use a dollar sign. In general, when you have a whole table of dollar values, the dollar sign is not used except, perhaps, in the first row.

6. Highlight the cells containing the future value, the interest earned, and the APY (B9, B10, and B12). Set the background color to light gray. Add borders around the cells and set the font to bold. This will allow you to distinguish between the input and the output of your model.

Format Tool Buttons

7. Save your workbook.

8. Follow the sequence given below to create a line chart illustrating the growth of the balance in the savings account. (If you have forgotten how to create a line chart, you might need to review the basic steps for creating a chart or the simple interest project.)

a. Highlight the final balance column (do not include the column heading). When creating a chart illustrating the growth (or, later on, the decline) of the balance in an account, always use the final or new balance column.

b. Insert a line chart (using the first option):

Line Chart Button

c. Use the values in the "End of Period" column as the horizontal axis labels.

d. Set the chart title to "Savings Account Balance" and the horizontal axis title to "Month". There should be no vertical axis title and no legend.

e. Right-click on a vertical axis label, select the Format Axis option from the menu, and change the number format to currency with a dollar sign symbol and zero decimal places.

f. Right-click on a horizontal axis label and select the Format Axis option from the menu. Near the top of the dialog, set the number of intervals between labels to four:

g. Near the bottom, position the axis on tick marks:

This lines up the vertical axis and the horizontal axis labels on the tick marks along the horizontal axis.

Your final chart should look something like this:

Final Chart 

9. Save your workbook.

What-If Analysis

The primary advantage of using cell references in Excel formulas is that you can alter the inputs to your mathematical model and Excel will recalculate all of the calculated cell values automatically.

1. Change the present value to $10,000. The formula results for future value and total interest should change to $12,209.97 and $2,209.97 respectively. However, the results given by the table will be 12,210.01 and 2,210.01 respectively. Remember that the formulas do not take rounding into account and, as a result, yield only very good approximations to the actual values. In this case the values obtained by the formulas are off by four cents.

2. Change the nominal interest rate to 6.00%. The formula results become $13,488.50 and $3,488.50. The table results become 13,488.47 and 3,488.47. You should also notice that the annual percentage yield changes to 6.168%.

3. Change the periods per year to 4 and the number of years to 15. The total number of periods should still be 60. The formula results should be $24,432,20 and $14,432,20. The table results will be $24,432,15 and $14,432,15.

Any of the basic parameters (P, r, t, ppy) can be changed freely and the formula results will change accordingly. However, since our table was based on 60 periods, it will only be correct if the number of periods (t*ppy) is 60.

We can use the Goal Seek tool to answer questions that involve something other than a simple modification of the one of the input values.

4. A business owner needs $10,000 five years from now in order to replace some outdated computer equipment. If the bank is paying 3% compounded monthly, use the Goal Seek tool to determine how much the business owner should invest now to meet that goal. You should find that the present value is $8,608.69. Help!

5. Suppose this same business owner only has $8,000 to invest. Use the Goal Seek tool to determine what nominal interest rate (compounded monthly) will allow the business owner to reach the goal of having $10,000 after 5 years. You should find that a nominal rate of approximately 4.471% will result in a future value of $10,000 after 5 years. Help!

Practice Problems

There is a separate 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. As you did at the beginning of this laboratory exercise, format the cells containing the specific answers requested by using a light gray background, bold font, and an outline around the cell(s).

For each problem (except #4), generate a table showing the growth of the balance in the account and illustrate the table with a line graph. Save your workbook after completing each problem. (Saving a workbook saves all of the worksheets in the workbook at the same time just like saving a Word document saves all of the pages in the document at the same time.)

Make sure you use an appropriate model for each problem. In particular, note that in problems 2 and 3, the future value is given and the model must calculate the present value. In other words, the future value is an input to the model and the present value is an output. Notice that the present value calculation involves multiplication and exponentiation and the result will be used again in the table showing the period-by-period details. Consequently, you should use Excel's ROUND function to round the present value to the nearest penny

Problem #1

A man invests $2,000 for 10 years at 6% compounded quarterly. How much will be in his savings account at the end of the term and how much interest will he earn? What is the APY for this investment?

Problem #2

A woman wants to have $1,000 in her savings account in 8 years. If the account pays 9% compounded monthly, how much should the woman invest now? How much interest will she earn? What is the APY for this investment?

Problem #3

Ten years ago, a woman invested an inheritance in a savings account paying 7.5% compounded annually. Her current balance is $5,500. How much did she inherit? How much interest has she earned? What is the APY for this investment?

Problem #4

Which yields a better APY: 6.07% compounded semiannually or 6.06% compounded monthly? (No line graph is needed for this problem.)

You can look at CompIntLab01Answers.xlsx to check all your answers.