Need this for a c# app, can't find the formula anywhere nor can I wrap my head around it. Any Algebra gurus out there in overflow land? Basically, I need to calculate a fixed payment amount for a loan term that has two different interest rates based on how long the loan has been open.
A:
Loan contracts are very complex. If you don't want to dive into the complexity you have to make some simplifying assumptions. Here are some of the variables you need to consider:
- What is the base rate? Does the loan float over Prime? Libor? CMT?
- What is the margin above the base rate?
- How often does the base rate reset?
- What happens if the reset date falls on a holiday? A weekend?
- Are there ceilings or floors on the base rate?
- Is there an initial period at which the base rate is fixed before the first reset? How long is that period?
- Is there an initial discount on the margin that is later adjusted (a teaser rate)?
- What's the term of the mortgage?
- Is it a negative-amortization mortgate? What's the stop period on the negative-amortizing payments?
- Is it a fully-amortizing mortgage?
- Is it a balloon mortgage?
- Is the interest simple interest or compounded interest? If the latter, what's the compounding frequency?
As you can see, if you haven't specified enough about the problem that you are trying to solve to even begin to come up with a solution.
If you're not a domain expert on ARMs or financial products in general I strongly encourage you to find someone who is.
Jason
2010-02-05 14:15:03
I'm not an expert on financial products, although this is just a simple calculator to find the payment required to payoff a loan with an interest rate that decreases after a set amount of time. I figured that finding the formula for an ARM would give me something I could modify to suit this specific need. I posted the specifics of the problem above in a comment reply.
mdvaldosta
2010-02-05 14:21:22
@mdvaldosta: I see your comment. First, that is not an ARM as ARMs reset periodically. Second, you still have not given enough information to approach your problem. Are those fixed rates or are they floating over a base rate? What's the interest calculation (simple vs. compounding, if the latter what's the compounding frequency).
Jason
2010-02-05 14:35:47
This is a fixed rate and it's a pretty simple loan scenario. It's a basic, simple loan at a fixed interest rate. Just like a car loan. The only difference, is that for the first 3 months the interest rate is higher. So there are two interest rates, one for the first 3 months and another for all months after that.
mdvaldosta
2010-02-05 14:47:24
Then just run the calculation over the first three months at one rate, then change the rate and run it over the remaining period...
Chris Lively
2010-02-05 16:38:25
I only wish it were that simple Chris. What you propose would be easy enough to implement if I had the payment amound and wanted to figure out how many payments would be necessary, but figuring out the payment amount based on a fixed number of payments isn't so easy.
mdvaldosta
2010-02-05 18:56:45
A:
The pmt function is based on this math: Payment = Loan Amount at current time / ( 1 - ( 1 / ( 1+ current rate)^numperiods remaining ) )
Figuring out the loan amount at the current time (i.e. after five years of making a payment at a different rate) is the tough part.
Summer
2010-02-05 14:24:48
+3
A:
This is a pretty complicated calculation that is usually part of a company's intellectual property. So I doubt anyone is going to post code. I've been down this road and it requires huge amounts of testing depending on how far you decide to go with it.
You might want to check out the following document. It has a lot of math on how to get there.
Constructing an Amortization Schedule
edit:
Just to add couple things. When performing the calculations in code it is critical that you use a data type such as Decimal instead of the floating point types like double. Decimal was explicitly created for these types of money calculations. Floating point types will cause many rounding errors, making the calculated values be off by unacceptable amounts.
Next, mortgage calculators that you find online are of highly varying quality. When testing your method it will be useful to see what the online calculators come up with, but by no means consider them more accurate than yours. Generally they are good to see if you are in the right ballpark, but they could be off by as much as .1% per year of the loan term.
Final note
Okay, this is the last edit. You might consider purchasing a library from a company like Math Corp instead of rolling your own. I'm pretty sure it'll be accurate AND much cheaper than the dev / qa time to get yours right.
Chris Lively
2010-02-05 14:27:02
+1 for suggesting to use an existing service and talking about purchase cost versus development cost.
Pierre-Alain Vigeant
2010-02-05 14:43:42
Good point on using decimal rather than double or float, as a hobby programmer any advise is greatly appreciated.
mdvaldosta
2010-02-05 19:32:57
A:
This gets a little ugly, so please bear with me.
Define:
- g1 = Initial monthly rate (For 3%, g=0.03/12.)
- g2 = Second monthly rate.
- T1 = Term for the initial rate (T1 = 3 for 3 months).
- T2 = Term for the subsequent rate.
- u1 = 1 / (1 + g1)
- u2 = 1 / (1 + g2)
Then:
- payment = g1 * g2 / (g1 * u1^T1 * (1 - u2^T2) + g2 * (1 - u1^T1))
Of course, I may have made a mistake, but that seems right.
Johann Hibschman
2010-02-05 17:08:54
You've got my attention Johann, but where's the orgininal loan amount in the equation?
mdvaldosta
2010-02-05 18:55:19
Ah, sorry. That's not clear. The "payment" is expressed as a fraction of the original loan amount, so just multiply that value by the loan amount. (If you're wondering, I got that expression by plugging the balance left after T1 (given a pmt) into the usual amortizing payment formula (for r2/T2) and solving for the payment.)
Johann Hibschman
2010-02-05 20:36:46
Awsome Johann, that's got it. Only thing is g1 and g2 would be .03 rather than .03/12. I owe you a beer.
mdvaldosta
2010-02-08 16:55:04
Glad it helped. And, yeah, I missed that your rates were "1% per month" when I first read it. You only divide by 12 if the rates are X% per year.
Johann Hibschman
2010-02-09 02:41:22