ECalcPad home |
Download ECalcPad free |
DataOdyssey Projects
home |
DataOdyssey
Express - Data Mining made easy |
EasySql4Fox -
VFP adapt to SQL(MySQL,ORACLE) quickly |

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

Variable | Expression | Result | Comments |

YEARS | 30 | 30 | |

INTEREST | 7.5 | 7.5 | % |

PRINCIPAL | 100000 | 100000 | |

PAYMENT | PAYMENT(PRINCIPAL,INTEREST/1200,YEARS*12) | 699.21 | Monthly payment |

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:

Variable | Expression | Result | Comments |

YEARS | 30 | 30 | |

INTEREST | 7.5 | 7.5 | % |

PAYMENT | 700 | 700 | Monthly payment |

PRINCIPAL | PV(PAYMENT,INTEREST/1200,YEARS*12) | 100112.3391231 |

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:

DOWNPAYMENT | first prepayment. Useful if you want to find out the total amount of all payments with interests included. |

TAXRATE | rate of income tax rate which you can declare as income deduction. |

TOTAL | loan sum with downpayment and all interests without tax deduction. If this is a home then it will be full cost. |

TAXES | income tax deduction. |

COST | final sum. The total amount with tax deduction. |

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:

Variable | Expression | Result | Comments |

FIXEDYEARS | 5 | 5 | Fixed rate years (first) |

ADJUSTEDPERIOD | 1 | 1 | Adjusted rate period years |

INTERESTFALL | 0.03 | 0.03 | Forecasted annual interest rate fall |

Next - sums fo FRM years

FIXEDPAYED | PAYMENT*FIXEDYEARS*12 | 41952.8705132 |
Fixed rate period sum |

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:

Variable | Expression | Result | Comments |

INTERESTn | INTEREST-INTERESTFALL*(FIXEDYEARS+ADJUSTEDPERIOD*n) | NNN | Adjusted rate for n period |

PAYMENTn | PAYMENT(PRINCIPAL,INTERESTn/1200,YEARS*12) | NNN | Monthly payment for n perion |

TOTALn | PAYMENTn*ADJUSTEDPERIOD*12 | NNN | |

ECONOMYn | PAYMENT*ADJUSTEDPERIOD*12-TOTALn | NNN |

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