Notice: MFTransparency is now a defunct organization. Click here for further information.

Calculating Effective Interest Rates Using Cashflow Discounting

Published on October 26, 2010

 

by Tim Langeman

In a previous post, I described the technique that computer programs like Microsoft Excel use to calculate the XIRR (effective interest rate) as a very smart version of “guess and check.” The post on Newton’s Method described how the “guessing” part works, but it did not describe how the computer is able to finally verify when it has the correct EIR figure — the “check” part.

In today’s post, I’m going to describe the process that a computer program uses to generate a discounted cashflow, a method of calculating the value of a cashflow that uses the time-value of money. By adding up the discounted cashflows we are able to determine whether we have the correct EIR.

I’m going to start with a sample loan of $4,825.00 that was disbursed on the 28th of the month but is paid back on the 16th of the month every month for about a year. The fact that the disbursement date is not exactly one month prior to the first repayment means that a simple IRR formula can not be used for accurate results, and the calculation must take all the actual dates into account. The exact details are at right.

How to Check an EIR

The first step in the process is to come up with a guess. For my example, I’ll start with a guess of 30 percent. The next step is then to take that 30% rate and plug it into the discounting formula for each date of the loan:

Discounted cashflow = cashflow * (rate +1)^(-days/365)

This gives us the discounted value of the cashflow for each date period. We then add up all the discounted cashflows to produce a total. This total should be 0 if the rate is correct, indicating that discounted cashflow is equivalent to the nominal cashflow at the specified rate. If it is not 0, we guess again, until we get closer and closer to 0. (For our purposes we decided that 8 decimal places of accuracy is good enough!) Below, you will see that Newton’s method is able to guess the correct EIR in only 5 guesses!

About the Table of Iterations

The tables below list a series of payments, sorted by date. The second column is a calculation of how many days between the payment and original disbursement. The “cashflow” column refers to the total value of all disbursements and payments on that date. The “Discounted” column indicates the value of the cashflow after it is discounted by the discounting formula; and the final column illustrates how to plug the numbers into the discounting formula.

Iteration 1: 30%

  # DATE          # DAYS  CASHFLOW      DISCOUNTED    Formula: cf * (rate + 1)^(-days/365)
   --------------------------------------------------------------------------------------------
   0 2010-06-28      days  -4825.00  -4825.00000000 = -4825.00 * ((0.3000000000 + 1)^(-0 /365))
   1 2010-07-16   18 days     48.00     47.38295189 =    48.00 * ((0.3000000000 + 1)^(-18 /365))
   2 2010-08-16   49 days    492.00    474.97264481 =   492.00 * ((0.3000000000 + 1)^(-49 /365))
   3 2010-09-16   80 days    492.00    464.50588150 =   492.00 * ((0.3000000000 + 1)^(-80 /365))
   4 2010-10-16  110 days    492.00    454.59641896 =   492.00 * ((0.3000000000 + 1)^(-110 /365))
   5 2010-11-16  141 days    492.00    444.57867758 =   492.00 * ((0.3000000000 + 1)^(-141 /365))
   6 2010-12-16  171 days    492.00    435.09432889 =   492.00 * ((0.3000000000 + 1)^(-171 /365))
   7 2011-01-16  202 days    492.00    425.50634649 =   492.00 * ((0.3000000000 + 1)^(-202 /365))
   8 2011-02-16  233 days    492.00    416.12965025 =   492.00 * ((0.3000000000 + 1)^(-233 /365))
   9 2011-03-16  261 days    492.00    407.83810625 =   492.00 * ((0.3000000000 + 1)^(-261 /365))
  10 2011-04-16  292 days    492.00    398.85075724 =   492.00 * ((0.3000000000 + 1)^(-292 /365))
  11 2011-05-16  322 days    492.00    390.34193788 =   492.00 * ((0.3000000000 + 1)^(-322 /365))
  12 2011-06-16  353 days    492.00    381.74014425 =   492.00 * ((0.3000000000 + 1)^(-353 /365))
  13 2011-07-16  383 days    488.00    370.55898273 =   488.00 * ((0.3000000000 + 1)^(-383 /365))
                             total:    287.09682872

