MathOnWeb.com

---

Simple Interest

Suppose that we need to borrow some money. We go to a lender, such as a bank, and they agree to lend us the money on the condition that we pay back the loan plus interest in a certain time. Interest is the amount that the lender charges us for the privilege of borrowing the money. This page is all about loans, interest, and how Excel can be used to do the calculations.

The interest charged depends on the size of the loan. It also depends on the time that we take to pay back the loan. (This is because of the time value of money. This is the concept that a sum of money is worth more now than the same sum will be worth at a future date, due to its earnings potential in the interim. It is a core principle of finance.)

The simplest form of interest is simple interest, given by the formula

I = P r t.

(1)

In this equation

• I is the interest charged, in dollars.
• P is the principal, the amount borrowed, in dollars.
• t is the term, or the time, in years, for the loan to be repaid.
• r is the interest rate, expressed in %/year (or 1/years).
  
  Note that "per cent" means "per 100", so 12% means 12 per 100 or 0.12, which is a number with no units. In a calculator or in Excel you must enter 12% as 0.12 and not as 12. In Excel you can then use the % button (located in the Number group in the Home tab) to change the format to display it as 12%, if you wish.
  
  Note also that if t is given in months or days, then it must be converted to years so that the units of r and t cancel in equation (1).

---

Example 1: Besides lending money to us, banks also “borrow” money from us. For example, if we deposit $100 in a bank's savings account then we are essentially lending $100 to the bank. If we close our account after one year then the bank will pay us interest for the privilege of having used our money for one year. (The bank will probably use our money to make investments which will grow even faster than our savings account does.)

Question: If the savings account offers simple interest of 12% per year, then how much interest does the bank owe us (a) after 6 months? (b) after 12 months, when we close the account?

Answer:
(a) We substitute in values for P, r and t in equation (1). We must express the time, 6 months, as 0.5 years so that the rate and time units cancel:

Thus the interest is $6. So after 6 months the account holds $106 (our original $100 principal plus the $6 interest). This sum is called the account's balance.

(b) We substitute time t = 1 year into eq. (1). This gives interest I = $12, so the balance in the account is now $112. When we close our account the bank will give us $112. These values are shown in the graph. Note the straight line describing the account balance.

---

Example 2: Introduction to compound interest. Let's modify Example 1. Instead of closing our account after 12 months, suppose that we close it after 6 months (when it has a balance of $106), and then immediately open a new account with the $106 as its principal. We will see that this is better for us because we are now earning interest on $106 rather than just $100.

Here is the calculation of the simple interest earned on principal of $106 in the second half of the year.

Thus the account balance at the end of the year is $112.36 (the sum of the $106 principal and the $6.36 interest). In the graph, the balance now follows the red line which bends upward from Example 1's straight line.

---

Compound Interest Spreadsheet

