Solved

Interest Rate of Uneven Series of Cash Flows

Posted on 2004-04-30
12
488 Views
Last Modified: 2006-11-17
Hello. I am a programmer (Visual Basic 6/.NET and C#) for a small finance company, and I'm trying to find a way to determine the interest rate of an UNEVEN series of cash flows. I can take the following values:

interest rate = 12%
Term = 12 months
Loan = $1000.00
Payment = $88.85

And use them to generate an amortization table. I can then use the ACTUAL payment history of the customer (which never matches an amortization table) to determine how much of each payment is interest, and how much of each payment is principal.

Where I am stuck is developing an iterative process for computing the interest rate of an UNEVEN series of cash flows, especially when issues like late fees are taken into account, to tell me the true yield of a transaction. For instance using the data above, and assuming that the loan is made on 5/1/04 with the first payment being due on 6/1/04, let's assume that this is the customer's ACTUAL payment history:

Date          Event          Amount
5/1/04       Loan           -1000
6/2/04       Payment      88.85
6/30/04     Payment      88.85
8/4/04       Payment      88.85
10/5/04     Payment      187.70 (two payments plus 10.00 late fee)
11/1/04     Payment      88.85
12/3/04     Payment      88.85
1/1/05       Payment      88.85
3/7/05       Payment      187.70 (two payments plus 10.00 late fee)
4/1/05       Payment      88.85
5/6/05       Payment      88.85

How would I use only the amount lent, and the event dates and amounts received to determine the interest rate yield of the cash flows? Using an amazing program (http://www.tvalue.com) I can calculate that the yield of that series of cash flows is 14.77634% (using normal interest, compounded monthly, and a 365 day year). But what I need is an algorithm so that I can do it in code and not in a third party program.

Other areas that this would probably answer are things like solving for the payment amount of a series of uneven cash flows. Say that the customer is a golf course and they only want to make payments from May-Oct. If the required yield is 12% and the equipment cost is $15,000 and they want a 36 month term (with payments of zero from Nov-April) how do you calculate the payment?? Again, using tvalue, I can do it easily... but I am looking for the concept of how to do it myself in code.

Please answer the question (preferably the first) in any way that you prefer. I can handle pseudo-code, vb, c++, java, c# or just about anything. I would like the flexibility of being able to switch compounding methods (exact days, daily, monthly, quarterly, annual, etc) and/or year length (360 or 365) and/or the compute method (normal US Amortization, Rule of 78, simple interest, etc) without too much effort.

Thanks in advance... I look forward to awarding 500 pts as quickly as possible.

Kevin


0
Comment
Question by:krsherm
  • 8
  • 2
12 Comments
 
LVL 10

Expert Comment

by:Mercantilum
ID: 10965733
Hmm I don't see what kind of interest can make a 14.77% from the values above ...
In the worst case (simple interest) 1000 / ( 88.85*12 ) is  6.62% ...
Compound interest calculated monthly would give  12th root of 1.0662 = 1.00535, or 0.535%
(without the 10$ late fees)
0
 

Author Comment

by:krsherm
ID: 10965774
Mercantilum,

You're not taking the time variable into account. Surely, receiving all of the payments on day 2 (88.85*12) would produce a yield substantially HIGHER than receiving all of the payments on day 365. And the yield on the cash flows above is equal to 14.77% it's greater than the original 12% because the increasing effect of the two late fees exceed the decreasing effect of the slowness of the payments. Thanks for giving it a go!
0
 
LVL 10

Expert Comment

by:Mercantilum
ID: 10965794
(actually  (88.85*12)/1000 in my prev calc. :)

Hu... how originally can you even get 12% ?
0
 

Author Comment

by:krsherm
ID: 10965855
Merc,

You're not taking TIME into account. My mortgage was for $176,000, and my monthly payment is $1,055.21 for 30 years. By your logic, the interest rate is equal to 360*1055.21/176000=215%. When actually, it's 6%.
0
 
LVL 10

Expert Comment

by:Mercantilum
ID: 10965939
Oh I see :) Will come back with a formula....
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 10

Accepted Solution

by:
Mercantilum earned 500 total points
ID: 10966667
( See also http://ray.met.fsu.edu/~bret/am_derive/derivation.html )

P being the principal (e.g. 176000)
I  being the yearly interest rate (e.g. 6% = 0.06)
Y being the number of years  (e.g. 30)
n being the number of payments yearly (e.g. 12)
N being total number of payments (e.g. Y*n)
i  interest for each payment, i = I/n e.g. 0.06/12 = 0.005
x being one payment  (e.g. a monthly payment)

You get

x = P * ( (1 - (1+i)) / (1 - (1+i)^N) + i)

E.g.

x = 176000 * ( (1-1.005)/(1-1.005^360) + 0.005)
   =  1055.2089


Now, if you know all variables, including x, but i ...

One way is to do an iterative method converging to i, looking for a x that you know.

e.g. you program a function return

  x =  getpayment (P, i, N)

You start with two rates variables low = 1.0 and high = 100.0 (limits of rates)
and an interative loop can be, looking for a 3 decimals rate

   do {
       i = low + high / 2.0;
       payment = getpayment (P, i, N);

       // payment less than means the i is too low
       // so let's set the lower bound to be i
       if (payment < x)        low  = i
       else if (payment > x)  high = i
    } while ( | payment - x | > 0.001 ) // |x| means absolute value of x
   
    print "I found the interest, it is : ", i

The loop is fast as you divide by 2 at each iteration, so roughly the number of loops is
  log2(100 / 0.001) = log2(100000) ~  17 loops only!

In my next reply, I'll provide you the C equivalent.
0
 
LVL 10

Expert Comment

by:Mercantilum
ID: 10966750
Here it is ; a few remarks

- low is to be 0 at first :)
- high does not need to be 100! (means 10000%), can be something like 2.
- I missed the parenthesis in i = (low+high)/2  it took me 10 mn to find out :)
- the number of loops depends on the payment calculation which is not linear on 'i',
   it is actually more than what I calculated before, it took 33 loops to get a
   'i' where payment is accurate up to 3 decimals (e.g.  1055.208 ),
   this is actually accurate. (did the calculus with high=100, with high=2,  24 loops)
