Newton


SoMangHills
Galileo


Mechanics

Chapter 2

Irregular Repayments toward Loans - Irregular Returns from Investment - Continuous Compounding


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.



From the purchaser's point of view he borrowed $1,000.00.  From the lender's point of view he invested $1,000.00
          
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.

A Method

As a borrower assume an interest rate to be applied to the outstanding balance at each payment instance. At each payment instance subtract the payment and the amount of interest due from the past outstanding amount to obtain the new outstanding amount. 

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 loan was not paid off when that interest rate was assumed)  - decrease it and try again; if the amount is negative the assumed rate was too low - increase it and try again.

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

For N intervals the amount is:

                               A = (1 + i) N

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 =  $1,100.00 then too

 $1,000.00  * (1+ i) 365 =    $1,100.00 and this implies that:
 
                     (1+ i) 365 = 1.10 or equivalently i = 1.1 (1 / 365) -1 or     

                     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)72  = $1,018.98

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

Compounding and e

If we take i as some nominal rate of interest for a period P and divide the period into two equal parts we could apply the rate i / 2 to each part of the period. Then the effective rate r for the period would become:

   r = (1 + i / 2) 2 -1) = (1 + i +( i 2 / 4) -1), a value greater than i

More generally, with n = parts:

    r = (1 + i / n) n -1

As n is made indefinitely large the expression approaches:

       r = e i -1      

                 [Hint: Construct a spreadsheet to make this observation.]
 
The constant e, 2.71828 ....., has cropped up again. In the previous Topic we found it as a limit value in an equation and as a constant in the solution of a differential equation that applied to the cooling of a warm body.

 
Top Previous Topic Topics Next Chapter