HELP: Solver routine for original principal loan amount?



#2

I have several model HP calculators, including the 12C, 17B-II, 19B, and 27S and love them all. Though the 27S is probably my favorite overall for its wide built-in functionality, I like the 17B-II for its simplicity and suitability for a specific purpose, and the 19B for its alphanumeric keyboard. I guess am somewhat of a junior collector, but not for collecting, so much as for business purposes. Like most who work in the mortgage or investment industries, I started out on a 12C, but although I like RPN, I felt the 12C was too complicated and too slow so I graduated to the 17B-II and that's what started the process. Once I discovered the Solver functionality, I was hooked. I have yet to discover the pleasure of working with the high-end graphing calculators and might be interested in a suggestion of which one I would want to try based on the information below.

I have written some fairly long custom solver routines using nested if-then statements in combination with algebraic equations, though I am not a mathematician by any stretch of the imagination. Most of the time I am flying by the seat of my pants by slicing and combining portions of known formulas, coupled with trial and error to work towards a functional formula for my specific purposes. Often times I create a short formula for one result, then repeat that process for other desired results until I have all the results I wish to obtain. Finally I compile all the formulas together into one solver routine. Not the best way to do things, but it works for me. Some things I have done are a moving average commission calculator formula for a commission versus draw compensation plan, and a pricing scheme for a very involved sales process of foundation repair which included many options, each with their own costs and with a graduated discount pricing structure. In that formula, I even incorporated a commission calculator so I could see how much I was earning real-time as I was pricing the job.

In one example several years ago, I was able to get almost all the correct results every time from a very complicated formula I created but it had one error. After speaking with a Tom Helm at HP technical support, he not only found the error for me, but he informed me my formula was about 4 times as long as needed and showed me a much shorter (and faster) solution using built-in USPV & USFV functions and rearranging the formula to find for 0.

He even taught me a layout technique where I could place all the variables at the beginning of the solver routine in the order I wished them to appear on the keys (A+B+C+D+E+V+W+X+Y+Z*0:), so I could enter the data on one screen, hit the "more" key, and in the second screen I would have all the keys to find the desired results. Pretty neat if you ask me. Unfortunately, I can't track him down now since tech support has all been outsourced to India (could be partly why we Americans are in this "recession" and are losing jobs and homes, but don't get me started on that soapbox). I know he would have had an answer for this present dilemma. So I decided to toss it out there and see if anyone can help me.

Which brings me to today's dilemma...I am evaluating clients with existing mortgages for possible modification of terms to reduce interest rate, possibly reduce principal amount, and even change terms to effectively reduce the monthly payment amount, thereby making it more affordable and preventing the need for foreclosure and Sheriff's sale. In order to do this properly, I need to know exactly what they have now.

This may not surprise most here, but it is shocking to me to see just how many people who purchase houses don't know what kind of loan they have or how it is calculated. As a result of economic downturn coupled with their lack of understanding of how the loans work, these people are now unable to pay and are at risk of losing their homes to foreclosure. For many, it's due to the aggressive predatory lending practices of the mid to late 2000s, being forced to borrow more than they should have.

Often times they were confronted with completely different terms than that which they had agreed to before. In those cases, they were forced to sign new "Truth in Lending" forms once they reached the closing table, basically absolving the mortgage broker of any "wrongdoing" such as a bait and switch. Some may say that's why they shouldn't own homes to begin with and that they deserve what they are getting (buyer beware), but I am not one to judge, I just want to help them keep their homes (and earn a fair income doing it).

I often have clients who not only don't know how much they originally borrowed, but many also don't know exactly what their interest rate is now or even what it may have started at. They may not even remember what month and year they took the loan out to determine how many payments have been made so far. Normally most, if not all of this information can be gleaned from a recent statement, but sometimes it only gives me a remaining loan balance and payment amount.