- the 'i' calculated has to be multiplied by n (12) to get a yearly rate

The program in main gets values
- it first calculates a monthly payment from values in x
- then estimate the 'i' and *12 to get yearly rate

-------------(program.c)-----------
#include <stdio.h>
#include <math.h>

//  P being the principal (e.g. 176000)
//  I  being the yearly interest rate (e.g. 6% = 0.06)
//  Y being the number of years  (e.g. 30)
//  n being the number of payments yearly (e.g. 12)
// Below values are calculated
//  N being total number of payments (e.g. Y*n)
//  i  interest for each payment, i = I/n e.g. 0.06/12 = 0.005
//  x being one payment  (e.g. a monthly payment)


// Calculate the periodic payment

double getpayment (double P, double i, double N)
{
  return (P * ( (1.0 - (1.0+i)) / (1.0 - pow(1.0+i, N)) + i));
}


// Looking for the interest rate...
// while you know the period (monthly) payment

double iterativesearch (double payment, double P, double N)
{
  double high,low;
  double i,val;

  low  =   0.0;
  high =   2.0;

  do {
       i = (low + high) / 2.0;
       val = getpayment (P, i, N);

       // val less than payment means the i is too low
       // so let's set the lower bound to be i
       if      (val < payment)  low  = i;
       else if (val > payment)  high = i;

  } while ( fabs ( payment - val ) > 0.001 ); // fabs(x) means absolute value of x

  return (i);
}


int main ()
{
  double P = 176000.0;
  double I = 0.06;
  double Y = 30.0;
  double n = 12.0;

  double N,i,x;

  N = Y*n;
  i = I/n;

  // first let's calculate your monthly payment for the values above,
  // to check out formula and to get a x for the following search...

  x = getpayment (P, i, N);

  printf ("Monthly payment: %lf\n", x);

  // now look for i...

  i = iterativesearch (x, P, N);

  // this is the periodic i, i*n will give the yearly rate

  printf ("Calculated interest rate: %lf\n", i*n);
}
0
 
LVL 10

Expert Comment

by:Mercantilum
ID: 10966833
Late fees

To come back to your initial question with the initial values being
  double P = 1000.0;
  double I = 0.12;
  double Y = 1.0;
  double n = 12.0;

If you know I (or i = I/12), you get the monthly payment as 88.848789

If you don't know I, the iterative search gives 0.120000  (12%)

   (  accuracy set to 0.0001:  while ( fabs ( payment - val ) > 0.0001 );    )

Correct me if I'm wrong but the late fees don't interfer with the payment. As it is agreed at the beginning between the bank and the client that he will pay x $ per month. However he may pay late fee, e.g. 10$ per month, while the payment for the period, including late months (according to your data above) will be (x being the known monthly payment)

    x + latemonths*(x + 10)

The first x being the payment for the month, he has to pay the  x  he didn't pay for the late months, plus 10$ per late month.

Next about uneven cash flows...
0
 
LVL 10

Expert Comment

by:Mercantilum
ID: 10972818
About uneven periods of time payments.

A formula can be hardly found on the Internet, so I made mine.

It is based on the sum of N payments being equal to principal + interests
   Nx = P + sum j from 1 to N of Ij
And the part of principal in x for period j is
   Pj = x - Ij
Provided that, ij the rate for the period j
   Ij = ij(P - P1 - P2 - ... - Pj-1)
So
   P1 = x - i1
   P2 = x - i2(P - P1)
   ...
   Pj = x - ij(P - P1 - P2 - ... -Pj-1)

I chose to represent a date of payment as a fraction of a year, since it works in any case (day, month...)

e.g  1.0 = 1 year, 1/12 = 1 month, 1/52 a week, 2.5 for 2 years and a half


