Mortgage calculation with ECalcPad

Lots of websites provide users with information on mortgage calculations, sometimes quite complicated. We've done some research and become real experts in mortgage calculations. Finally, we decided to write this article to show you how to properly calculate mortgage as well as demonstrate what can be done with the help of ECalcPad. 

To begin with, some general information on mortgage calculations - you'll really need this if you're thinking of buying a new house.

Types of mortgage:

Actually, all types of mortgage calculations are based on the same algorithm. The sum of loan is divided into a number of years. In the beginning of each year the lender will calculate an interest sum ( percentage from the remaining loan sum you have to pay). Divide the total sum of annual interests into number of months and you will see your monthly payment sum. It is a magic formula of bankers/investors which allows to convert very modest 5-10% into very actual 150%. All types of mortgage calculations differ by methods of interest definition. Below there are two of these types.

a) Fixed rate mortgage (FRM). Simplest and most popular loan. This loan type surely has its pros and cons. You always know your debt sum which will be constant even if next glacier period begins. Here's how you may calculate a monthly payment with ECalcPad:

First, we define the initial data as separate variables. The initial data we need are loan period in YEARS, loan sum (PRINCIPAL) and fixed INTEREST. Then we use  PAYMENT() function which allows to calculate monthly payments with the "magic formula" described above.

On the other hand,  you may only know the sum of monthly payments and still want to know the loan sum you can take. In this case we use monthly payment sum as initial data and use another PV() function which allows to calculate loan sum (without interest) from monthly payment. In this case ECalcPad calculation will look like:

If you use mortgage patterns generation (described below), the FRM pattern will remain as first lines of the template. Two types of calculation described above (direct and back calculations) will be combined inside IF ... ELSE ... ENDIF construction.

Type YES as expression for the line with IF in VARIABLE cell if you know the sum of loan and want to find out the amount of monthly payment. Type NO if you plan a monthly payment and want to find out loan sum.

Other variables and sums are presented in the template:

If you want to calculate something else about fixed rate mortgage, it's also easily done with ECalcPad.

b) Adjusted rate mortgage (ARM). Consist of several periods with fixed rates. Interest rate for each period can be different.

Those who took a loan in 80s save thousands. But who knows what awaits us in the future?

ARM always begins with fixed rate period and has two properties:

• number of fixed rate years;
• length of adjusted rate period in years.

According to these properties,  a loan can be called: 5/1, 7/1, 10/1, 7/23 where numerator is fixed rate years and denominator is the length of adjusted rate period in years.

The main question you will ask when selecting a type of mortgage is how much you can save and how much you will lose. You can think of a so-called "cap" (a limit on the increase amount of the interest rate or the monthly payment). With ECalcPad you can figure out what you save or lose using ARM formula and any interest rate function. As functions of rate deviation can be various, we offer to change the number of fixed rate years and each adjusted rate period. You can create a template of mortgage calculation where interest rate will initially fall linearly. You can change percentage of rate fall in single row of a calculation to receive various results, or you can enter interest rate manually for each adjusted rate period. Besides, you can enter various values of any initial data except years. If you want to change total years of loan or length of periods (fixed as well as adjusted), you should generate a new template.

Create a new calculation , click "Patterns" menu option and select  "Generate Fixed/Adjusted rates mortgage calculation..." bar.
Enter total years and periods length in the dialog box:

Click "Generate" and a new calculation will appear in ECalcPad. It begins with a fixed rate mortgage calculation which is described above. After FRM lines you will see ARM calculation lines, which begin with ARM initial data declaration:

Next - sums fo FRM years

See the FRM calculation to find variables used in ARM expressions.

Now we can begin modulating adjusted rate periods. In our case there are 25 periods. The calculation of each period will consist of the following lines:

where n is the number of a period.

First we calculate or define interest. Interest is calculated as a linear function with INTERESTFALL from INTEREST in FRM lines. Change a value of fall in expression of INTERESTFALL variable to predict various situations. In future you can replace the formula of INTERESTn for each period with actual value or rate.
Periods calculations in ECalcPad look like this:

And in conclusion we see the following lines:

Of course if you know all about mortgage calculations, you can calculate anything without this tutorial, still, it might have saved your time.

Home page                 "Valley of mind" software home page        ECalcPad manual