What makes this so difficult is that I am evaluating them mid-stream and often only have information about the exact position they are in now on the amortization cash flow stream (PMT & BAL), and a relatively rough estimate of when they started (i.e. "summer of 2005), and without the necessary 4 variables (N, I%, PMT, & FV), finding the 5th (PV) with the built-in TVM is impossible. Then there are the adjustable rate loans and of course, if it has adjusted one or more times, there is no way to actually back into the multiple rates it has adjusted through. There may also be an odd period payment at the end. Still there is a blended rate that can be solved for which yields essentially the same results.

In order to properly evaluate them, based on their known present monthly payment amount, and known number of years the loan was originally taken out for (i.e. $1,467.63 for 30 years), and using the remaining balance ($194,897.81), I have to come back to an original principal loan amount and an interest rate. Sometimes, I know the rate and can simply find for present value using the built-in TVM program, but I know that the solver can be programmed to use iterative solving to obtain a solution from less than the necessary known variables. In fact, I have found an online calculator that essentially does most of this (though it still needs 4 variables), but I can not extract the formula from that site to do the same thing. See http://www.webcalcsolutions.com/Mortgage-Calculators.asp and choose "Existing Mortgage Analysis". To see how the results for original loan amount are obtained, mouse over the result (Original Loan Amount). I have tried to recreate this formula on the 17B-II, but I can't figure it out.

This calculator works iteratively to solve for PV, but it does need both the interest rate and the original terms to accomplish this. I know the same can be done with the TVM built-in, using the known variables (N=360, I%=8, PMT=1,467.53, FV-0) to find PV=$200,000. Still, it seems likely that with ONLY the remaining balance (BAL), the original terms (N), the payment amount (PMT), and the ending balance (FV) that the rate (I%) AND original principal amount (PV) could be iteratively solved for, though it might need a seed and there could possibly be several solutions if the terms are not known. Still, if it gets me in the ballpark (i.e. PV=$200,000.12), the rest is obvious (PV=$200,000).

To still further complicate things, these mortgage payments often include an escrow for taxes, insurance, and PMI, and many times the portions are not disclosed on the statements. In those situations, I only have a balance and terms and an estimate of rate to work with. Using an approximate rate, the present balance and terms, it seems that again, an iterative solution could be found that would equal 0 at the end of 30 years, and work back to a balance at a guessed rate, and provide a payment amount accordingly. If again it gets me in the ballpark on the initial loan amount, that works.

Can anyone lend a hand in this project? I would be forever grateful if I could get a formula on the calculator that does this, and it would be used for a very good cause considering I am ultimately keeping people from losing their homes to the mortgage companies.

By the way, a plethora of very nice calculators for a myriad of purposes are free for demonstration use online here (http://www.webcalcsolutions.com/Calculators.asp). It would be one heck of a portfolio of solver routines for us if someone could intuitively extract them from the online versions. Perhaps someone or persons could create a database of solver routines that we could all share and work from to customize for our own purposes (hint)? Just a thought. ;-)


Edited: 15 Mar 2009, 11:32 p.m.


#3

Phil,

Next time, could you not be so parsimonious with your words? ;-)

I'm afraid I can't help you with your dilemma, but I can suggest that, if you are using your 17BII so effectively, why consider a graphing model? I have both the 17B and 27S, and I share your love for the solvers on these. I also have a 48SX, and, at least for me, it is maddeningly difficult to use, and excruciatingly slow at that. As someone once said, I cannot do much more than some simple calculations without getting out the manual(s).

Many here sing the praises of the 50G as much faster and a better screen, but still it must be complicated to use. I am sure you will get many opinions.


#4

Well, now you see why my solver routines were so long too! LOL!

#5

By the way, thanks for the calculator suggestions. I have a co-worker who uses a 48g (I think?), and his TVM is all on one screen, multiple lines. He can change a variable and the rest update on screen (COOL!) I will have to do this eventually, but for now I want to learn more about Solver and build my math skills as well.

(Could I have said that in 20 words or less...yes...would it have been representative of me...well, no.)

LOL!

#6

I will post the routines I wrote before, perhaps others can gain some insight as to how powerful Solver really is. I used to program in BASIC (never graduated to more powerful languages), and Solver is in many ways similar to BASIC, with respect to IF/Then/Else/Else. I really enjoyed seeing the fruits of my many hours of mental labor. Some will see it as pure overkill, but then...you already know me, so it won't surprise you.

Cheers!


#7

Here's the one I wrote to price out basement waterproofing, including many feature options, variations in pricing, and a full commission calculator based on several factors including tenure and commission scale, cost per foot discounting, three pricing schemes (residential, commercial, realty), and completely arbitrary profit padding. This tool allowed one to price on the spot, negotiate better pricing to get the sale, and all the while keep watch on how much commission he/she was gaining/losing based on the various features and pricing schemes (since it was 100% commission based compensation).

Disclosure: Those selling were only doing what they were hired to do and weren't breaking any laws. Negotiating the price for a particular job is completely legal and if the homeowner agrees to a specific price, it is their prerogative.

The basement waterproofing world is one of the most profitable construction trades there is, and it is a high-pressure, sell on the spot, "one-call close" world, where pricing is all across the spectrum, as is the type of work being done. The company this was written for was at the time, the largest company in the country, and in my opinion did the finest quality work of any, and provided the strongest warranty in the industry. It may very well still be, but I can not say for sure.

What I can tell you is, if you have a leaky basement, NEGOTIATE THE PRICE DOWN LIKE YOUR LIFE DEPENDS ON IT! NEVER settle for the first price, or the second, or even the third from the same company. Stick to your guns and the price will come down. They want your business and will only give up negotiating (usually days later) when it is no longer profitable. The price for a typical basement water management installation job can range from a couple thousand dollars for a shoddy installation, to well into the 5 digits (can be $20,000 or more for a 300 linear foot basement) for a top quality water management system.)

