How to Calculate Your Mortgage Repayment Amount

Buzz

Ngày cập nhật gần nhất: 1/5/2026

Content

Steps to Follow

Calculate Mortgage Repayment Using a Spreadsheet Program

Calculate the Repayment Using the Formula

Create an amortization schedule.

If you're considering purchasing a home or another property, you may need to calculate how to secure a mortgage. This is a specific loan designed for real estate purchases and generally comes with a lower interest rate compared to other types of loans. Since this loan is secured by the property itself, meaning the lender (typically a bank) can seize the property if the borrower defaults, it's essential to find a loan with the lowest interest rate possible. This ensures that you can repay the debt responsibly and within a reasonable timeframe. Below is a guide to help you calculate the monthly repayment to make an informed decision.

Steps to Follow

Calculate Mortgage Repayment Using a Spreadsheet Program

Understand the Formula Used. The mortgage repayment can be easily calculated using a spreadsheet program. In most popular spreadsheet applications (Microsoft Excel, Google Sheets, and Apple Numbers), this feature is known as the PMT function, or the payment function. To calculate the monthly repayment, you need to input various details such as interest rate, loan term, and principal amount.

For simplicity, let's focus on the PMT function in Microsoft Excel. The process and data input may be identical or similar to other programs you might be using. Refer to the help tab or customer service if you encounter any issues with this function.

