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