Re: HELP: Solver routine for original principal loan amount? Message #43 Posted by Phil K on 24 Mar 2009, 2:40 a.m., in response to message #42 by Don Shepherd
Gentlemen,
I have also been summoning the support from another forum, namely Google's Sci.Math forum, and more specifically this thread (http://groups.google.com/group/sci.math/browse_thread/thread/1a1f7c6552e53b1c/01d8091cca8db871?hl=en#01d8091cca8db871).
There are some helpful things there as well. I am still not totally convinced that finding both the original loan amount and interest rate from only a payment amount and midstream balance, assuming a standard 30/360 loan term and a guess for the rate is impossible. I agree that there may be an infinite number of solutions, but it seems to me that those infinite number of solutions would be at such varied interest rates (and carried out to so many decimal places) that most of those rate solutions would easily be ruled out from being a viable lending rate, considering lending rates are either whole numbers or fall no longer than 3 decimal places and they are often divisions of 8ths (i.e. 8 3/8 or 8.376), though rarely to 16ths or 32nds.
For instance, suppose that we use a simple $100,000 loan at 8% over 360 periods, 30 years. It works out to $733.76 per month. Now if someone gave me a payment of $733.76 (PMT) from their statement, and they could tell me that the balance on that statement is $93,608.47 (FV), and that they believe they bought the home about 6 years ago, I could guess 72 for the payments, and could also guess the term to be 30/360. Given that, I would use 72 as N. Now I need either an original loan amount or an interest rate. So again, I guess perhaps 7.5% (I%). Let's see what it produces for initial loan amount (PV).
The result for PV is $102,209.17. Now if I reverse the process using that as an initial loan amount and make it a full 360 month term, and with a future value of 0, what's the payment amount? Well, it falls off the charts at $714.66. So now I know that the original principal amount is too high, or the rate is too low (or both). So I adjust the rate up to get a higher payment...well, 7.625% gives me $723.43 (too low), and 7.75% gives me $732.24 (still too low), and 7.875% gives me $741.09 (too high).
So I suspect the original principal balance is wrong because it doesn't jibe with a standard % rate of 8ths, but I also know the closest rate was 7.75%. Let's look at the remaining balances around 72 payments...well, from 69 through 77 payments all result in remaining balances well above the known $93,608.47, in the $95,000 range. So I now suspect the original principal balance is considerably too high resulting in balances at around 72 payments well in excess of what the known balance is.
So I try to work with a standard rate of 7.75% which is my new best guess, and let's see what it gives me as a initial loan amount using the known payment amount. Well this time it results in an even higher original loan amount of $102,421.48, so I now know the rate is too low. So I push the rate up to 7.875 and let it bring the loan amount down. Now it results in $101,198.59. So let's see the payment stream's effect on the remaining balance at near 72 payments...at 70 payments, it's $95,035, so that's still too high, and at 74 payments, it is still $94,478.12. At 82 payments, it's $93,666.04, and at 83 payments, it's $93,546.96, so I know it is still too high of an original loan principal amount. So let's push up the interest rate another 1/8% to bump down the original loan amount and let's see what we get.
Now, at 8%, the result is $99,999.38. Let's see what 72 payments look like...$93,824.72, too high, but real close...how about 73 payments...now it's $93,716.46, almost there...at 74 payments, it's $93,607.48, only $0.99 away. I now know the rate is essentially dead on, and that the borrower has mad 74 payments, not 72. It's just the loan amount that's off, and by how much?, how about rounding the loan to $99,999.00? Well that would push the rate to a higher number by about 4/100,000 and that's obviously not a legitimate lending rate, and pushes the balance at 74 payments to a lower number, $93,607.18, 30 cents less and not what we want. SO if I round UP to $100,000 and push the rate back to 8% flat, what is the result for the payment amount? $733.76, and the balance at 74 payments? Well, you know the rest.
If it can be figured out mentally that way, why then can't the calculator do the same cross iteration calculations? It seems that a series of if/then/else statements with < & > tests should be able to come up with a solution, as long as you limit the interest rates to perhaps 3 decimal places.
If anyone can tell me where my logic is flawed, please do. I know I am chasing a difficult task, but I believe it is one even better suited for a computer than the human mind, and yet the human mind can accomplish it (with a standard formula, mind you).
So I come back to my claim that for a certain known remaining balance, and at a certain rate not to exceed 3 decimal places (within a range of perhaps 2% to 18%), and if kept to divisors of 8, there is only one possible solution for an initial loan amount that is not greater than 2 decimal places long, and only one possible solution for number of payments past.
Likewise, for a known interest rate (at 8ths max) and known number of payments, and at known balance, there is only one possible payment amount, again limited to 2 decimal places that results in an original loan amount also limited to 2 decimal places.
Finally, it is very rare that an original mortgage loan amount is carried beyond a whole dollar amount into pennies. So if we eliminate the 2 decimal places for the original loan amount, then we further narrow the possibilities to only 8ths in the rate, and 2 decimal places in the payment amount. I think at that point, again the numbers for balance, payment amount, rate, and original loan amount can only converge at one possible solution for all variables. Even more limiting is that in many cases the loan amount ends in even hundreds, such as $275,200.00, although the financing in of closing costs and other charges can squash that possibility.
Remember, these are all assuming P&I payments, and are also assuming 30/360 terms. There may even be enough data available from those converging numbers to nail down the term as 30/360, and eliminate other possibilities such as 20/240, or 40/480, etc. as well, but I don't know.
I would like to be proven wrong (and I know there are minds out there that if it is possible, they could do it), so I can put this exercise to rest. I will be posting this exercise on the Sci.Calc Google site as well. Let's see who can come up with a working model for the above. The closest I have now is Mr. Don Shepherd's two routines and the combined routine he sent via his link on the comment several posts above.
Phil
Edited: 24 Mar 2009, 2:56 a.m.