Activate the PMT Function. Start using the PMT function by entering =PMT( into your spreadsheet. The program will then prompt you to enter the appropriate values for each part of the function, displaying the following parameters: PMT(rate, nper, pv, [fv], [type]). The first three values are required, while the last two are optional.

rate is the monthly interest rate. Note: This is calculated by dividing your annual interest rate (the rate stated on your loan agreement, e.g., 4% or 5%) by 12. This value must be entered as a decimal.
For example: If your annual interest rate is 6%, divide this by 12 to get the monthly rate. You get 6%/12, which equals 0.5%. However, the value entered into the formula must be in decimal form, so divide this by 100. This gives you 0.005, which is the monthly rate you’ll use for your mortgage calculations.
This calculation can also be done in the reverse order (6%/100 = 0.06, 0.06/12 = 0.005).

nper stands for “number of periods,” meaning the total number of payments you’ll make for the loan. For monthly payments, this value will be 12 times the number of years of the loan.

For example, if you have a 15-year mortgage, your “nper” value would be 12*15, which equals 180.

pv stands for “present value,” but in this case, it simply refers to the principal loan amount.

For this example, let’s say you have a loan of $100,000. This would be your “pv” value.

Don’t worry about the other two values; if you leave them blank, the program will default them to 0.

Enter Information and Press Enter. Once you enter the data, the program will display the monthly repayment amount in the same cell where you entered the formula. Keep in mind that this value will appear as a negative number, which simply means that the program is showing this amount as an outgoing payment (or expense).

In the example above, you would input =PMT(0.005, 180, 100000).

Interpret the Results. The PMT function will give you the amount you need to pay each month on your loan. This number will be displayed as a negative value. This doesn't mean you entered the wrong information; the negative sign just indicates that the amount is an outgoing payment. If you need this number for further calculations or want to understand it more clearly, you can simply multiply the result by -1.

For the example above, the result would be -843.86 USD. Multiply this by -1 to get the monthly repayment amount of 843.86 USD.

Calculate the Repayment Using the Formula

Learn About the Formula.

M = P \frac{r (1 + r)^{n}}{(1 + r)^{n} - 1}

M is the amount you need to pay monthly.
P is the principal loan amount.
r is the monthly interest rate, calculated by dividing the annual interest rate by 12.
n is the number of payment periods (the months over which you will repay the loan).

Enter your information into the formula.

For example, let's assume you have a mortgage loan of 100,000 USD with an annual interest rate of 6% over 15 years.

Thus, "P" would be 100,000 USD.

For "r", you will enter the monthly interest rate, which is calculated by dividing 0.06 (6%) by 12, resulting in 0.005 (0.5%).

For "n", you will enter the total number of payments, with one payment per month over 12 years, which is 12*15, equaling 180.

M = $ 100.000 \frac{0, 005 (1 + 0, 005)^{180}}{(1 + 0, 005)^{180} - 1}

Simplify the formula by adding 1 to the value of "r."

M = $ 100.000 \frac{0, 005 (1, 005)^{180}}{(1, 005)^{180} - 1}

Solve the exponentiation.

x^{y}

To perform this step, input the value inside the parentheses, which in this case is (1.005), then press the exponentiation button, input the value of "n", and press enter or =. For example, the result calculated is 2.454.

If your calculator does not have an exponentiation function, you can type the calculated value inside the parentheses into the Google search bar followed by the string ^(n), ensuring to replace "n" in the parentheses with the value of "n". The search engine will assist in computing the result for you.

Note that the exponentiation is only applied to the value inside the parentheses, not to the value of "r" outside the parentheses (before) or the -1 at the end of the equation.

M = $ 100.000 \frac{0, 005 (2, 454)}{2, 454 - 1}

Proceed with simplifying the equation further.

M = $ 100.000 \frac{0, 01227}{1.454}

Now, divide the numerator by the denominator.

M = $ 100, 000 * (0.008439)

Multiply the value of "P" by the result obtained. This product is the monthly payment you need to make.

In this example, the monthly payment required will be (100,000 USD) * (0.008439), which equals 843.90 USD.

Create an amortization schedule.

Set up your amortization schedule. The schedule will show you how your monthly mortgage payment will be divided between principal and interest. It will also display the remaining loan balance at the end of each month. Start by entering basic loan details in the top left corner of your spreadsheet. For example, in cell A1, enter "annual interest rate." Then, input your annual interest rate as a percentage in cell B1. Continue by entering the loan term in years in cell A2 and its corresponding value in column B. Similarly, fill in the annual payment and principal balance in cells A3 and A4.

Create the amortization time column. After entering your loan information, leave one blank row. Then, enter the following labels in row 6, from column A to E, in the spreadsheet:

Number of Payments.
Payment Amount.
Principal Payment.
Interest Payment.
Remaining Loan Balance.

Fill in the first month's deduction details. Below the column titles you just created, begin entering your loan data. Under the "Number of Payments" column, input the number 1. Then, under the "Payment Amount" column, enter "=pmt(B1/B3,B2*B3,B4)". This is the payment formula. In the "Principal Payment" column, input "=ppmt(B1/B3,A7,B2*B3,B4)". This formula calculates the principal portion of your payment. In the "Interest Payment" column, type "=ipmt(B1/B3,A7,B2*B3,B4)". This formula computes the interest portion and shows how much interest is being paid each month. Finally, in the "Remaining Loan Balance" column, input "=(B4+C7)".

Cell A7 should represent the first payment number, which is 1.
Cell C7 contains the payment amount.

Complete the amortization schedule. Select the range from cell A7 to E7. Then, drag the calculations down to the last payment row. At this point, the balance in column E will be 0 USD. Remember, the number of payments is calculated by multiplying the annual number of payments by the loan term in years.

If the payment count column does not update automatically, enter "=(A7+1)" in cell A8 (second payment) and drag it down to the end of your schedule. The other cells will update accordingly.

Advice

This can also be a useful method to compare mortgage plans. For example, you might choose between a 15-year loan with a 6% interest rate or a 30-year loan with a 4% rate. The calculator will help you see that, although the 15-year loan has a higher interest rate, it ends up being cheaper overall.
Depending on your mortgage loan terms, you may be able to make payments that exceed the required monthly amount, and the extra will be applied towards either interest or principal. Check with your lender to see if this option is available.
The easiest way to calculate this is by using an online mortgage calculator. These tools can help you figure out your monthly payment by simply entering a few key details. Try searching for "mortgage loan calculator" in your preferred search engine. Usually, you’ll need to enter details about your loan such as the term, annual interest rate, and loan principal value. Then, just hit "calculate" and the calculator will show you the results.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]