This one should cause a few questions... :-)

I apologize for not formatting (indenting) this out to show the nested if/then statements properly. I think it's supposed to be a new indentation for each set of parenthesis, but I don't know for sure. Since I don't know the proper way to show this, perhaps someone can clean it up for all.

(FEET+PUMP+ACE+C/OUT+PIN+POST+WTAP+OIL+STAIR+SPEED+RDON+STEPS+BOARD
+CORDS+EXTRA+TOTAL+JOB+REALT+$/FT+MIN+COMP+CONTR+PAR+PAR%+OVER+OVER%
+YEAR)*0=
IF(S(TOTAL):
TOTAL-(L(MIN:L(PAR:L(BASE:PUMP*800+800+ACEx995=C/OUT*150
+PIN*325+POST*600+WTAP*200=OIL*350+STAIR*500+SPEED*350+IF(RDON>0:
FEET*6:0)+STEPS*250+BOARD*9+CORDS*15+EXTRA)-ACE*995=(FEET*L($/FT:50)))+ACE*995)=L(OVER:PUMP*100=5758)):
IF(S(PAR):PAR-(TOTAL-(OVER+ACE*995)):
IF(S(CONTR):CONTR-(JOB-ACE*995):
IF(S(MIN):MIN-PAR+ACE*995):
IF(S(JOB):JOB-(BASE+$/FT*FEET+OVER):
IF(S(OVER):L(OVER:IF(JOB-(PAR+ACE*995)>0:
JOB-(PAR+ACE*995):0)):
IF(S($/FT):
IF(OVER>0 AND $/FT<5):
IF(OVER÷FEET>=(50-$/FT):
L(OVER:OVER-((50-$/FT)*FEET)+L($/FT:50)-50):
L(OVER:0)):
JOB-(FEET*$/FT+BASE+OVER)):
COMP-IF(JOB>=MIN:
L($/FT:50)-50+PAR*.1+ACE*300+L(OVER:JOB-MIN)*
IF(YEAR>0:.33:.5):
JOB-(FEET*$/FT+BASE+OVER)+
IF($/FT>46:JOB*.09:
IF($/FT>=42:JOB*.08:
IF($/FT>=40:JOB*.07:
IF($/FT>=38:JOB*.06:
IF($/FT>=35:JOB*.05:
IF($/FT>=33:JOB*.04:
IF($/FT>=30:JOB*.03:0)))))))))))))))

Enjoy!

Edited: 16 Mar 2009, 3:37 a.m.

#8

I would refer you to John Tirone of Macomb MI. He wrote the book on using the solver for real estate problems.


#9

Thanks for the suggestion. Now where might I find this veritable resource to end all resources for my RE formula needs?


#10

Phil,

John Tirone's book is available at amazon. John is one of the most oustanding business men I met during my life - his service & willingness of support are 2nd to none!

http://www.amazon.com/Professional-Estate-Problem-Solving-Using/dp/0962423637/ref=sr_1_2?ie=UTF8&s=books&qid=1237201493&sr=8-2

Best regards,

Peter A. Gebhardt

Edited: 16 Mar 2009, 7:54 a.m.


#11

Thanks for the info. I didn't realize he was an author. I did try to contact him and he did respond today! I will likely talk to him tomorrow, but I didn't have time today.

Thanks.

I will have to buy one of his books, but I may also buy one other book I saw mentioned that is mentioned as another "bible" for the HP 17B-II.

#12

Phil, even if I can't answer your question, a few things come into my mind:

1) Banks and mortgage brokers tend to be scrupulous; not all of them of course. ;)

2) People are stupid. For me it is unbelievable that someone buying a home has no idea of the details of the mortgage and no written records of past payments or a schedule of upcoming financial burdens.

3) The 17b and its relatives have one big problem: there is no way to save (and reload) your formulas to an external system or device. The HP-48 family (from 48SX to 50g) are much better equipped in that respect. A PC (or an SD card for the 49g+/50g) serves as a backup storage medium. Your complicated formulas deserve a safe place to live. Editing your formulas on a PC is an added benefit.

Marcus


#13

1) Hmmmm, maybe in your country...

2) Caveat emptor.

3) The HP-50g also has the added benefit of a built-in TVM solver under the FINANCE key.

Still, I wonder if problems of this kind might not be easier to handle using a PC spreadsheet such as EXCEL. I've solved a lot of engineering "what if" problems this way.


#14

Quote:
Still, I wonder if problems of this kind might not be easier to handle using a PC spreadsheet such as EXCEL. I've solved a lot of engineering "what if" problems this way.

Perhaps... but not nearly as much fun.

