Erin Mueller

Excel and APY

Excel can be used to explore many areas of mathematics including functions and graphs. Excel is a great tool for exploring annual percentage yields. When money is deposited into a savings account, this money will accrue interest over time. This means you will have more money than when you started (assuming you did not withdraw any) without ever depositing any. The first deposit made into your account is called the principal amount or the beginning amount. Your interest rate will vary depending on the length of time the account is open (will depend entirely upon yourself) and the type of account chosen. Accounts known as certificates of deposit (CD) will require you to keep your first deposit in the bank for a certain length of time. You are not allowed to touch this money while it is in the bank. However, interest rates on these types of accounts tend to be good and can range from 2% - 6% annual interest rate. Other factors include how long you tend to keep the account open and how many times per year, interest is accumulated. It may be yearly, semi-annually, or quarterly, depending on the bank. The Excel chart below shows how much money will be accrued after 10 years, in which the interest is compounded quarterly (4 time per year), and the interest rate is 1.99%. The chart shows how much money will be accrued based on the principal while the length of time, interest rate, and number of times interested is compounded are kept constant.

 Principal Interest Rate (Annual) #Of Times Interest is Compounded per Year # Of Years Total Amount in Account After N Years P r q n A 2000 0.199 4 10 3250.04 2500 0.199 4 10 4062.55 3000 0.199 4 10 4875.06 3500 0.199 4 10 5687.57 4000 0.199 4 10 6500.08 4500 0.199 4 10 7312.59 5000 0.199 4 10 8125.10 5500 0.199 4 10 8937.61 6000 0.199 4 10 9750.12 6500 0.199 4 10 10562.63 7000 0.199 4 10 11375.14 7500 0.199 4 10 12187.65 8000 0.199 4 10 13000.16 8500 0.199 4 10 13812.67 9000 0.199 4 10 14625.18 9500 0.199 4 10 15437.69 10000 0.199 4 10 16250.20 10500 0.199 4 10 17062.71 11000 0.199 4 10 17875.23 11500 0.199 4 10 18687.74 12000 0.199 4 10 19500.25 12500 0.199 4 10 20312.76 13000 0.199 4 10 21125.27 13500 0.199 4 10 21937.78 14000 0.199 4 10 22750.29 14500 0.199 4 10 23562.80 15000 0.199 4 10 24375.31 15500 0.199 4 10 25187.82 16000 0.199 4 10 26000.33 16500 0.199 4 10 26812.84 17000 0.199 4 10 27625.35 17500 0.199 4 10 28437.86 18000 0.199 4 10 29250.37

We can also see this data in a chart. The Principal amount represents the input (x-axis) and the output is the Amount Earned (y-axis).

As we can see, this is a linear function. Note that in this case, the interest rate remains a steady 1.99%, the number of times compounded in a year remains 4 (quarterly), and the number of years the money is kept in the account remains 10. We can find the regression of our data as well as the r-square value using Excel.  Y=812.51x+2437.5 and r-square = 1. The r-square value represents the association between x and y. In this case, the data points accurately portray the linear regression function. Now let’s look at what happens when the interest rate is isolated. Below is the chart of data when the Principal is a constant \$2,000, the interest is still compounded quarterly, and the money is still in the account for 10 years. The only variable changing is the interest rate.

 Principal Interest Rate (Annual) # Of Times Interest is Compounded per Year # Of Years Total Amount in Account After N Years P r q n A 2000 0.199 4 10 3250.04 2000 0.209 4 10 3328.28 2000 0.219 4 10 3408.20 2000 0.229 4 10 3489.85 2000 0.239 4 10 3573.26 2000 0.249 4 10 3658.45 2000 0.259 4 10 3745.47 2000 0.269 4 10 3834.35 2000 0.279 4 10 3925.12 2000 0.289 4 10 4017.82 2000 0.299 4 10 4112.49 2000 0.309 4 10 4209.16 2000 0.319 4 10 4307.87 2000 0.329 4 10 4408.65 2000 0.339 4 10 4511.56 2000 0.349 4 10 4616.62 2000 0.359 4 10 4723.88 2000 0.369 4 10 4833.38 2000 0.379 4 10 4945.15 2000 0.389 4 10 5059.25 2000 0.399 4 10 5175.71 2000 0.409 4 10 5294.57 2000 0.419 4 10 5415.89 2000 0.429 4 10 5539.71 2000 0.439 4 10 5666.06 2000 0.449 4 10 5795.01 2000 0.459 4 10 5926.59 2000 0.469 4 10 6060.85 2000 0.479 4 10 6197.84 2000 0.489 4 10 6337.62 2000 0.499 4 10 6480.22 2000 0.509 4 10 6625.71 2000 0.519 4 10 6774.13