Iteration 2: 42.8 %

  # DATE          # DAYS  CASHFLOW      DISCOUNTED    Formula: cf * (rate + 1)^(-days/365)
   --------------------------------------------------------------------------------------------
   0 2010-06-28      days  -4825.00  -4825.00000000 = -4825.00 * ((0.4289338537 + 1)^(-0 /365))
   1 2010-07-16   18 days     48.00     47.16249843 =    48.00 * ((0.4289338537 + 1)^(-18 /365))
   2 2010-08-16   49 days    492.00    468.98100804 =   492.00 * ((0.4289338537 + 1)^(-49 /365))
   3 2010-09-16   80 days    492.00    454.97741867 =   492.00 * ((0.4289338537 + 1)^(-80 /365))
   4 2010-10-16  110 days    492.00    441.82381340 =   492.00 * ((0.4289338537 + 1)^(-110 /365))
   5 2010-11-16  141 days    492.00    428.63112723 =   492.00 * ((0.4289338537 + 1)^(-141 /365))
   6 2010-12-16  171 days    492.00    416.23920530 =   492.00 * ((0.4289338537 + 1)^(-171 /365))
   7 2011-01-16  202 days    492.00    403.81046552 =   492.00 * ((0.4289338537 + 1)^(-202 /365))
   8 2011-02-16  233 days    492.00    391.75284304 =   492.00 * ((0.4289338537 + 1)^(-233 /365))
   9 2011-03-16  261 days    492.00    381.17184633 =   492.00 * ((0.4289338537 + 1)^(-261 /365))
  10 2011-04-16  292 days    492.00    369.79020416 =   492.00 * ((0.4289338537 + 1)^(-292 /365))
  11 2011-05-16  322 days    492.00    359.09940022 =   492.00 * ((0.4289338537 + 1)^(-322 /365))
  12 2011-06-16  353 days    492.00    348.37683266 =   492.00 * ((0.4289338537 + 1)^(-353 /365))
  13 2011-07-16  383 days    488.00    335.55465113 =   488.00 * ((0.4289338537 + 1)^(-383 /365))
                             total:     22.37131413

Iteration 3: 44.07%

  # DATE          # DAYS  CASHFLOW      DISCOUNTED    Formula: cf * (rate + 1)^(-days/365)
   --------------------------------------------------------------------------------------------
   0 2010-06-28      days  -4825.00  -4825.00000000 = -4825.00 * ((0.4407450200 + 1)^(-0 /365))
   1 2010-07-16   18 days     48.00     47.14335675 =    48.00 * ((0.4407450200 + 1)^(-18 /365))
   2 2010-08-16   49 days    492.00    468.46303106 =   492.00 * ((0.4407450200 + 1)^(-49 /365))
   3 2010-09-16   80 days    492.00    454.15728021 =   492.00 * ((0.4407450200 + 1)^(-80 /365))
   4 2010-10-16  110 days    492.00    440.72909533 =   492.00 * ((0.4407450200 + 1)^(-110 /365))
   5 2010-11-16  141 days    492.00    427.27027316 =   492.00 * ((0.4407450200 + 1)^(-141 /365))
   6 2010-12-16  171 days    492.00    414.63706332 =   492.00 * ((0.4407450200 + 1)^(-171 /365))
   7 2011-01-16  202 days    492.00    401.97502997 =   492.00 * ((0.4407450200 + 1)^(-202 /365))
   8 2011-02-16  233 days    492.00    389.69966512 =   492.00 * ((0.4407450200 + 1)^(-233 /365))
   9 2011-03-16  261 days    492.00    378.93475953 =   492.00 * ((0.4407450200 + 1)^(-261 /365))
  10 2011-04-16  292 days    492.00    367.36298994 =   492.00 * ((0.4407450200 + 1)^(-292 /365))
  11 2011-05-16  322 days    492.00    356.50107412 =   492.00 * ((0.4407450200 + 1)^(-322 /365))
  12 2011-06-16  353 days    492.00    345.61437612 =   492.00 * ((0.4407450200 + 1)^(-353 /365))
  13 2011-07-16  383 days    488.00    332.66871441 =   488.00 * ((0.4407450200 + 1)^(-383 /365))
                             total:      0.15670905