Jeff Kearns


#15

Jeff,

You may very well be right. I would describe a spreadsheet as a geometric look at mathematics (or that's my take on it anyway), and by way of cell relationships it can be used to replicate calculations X# of times quite easily. I am just not sure of the relationships between the variables I often have to work with and therefore can't devise a working model.

It seems to me that there should be a way to find for the original loan amount using ONLY the balance, term of 360 months, and payment amount. I say this since from my perspective there should be only ONE point in an amortization at which any loan balance is exactly what is stated to the penny (and possibly even to the dollar), at a certain payment - call it an intersection if you wish (such as in a spreadsheet). This of course would be based on X number of times a known full payment amount has been made within a defined period of 360 payments, and where the future value of 0 is known, even without knowing the exact rate of interest.

Further, it seems that the narrower the interest rate range (i.e. 2% - 18%) and the fewer the possible variations of the rate straying from a whole number (i.e. 6.5%, 7.25% or 8.625%), the less likely there would be more than one possible intersection in the same 360 months payments. In a similar relationship, if the rate, the balance, and the term are known, then the payment should be solvable for the same reason as above. Once you know the so-called missing variable(s) in either example, then the number of months paid versus how many are remaining to be paid should be obvious as well.

I mean, I might be going out on a very thin limb here, but if I were to throw a specific dollar amount for balance out, and tell you it could be anywhere within 48 to 60 payments into a loan of 360 months without divulging the interest rate, shouldn't there only be perhaps a very few possible solutions (or even only one) using the ranges of rates and limited fractions thereof above? I could even limit the range of rates to perhaps between 6 & 8%, and that should further eliminate possible other solutions to bring me to within the range of the desired solution.

I am imagining a massive spreadsheet or series of spreadsheets with 360 month amortizations for all the possible rate combinations eluded to above, to minimum fractional percentages of 32nds, and then search for the exact amount of that one known balance to see if there are any more cells than one in which that exact balance can be found. Since there are only 360 possible balances for each interest rate, and far fewer at any value near that balance, it should be extremely low odds that a number which is potentially in the hundred thousands and 2 decimal places is duplicated in different interest rates.

So, if we have 17 possible interest rate whole numbers (2% through 18%), times 6 possible fractional variations (whole number, 1/2, 1/4, 1/8, 1/16, 1/32), times the 360 possible monthly remaining balance values, the total possible number of amortized cells is 36,720, but each could be valued at everything from zero to the maximum value of the original loan amount that we are finding for, though only a relatively few could be within a range of the known balance value.

Right?

Perhaps an appropriate question to ask first is, can a formula be designed to allow you to enter "guesses" or "seed" values into certain variables registers and have the calculator use those estimates to start its iteration?

Forgive me if my logic is anything but...as I am no mathematician. I know enough to be dangerous, coupled with an analytical mind and an IQ to make it interesting. I am simply looking for a way to find these answers using fuzzy logic if it can be done.


Edited: 21 Mar 2009, 8:44 p.m. after one or more responses were posted


#16

Phil, I have been thinking about, and experimenting with, this problem for a few days now. The web site you referred to is able to calculate the original loan amount given the current balance, interest rate, length of loan, and monthly payment. I see what they are doing (iterating), I just don't know how they know to stop iterating at a certain point and call that the original loan amount. I admit that their method works, but I just don't quite know how. If I knew how, I might be able to implement that method on the 17bii+.

I admit that, theoretically, it should be possible to determine the original loan amount given the current balance, term, and payment amount. It would probably take an Excel VBA program and a PC to do it, however; I doubt that could be done using the solver on the 17bii+, but I would like to be proved wrong on that.

Did John Tirone give you any insights you can share with us regarding this problem?


#17

Don, I appreciate the fact that you took an interest and the analysis you did. Yes, it is an iteration, but just as you, I can't figure it out. The 17B-II and the other cousins are capable of iteration, but how to implement it is the issue.

Also, knowing you feel as though it should be possible to solve with only the 3 variables, makes me feel good that my analytical mind isn't so far from center (unless we're both out in left field)! if you or anyone else can come up with an answer, it would be very useful for my function.

As for Mr. Tyrone, I have received a couple calls from him but none that actually solve the problem. I haven't gotten a chance to actually speak with him, they were long voicemail messages, and one email response. I'll try to capsulize and post later.

Thanks.

Edited: 21 Mar 2009, 12:21 a.m.


#18

Phil,
Don,

I doubt that there is a "one-and-only-one" single solution of Phil's problem.

As a crude verification one could put Phil's numbers into a 19bII or 200LX Cash-Flow program and use PLOT to verify.

For each an every other CF0 (CF-zero) Cashflow the PLOT function shows only one (different!) solution for I%YR.

So I suppose there is an unlimited combination of interest rates and loan amounts possible.

