### Interest and Loans in Excel

In this web page we describe the math behind compound interest and periodic payments, and derive formulas for them. We also show how to use the financial functions that are built into Excel that do the same job. But the unique thing about using a spreadsheet to do the calculations is that we control everything: the payments, the payment schedule, lump sum payments, and more.

You can download a spreadsheet containing all the examples in this web page here.

**Contents of this page**

- Simple Interest
- Compound Interest Spreadsheet
- Compound Interest Formula
- Excel's FV (Future Value) Function
- Periodic Payments Spreadsheet
- Periodic Payments Formula
- Excel's Financial Functions (PMT, NPER, RATE, PV and FV)
- How Excel Handles Dates

#### 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:

- Break the term of the loan into a number of equal-time periods.
- 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.
- 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*. - 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).

**Explanation of the cells.**

- Row 1 contains headings. To force cells D1 and E1 to be text, not arithmetic,
we must start them with a single-quote character, like this:
`'+interest`

, and this:`'=closing balance`

. - Row 2 describes what happens in the first period, the month of January.
- Columns A and B
*describe*the period (month). Column A uses text and column B uses a number. - Column C implements step 2 of the algorithm: describing the opening balance. For January, the opening balance is the principal. For February, the opening balance is the closing balance from January.
- Column D implements step 3 of the algorithm: calculating simple interest for the period (month). Here is the interest for January: (Note that using 1/12 year for the period is only an approximation. January has 31 days while February has 28, and the year could be a leap year.
- Column E implements step 4 of the algorithm: the opening balance and the interest are added to get the closing balance.

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

- 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.
- 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.
- 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

*I = P r t*= $1 * 0.20/yr * 20yr = $4

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:

*B = P*(1 +

*r t*)

^{ n}

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:

- the product
*r·t* - the number of periods,
*n* - 0 (for now. This argument is used for periodic payments, described later.)
- 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^{5}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**:

- Break the term of the loan into a number of equal-time periods.
- 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.
- 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*. - 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:

- If we insist on payments of $200 then
**how many**payments will there have to be? - 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.)

**Explanation of the cells.**

- The cells with a yellow background contain the
**parameters**of the loan. For example, cell B2 contains the periodic payment amount. We will want to adjust this number to answer our second question. It is better to put this value in one cell, and then have other cells refer to this cell.

Similarly, cell B1 contains the interest rate and cell C4 contains the amount of the loan, or principal. We may want to change these values at some point as well. - Row 4 describes what happens in the first two-week period (or biweek), the one that starts on May 28.
- Columns A and B
*describe*the biweek. Column A is the biweek number and Column B is the starting date for the biweek. Notice that cell B5 simply adds 14 days to get the starting date for the next biweek. - Column C implements step 2 of the algorithm: describing the opening balance. For the first biweek, the opening balance is the principal. For the second biweek, the opening balance is the closing balance from the first biweek.
- Column D implements step 3 of the algorithm: calculating simple interest for the biweek.
- Column E is the payment for each biweek. Cell E4 refers to the amount in cell B2.
The nice thing about making the reference
**absolute**is that it is easy to copy cell E4 and make every cell in column E contain the same value.

The payment column is main new feature in this spreadsheet. We put the payment column to the right of the interest column to emphasize the fact that the payment is made at the end of the biweek. - Column F implements step 4 of the algorithm: we take the opening balance and add the interest and subtract the payment to get the closing balance.

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

- Select the range A4:A5 and drag the fill handle downward until the pop-up says 60.
- Select the range B5:C5 and double-click its fill handle. Columns B and C get extended to the same length as column A.
- 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) |

We should have guessed eq. (4). Suppose that **every** sum of money
grows by the factor 1 + *r t* every period.
At the end of *n* periods the **principal** has grown to the value
*P* (1 + *r t*)^{ n}.
This is the first term on the right-hand-side.

The **first payment**, which was made at the *end of the first period*,
has grown for *n* − 1 periods and has value
*A* (1 + *r t*)^{ n−1}.
This is the first underlined term.

Each subsequent payment has grown for one less period than the one before.
The **final payment** was just made and has not grown at all. It is the last
underlined term.

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).

- 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(`

in a cell,*rt*,*n*,*P*,*B*)

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(`

in a cell,*rt*,*A*,*P*,*B*)

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(`

in a cell,*n*,*A*,*P*,*B*)

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(`

in a cell,*rt*,*n*,*A*,*P*)

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.)

Format | Display |
---|---|

General | 1.1 |

Short Date | 1900-01-01 |

Long Date | January 1, 1900 |

Time | 2:24:00 AM |

Custom: yyyy-mm-dd h:mm AM/PM |
1900-01-01 2:24 AM |

Custom: yyyy-mm-dd hh:mm |
1900-01-01 02:24 |

The last two entries show how you can create your own custom formats.

If you type something like `5/28`

or `05-28`

into a cell, Excel assumes
that it is the date, May 28, **of the present year**.

If two cells contain dates and you subtract them, then you get the number of days between the dates.

If you want to fill a row of cells with **dates separated by a year, month,
day**, etc., then use the right mouse button to click and drag the fill handle
of the cell downward.
When you release the mouse a pop-up menu appears with all of
those options. This is very useful because different months and years have
different numbers of days.

**Example 9:** You may have wondered how accurate it was to use 1/12 year
for the period in the monthly compounding interest problem in
Example 3. To find out, let's redo that problem,
taking into account the length of each month and the
length of the year (in the case of a leap year).

Start a new sheet in Excel and fill in columns A to D. These
columns will describe the periods. Fill in the **month** column with the numbers
1 to 12. To set up the **opening dates** column,
type the day the account is opened in B2. (I typed in `2024-01-01`

.)
Interest is added on the first day of each month. To get Excel to produce this series,
right-click and drag B2's fill handle one cell farther than the end of column A.
From the pop-up menu choose “Fill Months”. To set up the
**closing dates** column use the fact that the closing date of each month equals
the opening date of the following month. (So cell C2 is `=B3`

, etc.)
The **days** column holds the number of days between the opening and closing date.
(So cell D2 is `=C2-B2`

, etc.) At the end of column D, in cell D14,
put the formula `=SUM(D2:D13)`

to get the number of days in the year.
Notice that 2024 is a leap year.

Now fill in the cells in columns E, F and G as shown here:

**Explanation of the cells.**

- Column E implements step 2 of the compound interest algorithm: describing the opening balance. For the first month, the opening balance is the principal. For the second month, the opening balance is the closing balance from the first month.
- Column F implements step 3 of the algorithm: calculating simple interest for the month. Notice that the time period is circled in red. It is the length of the month divided by the length of the year. Notice the absolute reference to the year. This will allow us to copy cell F2 correctly.
- Column G implements step 4 of the algorithm: we add the interest to the opening balance to get the closing balance.

Now we can use the fill handle to fill in the rest of the spreadsheet. Select cell E3 and double-click its fill handle. Select the range F2:G2 and double-click its fill handle. The spreadsheet is complete.

Notice that the final balance is $112.68247. For comparison, the formula

`= FV( 0.12*(1/12), 12, 0, -100)`

gives $112.68250.

A tiny difference, indeed!

If you would like to leave a comment or ask a question please send me an email!