To compute the Mortgage payment, the best way is to design a Mortgage Calculator with details of payment.

To design a Mortgage Calculator, you need to follow the following steps:

► Play

  1. In a new Sheet of excel, In the very first column, type Loan Amount (Principal), Annual Interest, Payment in year and No of years. You will get something like this:
  1. For Total Payments use formula, Payments per year * Number of years. In making calculator we have value of Payments per year in B3 and No of years in B4. So we will use formula “=B3*B4” in B5.
  1. Now in another cell of A column, type Mortgage Payment to compute actual mortgage value
  1. Now in cell adjacent to Mortgage Payment, we will use PMT formula. This Formula is used to calculate the payment for a loan based on constant payment and constant interest rate.
  1. Now to construct PMT formula
  • First we need to input value of Interest according to Payment Cycle. We can get the desired result by dividing the Annual Interest by Payment per year that is B2/B3, according to above picture.
  • Then we need total payments, which we derived in cell B5.
  • Then at last, we need present value that is Loan amount or principal. We have such amount in Cell B1.
  • After this we will use the following formula in cell B6:
    =PMT(B2/B3,B5,B1)
  1. Calculator is fairly constructed and now we just need to enter the values and compute the mortgage payment also known as EMI.   
                       

Tips:

  • Always use percentage sign after writing annual interest, otherwise calculator might not work.
  • Don’t panic on getting Payment mentioned with Red Text showing some negative value, because it is the general format in which Answer is shown.

Always use this calculator if loan is charged on Simple interest. This formula doesn’t work with loans charging compounded. For this you need to convert compound interest rate into simple interest rate.

Send us a feedback0/500

Do you like this article?
Yes
No