BUT, I'm open for any arguments proving me wrong ;-))

Best regards,

Peter A. Gebhardt

Edited: 21 Mar 2009, 8:21 a.m.

#19

Phil, see this link. I figured out how that website knew when they were at the iteration for the original loan amount. They just figured out what the monthly payment would be if that was the original loan amount, and if that payment was very close to the actual payment, Bingo!

This works on the 17bii+. It takes advantage of the fact you can exit a loop early (so you don't have to iterate all 360 times, for example) by dividing by 0 AFTER storing the values you are interested in in some variables, and RCL'ing the variables after the execution stops. I learned this a couple of years ago from another member of this forum (Mike Ingle).

This equation essentially duplicates the functionality of the web site you referred to. If you know current loan balance, APR, term (months), and monthly payment, it will tell you how many payments have been made and the original loan amount.

I would have liked to use the solver functions USFV or USPV, but I couldn't make them work with loan balances, only savings account values. I'm sure there is a way, I just couldn't determine it.

This has been fun!


#20

Don,

Well done!

I typed that equation into my 19BII, and at first, it kept coming back not failing to find a solution, and with 0 for a value in PV & PTD. I tried several times until I went back and re-read your instructions and realized you had indicated (mos) for TERM. I was actually typing in 5 (years) instead of 60 (months) for the example and when I changed to 60, it worked like a charm.

Since I am so used to using the years rather than months on the 17BII (30, shift, N), and since that habit tripped me up, I changed the formula to add the blind variable "N", edited the menu selections to add "YEARS", and changed the variable "TERM" to "L(N:(YEARS*12)" in both locations. Now I can enter 30 for years and it converts to 360 months.

That was really neat. Next, it would be interesting to see if the USFV & USPV can be incorporated, since they are much faster at computations. Eventually, it would be neat to see whether it can figure out the same solution with only 3 variables, rather than 4.

Now, is there anyone up to the task of modifying that formula to include the ability to find with less variables? Again, it seems to me that there should only be one, or at most a couple solutions to the scenario I described before, where only 3 variables are known, or even more interesting, where only 1 or 2 are known, but others are within a range of guesses.

I may be pushing my luck here, so I will say THANK YOU FOR WHAT YOU DID! It was spectacular to see it come to fruition. Now I have to study it and understand what's happening internally.

This is a great education for me.

Edited: 21 Mar 2009, 8:50 p.m.


#21

Hey Phil, I figured part of USPV out:

PV=PMT*USPV(I%/12:TERM)

where I% is like 6 for 6% and TERM is in months. This gets reasonably close to the original loan amount without iterating and without requiring loan balance!

I'm still working on using USPV to determine payments-to-date.

#22

Phil, this much simpler formula seems to work, using USPV for both original loan amount and payments to date:

CALC=0*L(PV:PMT*USPV(I%/12:TERM))+0*SIGMA(I:1:TERM:1:
0*IF(ABS(PMT*USPV(I%/12:I)-BAL)<.2:L(PTD:TERM-I)+PV+PTD/0:0))

#23

Don, YES it DOES work seemingly identical. Excellent work. I didn't try the short formula above yet, but that's next.

I have played with USFV & USPV before, but today I lost 3 calculators (a 27S, a 17BII, and a 12C) along with my 17BII manual that had MANY solver routines I had written, added in as notes, and of course, it explains the USPV/USFV in the back. I guess it will be a while before I have another manual to refer to and am able to experiment with them, and I may never be able to recreate some of the formulas I had written in that book.

Fortunately I still have 2 of the 17BIIs and I had my 19BII in my coat pocket. I would have been totally smashed if I had lost the 19BII as well.

It makes me sick that someone would smash a car window to steal something. I can't imagine being so selfish and uncaring of others. This is a stark contrast to you and the others on this forum - you are all so giving and genuinely interested in the common good. Others need to take a lesson.

This country is in the mess it is in, due to a similar mentality as the one the person was operating under when he/she decided to destroy my day for their own personal gain. If I had caught someone in the act of similar aggression toward anyone, I would have detained them, called the police, and then applauded and cheered loudly while they were cuffed and taken away.

They ruined my entire mental state for probably weeks to come.


#24

Phil, this formula is not as exact as the others, but it will probably suffice for your purposes, and it lets the calculator iterate and find the solutions for PV and PTD, and it doesn't do the funky divide by zero thing. Enter values for: BAL, I%, TERM (months, you can adjust), PMT. Solve for PV (original loan amount) and PTD (payments to date):

0=IF(S(PV):PMT*USPV(I%/12:TERM)-PV:PMT*USPV(I%/12:TERM-PTD)-BAL-PTD)

I think I've taken this about as far as it can go. Let me think about your other request (no APR neeeded).


#25

Don, I think perhaps the finding of the APR may relate to the IRR of the stream of payments, but I can't put my finger on it.

Perhaps if we know the payment amount, and know the balance, we could first apply the payment against the balance as the PV, to find the I%, using the full term of the loan. This would give us a false I% because we would both be shorting the PV (initial loan amount), and exceeding the remaining payments. Still, once that I% has been computed, perhaps it could be applied toward the payment amount using a close guess for the term remaining and allow it to iteratively find for the real rate. Or the inverse, where we would know the rate but would not know where in the exact amortization stream we are.

I don't know, I may be totally off base, but it seems to make sense somehow in my twisted way of thinking.


#26

Phil, I know IRR exists, but I don't really understand much about it. I'm not a financial guy, but I do enjoy finding solutions to problems using the 17bii+ solver. When I first saw the web site you referred to, I couldn't figure out how they knew when to stop iterating, so I thought about it and finally figured it out. And I had a feeling that I could use the USPV or USFV functions, but I couldn't relate them to loan balances until I actually took a simple loan ($1000/12 months/12%APR) and wrote down the loan balances after each payment, then I wrote down the USFV and USPV amounts for those same periods, and I noticed an inverse relationship between loan balance and USPV amount, and that led me to my final formula using USPV for both original loan amount and payments-to-date. This was a rewarding exercise!

If you eventually find a way to determine original loan amount and interest rate, please post it on this forum; I know that myself and many others would like to see it.


#27

Don, I will surely let everyone know if I do find a solution. This is alive and well in my mind at the moment, so I will be thinking about it even when I sleep. Strangely, some of my best results came right after a good night's sleep. I have woken up and realized a breakthrough several times before.

I will keep you all informed.

Thanks for all the help.
Phil

#28

Quote:
It seems to me that there should be a way to find for the original loan amount using ONLY the balance, term of 360 months, and payment amount.

I'm no financial formula expert, but I think the answer is yes. (I did once upon a time derive from scratch the formula for mortgage payment given loan amount, rate, and number of payments. I leave that as a challenge to the members of the forum.)

To see the answer for the payment amount, check out mortage formulas . That reference also has a time value of money equation. I think that if you take your current balance as the future value and the original loan balance as the beginning value and combine that with the payment formula, you can derive an equation with the original balance as the unknown. It may be a challenge for a SOLVER, though - it includes an Nth root, where N is the total number of payments.


#29

Hi, Dave --

Quote:
(I did once upon a time derive from scratch the formula for mortgage payment given loan amount, rate, and number of payments. I leave that as a challenge to the members of the forum.)

I did, too -- during my high school years, after we had covered finite series and limits. I showed dad my work, and we verified the equation by calculating the mortgage payment.

The derivation is basically a straightforward, "grind it out" application of algebra.

-- KS

Edited: 21 Mar 2009, 4:29 p.m.


#30

Karl, wouldn't that be the same formula that's in the back of the manual?

Might you be so kind as to provide an explanation or formula? By the way, Don's solution DOES WORK. If we could build on that, and perhaps even bring in the USFV & USPV into the mix, it could be a very powerful formula for my purposes. I am working with people in many cases, who know nothing more than how much they are paying, and they often don't even know what their taxes or insurance payments are, but they do many times know APPROXIMATELY what the interest is, and MAY KNOW how long they have been there...

It's like trying to lead a whale out to sea, only to have them circle in the bay.

Phil

Edited: 21 Mar 2009, 8:56 p.m.


#31

Quote:
Karl, wouldn't that be the same formula that's in the back of the manual?

Might you be so kind as to provide an explanation or formula? By the way, Don's solution DOES WORK. If we could build on that,


Manual? Dave and I didn't need no stinkin' manual!

;-)