To find x, payment, the algorithm comes as, I is the yearly rate (e.g 0.10)

   Sx = S = 0.0;  // Sx is the sum of 'x', S scalar value

   Loop N times
   {
       r  = (date_period - date_previous_period)  * I;
       ax = 1.0 + r*Sx;
       b  = -r*(P - S);
       Sx += ax;
       S  += b;
   }

   Get x = ((P - S)/Sx);


In the next message, I'll put the test program I did.
It includes a 2 year months periods in an array (dates[]), as it is easy to check against the first formula for static periods.

Functions: (common financial variables like N, P, Y, I are global)

- double getpayment ()
   returns x for static periods of time

- double unevenperiod ()
   returns x for variable periods of time given in array dates[]
   the macro PERIODS gives the number of items

- double iterativesearch (double payment, int method)
   calculates the rate i from iterations, it is O(log n) based on given payment
   method:
   1,  means use static periods of time (call getpayment())
   2,  means use variable periods of time (call unevenperiod())

- main

Output for 1000$, 12%, 2 years (24 months)

Monthly payment:                       47.073472
Calculated interest rate:               0.120000
Uneven period payment:              47.073472
Calculated interest rate (uneven):  0.120000

Next: test program
0
 
LVL 10

Expert Comment

by:Mercantilum
ID: 10972821
#include <stdio.h>
#include <math.h>

//  P being the principal (e.g. 176000)
//  I  being the yearly interest rate (e.g. 6% = 0.06)
//  Y being the number of years  (e.g. 30)
//  n being the number of payments yearly (e.g. 12)
// Below values are calculated
//  N being total number of payments (e.g. Y*n)
//  i  interest for each payment, i = I/n e.g. 0.06/12 = 0.005
//  x being one payment  (e.g. a monthly payment)


// made globals for multiple iterative search

double P = 1000.0;
double I = 0.12;
double Y = 2.0;
double n = 12.0;

double N,i,x; // to be calculated


// dates for uneven periods of payments

#define PERIODS  24

double dates[] = { 1.0/12.0, 2.0/12.0, 3.0/12.0,  4.0/12.0,  5.0/12.0,  6.0/12.0,
                   7.0/12.0, 8.0/12.0, 9.0/12.0, 10.0/12.0, 11.0/12.0, 12.0/12.0,
                   13.0/12.0, 14.0/12.0, 15.0/12.0, 16.0/12.0, 17.0/12.0, 18.0/12.0,
                   19.0/12.0, 20.0/12.0, 21.0/12.0, 22.0/12.0, 23.0/12.0, 24.0/12.0 };


// Calculate the (static) periodic payment

double getpayment ()
{
  return (P * ( (1.0 - (1.0+i)) / (1.0 - pow(1.0+i, N)) + i));
}


// Uneven periods, dates are fractions of year in dates[]
// e.g 1/12 for a month, 1/365 for a day, 2.0 for 2 years...
// return the equal payement to be done for each date
// Note that we use yearly rate I
// 'number' is number of dates
// Based on Nx = P + sum Ij = Nx - sum Pj <=> P = sum Pj

double unevenperiod ()
{
  double ax,b,r;
  double Sx,S;
  int z;

  Sx = S = 0.0; // sum for x Sx and for scalars S

  // calculate sum(Pi) in (Sx,S) as Sx*x + S

  for (z=0 ; z<PERIODS ; z++)
  {
    r  = (dates[z] - (z ? dates[z-1]:0)) * i*n; // i*n is I
    ax = 1.0 + r*Sx;
    b  = -r*(P - S);
    Sx += ax;
    S  += b;
  }
  return ((P - S)/Sx);
}


// Looking for the interest rate...
// while you know the period (monthly) payment
// method = 1: static period  payments
// method = 2: uneven periods payments

double iterativesearch (double payment, int method)
{
  double high,low;
  double val;

  low  =   0.0;
  high =   2.0;

  do {
       i = (low + high) / 2.0;

       switch (method) {
         case 1: val = getpayment () ; break;
         case 2: val = unevenperiod () ; break;
       }
       // val less than payment means the i is too low
       // so let's set the lower bound to be i
       if      (val < payment)  low  = i;
       else if (val > payment)  high = i;

  } while ( fabs ( payment - val ) > 0.0001 ); // fabs(x) means absolute value of x

  return (i);
}


// main

int main ()
{

  N = Y*n;
  i = I/n;

  // first let's calculate your monthly payment for the values above,
  // to check out formula and to get a x for the following search...

  x = getpayment ();

  printf ("Monthly payment: %lf\n", x);

  // now look for i...

  i = iterativesearch (x, 1); // search with getpayment function

  // this is the periodic i, i*n will give the yearly rate

  printf ("Calculated interest rate: %lf\n", i*n);


  // uneven periods

  x = unevenperiod ();

  printf ("Uneven period payment: %lf\n", x);

  // now look for i...

  i = iterativesearch (x, 2); // search with unevenpayment function

  // this is the periodic i, i*n will give the yearly rate

  printf ("Calculated interest rate (uneven): %lf\n", i*n);
}
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Have you ever thought of installing a power system that generates solar electricity to power your house? Some may say yes, while others may tell me no. But have you noticed that people around you are now considering installing such systems in their …
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now