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