Phil, I think that you're misunderstanding what Mr. Shaffer and I said. Long ago, we had each derived the standard Time Value of Money equation from scratch. I'd never even heard of "TVM"; I just wanted to determine what would be the level periodic repayment (PMT) for completely amortizing a loan (FV = 0), given the loan amount (PV), interest rate (I), and number of periodic payments (n).

-- KS

Edited: 21 Mar 2009, 10:19 p.m.


#32

LOL! I guess you are right, I didn't understand. So you two are (perhaps loosely said) pioneers in the TVM world?!

I just realized something as I looked to my right and saw my hardbound copy of Time & Money by Robert M. Crowe. He was a teacher at the college I went to, and I never knew it. I live in Pennsylvania, USA, near the American College in Bryn Mawr, and Robert M. Crowe Referenced himself at the American College in Bryn Mawr, Pennsylvania, which is not more than 15 minutes away from my home! Small world, I guess.

#33

Dave, thanks for coming to the same hypothesis. I feel better knowing I am perhaps thinking like others here.

#34

Michael,

The built-in TVM would make the 50G the likely choice, and I appreciate the suggestion. I would rather have one that combines most (if not all) of my needs into one neat (albeit not necessarily little) package.

Thanks.

Phil

#35

Marcus,

Quote:
3) The 17b and its relatives have one big problem: there is no way to save (and reload) your formulas to an external system or device.

