Simple Interest Lab

The Excel material in this project is based on what you learned when you completed the first Excel project (on percents). You must complete that project before starting this one.

Objectives

Running Excel

1. Log-in to your user account and then double-click on the Excel icon Excel Icon.Depending on how your computer is set up, you may find the Excel icon on the desktop, the taskbar at the bottom of the screen, and/or the start programs menu.

2. Enter your name in cell A1 of the worksheet.

Mathematical Model of Simple Interest

As noted in the percents lab, a mathematical model has three basic components: output, input, and process. The output represents what it is we are trying to determine. In the simplest models this might be a single result often referred to as "the answer". Often the output consists of more than just one value. The input represents data that we are given about the problem that we are modeling. The process refers to the steps that need to be taken in order to find the output based on the values of the input.

Given the present value of an investment, the annual interest rate, and the number of years, how much simple interest will be earned and what is the future value of the investment? This model has two output values: the simple interest earned and the future value of the investment. The input for this model consists of the present value, the annual interest rate, and the number of years. The process can be represented using the formulas needed to calculate the output values given the input values:

Simple Interest = Present Value * Annual Interest Rate * Number of Years
Future Value = Present Value + Interest

While there is absolutely nothing wrong with using these formulas to represent the process for this model, we more frequently use abbreviated versions of these two formulas:

I = P*r*t or, more commonly, I = Prt
A = P + I

While these equations are much more concise, using them assumes that we know that:

Implementing the Model in Excel

In the models we created in the percents lab, the input and output values were listed in different rows of the same column and we used row headings to identify what each value represented. For this model, we will place the values in different columns of the same row and use column headings to identify what each value represents.

1. In cells B3:F3 (cells B3 through F3) enter the column headings "Present Value", "Interest Rate", "Years", "Interest", and "Future Value". Don't forget that you enter only what is between the quotes.

2. Adjust the column widths so that the entire column heading is visible in each column. You learned how to adjust column widths in the previous lab. You can use either one of those techniques or, if you are feeling adventuresome, you can try the following optional steps. If you choose to adjust the column widths one at a time, you can skip down to step 7 below.

3. (Optional) Place the mouse cursor on the column identifier B at the top of the second column. Holding the left mouse button down, drag the mouse over to the F column. This action selects columns B through F:

Select Columns B through F

4. (Optional) With all five columns selected, change the width of column F so that it is just wide enough for the "Future Value" label. Notice that when you release the mouse button, the widths of the other four columns are set to the same value so all five columns have the same width. Unfortunately, column B probably still isn't wide enough:

Labels

5. (Optional) Repeat steps 3 and 4 only this time, with all five columns selected, change the width of column B rather than column F. Change the width of column B so that it is wide enough for the "Present Value" label. When you release the mouse button, the widths of the other 4 columns will be set to the same value. Since the heading in column B is the longest heading, you should find that the entire heading in each column is visible. However, the columns with shorter headings will be a little wider than they really need to be (especially the "Time" and "Interest" columns):

Labels

6. (Optional) Select all of the columns (as you did in step 3). Point at any of the vertical lines between two columns (in the selected range). That is, point at the vertical line between the B and the C, or the C and the D, and so on. You can even point at the vertical line between the F and the G. Now, double-click the left mouse button. No matter which vertical line you were on, the widths of each of the five columns was automatically adjusted so that the label in that column just fit. No column is either too small or too large:

Labels

7. Ultimately, columns B, C, and D will contain input values (numbers). The output values in cells E4 and F4 must be calculated. Using the mathematical model given above, enter the expression for interest earned in cell E4 and the expression for future value in cell F4. Remember that the variables in the formulas are replaced by the cell references of the cells containing the corresponding values. Since empty cells are interpreted as zeroes, you should get a value of zero for both the interest and the future value.

You may think it strange that I am asking you to enter the formulas to calculate the output before any input values have been entered. I'm doing that because I want you to understand that the entire mathematical model can be implemented in Excel even though there are no actual numbers to work with. Our formulas involve cell references rather than numbers. Consequently, I can enter the formulas whether or not I know what the actual numbers are (as long as I use the appropriate cell references).