Iteration 4: 44.0828927%

   # DATE          # DAYS  CASHFLOW      DISCOUNTED    Formula: cf * (rate + 1)^(-days/365)
   --------------------------------------------------------------------------------------------
   0 2010-06-28      days  -4825.00  -4825.00000000 = -4825.00 * ((0.4408289273 + 1)^(-0 /365))
   1 2010-07-16   18 days     48.00     47.14322135 =    48.00 * ((0.4408289273 + 1)^(-18 /365))
   2 2010-08-16   49 days    492.00    468.45936857 =   492.00 * ((0.4408289273 + 1)^(-49 /365))
   3 2010-09-16   80 days    492.00    454.15148325 =   492.00 * ((0.4408289273 + 1)^(-80 /365))
   4 2010-10-16  110 days    492.00    440.72136020 =   492.00 * ((0.4408289273 + 1)^(-110 /365))
   5 2010-11-16  141 days    492.00    427.26066094 =   492.00 * ((0.4408289273 + 1)^(-141 /365))
   6 2010-12-16  171 days    492.00    414.62575064 =   492.00 * ((0.4408289273 + 1)^(-171 /365))
   7 2011-01-16  202 days    492.00    401.96207459 =   492.00 * ((0.4408289273 + 1)^(-202 /365))
   8 2011-02-16  233 days    492.00    389.68517791 =   492.00 * ((0.4408289273 + 1)^(-233 /365))
   9 2011-03-16  261 days    492.00    378.91897968 =   492.00 * ((0.4408289273 + 1)^(-261 /365))
  10 2011-04-16  292 days    492.00    367.34587501 =   492.00 * ((0.4408289273 + 1)^(-292 /365))
  11 2011-05-16  322 days    492.00    356.48275888 =   492.00 * ((0.4408289273 + 1)^(-322 /365))
  12 2011-06-16  353 days    492.00    345.59491081 =   492.00 * ((0.4408289273 + 1)^(-353 /365))
  13 2011-07-16  383 days    488.00    332.64838595 =   488.00 * ((0.4408289273 + 1)^(-383 /365))
                             total:      0.00000779

Iteration 5: 44.0828931%

   # DATE          # DAYS  CASHFLOW      DISCOUNTED    Formula: cf * (rate + 1)^(-days/365)
   --------------------------------------------------------------------------------------------
   0 2010-06-28      days  -4825.00  -4825.00000000 = -4825.00 * ((0.4408289314 + 1)^(-0 /365))
   1 2010-07-16   18 days     48.00     47.14322135 =    48.00 * ((0.4408289314 + 1)^(-18 /365))
   2 2010-08-16   49 days    492.00    468.45936839 =   492.00 * ((0.4408289314 + 1)^(-49 /365))
   3 2010-09-16   80 days    492.00    454.15148296 =   492.00 * ((0.4408289314 + 1)^(-80 /365))
   4 2010-10-16  110 days    492.00    440.72135982 =   492.00 * ((0.4408289314 + 1)^(-110 /365))
   5 2010-11-16  141 days    492.00    427.26066046 =   492.00 * ((0.4408289314 + 1)^(-141 /365))
   6 2010-12-16  171 days    492.00    414.62575008 =   492.00 * ((0.4408289314 + 1)^(-171 /365))
   7 2011-01-16  202 days    492.00    401.96207394 =   492.00 * ((0.4408289314 + 1)^(-202 /365))
   8 2011-02-16  233 days    492.00    389.68517719 =   492.00 * ((0.4408289314 + 1)^(-233 /365))
   9 2011-03-16  261 days    492.00    378.91897890 =   492.00 * ((0.4408289314 + 1)^(-261 /365))
  10 2011-04-16  292 days    492.00    367.34587416 =   492.00 * ((0.4408289314 + 1)^(-292 /365))
  11 2011-05-16  322 days    492.00    356.48275797 =   492.00 * ((0.4408289314 + 1)^(-322 /365))
  12 2011-06-16  353 days    492.00    345.59490985 =   492.00 * ((0.4408289314 + 1)^(-353 /365))
  13 2011-07-16  383 days    488.00    332.64838494 =   488.00 * ((0.4408289314 + 1)^(-383 /365))
                             total:      0.00000000

Summary

As you can see, the process of generating the exact EIR, is fairly simple:

  1. formulate a guess
  2. check the guess using the discounting formula
  3. repeat until the discounted cashflow equals zero

Knowing this procedure allows us to calculate any EIR, for any loan, no matter what the breakdown of fees, and no matter how irregularly spaced the payment dates.

If you are designing your own EIR calculator, you don’t have to use Newton’s method. Any “guess and check” technique will work, even if it requires additional guesses. The key part is to discount your cashflow using the formula:

discounted cashflow = cashflow * (rate + 1)^(-days/365)

A Note about APR

This technique is used to calculate the EIR very exactly. The APR formula is simpler, and does not perform this type of cashflow discounting. APR is technically less accurate than EIR, but in many cases it is still a useful way of communicating the price of a loan. For more information on the IRR formula used to calculate the APR, see “Calculating Interest Rates with Excel“.

Further Information

 

One Comment

  1. Neil Jensen says:

    This has been a great series of posts, keep up the good work.

    Neil