Below is the graph of the data.

This function looks exponential. The regression equation and correlation coefficient for this graph is .

This means that if we plug a value for the interest rate in for “x”, the out put will be our amount earned based on a \$2,000 principal and ten year, quarterly return.

Next, we can see what happens when we change the number of times interest is compounded throughout the year.

 Principal Interest Rate (Annual) #Of Times Interest is Compounded per Year # Of Years Total Amount in Account After N Years P r q n A 2000 0.199 4 10 3250.04 2000 0.199 5 10 2954.80 2000 0.199 6 10 2771.62 2000 0.199 7 10 2647.11 2000 0.199 8 10 2557.05 2000 0.199 9 10 2488.92 2000 0.199 10 10 2435.60 2000 0.199 11 10 2392.74 2000 0.199 12 10 2357.54 2000 0.199 13 10 2328.13 2000 0.199 14 10 2303.18 2000 0.199 15 10 2281.75 2000 0.199 16 10 2263.14 2000 0.199 17 10 2246.84 2000 0.199 18 10 2232.44 2000 0.199 19 10 2219.63 2000 0.199 20 10 2208.15 2000 0.199 21 10 2197.81 2000 0.199 22 10 2188.45 2000 0.199 23 10 2179.94 2000 0.199 24 10 2172.16 2000 0.199 25 10 2165.03 2000 0.199 26 10 2158.46 2000 0.199 27 10 2152.39 2000 0.199 28 10 2146.78 2000 0.199 29 10 2141.56 2000 0.199 30 10 2136.70 2000 0.199 31 10 2132.16 2000 0.199 32 10 2127.91 2000 0.199 33 10 2123.93 2000 0.199 34 10 2120.19 2000 0.199 35 10 2116.67 2000 0.199 36 10 2113.35

Below is the graph of the data.

The regression equation for this function is . Again, if we plug our compounded value in for “x”, the output will be the amount earned based on a \$2,000 principal, 1.99% interest rate and a 10 year period.

We can also find a function for the amount of time our money is in the bank. Let’s look at the chart where the “year” variable changes.

 Principal Interest Rate (Annual) #of Times Interest is Compounded per Year # of Years Total Amount in Account After N Years P r q n A 2000 0.199 4 10 3250.04 2000 0.199 4 11 3328.28 2000 0.199 4 12 3408.20 2000 0.199 4 13 3489.85 2000 0.199 4 14 3573.26 2000 0.199 4 15 3658.45 2000 0.199 4 16 3745.47 2000 0.199 4 17 3834.35 2000 0.199 4 18 3925.12 2000 0.199 4 19 4017.82 2000 0.199 4 20 4112.49 2000 0.199 4 21 4209.16 2000 0.199 4 22 4307.87 2000 0.199 4 23 4408.65 2000 0.199 4 24 4511.56 2000 0.199 4 25 4616.62 2000 0.199 4 26 4723.88 2000 0.199 4 27 4833.38 2000 0.199 4 28 4945.15 2000 0.199 4 29 5059.25 2000 0.199 4 30 5175.71 2000 0.199 4 31 5294.57 2000 0.199 4 32 5415.89 2000 0.199 4 33 5539.71 2000 0.199 4 34 5666.06 2000 0.199 4 35 5795.01 2000 0.199 4 36 5926.59 2000 0.199 4 37 6060.85 2000 0.199 4 38 6197.84 2000 0.199 4 39 6337.62 2000 0.199 4 40 6480.22 2000 0.199 4 41 6625.71 2000 0.199 4 42 6774.13

Above, we can see the effect that “number of years” has on the amount earned.  Below is a graph of the data including the regression equation and correlation coefficient.

Again, this equation is the natural log function .

From the above equations for principal, interest, number of times compounded, and number of years in the account, we can estimate the amount earned based on any one of these variables.