4

How can I calculate a fixed payment amount for a loan term that has two different interest rates based on how long the loan has been open?

Michael Currie
  • 13,721
  • 9
  • 42
  • 58
mdvaldosta
  • 291
  • 2
  • 6
  • 19
  • 2
    Think you need to explain it a bit more maybe with an example – David Feb 05 '10 at 13:31
  • The interest rate for a 12 month loan of $1000 will be 2% per month for the first 3 months, and 1% per month for the remaining months. What will the monthly payment be? – mdvaldosta Feb 05 '10 at 14:17

4 Answers4

5

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
  • 1,997
  • 1
  • 16
  • 17
  • You've got my attention Johann, but where's the orgininal loan amount in the equation? – mdvaldosta Feb 05 '10 at 18:55
  • 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 Feb 05 '10 at 20:36
  • 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 Feb 08 '10 at 16:55
  • 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 Feb 09 '10 at 02:41
  • I can confirm this is correct - derived [here](http://money.stackexchange.com/a/61658/11768). – Chris Degnen Mar 15 '16 at 16:14
3

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.

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
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.

NotMe
  • 87,343
  • 27
  • 171
  • 245
0

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:

  1. What is the base rate? Does the loan float over Prime? Libor? CMT?
  2. What is the margin above the base rate?
  3. How often does the base rate reset?
  4. What happens if the reset date falls on a holiday? A weekend?
  5. Are there ceilings or floors on the base rate?
  6. Is there an initial period at which the base rate is fixed before the first reset? How long is that period?
  7. Is there an initial discount on the margin that is later adjusted (a teaser rate)?
  8. What's the term of the mortgage?
  9. Is it a negative-amortization mortgate? What's the stop period on the negative-amortizing payments?
  10. Is it a fully-amortizing mortgage?
  11. Is it a balloon mortgage?
  12. 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
  • 236,483
  • 35
  • 423
  • 525
  • 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 Feb 05 '10 at 14:21
  • @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 Feb 05 '10 at 14:35
  • 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 Feb 05 '10 at 14:47
  • Then just run the calculation over the first three months at one rate, then change the rate and run it over the remaining period... – NotMe Feb 05 '10 at 16:38
  • 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 Feb 05 '10 at 18:56
0

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
  • 2,488
  • 3
  • 23
  • 32