8. Of course, eventually we want to enter numbers into our input cells. Set the present value (cell B4) to 1000, the interest rate to 5%, and the time to 4 years. Calculate the interest earned and the future value by hand and confirm that your Excel model gave you the correct results.

9. Format the dollar amounts using the currency format. The interest rate should be displayed using the percent format rounded to two decimal places.

What-If Analysis

Now let's try some what-if analysis. In the next three steps you are to change just the indicated input value and then calculate the corresponding interest and future value by hand and confirm that your model gives you the same results. Backtracking is not necessary. For example, when you change the interest rate in step 11, you do not need to change the present value back to 1,000.

10. What if we change the present value to 2,000?

11. What if the interest rate changes to 8%?

12. What if the time changes to 12 years?

Now let's try some harder problems which will require the use of the Goal Seek tool. Remember that the goal seek tool allows us to set the value of one of the output variables (a calculated result) to a specific value by changing one of our input values.

13. What if the present value is $800, the interest rate is 4.50%, and our goal is to amass a future value of $1,000? How long will it take? Display the years rounded to one decimal place. You should get 5.6 years. If you click on cell D4 (Time), you should find that the actual time is 5.55555555555556 years. Remember that formatting a cell determines how the value in the cell is displayed. In this case, we asked Excel to display the value rounded to one decimal place. Formatting a cell does not change the actual value stored in that cell.

14. What if we are willing to invest $400 for 10 years with the goal of getting back $620. What interest rate will allow us to meet our goal? Display the interest rate as a percent rounded to two decimal places. You should get 5.50%.

An Enhanced Model of Simple Interest

As useful as our simple interest model might be, it has its limitations. In particular it gives us the output values for a single set of input values. Suppose that what we really want to know is how the interest and future value of an investment change as the interest rate increases from 0.0% to 6.0% in 0.5% increments. Using the model we created above, we would have to enter the interest rates 0.0%, 0.5%, 1.0%, 1.5%, and so on through 6.0% one at a time and write down the corresponding results. However, we can enhance the worksheet we just created to do all of that automatically and, at the same time, draw a line chart that graphically illustrates the resulting trend.

1. Go to worksheet 2 by clicking on the Sheet2 tab at the bottom of the workbook:

Sheet2 Tab

2. Set up the worksheet as shown here:

Initial Setup

Filling a Column with Sequential Values

In the interest rate column, we want interest rates ranging from 0.00% to 6.00% in increments of 0.50%.

3. Enter the value 0.00% in cell A7, 0.50% in cell A8, 1.00% in cell A9, and so on until you get to 6.00%. By entering the values as percents to two decimal places, you are also telling Excel what format to use when displaying these values.

That was pretty tedious wasn't it? You'll be glad to know there is an easier way to enter a sequence of values.

4. Leaving the top two values alone, delete the rest of the interest rate values you just entered in cells A9:A19 (cells A9 through A19). The easiest way to do this is to highlight the whole block and then tap the Delete key. Tapping the Backspace key will not work. You must tap the Delete key.

5. Select cells A7:A8 with your mouse. The small black box on the lower-right corner of your selection is called the fill handle:

Fill Handle

6. Drag the fill handle down and note the small box that indicates the current value. Continue dragging the fill handle down until you see 6.00% in the box and then release the mouse button:

Drag Fill Handle

By selecting the first two cells containing 0.00% and 0.50%, you were indicating to Excel that you wanted a sequence of values starting at 0.00% and incrementing by 0.50% per cell. In general, Excel simple subtracts the first value from the second to determine the size of the increment. As you drag the fill handle down, Excel shows you the current value and keeps adding the increment as you drag the fill handle down the column.

Copying a Formula Down a Column

7. Enter the formula for the interest in cell B7 using cell references for the input values (present value, time, and interest rate). Because the input cells are empty, you should get a value of zero.

