Calculating Effective Interest Rates Using Cashflow Discounting
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:
- formulate a guess
- check the guess using the discounting formula
- 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
- Example Spreadsheet: Excel 2007
- Python Source Code: XIRR EIR interest Rate Calculator Example
This has been a great series of posts, keep up the good work.
Neil