Plotting graphs using Excel

Here we use Excel to calculate compound interest and make a graph of the value of an investment as it grows in time.

$500 is invested for four years at 5% interest compounded monthly.

  • Use Excel to calculate the value of the investment
  • Plot the value of the investment as a function of time.

    • ............................................................................................

    Example

    Calculate and plot the value of $100 in vested at 3% interest compounded quarterly for 3 years.

  • The periodic interest rate is 3%/4 = 0.75%. That is, the interest earned each quarter is 0.75% of the value of the investment at the start of the quarter.

  • The number of periods is 4*3=12. That is, 4 quarters for 3 years.
    • Set up a worksheet. Each row will describe the activity each quarter. The first row is shown in Figure 1. Enter the period, periodic interest, and beginning value in cells B6, C6, and D6 as shown in Figure 1. To calculate the ending value, (1)select cell E6, (2) click on the equal sign to the left of the formula bar, and (3) type in the formula in the formulas bar as shown in Figure 1.

    Figure 1

    Use formulas to calculate the values for the second period.

  • The period is 2, that's the first period + 1. Therefore in cell b7 put the formula b6+1. Recall, write formulas into the formula bar after clicking on the equal sign.
  • Put the periodic interest rate (0.75) in cell C7. Use a formula, select cell C7, click on the equal sign in the formula bar, and write C6 into the formula bar.
  • Period 2 beginning value is the ending value of period 1. Therefore in cell D7 put the formula E6.
  • Excel will copy the formula used in the first period, row 6, to calculate the ending value during the second period, row 7 in this example.

    Select cell E6. Move the cursor to the lower right corner of cell E6. When the cursor turns to a solid cross, click and drag it down over cell E7. Notice that the investment has grown by a little more than 75 cents. This is the interest during the second period. Excel has automatically used the correct formula and the correct values as shown in Figure 2.


    • Figure 2

    Now repeat the calculation for 12 periods.

  • Select cells B7, C7, D7, and E7. Move the cursor to the lower right corner of cell E7. When it turns into a solid cross, click and drag it down to the 17 row. That will make 12 rows, one for each period, as shown in Figure 3.

    • Figure 3

    A total of $9.38 interest was earned over the 3 years. If instead of Excel, we used the formula to make the calculation we would proceed as follows.

    Future value is

    FV = P (1 + i) n = 100(1.0075)12 = 109.38
    where P = 100, i = 0.75/100 and n = 12.

    Great. The formula and Excel calculations agree.

    Format Cells

    The beginning and ending value displays are not neat. There are too many decimal places. To remedy this we will format these cells.

  • Select the columns containing the beginning and ending values. From the Format pulldown menu, select cells and currency.
    Format > Cells > Currency

    • Plotting graphs

    To plot the value as a function of time,

  • Select the ending values, (column E, E6:E17)
  • Press the Ctrl key and click and drag to also select the periods, (column B, B6:B17)
  • Click on the Excel chart wizard icon.
  • In the chart wizard window that comes up, select xy scatter and the points connected by a curved line as shown in Figure 4.

    • Figure 4
  • Enter labels for the chart, the x and the y axis.
  • Proceed to Finish The axis can be reformatted by right clicking on the axis to get a menu. You may want to change the font size.


    Figure 5   The resulting graph is shown.