# How to Calculate Mortgage Payment in Excel

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:

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

**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 “” in B5.*=B3*B4*

**Now in another cell of A column,**type**Mortgage Payment**to compute actual mortgage value

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

**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, according to*B2/B3***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)*

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

**Maybe you interest:**