• Status: Solved
• Priority: Medium
• Security: Public
• Views: 609

# Interest Rate of Uneven Series of Cash Flows

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
krsherm
• 8
• 2
1 Solution

Commented:
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 Commented:
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

Commented:
(actually  (88.85*12)/1000 in my prev calc. :)

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

Author Commented:
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

Commented:
Oh I see :) Will come back with a formula....
0

Commented:

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

Commented:
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

Commented:
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

Commented:
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

Commented:
#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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 8
• 2
Tackle projects and never again get stuck behind a technical roadblock.