although your point is correct, I kindly reject your proposed solution:

1. I stopped using the 50G for finance calculations, because of its heritage lying mostly in egineering, calculus and analysis. It's one thing needing only the readily available programs of John H. Meyers or Mr. Mc Donald. Another one is to programm the 50G based on readily available finance routines - say for "wrap arounds" etc. similar to Phils problems.

2. There is still an alternative available (which also has more options and less physical footprint than the 50G) - the 200LX. This system (available mostly in good shape through auctions) offers Phil the stated "Save/Reload" capability and also as an undisputed advantage the literal code compatibility of hundreds of dedicated finance equations ready to be typed in into the HP Solver.

Best regards,

Peter A. Gebhardt

Edited: 16 Mar 2009, 8:22 p.m.


#36

I would love to have a 200LX. I must look now and see if there are any being auctioned off. I will also do a search on Craigslist. I might also like to find and have a 50G or 48G (if nothing else, but for novelty).

#37

Why can't you enter the formulas using the 50g solver, rather than RPL? I don't see why the 50G can't be used for all formulas that a 17BII is capable of handling.
--Tod

Edited: 17 Mar 2009, 2:23 a.m.


#38

Tod,

especially the L() and G() functionality is lacking on the 50G - that alone makes it time consuming to transfer (and later maintain/support) equations written for the 19bII/17bII family to (on) the 50G.

Believe me, I tried it. And yes: If you could come up with a functional replacement of the above mentioned functions, I will change sides ... ;-))

Best regards,

Peter A. Gebhardt


#39

Peter, the solver can solve programs too. Or you can create a simple wrapper function around an RPL program so that it accepts arguments in algebraic notation and can therefore be used in the solver with ease.


#40

Interesting... I will have to explore that and see if perhaps the 50G is not after all, the better calculator.

Thanks

#41

Marcus,

Thanks for the suggestions. I would definitely go with a unit that has TVM built-in, so the 50G would be a likely choice if I were to go with the 48 family calculators. I would also want one with an SD card slot, so again the 50G makes sense. Finally, being able to edit the formulas on a PC would be a HUGE +++++!

Still, the suggestion of the 200LX by Mr. Gerhardt makes sense as well. I would likely get both just to play around, but may settle on one or the other for personal reasons.

Thanks.
Phil

Edited: 16 Mar 2009, 11:02 p.m.

#42

Gentlemen,

Phil's original question was:

Quote:
It seems to me that there should be a way to find for the original loan amount using ONLY the balance, term of 360 months, and payment amount.

Solving an equation of 5 unknowns with only 3 knowns (lacking I% and PV) was asked for. This is impossible - unless someone proves me wrong.

All other questions can (and could) already be solved with standard TVM solver routines available within the calculators mentioned.

Best regards,

Peter A. Gebhardt

Edited: 22 Mar 2009, 7:29 a.m.


#43

Peter, I originally thought that a program could solve this problem, but upon reflection I don't think it can. You would be asking which unique combination of principal/interest rate would produce a specified balance and payment amount, and I imagine there might be an infinite number of such combinations. I tend to agree with you, I don't think it is solvable.


#44

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


#45

Phil, it occurs to me that if you have a recent statement showing the loan balance and payment applied, perhaps the client also has the statement for the previous month laying around the house somewhere, and if that's true, couldn't you calculate the APR using those two statements? I don't know, as I said I'm not a financial guy, but if you had two statements wouldn't that solve your problem?


#46

Don,

That's an excellent point, and something I had completely overlooked. Even if they were more than 1 month apart, the number of periods is a known since the statements are dated, so the irr could be figured out, and from that, perhaps the information could be culled to determine the APR.

It is often that the statements don't show the APR, so we are often going on what the client "remembers", which is often close, but often also inaccurate. Thanks for that suggestion. Perhaps someone can help me to create a formula to use two balances and the payment amount to determine the APR...

...no, wait...

...if the two statements have the remaining balances, and also the payment amount, then the earlier balance would be the PV, and the newer balance would be the FV, and then the known number of periods (by statement dates), coupled with the payment amount (another known), would give me the APR!

Don, I think you've got it!

I have to go and test it now! I am excited by this new information. You may have pointed out an obvious solution to the problem for me.

I'll let you know how it goes.

Thank you.

Phil


Edited: 24 Mar 2009, 12:03 p.m.

#47

Don,

The solution you suggested works!