8. Enter the formula for the future value in cell C7 using cell references for the input values (present value and interest). Again, you should get a value of zero.

9. We are almost done, but before we go on, let's make sure everything is correct so far. Set the present value to $1000 and the time to 5 years. The interest in cell B7 should still be zero (because the interest rate in cell A7 is zero) and the future value should change to $1000.

10. Use the number format for the cells containing the present value (B3), the interest (B7), and the future value (C7):

Number Format

Relative and Absolute Cell References

11. Select the range of cells B7:C7 and copy the contents down one row by dragging the fill handle down one row. Here is what you should see:

Results

The problem is that neither the interest nor the future value is correct in the second row (corresponding to an interest rate of 0.50%). To understand why, go through the following steps very carefully.

12. Click on cell B7 and examine its contents. Remember that what you see in the cell is the result of the expression you entered. I want you to examine the formula itself which is shown in the text box labeled fx above the worksheet:

Contents of B7

13. Answer these questions:

Your answers should have been these:

14. Now click on cell B8 and examine its contents (the Excel expression it contains):

B4*A8*B5

15. Answer these questions:

Your answers should have been these:

By default, when Excel copies a formula to a new cell, it assumes that the cell references are relative cell references. Go back to the formula in cell B7 which is B3*A7*B4. The present value (in B3) is 4 rows above B7, the interest rate (in cell A7) is one column to the left of B7, and the time (in cell B4) is 3 rows above cell B7.

These relative distances are preserved when the formula is copied down one row to cell B8. That is, Excel assumes that the present value is 4 rows above B8 (in cell B4), the interest rate is one column to the left of B8 (in cell A8), and the time is 3 rows above cell B8 (in cell B5). More generally, as we copy the interest formula down the column, Excel assumes that the present value is always 4 rows above, the interest rate is always one column to the left, and the time is always 3 rows above.

Only one of these assumptions is correct. The interest rate is, in fact, always one cell to the left of  the interest formula (no matter which row we are referring to). The other two assumptions are dead wrong. The present value and the time are always in exactly the same location; namely B4 and B5. We say that the locations of these two values are absolute; neither changes as you copy the formula down the column. In Excel, a dollar sign is used to identify an absolute reference.

16. Go back to cell B7 and enter the following expression, "=B$3*A7*B$4". The resulting value should still be zero.

17. Using the fill handle, copy the formula in cell B7 down into cell B8. The interest should change to 25.00 which is the total interest earned on $1,000 at 0.50% for 5 years.

Putting the $ in front of the 3 (in B$3) tells Excel that the row reference is absolute. When you copy the formula down the column, the row reference will not change. The present value is always in row 3. Similarly, putting the $ in front of the 4 (in B$4) tells Excel that this row reference is absolute. The time in years is always in row 4.

The principles of relative and absolute reference can also apply to columns. A relative column reference will change if the formula is copied to the right or to the left into a new column. An absolute column reference will not change. That is not important in this particular situation because the interest formula is only being copied down; not to the left or to the right.

18. Examine the formula you entered in cell C7. What does B3 represent and what does B7 represent?

19. Examine the formula in cell C8. What does B4 represent and what does B8 represent?

In step 18, you should have noted that B3 and B7 represent the present value and the interest respectively. In step 19, you should have observed that B4 and B8 represent the time and the interest respectively. Since the future value formula requires the present value rather than the time, the reference to B4 in the formula in cell C8 is incorrect. It should have been B3. That is, back in the original formula in cell C7, we should have used an absolute reference for present value and a relative reference for the total interest earned.

20. Go back to cell C7 and correct the formula to read "=B$3+B7". The future value should still be 1,000.00.

21. Using the fill handle, copy the formula in cell C7 down into cell C8. The future value should change to 1,025.00; the correct value.

Now that we have corrected our expressions, we are ready to copy our formulas all the way to the bottom of the table.

22. Select cells B7:C7 and drag the fill handle down to the bottom of the table (row 19). At an annual rate of 6%, the interest should be $300 and the future value should be $1,300. If not, then you need to correct the formulas in cells B7 and/or C7 and copy the corrected formulas down to the bottom of the table.

