Hands-On Math

Last Revision March, 2011

Last Revision March, 2011

There are straightforward expressions and tables available to calculate compounded amounts and compound interest rates related to a series of payments or repayments that are made at regularly spaced intervals. See Wikipedia as an example.

Not so when the payments occur at irregularly spaced intervals. As an example consider borrowing at a daily interest rate for a period not to exceed 1 year. You make repayments at irregular intervals as shown next.

On August 7/2008 you check with the lender for the amount outstanding and pay off the loan.

What was the daily or annual rate of interest that you paid or, equivalently, the rate of return that the lender earned on his investment? There is no direct expression that can be used to determine this but there is an iterative method that can be employed to estimate the rate as accurately as necessary.

At the instance of the final payment: if the new outstanding amount is zero the loan has been repaid; if the amount is positive then the assumed interest rate was too high, (

The lender may use the same computation method as used by the borrower but the lender knows the daily rate and can determine at a given date the outstanding amount of the loan.

The principal relationship required is that for the amount that $1.00 amounts to if compounded for N intervals at an interest rate of i percent per interval. The resulting amount A after the first interval is:

A = 1 * (1 + i)

After the second interval, as it is no longer $1.00 that was invested. For the second interval, the amount is:

A = 1 * (1 + i ) * (1 + i) or A = (1 + i)

For N intervals the amount is:

A = (1 + i)

We can relate an annual interest rate to an equivalent daily rate. Suppose that the interest paid on $1,000.00 borrowed for a 365-day period is $100.00. We would say that the annual rate of interest is 10%. What would be the equivalent daily interest rate i?

Given that:

$1,000.00 * (1+ 0.1)

$1,000.00 * (1+ i)

(1+ i)

i ~= 0.000261158 or 0.0261158%

Choosing this daily interest rate as an initial guess, we note that there was $1,000.00 of outstanding debt for a period of 72 days in which no payment had been made. The amount A of that debt at the end of 72 days is:

A = $1,000.00 * (1+ i)

The amount of interest I for this first period is:

I = A - $1,000.00 = $18.98

The first payment is made at this time and reduces the outstanding debt by $260.00 to $758.98.

Although the method can be completed by hand calculations it is much less tedious to employ a spreadsheet as is seen below. It can be seen there that the final balance is negative. This indicates that the initial guess of an annual interest rate of 10% was too low. If one were doing this by hand a higher rate should be tried next.

Excel provides the facility to display cell values in a variety of formats.

In this case cells B2:B7, cells D2:D7, H2:H7 and cells I3:I7 are formatted as currency; cells C2:C7 are formatted as dates; cells E2:E7 as numbers; and F2:G2 as percentages.

Clicking a cell address in the following table will display that cell's expression.

. | . |

D3 | E3 | G2 | H3 | I3 |

Excel provides a handy tool on the Tool menu called Goal Seek as is seen next.

When there is a cell value that can be adjusted to create a desired value in a different cell. Goal Seek will do this for you. Just provide the cell addresses and desired value and select the OK button. In this case the value in cell F2, annual interest rate, will be iteratively adjusted until the value in cell H7 becomes ~ 0.0. See next.

Select OK. We have found that the lender's target was an annualized return of 24.5%.

r = (1 + i / 2)

More generally, with n = parts:

r = (1 + i / n)

As n is made indefinitely large the expression approaches:

r = e