You are likely to shop around for a mortgage loan before you sign on the dotted line prior to a big-ticket purchase. Such loans are usually applicable to purchases such as a new house or any other form of property and typically have a low rate of interest. That again is because of the fact that the loan is secured against the title of the property you intend to buy.

In order words, the legal rights to the property get vested with the lender, usually a bank or an organization, in case the borrower is unable to pay back the debt as per terms laid down by the lender. This makes it extremely important that you weigh in your options before committing to a mortgage loan.

That includes looking beyond the monthly payment that you need to make as there could be other terms hidden between the lines and which could prove not too friendly in the long term. Mentioned below are a few methods to calculate the mortgage loan payments so that the same does not prove to be a liability on your finances as the month’s pile on.

Using Spreadsheet programs to calculate mortgage loan payments

Spreadsheet programs such as Microsoft Excel can be used to calculate mortgage loan payments. The way to do that is using the payment function or the PMT which takes into account info such as the principal amount you have borrowed, the rate of interest applicable, and the number of months or years you need to repay the loan. With this information, you can easily calculate the amount you need to pay every month.

While we will be focussing here on Microsoft Excel, the method is all the same for other spreadsheets such as the Google Spreadsheet or Apple Numbers.

You start by typing =PMT in the spreadsheet. You will be shown the following PMT(rate, nper, pv, [fv], [type]), which prompts you to enter the other factors.

Towards that, the rate happens to be the rate of interest calculated on an annual basis. You have to convert it into the monthly rate of interest using this simple process.

Suppose the annual rate of interest is 6 percent. The monthly rate of interest will be 6 percent/ 12. However, the equation requires you to input the value in decimal form, which is achieved by dividing the entire thing by 100. So, it is going to be like this – (6%/12)/100 = 0.005.

nper stands for the number of periods and refers to the number of payments you need to make to clear the loan. This is a yearly figure and if you wish to calculate the monthly figure, you got to multiply it by 12.

To elaborate, if you are taking a loan for a 10-year period, the nper value is going to be 10 * 12 = 120.

pv is short for the present value or principal value of the loan. For instance, if you have borrowed $50,000, that is going to be the pv figure.

The other two values are options and can well be left blank.

So, for a $50,000 loan for 10 years at an annual 6% rate of interest,

  • pv = $50,000
  • nper = 120
  • rate = 0.005

In the spreadsheet, here is what you will have to enter

=PMT(0.005, 120, 50000).

Once you have pressed Enter, the PMT function will return the value that you will have to pay back to the lender every month. Here, it stands at 421.93.

The point to note here is that the figure is represented as a negative number. That is simply because it is an expense that will be flowing out of your savings every month.

Using Equation to calculate mortgage loan payments

The sight of the equation might seem overwhelming but once you become familiar with each constituent value, you will fall in love with it. Here is the equation:

Here is what each variable stands for:

  • M – monthly payment
  • P – principal amount
  • r – the monthly rate of interest which is deduced by dividing the yearly rate by 12 and then again by 100 as before.
  • n – number of months you will be repaying the loan amount.

We will be using the same figures as used in the last method to arrive at the monthly amount to be paid.

As such,

  • P = $50,000
  • r = 0.005
  • n = 120 months

Substitute the variables with the above values. Post that, allow your math skills to take over. Just in case you need a headstart, solve the portion within the parenthesis, followed by the multiplication and division portions.

Developing an Amortization Schedule

An amortization schedule spells out how the monthly amount you pay will be sliced off the principal and the interest. The schedule will also show you how much you owe at the end of the month. As with the first method, this is done using a spreadsheet. We will be demoing it using Microsoft Excel.

Here is how it is done.

In cell A1, enter the Annual Interest Rate, followed by entering the annual interest figure in cell B1.

Then in cell A2, enter Loan Duration while entering the loan tenure in years in cell B2.

Follow the same for other figures such as Payment Per Year and Loan Principal in the A3 and A4 cells.

Next, you need to enter the following in the 6th Row, from columns A to E:

  • Payment number.
  • Payment amount.
  • Principal payment.
  • Interest payment.
  • Loan balance.

Thereafter, here are the values you need to enter in each column. For Payment number, enter 1.

  • Under Payment amount, type = pmt(B1/B3,B2*B3,B4)
  • Under Principal payment, type = ppmt(B1/B3,A7,B2*B3,B4) refers to the principal amount you will be paying each month.
  • Under Interest payment, type = ipmt(B1/B3,A7,B2*B3,B4) this shows the interest amount you are paying each month.
  • Finally, under Loan balance, type = (B4 + C7). The C7 cell will be showing the payment amount.

To complete the amortization schedule, you will have to select the cells from A7 to E7, followed by dragging the calculations to the last payment. The loan balance under column E should be showing $0. Keep in mind the payment number is calculated by the multiplication of the number of annual payments and the loan duration years.

Also, just in case the loan payment numbers aren’t updating, type = (A7 + 1) in cell A8. Thereafter, drag cell A8 to the end of the schedule. The figures will update automatically.