Inserting a Line Chart

Finally, we want Excel to draw a line chart to illustrate what happens to the total interest earned on an investment as the annual interest rate varies from 0% to 6%.

23. Select the values in the interest column (cells B7:B19):

Select Interest Values 

24. Click on the Insert tab (next to the Home tab):

Insert Tab

25. On the Insert tool ribbon, insert a line chart:

Insert Line Chart

If you are running Excel in a small window, you may have to click on the Charts tool before you can see the different kinds of charts that are available.

26. There are several types of line charts available. Select the first option in the first row:

Basic Line Chart

Two things will happen. First, a set of Chart Tools will open up on the tool ribbon at the top of the Excel window. By default, the Design tab will be the active window. Second, a basic line chart will appear in your worksheet:

Basic Line Chart 

There is still a lot of work to do. For almost any chart you create, you will need to work through the following steps. They don't necessarily have to be done in the same order, but if you always do them in this order, you are less likely to omit a step.

Set up the Horizontal Axis Labels

The horizontal scale should represent the various interest rates but it doesn't.

27. Click the Select Data tool on the Design tab of the Chart Tools menu:

Select Data Tool

28. In the Select Data Source dialog window, click the Edit button on the right to edit the horizontal axis labels:

Edit Horizontal Axis Labels

29. Highlight the values in the interest rate column (cells A7:A19). Excel will fill in the range of cells in the Axis Labels dialog and display the interest rates as the horizontal axis labels on the chart:

Horizontal Axis Labels 

30. Click OK to close the Axis Labels dialog and click OK again to close the Select Data Source dialog.

Set the Chart and Axis Titles

31. Click on the Layout tab of the Chart Tools menu:

Click Layout Tab

32. Every chart should have a title. Select the Chart Title tool and then the Above Chart option (as illustrated below). Then, all you have to do is type in your chart title, "Total Interest Earned".

Chart Title Tool

33. Next, enter "Annual Rate" as the horizontal axis title which should be located below the axis:

Horizontal Axis Title

34. Set the vertical axis title to "Interest". In most cases, you will want to use the rotated title format:

Rotated Title 

35. When there is only one set of data be charted, a legend is not needed. Select the Legend tool and turn off the legend:

Turn Off Legend

36. If you look at your graph, you will notice that the line doesn't start right on the vertical axis as it should. We want to correct that now. Select the Axes tool and then the Primary Horizontal Axis option and then the More Primary Horizontal Axis Options button:

Axes Tool

37. In the Format Axis dialog, click on the radio button labeled "On Tick Marks" near the bottom of the dialog:

Position Axis On tick marks

You can also open the Axis Options dialog by right-clicking the mouse while pointing at the horizontal axis labels and selecting Format Axis from the context-sensitive menu. In the next instruction, you will use that approach to adjust the vertical axis.

38. Finally, point at the vertical axis labels (anywhere as long as you are just to the left of the vertical axis) and click your right mouse button. In the context-sensitive menu, choose the Format Axis option (at the bottom of the menu). That will open a Format Axis dialog for the vertical axis.

39. In the Format Axis dialog, select the Number option and then set the format to Currency and set the number of decimal places to zero:

Format Vertical Axis Numbers

Your final chart should look like this:

Final Chart 

What-If Analysis

40. Change the present value to $400. The values in the interest and future value columns should change and your chart should also change to illustrate the new values for interest.

41. Change the number of years to 10. Again, notice that the interest and future values are adjusted and the chart also changes to reflect the new interest values.

Insert a Future Value Chart

42. Now that you have learned how to create a line chart, insert a line chart that illustrates the future values of the investment for the range of interest rates in your table. Use the same steps you used to create your interest chart except select the values in the future value column rather than the values of in the interest column. Of course, your titles will also be different. Your chart should look like this:

Future Value Chart

My Worksheet

If you are unclear about something or just want to see my workbook, here is a link to it: SimpleIntLab.xlsx