Need a formula to find original loan value when only balance, payment, and term are known
FoxKat
stream in their mortgages, attempting to save them from foreclosure,
and I need to get a clear indication of what the mortgage they
presently pay is. They often know the balance or payment amount, and
may know the full payment amount (PITI), but are sketchy on the P&I
alone. They may also know the rate, but many are sketchy there as
well. Some may know the payment amount, the escrow (or the Taxes and
Insurance annually), and may also know the rate, but are somewhat
sketchy as to the remaining balance or exactly when they started,
though they might have a rough idea when the loan was taken out.
There are even examples where the rate is variable, and may have
adjusted one or more times over an unknown period of time. I don't
need an exact original principal amount, but close is close enough.
I am looking for a formula that can iteratively solve for original
loan amount (PV) when only the outstanding balance in the middle of
the loan (FV) and term (N) are known, but I have an approximate
indication of rate. A twist to that is when I know the outstanding
balance, the rate, and the terms, but don't know the actual payment
(PMT) amount.
I found an online mortgage analysis tool that seems to have most of
this down (http://loan.bizcalcs.com/Calculator.asp?Calc=Existing-Loan-
Analysis), but not all that I need. What's nice about this one is it
tells me how far along in the amortization they are, but still needs 4
variables. I am often faced with only three variables known,
outstanding balance, payment amount, and term (length of loan). Of
course, the phantom variable of future value (FV) which is the paid
off value of 0 is always assumed here. It seems that a formula should
be able to be devised that can use iterative solution methods to
"guess" the rate twice and then see if the first or second is closer,
then simply continue to repeat until it lands on the right number.
Consider the following information originally given by the borrower:
Balance: $113,989
Total Monthly Payment: $1,398 (but this includes an unknown escrow
portion, so it's "PITI")
Rate: 10% (variable, so it could have adjusted any number of times to
date)
Term: 30 years (360 months)
We know they have been in the home about 5 years.
The real numbers are:
Original loan amount: $117,600
Payment is $1,032.02 ("P&I" only)
Rate is 10% (no adjustments yet as it is a 5/25)
Balance is $113,989.18
Therefore, they have made 55 payments toward the amortization.
Could a formula be created that will allow me to enter say, 60 as a
guess for the payments past, then enter the present balance of
$113,989.18, the rate of 10%, and the term of 360 months, and find for
both payment amount of $1,032.02 AND initial loan amount of $117,600?
What about if I also guess the payment amount as a seed, say perhaps
$1,065 based on an estimate of $333 per month for escrow at a known
$4,000 per year of Taxes and Insurance divided by 12?
I have used Solver in the HP 17B-II, 19B, and 27S, and have worked
with various levels of formula sophistication. I typically use the
19B to create the formula since it has an alphanumeric keypad & 4-line
display making it easier to debug, and then port it over to the 17B-II
once debugged.
I really need a formula that can use fuzzy logic to find for more than
one variable, so that the end result is a FV of 0 based on a 360 month
amortization. If this is technically impossible, I understand. I now
await the flames.
Thanks.
William Elliot
> Please help me if you can. I am working with homeowners who are mid-
> stream in their mortgages, attempting to save them from foreclosure,
> and I need to get a clear indication of what the mortgage they
> presently pay is. They often know the balance or payment amount, and
> may know the full payment amount (PITI), but are sketchy on the P&I
> alone. They may also know the rate, but many are sketchy there as
> well. Some may know the payment amount, the escrow (or the Taxes and
> Insurance annually), and may also know the rate, but are somewhat
> sketchy as to the remaining balance or exactly when they started,
> though they might have a rough idea when the loan was taken out.
>
The best help you can give your clients is to admit you're an
amature and don't charge them; you could be libel for mistakes.
> There are even examples where the rate is variable, and may have
> adjusted one or more times over an unknown period of time. I don't
> need an exact original principal amount, but close is close enough.
>
Advise them to refuse those loan product things and get a loan instead,
a real loan before they became those loan product mass looting things.
> I am looking for a formula that can iteratively solve for original
> loan amount (PV) when only the outstanding balance in the middle of
> the loan (FV) and term (N) are known, but I have an approximate
> indication of rate. A twist to that is when I know the outstanding
> balance, the rate, and the terms, but don't know the actual payment
> (PMT) amount.
I charge for financial advise.
> Consider the following information originally given by the borrower:
>
> Balance: $113,989
> Total Monthly Payment: $1,398 (but this includes an unknown escrow
> portion, so it's "PITI")
Does it include taxes, insurance?
> Rate: 10% (variable, so it could have adjusted any number of times to
> date)
> Term: 30 years (360 months)
> We know they have been in the home about 5 years.
>
Don't they have a record of how much they've paided
and what, with each statement, the balance is.
> The real numbers are:
>
> Original loan amount: $117,600
> Payment is $1,032.02 ("P&I" only)
> Rate is 10% (no adjustments yet as it is a 5/25)
> Balance is $113,989.18
> Therefore, they have made 55 payments toward the amortization.
>
$55,000 cost for $3,700 equity is a disaster.
They need to be immediately refinanced.
Does the $55,000 include taxes and insurance?
Then an adjustment to the extend of the disaster needs to be made.
Something like $37,000 to $40,000 for #3,700 equity, if they
haven't over bought.
Can you offer them a 5% fixed rate loan?
Can you advise them how to get 5% fixed rate loan?
If you can't, refund their money and admit you can't help them.
Forget the formula, you don't know what you doing anyway.
FoxKat
Mr. Elliott,
After reviewing some of your other posts on various mathematical
questions, I am now beginning to wonder if, when you responded to me
initially, you were either intoxicated, or if perhaps you should seek
medical attention for possible mild stroke. Your demeanor, along with
the poor grammar, poor spelling, poor punctuation, and aggression
toward me seems quite out of character (at least the character named
William Elliott who posted on other subjects which I explored here.)
The only other possible explanation that makes sense to me is that
perhaps your son or daughter jumped on under your username and decided
to have some fun. Could you elaborate?
Ken Pledger
<a33148d5-91fb-4f20...@t3g2000yqa.googlegroups.com>,
FoxKat <fox...@verizon.net> wrote:
> ....
> I found an online mortgage analysis tool that seems to have most of
> this down (http://loan.bizcalcs.com/Calculator.asp?Calc=Existing-Loan-
> Analysis), but not all that I need....
I haven't looked through the details of your problem; but you may
like to look also at Stan Brown's page
<http://oakroadsystems.com/math/loan.htm>.
Ken Pledger.
zis...@cshore.com
fox...@verizon.net wrote on March 19, 2009 re the problem of
determining all six variables for a level payment loan when given four
of the data. Since there are two controlling equations, this is
always possible (ie, the two equation system is solvable).
There are three possibilities as to numerically solving the system:
(1) the two equations have to be solved iteratively, (2) one equation
can be solved explicitly and the remaining one is then solved
iteratively, or (3) both equations can be solved explicitly. The OP
mentioned two cases of particular interest, one being a type 3
situation, the other, type 2.
Before proceeding, it should be mentioned that there are two caveats
to this whole discussion. First, if the principal and interest
payment is to be one of the givens, and only PITI is supplied, then
some guess will have to be made as to how much of that payment is
indeed attributable to principal and interest. Second, this whole
problem becomes much more complex for adjustable rate mortgages
(loans). See the discussion at the end of this note for further
discussion of that aspect.
Accordingly, all of the following calculation assumes that the
mortgage (loan) has a fixed rate.
The six variables are:
K = principal and interest payment in one period (usually one
month), measured in dollars
R = (interest rate) * (length of one period);
ie, Period Interest Factor
Eg: rate is 10% per year; period is one month;
R = 0.10 * (1/12) = 0.00833333
n = label identifying the n th payment
Alternatively, with zero start time assumed:
(time of the n th payment) = n * (length of one period)
P_n = balance immediately after the n th payment
(measured in dollars)
P_0 = initial loan amount (measured in dollars)
M = number of periods required to amortize the loan
The following two formulae are obtained by solving the finite-
difference equation for level payment amortization:
P_0 = [P_n + (K/R)*(((1+R)^n) - 1)] / ( (1+R)^n) (1)
K/R = P_0 / [1 - 1/((1+R)^M)] (2)
These two formulae provide a general starting point and can be
deployed in one way or another to obtain a solution depending on what
data is given.
=====================
SPECIFIC PROBLEM ONE: n, P_n, R, and M are given --
From (2), substitute K/R into (1). After some algebra, we obtain:
P_0 = P_n / D ; (3)
with D = (1+R)^n - [((1+R)^n) - 1] / [1 - 1/((1+R)^M)]
K has been eliminated. In (3), we have one equation and five
variables. By taking four of these (n, P_n, R, and M) as knowns, we
can compute the fifth (P_0). Once, P_0 is computed, we can substitute
the value back into (2) to obtain K.
In the OP, the following example (a Case Three situation) was
supplied:
n = 55 (The OP spoke of *guessing* that n= 60. Due to some doubt on
my part as to exactly which elements the OP supposes to be givens and
which are to be computed, I am going to keep life simple, and assume
for the present that n is indeed a given. )
P_n = 113,989.18
R = 0.00833333
M = 360
Substituting these values into (3), we obtain:
D = 1.00833333^55 - [1.00833333)^55 - 1] / [1 - 1/1.00833333^360]
= 1.57843 - [1.57843-1]/[1-1/19.8372]
= 1.57843 - 0.57843/0.949590 =0.969293
Thus, P_0 = 113,989 / 0.969293 = 117,600
Substituting this value into (2), we obtain:
K = 0.00833333 * 117,600 / [1 - 1/(1.00833333)^360]
= 0.00833333 * 117,600 / 0.949590 = 1032.02
Note that the values of P_0 and K that we obtain in this way agree
with the underlying data in the OP obtained independently.
=====================
SPECIFIC PROBLEM TWO: n, P_n, K, and M are given --
This is the second of two problems that was posed in the OP. It is
similar to the first -- R has moved from given to computed, and K has
moved from computed to given.
Once again, we go back to equations (1) and (2) and this time, we
substitute P_0 from (1) into (2). After a small amount of algebra, we
obtain:
K/R -
[ [P_n+(K/R)*(((1+R)^n)-1)] / [ [(1+R)^n] * [1-1/((1+R)^M)] ] ]
= 0 (4)
Inserting the givens (K=1032.02, n=55, P_n=113,989.18, and M=360); we
obtain:
1032.02/R -
[ [113,989.18+(1032.02/R)*(((1+R)^55)-1)] /
[ [(1+R)^55] *[1-1/((1+R)^360)] ] ] = 0 (5)
We now have one equation in one unknown (namely R). This equation can
be solved by trial and error. One starts with trial values of, say, R
equals 0.00 and 0.05 and observes that the left-hand side of (5) has
opposite signs for the two estimates. We slice the initial interval
roughly in two and compute the sign of the left-hand side of (5) for
the mid-point. This generates a new sub-interval by picking that end-
point which has sign opposite to the mid-point. And repeat. In this
fashion, R can be calculated to any desired precision in a simplistic
way.
Most spreadsheet programs have a built-in "solution finder" for
situations where there is one variable and one constraint. (The HP
Solver mentioned in the OP may do the same -- a guess on my part.)
Once the jargon of the manual is mastered, setting up the spreadsheet
is quite basic.
With the value of R obtained, we can then substitute it into (2) to
obtain the remaining computed, P_0.
=====================
Overall, given 6 variables and 2 equations; there are 6!/2! = 360
specific problems that could be posed. Obviously, it is not very
effective to develop particular approaches for all 360 of these. If
one wishes to develop a general computer program that would compute
the two unknowns for any four givens, (1) and (2) are the starting
point and can be solved numerically in a number of ways. There are
some similarities between solving a single Case One problem versus
solving in a general way all 360 problems. If there are one or two
Case One problems of interest, hand-coding an iteration program (for
each) might be the best approach.
Although I do not have hands-on experience with full-featured
mathematics packages, if one desires a general approach to solving all
360 problems generated by system (1) and (2), I'd suggest looking at a
package such as MATLAB, Maple, Mathematica, etc. My limited
understanding is that these packages can generally handle finding the
solution of a multi-equation, multi-variable system.
=====================
As mentioned earlier, all of the above is based on the fixed rate
mortgage premise. When we attempt to migrate to adjustable rate
mortgages, the idea of attempting to obtain "original data" from
"current data" mostly breaks down. The process becomes rather
impracticable .
Suppose we have an ARM and there have been two rate adjustments. This
adds into the basic mix of six variables, an additional four data: 1,
time of first rate change; 2, the first new Period Interest Factor; 3,
time of second rate change; and 4, the second new Period Interest
Factor.
The problem is now changed to: two equations and ten unknowns.
We will grant that, say, four unknowns will be available per
traditional formulation. The system is now reduced to two equations
and six unknowns. In order to solve the system, we have to come up
with an additional four data.
After the uncertainty created by guessing the additional data (and to
some degree the additional mathematical complexity), I wonder if it is
really worthwhile in following this approach.
My conclusion, therefore, is that this exercise can be useful:
(a) for fixed rate mortgages, and
(b) for adjustable rate mortgages that can be shoe-horned into a fixed
rate approximation.
If dealing with adjustable rate mortgages, the "backward calculation"
approach that works well enough for fixed rate mortgages does not seem
that helpful. As I see it, in most of these situations, researching
the loan from primary sources may ultimately be the most effective
approach.
David Ziskind
http://home.cshore.com/ziskind/
zis...@cshore.com
FoxKat
I appreciate your tremenodus analysis and solutions of the given
problems. I will need to take some (extended) time to read,
understand, and then apply those formulae, but based on your statement
that the results confirmed your hypothesis, I am excited.
Thank you so much for devoting such a strong and well though-out
execution. I can assure you that your efforts will have a direct
impact on saving people across the country from foreclosure. You can
visit our website and see actual case histories of modifications we
have completed for people. Go to www.nationwidehomerelief.com/gmk,
and click on the "Testimonials" link at the top. Each case can be
opened up for further review, and you can actually see the offer
letters and other supporting documentation for the completed
modifications.
Thanks once again!
Sincerely,
Phil
FoxKat
> I haven't looked through the details of your problem; but you may
> like to look also at Stan Brown's page
> <http://oakroadsystems.com/math/loan.htm>.
>
> Ken Pledger
Ken,
I had actually found that page myself before, but I didn't read
through it deep enough to see that it had several of the solutions I
was looking for. At first I was overwhelmed at the numerous
reworkings of the formulae that he did, but after your reference to
it, I have taken a new, perhaps more careful peruse through it and I
see that there are several key solutions I was looking for.
I have also seen (below), David's take on the whole thing. He too
speaks at a level a few rungs up the ladder from me, and I understand
that is natural for high-power thinkers like the two of them. I have
a high aptitude and perhaps with the level of formal mathematical
educations these two appear to have, I too might sound like them at
their similar levels of accomplishment (I wish...), but I have to
really study them and work through them a few times before I will have
a complete grasp. I typed in one of David's formulas and it worked.
Now I have to understand and adapt it to meet my needs.
Thanks for re-introducing me to Stan's page. I will enjoy applying
that knowledge as well. Maybe with the blend of both views (David's
and Stan's), I can achieve the Holy Grail of solutions for my specific
purposes. The problem for me stems from the fact that most people
don't understand how a loan works internally, especially a mortgage.
Furthermore, they often don't even care to know, and so can't provide
me with the necessary accurate numbers which would allow me to use
standardized formulas such as those hard-coded into the HP 17BII, TI
BA-II and cousins. All they are concerned with is what the monthly
payment is, and they seem to gain some comfort in knowing what the
interest rate is, and whether it is "high" or "low" respective to some
reference rate of the time, or simply to what they think is high or
low. The actual numbers are often however, quite a distance from what
they think they are, and may not get the idea that, the mortgage
payment is actually 4 separate payments (for most), i.e. a principal
payment, an interest payment, (and if they have an escrow), a tax
payment, and an insurance payment. They instead just accept it as a
"mortgage payment", and are really only concerned about whether they
can afford it or not.
In order to properly evaluate their situations, and to be able to give
fair, conservative, and realistic estimates of what we can obtain, we
first have to know exactly (or within a small margin of actual), what
they have now. To work with anything less than a near perfect
representation of what they are currently paying causes our estimates
to be either too high or too low. Either result could cause them to
feel as though the effort is not worth the price, or that the results
are far too good to be real and so must be artificially inflated. The
end result is a person who we could actually help, shying away and
perhaps giving up.
Well, enough of my story. Thanks again.
Phil
zis...@cshore.com
23, 2009 with identifiers zis...@cshore.com and
08eb9968-04d3-45d9...@w34g2000yqm.googlegroups.com .
Correction:
Please delete the three paragraphs: sixth, fifth, and fourth from the
end. In other words, the deletion is the text running from: "Suppose
we have an ARM ..." through "... we have to come up with an additional
four data." These three paragraphs are concerned with the counting up
of equations, variables, and additional required data (for the ARM
situation), and are inaccurate. The various errors have no bearing or
effect on the preceding part of the article (dealing with conventional
fixed-rate mortgages).
Supplement:
The equation labeled "(1)" in the article can be modified to fit the
situation where there are rate changes (i.e., ARM's). Using the
example of two rate changes, one can develop a system of 4 equations
with 14 variables. Once one supplies data for 10 of these, the
remaining 4 can be obtained by solving the system. (Current time and
balance will be 2 of the 14 variables.) Such a system can be solved
iteratively and most likely one would use some kind of general purpose
numerical-solution program.