I did a quick analysis of a loan for $100,000 at 8% and for $733.76. I then took the 74th ($93,608.47) and 75th ($993.498.77) payment balances from the amortization and used them as PV & FV, for 1 month period, and solved for the interest rate, and it computed the right amount of 8%. That gives the calculator the right information to properly evaluate the loan.

Once that has been done, I simply replaced the FV with a ZERO and solved for PV, and it came back with $99,999.01. Close enough to assume $100,000.

It does solve one scenario for me, that is, where the client HAS at least 2 statements and can give me balances along with a payment amount. This gives me both their mortgage rate AND the principal balance, essentially finding for 2 variables (and they said it couldn't be done!) Truthfully, it is really only finding for one variable, since the number of periods is known to be 1, but it is essentially a back door to the solution.

Now that this works, I am still not giving up on the idea that with the limited set of only 3 variables; payment amount, present balance, and term (number of periods), you can still solve for the other 2 unknowns; interest rate and original loan balance.

It should be interesting to see what comes of it.

Thanks.
Phil


Edited: 25 Mar 2009, 7:12 p.m. after one or more responses were posted


#48

I think the best you'll end up with is a set of infinite solutions. However, as you hinted at earlier, the two unknown values will likely fall into some reasonable intervals. You could treat them as random variables. Then, statistically, you'd have some reasonable subset of solutions centered around average values.

If you know two consecutive values over a known period of time, you should be able to use law of organic growth to determine the growth rate (interest rate). That will reduce your problem to one unknown.

-Mike


#49

MikeO, "reasonable intervals"...that's exactly what I meant. I couldn't have put it any better than that. I believe that the word "reasonable" sums it up nicely. It is unreasonable to think that the interest rate on a mortgage could be beyond, say...20% (I've seen 16%!), and it is also unreasonable to be less than perhaps %1.5%, so the real question is "how many intervals are there between those two most reasonable of the unreasonable rates (those outside the two)?".

Then by using the most reasonable rate of the range of reasonable interest rates, the same holds true for the initial loan amount. Once you have reasonable rate, it should be even easier to nail down to a range of reasonable numbers for the PV. It is highly unlikely and therefor unreasonable to think there is more than one interval within a range of for example, between $0.00 and $200,000.00 for PV when the payment is 733.76 (PMT), and the current remaining balance is $93,666.04 (using it as FV), especially if I can guesstimate that the number of periods elapsed is approximately 7 years at 12 periods per year (N=84). For there to have been more than 50% of the principal paid down in such a short time with as low a payment as that would be unreasonable, unless the interest rate were a negative (bank pays you to borrow money), and that is again unreasonable.

It seems to me that a formula could be used that would land on an iterative solution that shows perhaps 2 results just slightly above $100,000 (or is it below $100,000?), indicating the result is likely near $100,000. Then if I plug in $0 into the formula as a known variable for FV and solve for periods (N), it should come back with a number near 360 and some change. Since the number of periods has to be a whole number, and since we know that nearly all mortgages are in increments of 5 years (15, 20, 30, 40, etc.), the obvious answer would be 360 (30 yrs, 12 periods per year). At that point, I could solve for PV and it should result in almost exactly $100,000.00.

Again, my logic may be flawed, but then I am not a mathematician. I am waiting for those who can either confirm or deny my suspicions.


Edited: 25 Mar 2009, 10:16 p.m.


Possibly Related Threads…
Thread Author Replies Views Last Post
  hp-prime solver and variable name fabrice48 22 8,482 12-10-2013, 03:25 AM
Last Post: fabrice48
  WTF? W-here T-o F-ind the original hpmuseum forum? Alexander Oestert 40 14,113 12-05-2013, 01:25 PM
Last Post: Matthias Wehrli
  HP Prime Triangle solver BruceH 29 8,803 11-28-2013, 12:03 AM
Last Post: Dale Reed
  Using units in Numeric Solver Harold A Climer 1 1,293 10-13-2013, 10:44 AM
Last Post: Tim Wessman
  Does Prime Have a Multiple Equation Solver? Norman Dziedzic 2 1,405 09-20-2013, 09:43 AM
Last Post: Norman Dziedzic
  re-recording original cards aj04062 2 1,541 09-15-2013, 10:32 AM
Last Post: Dieter
  WP-34S Matrix operations with routine-local registers? Tom Grydeland 1 1,214 09-04-2013, 10:46 AM
Last Post: Marcus von Cube, Germany
  Just a lazy solver algortihm PGILLET 1 1,093 06-28-2013, 11:47 PM
Last Post: Namir
  SandMath routine of the week: Inverse Gamma Function Ángel Martin 39 9,707 03-24-2013, 08:19 AM
Last Post: peacecalc
  [43s] : How the solver will be implemented Miguel Toro 3 1,641 03-14-2013, 06:09 PM
Last Post: Walter B

Forum Jump: