EXCEL – Mortgage

Excel offers incredible features that can assist your business and personal finances. One of the common scenario faced is loan negotiation, whether it be buying a first home or equipment purchase for business. Out of this, the key question arise as to HOW MUCH is required to be paid to service this loan. Excel offers a simple but effective formula that you can use.

The syntax for the PMT() function:

PMT (rate, nper, pv, [fv], [type])

The variables covered with “[ ]” are optional, in that it is not mandatory for the formula to work.

Syntax Description
rate Fixed rate of interest
nper No. Of payments
pv Loan Principal
fv Future value of Loan
type

0 for end-of-period

1 for beginning-of-period

As in mathematical proportion, your input variables must adopt a COMMON time frame, otherwise your results will be inaccurate. Taking an example,

Variable Input Explanation
rate 6 % Annual (per annum)
nper 3 Years (term of loan)
pv $150,000 Loan Amount

Enter into Excel:

=PMT(0.06,3,150000)

This will give you the ANNUAL repayment amount on a $150,000 loan with a term of 3 years at 6% per annum. To obtain the monthly repayment amount, you simply divide this annual amount by 12. You may try out the formula on our EXCEL PAGE

Obviously, the power of Excel is further compounded by placing cell references in the formula. Therefore, each of the syntax could actually point to a specific cell reference. You could then observe the result changes by changing the variables in the specific cells. So for example,

Let’s say you enter the following into Excel.

  1. In CELL A1, you enter 6%. This is your interest rate.
  2. In CELL A2, you enter 3. This is the term of your loan.
  3. In CELL A3, you enter 150000. This is the amount of your loan.
  4. In CELL A4, you enter the formula “=PMT(A1,A2,A3)”
  5. Now instead of going into the formula to change the variables, you merely change them at the various cell locations and see your results change accordingly.


Leave a Reply


*