Compound interest uses the idea described in Example 2. (Except that we don't actually close and reopen the account. We just do the calculation as if we did.) Here is an algorithm for applying compound interest on a loan:

1. Break the term of the loan into a number of equal-time periods.
2. Each period has an opening balance. For the first period the opening balance is the principal. For each subsequent period the opening balance is the closing balance from the previous period.
3. For each period, calculate the simple interest using eq. (1). Use the opening balance of the period for P. Use the length of the period for t.
4. For each period, add the interest to the opening balance to get the closing balance.

In Example 2, the period was 6 months or ½ year. Thus the interest is said to be “compounded semi-annually”. It is also common to have interest compounded monthly, bi-weekly, or even daily.

---

Example 3: A bank offers a savings account that yields 12% interest per year, compounded monthly. Suppose that we open a savings account on January 1st with a deposit of $100. Let's implement the algorithm for compound interest, given above, in an Excel spreadsheet. We want to see how the balance in the account grows every month for a year.

Step 1: Start a new sheet in Excel. Although it is not necessary, it is useful to see the formulas in the spreadsheet. There is a Show Formulas toggle button, , in the Formula Auditing group in the Formulas tab of Excel. Click it to toggle the display to show the formulas rather than the corresponding values.

Step 2: Fill in the cells shown in this screenshot. (The rest of the spreadsheet will be filled in using the fill handle).

Step 3: Use the fill handle (explained in another blog page) to fill in the rest of the spreadsheet.

1. Select the range A2:B3 and drag the fill handle downward. A pop-up appears showing Mar, Apr, May, etc. Drag it until it says Dec. This “pattern copying” works whenever two cells are selected. If Excel finds a pattern in the selected cells then it copies the pattern.
2. Select cell C3 and double-click its fill handle. Column C gets extended to the same length as columns A and B. By default the copy uses relative referencing so now the opening balance for all the rest of the months equals the closing balance of the previous month.
3. Select the range D2:E2 and double-click its fill handle. Columns D and E get extended to the same length as the previous columns, again with relative referencing.

The spreadsheet is now complete.

It is interesting to trace how the final balance in the account (in cell E13) grows from the initial principal (in cell C2). Select cell E13 and then repeatedly click the Trace Precedents button (located in the Formula Auditing group in the Formulas tab). Here is the result.

The arrows can be removed by clicking the Remove Arrows button, located just below the Trace Precedents button.

Finally, toggle the Show Formulas button again to see the normal view. Column D shows the interest earned every month. Column E shows the balance in the account at the end of every month.

The total interest earned can be found by summing up column D or, more easily, by just subtracting the principal, $100, from the balance at the end of the year, $112.68. The result is $12.68. Compare this to the interest in Example 2, which was $12.36. It is a fact that more frequent compounding always results in more interest.

---

Compound Interest Formula

It is not hard to derive a formula for compound interest. We just take the formulas that we used in the spreadsheet above, remove the relative references one-by-one, and express them in terms of P, r and t. We get this table:

(We have made one algebra simplification. Since columns C and D always have a factor of P, we have factored it out when we added them in column E.) The pattern in column E shows that the balance is given by the formula

B = P (1 + r t) n.

(2)

In the table and in eq. (2):

• B is the balance in the account, in dollars, after n time periods.
• P is the principal, the initial amount in the account, in dollars.
• r is the interest rate, expressed in %/year (or 1/years). (It is often called APR (Annual Percentage Rate.)
• t is the length of each time period (the time between compoundings), in years.

Note that 1/t is the number of periods per year, whereas n is number of periods after which we want to calculate B. Note also that if n = 1 then there is no compounding and eq. (2) reduces to B = P + P r t. The second term on the right-hand-side, P r t, is the simple interest given in eq. (1). Here are two examples:

---

Example 4: This was the final screenshot from Example 3:

Recall that this spreadsheet was created using the compound interest algorithm. The aim, in Example 3, was to find the balance in the account at the end of 12 months (the value in the green cell).

In this example we want to use eq. (2) to get that value directly. Substitute in the values P = $100, r = 0.12/year, and t=1/12 year (because the compounding period is 1 month or 1/12 year). Then eq. (2) reads

B = $100 (1 + 0.01) n.

(3)

Now substitute in n = 12. Eq. (3) gives B = $112.682503, meaning that after 1 year the balance is $112.68. If we want the balance after, say, 5 years then we substitute in n = 60. Then eq. (3) gives B = $181.67.

---

Example 5: Let's compare the growth of $1 in two different savings accounts over the course of 20 years. One account gets 20%/yr, simple interest, and the other gets 20%/yr, compounded daily.

Eq. (1) describes the account that gets simple interest. Using the values P = $1, r = 0.20/year, and t = 20 years, it gives

Thus the balance after 20 years is $5 ($1 principal + $4 interest).

Eq. (2) describes the account getting compound interest. We use the same values for P and r, but we use t = 1/365 year (i.e. 1 day) for the period length, and n = 20 * 365 for the number of periods. Thus eq. (2) reads

The graph to the right shows how the two accounts grow for 20 years. At first the difference between them is not much, but after 20 years the difference is huge.

---

Excel's FV (Future Value) Function

Let's start by stating, once more, the compound interest formula:

Recall that in this formula:

• B is the balance in the account, in dollars, after n time periods.
• P is the principal, the original amount in the account, in dollars.
• r is the interest rate, expressed in %/year (or 1/years).
• t is the length of each time period (the time between compoundings), in years.

Excel has a built-in function called FV (for Future Value) that also describes compound interest. (We will discuss all of Excel's financial functions later in this page.)

The FV function returns (the negative of) the balance, B, in the account. It has these four arguments:

1. the product r·t
2. the number of periods, n
3. 0 (for now. This argument is used for periodic payments, described later.)
4. the principal, P

This screenshot shows how to use the compound interest formula (in column B) and the FV function (in column C) to answer the questions posed in Examples 3, 4 and 5. Both Columns B and C give the values shown in column D.

Note:

• The exponentiation operator in Excel is the caret (^). For example the formula =2^5 in a cell gives the value 2⁵ or 32.
• The FV function considers the principal to be a flow of cash from lender to borrower (hence positive) and the balance (the amount to be repaid) to be a flow back to the lender (hence negative).
• Putting brackets around the t value, e.g. (1/12) and (1/365), is only for clarity. It isn't necessary.

---

Periodic Payments Spreadsheet

Many loans are so large that they cannot be paid back with a single lump sum at the term (end) of the loan. An alternative is to pay the loan back with a number of equal payments, made at equal time intervals. This is called a periodic payment loan.

The payments could be large enough to completely pay off the loan (usually the case for a car loan), or they could just reduce the amount owing (often the case for a mortgage). We will consider both possibilities.

Here is an algorithm for a periodic payments loan:

1. Break the term of the loan into a number of equal-time periods.
2. Each period has an opening balance. For the first period the opening balance is the principal. For each subsequent period the opening balance is the closing balance from the previous period.
3. For each period, calculate the simple interest using eq. (1). Use the opening balance of the period for P. Use the length of the period for t.
4. For each period, take the opening balance and add the interest and subtract the periodic payment to get the closing balance.

Note that the periodic payment is made at the end of each period. If we compare this algorithm to the compound interest algorithm we see that there is only one difference: the red text added in step 4.

---

Example 6: Suppose that we want to borrow $10,000 to buy a car. We earn a paycheck every 2 weeks and we think that a payment of $200 every 2 weeks on the loan is manageable. Obviously, 50 payments of $200 would pay off the loan if there was no interest.

But of course there is interest. The interest rate offered by the bank is 15%/year, compounded biweekly. We want to set up a spreadsheet to implement the algorithm for periodic payment loans and answer these questions:

1. If we insist on payments of $200 then how many payments will there have to be?
2. If we insist on paying off the loan in 50 payments, then how big will the payments have to be?

The way that we will set up this spreadsheet closely parallels Example 3, so it might be useful to review that.

Setting up the spreadsheet.

Step 1: Start a new sheet in Excel. In cell B4 enter the date that the loan will be taken out. (I entered 2023-05-28, meaning May 28, 2023.) Then click the Show Formulas button (in the Formulas tab) to show formulas. Notice that the date is now 45074. The reason is that the date formatting has been removed and this date is 45,073 days after January 1, 1900, which is day 1 for the Windows operating system. (More on dates later in this blog.)

Step 2: Fill in the cells shown in this screenshot. (The rest of the spreadsheet will be filled in using the fill handle.)

Step 3: Use the fill handle (explained in another blog page) to fill in the rest of the spreadsheet:

1. Select the range A4:A5 and drag the fill handle downward until the pop-up says 60.
2. Select the range B5:C5 and double-click its fill handle. Columns B and C get extended to the same length as column A.
3. Similarly, select the range D4:F4 and double-click its fill handle.

Step 4: Now we can toggle the Show Formulas button to go back to the normal view. One more thing. This spreadsheet is very long. It is convenient to see the top and bottom simultaneously. To do this select a row roughly halfway down the screen and click the Split button, , (located in the View tab, Window group). The window splits into two panes. You can scroll the two panes separately. Do this until your view looks like this:

Now we are ready to answer the two questions.

Question 1: How many $200 payments will there have to be?

To answer this, take a look at the last two rows of the screenshot above. The second-to-last row shows that we have made 59 payments of $200. The last row shows that at the beginning of the 60th biweek (August 31, 2025) there is still $25.59 still owing. Two weeks of interest on that is $0.15. So the final payment, due at the end of that biweek (on September 14), should be the sum of these two: $25.74.

To reflect this we will “clean up” the spreadsheet by making the following final adjustments:

• Put the formula =C63+D63 into cell E63. This causes F63, the closing balance, to become zero.
• Extend column B by one cell to get the date of the final payment (since the closing date for the 60th biweek is the opening date for the 61st).
• Put the formula =SUM(D4:D63) into cell D65 to show the total interest paid.
• Put the formula =SUM(E4:E63) into cell E65 to show the total payments made.

The affected cells are shown in green in the spreadsheet below:

Question 2: How big do the payments have to be to pay off the loan in 50 equal payments?

To answer this, scroll the bottom pane so that biweek 50 is visible. Cell F53 (circled in the screenshot below) is the balance at the end of 50 payments and we want it to become zero. We can make this happen by making the payment amount in cell B2 (also circled) a bit larger. With a bit of experimentation we find that the payment amount should be around $230. Excel has a feature called Goal Seek that can find the exact value. Goal Seek is described in another blog page. It gives the value $230.71533. Of course we can't pay a fraction of a cent so we will have to round it off. As a result the final payment will always be a bit different from the rest.

---

Periodic Payments Formula

Let's derive a formula for the periodic payment loan. The derivation parallels that of the compound interest formula, but is a bit more complicated. We just take the formulas that we used in the spreadsheet above, remove the relative references one-by-one, and express them in terms of P, r, t and A. We get this table:

Remember that the n^th row describes the n^th period, and that the last column is the balance remaining in the loan at the end of that period. By studying the pattern in the last column, we find that the balance, B, at the end of the n^th period is given by the formula

(4)

Eq. (4) is not easy to use because if our loan has, say, 50 periods, then the underlined part of eq. (4) has 50 terms in it. But fortunately the underlined part is a geometric series, and there is a nice formula for a geometric series.

Click here to see the details in a pop-up.

Using the formula for a geometric series, eq. 4 can be written as

(9)

In the table and in eqs. (4) and (9):

• B is the balance of the loan, in dollars, after n time periods.
• P is the principal, the initial amount of the loan, in dollars.
• r is the interest rate, expressed in %/year (or 1/years). (It is often called APR (Annual Percentage Rate.)
• t is the length of each time period (the time between compoundings), in years.
• A is the payment made at the end of each period, in dollars.

Note that 1/t is the number of periods per year, whereas n is number of periods after which we want to calculate B. Here is an example:

---

Example 7: Let's redo Example 6, using the periodic payments formula, (9). (In that example a $10,000 loan was to be repaid in $200 biweekly payments and the interest rate was 15%/year, compounded biweekly.

Start a new sheet in Excel, go into Show Formulas mode, and fill in the cells shown here:

Note that Column A contains text describing each of the items in column B. Yellow indicates parameters of the loan that the user may want to change. Cell B2 contains the product of r and t. (They never appear separately so it is more convenient to put them together.) Cell B5 contains the code for eq. (9):

=B1 * (1 + B2)^B4 + B3/B2 * (1 - (1+B2)^B4)

In cell B7, a date entered in date format, for example, 2023-05-28, displays as a number. When we get out of Show Formulas mode the spreadsheet looks like this:

Happily, the balance of $1773.60 at the end of 50 biweeks agrees with the spreadsheet in Example 6.

We can easily answer the question “what do the payments have to be to pay off the loan in 50 biweeks” using Goal Seek. In the Goal Seek's dialog box we enter “Set cell B5 to value 0 by changing cell B3”. Answer: $230.72.

The other question, “how many payments of $200 would there have to be?”, is more involved. There are several steps:

• We again use Goal Seek, but this time the dialog is “Set cell B5 to value 0 by changing cell B4”.
• The answer, 59.128 biweeks, is not a whole number so we must round it down to 59. As a result, after 59 payments of $200, there is a balance of $25.59 still owing. This is shown in the left two columns in the screenshot below.
• To handle the final payment, it is best to copy columns A and B to columns D and E, and change the following 4 cells in column E:
  • Set the principal in E1 to be the balance still owing from cell B5.
  • Set the start date in E7 to be the end date in B8.
  • Set the final payment amount in E3 to $0 (temporarily) because we want to see what the balance is, due to interest alone.
  • Set E4 to 1 (1 final biweek).
  This is shown in the middle two columns in the screenshot. We see that the final payment should be $25.74.
• So we modify column E one more time, making the final payment in E3 equal $25.74. This gives us a balance of $0. This is shown in the right two columns in the screenshot.

---

Excel's Financial Functions (PMT, NPER, RATE, PV and FV)

Let's start by stating, once more, the periodic payments formula:

(9)

Recall that in this formula:

• P is the principal, the initial amount of the loan, in dollars.
• A is the payment amount, made at the end of each period, in dollars.
• B is the balance of the loan, in dollars, after n time periods.
• n is the number of time periods. It is a positive integer.
• r is the interest rate, expressed in %/year (or 1/years).
• t is the length of each time period (the time between compoundings), in years.

Note that r and t never appear separately, but always as the product, r·t. Furthermore, since r and t have cancelling units, their product is a dimensionless number. For example if r = 15%/year and t = 14 days (or 14/365 years) then

Thus, we can think of eq. (9) as having 5 variables: P, A, B, n, and r·t. Given any 4 of them, we can solve for the 5^th. We don't have to use algebra to do it – Excel has 5 built-in functions to do it for us.

• To solve for P, use the PV function.
• To solve for A, use the PMT function.
• To solve for B, use the FV function.
• To solve for n, use the NPER function.
• To solve for r·t, use the RATE function.

Warning: In these functions you must treat inflows (money flowing into an account) as positive and outflows as negative, or vice versa.

---

Example 8: Suppose that we want to buy a house. The bank is offering to give us a $500,000 mortgage (loan for a house) at an interest rate of 6%/year, compounded monthly and with an amortization period (term) of 25 years. Let's answer the following questions using the 5 Excel functions:

• How big will the payments be?
  Answer: $3221.51.
  Use the formula =PMT(rt, n, P, B) in a cell,
  with rt=0.06*(1/12), n=25*12, P=−500000 and B=0.
  
  
• If we made bigger payments of $4000 every month, how quickly would the mortgage be paid off?
  Answer: 196.656 months or about 16½ years.
  Use the formula =NPER(rt, A, P, B) in a cell,
  with rt=0.06*(1/12), A=4000, P=−500000 and B=0.
  
  
• It is possible that the interest rates are going up. The most we can afford are monthly payments of $5000. At what interest rate would the payments be this high?
  Answer: 11.3%/year.
  Use the formula =RATE(n, A, P, B) in a cell,
  with n=25*12, A=5000, P=−500000 and B=0.
  Note that formula returns rt=0.009395, which must be multiplied by 1/t = 12/year to get r itself.
  
  
• The bank is willing to enter a contract to keep the rate fixed for 3 years, at which point they want the ability to renegotiate the mortgage. What is the amount still owing at that point?
  Answer: $471,618.72.
  Use the formula =FV(rt, n, A, P) in a cell,
  with rt=0.06*(1/12), n=3*12, A=3221.51 and P=−500000.
  
  
• After 3 years, how much will we have made in payments? And of that, how much will have gone to paying down the balance, and how much to paying interest?
  Answer: After 3 years we have made 36 payments of $3,221.51, or $115,974.25. From the previous bullet we know that the balance remaining on the loan is $471,618.72. Subtracting this from $500,000, we get that $28,381.28 of our payments went to paying down the balance, and the remaining $87,592.98 went to paying interest.
  
  

---

How Excel Handles Dates

A date is just a positive number formatted to look like a date. Day 1 is January 1, 1900; day 2 is January 2, 1900; and so on, all the way up day 2,958,465, which is December 31, 9999.

The time on a specific date is represented by the part of the number after the decimal point. Since a day is 24 hours long, 0.1 days is 2.4 hours or 2 hours and 24 minutes. And since the day is defined to start at midnight, the number 1.1 represents 2:24 AM on Jan 1, 1900. Try the following experiment in Excel. Type the number 1.1 in a cell and change its format to each of the following types. (The Number group in the Home tab has several ways to do this.)