4 Ways to Calculate Mortgage Payments - wikiHow (2024)

  • Categories
  • Finance and Business
  • Banks and Financial Institutions
  • Lending
  • Property Loans and Mortgages

Download Article

Explore this Article

methods

1Calculating Mortgage Payments Using a Spreadsheet Program

2Calculating Mortgage Payments with an Equation

3Creating an Amortization Schedule

Useful Documents

+Show 1 more...

-Show less...

Other Sections

Video

Tips and Warnings

Related Articles

References

Article Summary

Co-authored byDmitriy Fomichenko

Last Updated: May 17, 2024Approved

Download Article

If you're considering buying a house or another type of property, you'll likely have to shop around for a mortgage loan. This type of loan is specific to property purchases and usually carries a low interest rate compared to other loans. This is because the loan is secured using the property, meaning that the lender, in many cases a bank, has the right to seize the property in the event that the borrower fails to pay them back.[1] As such, it's important to find the loan with the cheapest rate you can so that you can pay it back responsibly and in a reasonable amount of time. Use the following methods to calculate your monthly payments so that you can make the right choice.

Method 1

Method 1 of 3:

Calculating Mortgage Payments Using a Spreadsheet Program

Download Article

  1. 1

    Understand the function used. Mortgage payments can be easily found using your chosen spreadsheet program. This function, in all major spreadsheet programs (Microsoft Excel, Google Spreadsheet, and Apple Numbers), is known as PMT, or the payment function.[2] It combines information like your interest rate, number of periods, and principal to arrive at an amount for each monthly payment.

    • For simplicity, we will be focusing on Microsoft Excel's PMT function here. The process and inputs will likely be identical or very similar for any other program you are using. Consult the help tab or customer service if you have any problems using these functions.
  2. 2

    Start using the PMT function. Start using the PMT function by typing =PMT( into your spreadsheet. The program will then prompt you for the proper entries into each part of the function by showing the following: PMT(rate, nper, pv, [fv], [type]). The first three represent required inputs, while the last two are optional.[3]

    • rate stands for the monthly interest rate. Note that this will be your annual interest rate (the quoted rate on your loan agreement, like 4 or 5 percent) divided by 12. It should also be expressed as a decimal.
      • For example, if your annual interest rate is 6%, you would divide this number by twelve to get your monthly interest rate. This would be 6%/12, or 0.5%. However, this number must be input in the equation as decimal, so we divide again by 100. So we have 0.5%/100, which equals 0.005. This will be your monthly interest you will use to calculate mortgage payments.
      • These calculations can also be done in a different order (6%/100 = 0.06, 0.03/12 = 0.005).
    • nper is short for "number of periods" and simply represents how many payments you will make on your loan. For a monthly payment, this would be 12 times the number of years on your loan.
      • Imagine for this example that you have a 15-year mortgage. So, your "nper" value, or your number of payments, would be 12*15, or 180.
    • pv stands for "present value" but here it simply means the principal of your loan.
      • For this example, imagine you have a $100,000 loan. This will be your "pv".
    • Don't worry about the other two values; leaving them blank will make the program assume their correct value of 0.

    Advertisement

  3. 3

    Enter this information and press enter. The program will display your monthly payment amount in the same cell you entered the formula into. Note that this number will be negative, this is simply the program expressing it as a payment (or expense).[4]

    • In the example above, this information would be entered as =PMT(0.005, 180, 100000).
  4. 4

    Analyze your result. The PMT function will return an amount that represents the total amount you will pay on the loan each month. Know that this number will be expressed as a negative number. This doesn't mean you entered your information incorrectly, but simply that the program represents payments as an expense and therefore, a negative number. Multiply by -1 if this helps you understand and use the figure.[5]

    • The spreadsheet should return -$843.86 when you enter your function as described above. Multiply this number by -1 to get your monthly payment of $843.86.
  5. Advertisement

Method 2

Method 2 of 3:

Calculating Mortgage Payments with an Equation

Download Article

  1. 1

    Understand the equation. In order to calculate the monthly payment, we can rely on a relatively simple equation. The monthly payment equation can be represented as follows: 4 Ways to Calculate Mortgage Payments - wikiHow (10). These variables represent the following inputs:

    • M is your monthly payment.
    • P is your principal.
    • r is your monthly interest rate, calculated by dividing your annual interest rate by 12.
    • n is your number of payments (the number of months you will be paying the loan)[6]
  2. 2

    Input your information into the equation. You will need to input your principal, monthly interest rate, and number of payments in order to find your monthly payment. This information can be easily found in your loan agreement or from a quoted loan estimate. Check the information again to be sure of its accuracy before using it in calculations.

    • For example, imagine you have a $100,000 mortgage loan with 6 percent annual interest over 15 years.
    • Your input for "P" would be $100,000.
    • For "r," you would use your monthly interest rate, which would be 0.06 (6 percent) divided by 12, or 0.005 (0.5 percent).
    • For "n" you would use your total number of payments, one for each month in fifteen years, which would be 12*15, or 180.
    • In this example, your complete equation would look like this:4 Ways to Calculate Mortgage Payments - wikiHow (12)
  3. 3

    Simplify your equation by adding 1 to the "r." Simplify your terms by doing the first step in the order of operations, which is adding the 1 and "r" inside the parentheses on the top and bottom of the equation. This is a simple step that will make your equation look much less complicated.

    • After this step, your sample equation would look like this:4 Ways to Calculate Mortgage Payments - wikiHow (14)
  4. 4

    Solve the exponents. The results inside the parentheses, (1+ r), from the previous step must now be raised to the power of "n." Again, this "n" represents the total number of payments. This step requires a calculator with an exponent function, which is usually represented like this: 4 Ways to Calculate Mortgage Payments - wikiHow (16).

    • This is done by entering the value to be raised, (1.005) in the example equation, then pressing the exponent button, then entering your value for "n" and pressing enter or =. In the example, the result comes out as 2.454.
    • If you don't have such a calculator, type your values from the last equation into Google followed by ^(n) while replacing the "n" in parentheses with your value for "n." The search engine will calculate this value for you.
    • Keep in mind that only the figures inside the parentheses will be raised to this power, not the "r" outside of them (at the front) or the -1 at the end of the equation.
    • After this step the sample equation would look like this:4 Ways to Calculate Mortgage Payments - wikiHow (17)
  5. 5

    Simplify again. Here, you should multiple "r" times the result of the last step on the top (the numerator) and subtract 1 from your result on the bottom (the denominator).

    • The same equation would look like this after this step:4 Ways to Calculate Mortgage Payments - wikiHow (19)
  6. 6

    Divide the numerator by the denominator. This means dividing the top part of the equation by the bottom part of the equation. This should leave you with a small decimal.

    • In the example, your equation would now be:4 Ways to Calculate Mortgage Payments - wikiHow (21)
  7. 7

    Multiply "P" by this result. This will give you your monthly loan payment.

    • In the example, this would be ($100,000)*(0.008439), or $843.90. This represents your monthly payment.

    4 Ways to Calculate Mortgage Payments - wikiHow (23)

    4 Ways to Calculate Mortgage Payments - wikiHow (24)Financial literacy is empowering. "This was really useful to me. Not only was I looking to calculate whether I could afford a mortgage on my dream property, but I also learned about how I could use these tutorials for math and ICT lessons, so thank you!" - Marie W.

    4 Ways to Calculate Mortgage Payments - wikiHow (25)I needed to learn this calculation for work. "My boss asked me if I knew how to calculate a mortgage. I said I could look it up and found this article. I made a spreadsheet calculating a mortgage payment using both the formula and function methods I learned here. My boss was really impressed with my work!" - Daniel G.

    4 Ways to Calculate Mortgage Payments - wikiHow (26)Did you know that wikiHow has collected over 365,000 reader stories since it started in 2005? We’d love to hear from you! Share your story here.

  8. Advertisement

Method 3

Method 3 of 3:

Creating an Amortization Schedule

Download Article

  1. 1

    Set up your amortization schedule. An amortization schedule will tell you exactly how your monthly mortgage payments will be split between paying off principal and interest and what your balance will be at the end of each month. Start by inputting the basics of your loan information in the top left of a spreadsheet program. For example, in cell A1, write out "annual interest rate." Then, input your annual interest rate as a percentage in the next cell over, B1. Continue down to cell A2 for the loan duration in years, inputting the amount in column B as before. Do the same for payment per year and loan principal in cells A3 and A4, respectively.[7]

  2. 2

    Create your amortization schedule columns. Skip a line under your loan information. Then, place the following words across the spreadsheet, in row 6 from columns A to E:

    • Payment number.
    • Payment amount.
    • Principal payment.
    • Interest payment.
    • Loan balance.[8]
  3. 3

    Fill out your first month's amortization. Directly under the column headers you just created, start filling out your loan information. Under payment number, place a 1. Then, under payment amount, type in "=pmt(B1/B3,B2*B3,B4)". This is the payment function. Under principal payment, type "=ppmt(B1/B3,A7,B2*B3,B4)". This is the principal payment function and it shows the amount of the principal paid down each month. Under interest payment, type "=ipmt(B1/B3,A7,B2*B3,B4)". This is the interest payment function and it shows the amount of interest paid down each month. Finally, under loan balance, type "=(B4+C7)".

    • Cell A7 should contain your first payment number, 1.
    • Cell C7 should contain the payment amount.[9]
  4. 4

    Complete your amortization schedule. Select the range from cell A7 to E7. Then, drag the calculations down to the last payment. At this point, the loan balance in column E should be $0. Remember that your number of payments is calculated by multiplying the number of annual payments by the loan duration in years.[10]

    • If your loan payment numbers don't update down the amortization schedule. Type "=(A7+1)" into cell A8 (payment 2) and drag that down to the end of your schedule. The rest of the numbers will then update.
  5. Advertisement

Mortgage Payment Cheat Sheet

Mortgage Payment Calculator

Expert Q&A

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

      Advertisement

      Video

      Tips

      • It's easiest to use an online mortgage loan calculator.[11] Online calculators that can find your monthly payment with the simple input of a few pieces of key information. Try searching for "mortgage loan calculator" using your preferred search engine. Usually, you'll have to input details of your loan, like the number of years, annual interest rate, and value of your principal. Then, simply hit "calculate" and the provided readout should tell you anything else you need to know.[12]

        Thanks

        Helpful1Not Helpful3

      • This can also be a useful way to compare mortgage plans. For example, you may be deciding between a 15-year loan at 6 percent or a 30-year loan at 4 percent. The calculator will help you easily see that, despite the higher interest rate, the 15-year loan is a cheaper option.

        Thanks

        Helpful11Not Helpful11

      • Depending on the terms of your mortgage loan, you may be able to pay more than your required payment each month and apply your extra amount to interest or your principal. Contact your lender to see if this is a possibility.

        Thanks

        Helpful11Not Helpful14

      Submit a Tip

      All tip submissions are carefully reviewed before being published

      Name

      Please provide your name and last initial

      Submit

      Thanks for submitting a tip for review!

      Advertisement

      You Might Also Like

      How toCreate a Mortgage Calculator With Microsoft ExcelHow toPay off Someone Else's Mortgage
      How toAdd Someone to Your MortgageHow toRemove a Name from a Mortgage Without RefinancingHow toAssume a MortgageHow toRemove a LienHow toCalculate an Escrow PaymentHow toCalculate Mortgage InterestHow toTalk to Mortgage LendersHow toCalculate Mortgage PayoffHow toCalculate Mortgage Insurance (PMI)How toReport Mortgage Fraud

      Advertisement

      More References (3)

      About This Article

      4 Ways to Calculate Mortgage Payments - wikiHow (47)

      Co-authored by:

      Dmitriy Fomichenko

      Financial Planner

      This article was co-authored by Dmitriy Fomichenko. Dmitriy Fomichenko is the president of Sense Financial Services LLC, a boutique financial firm specializing in self-directed retirement accounts with checkbook control based in Orange County, California. With over 19 years of financial planning and advising experience, Dmitry assists and educates thousands of individuals on how to use self-directed IRA and Solo 401k to invest in alternative assets. He is the author of the book "IRA Makeover" and is a licensed California real estate broker. This article has been viewed 1,198,894 times.

      6 votes - 50%

      Co-authors: 28

      Updated: May 17, 2024

      Views:1,198,894

      Categories: Property Loans and Mortgages

      Article SummaryX

      To calculate what your mortgage payments will be, type the payment, or PMT, function into a spreadsheet. You will be prompted to input your monthly interest rate, the number of payments during the loan period, and the principal on your loan. Once you have typed in these numbers, hit enter to get your monthly payment. Keep in mind that the result will be displayed as a negative number because it is an expense. To learn how to use a mathematical formula to calculate your mortgage payments, keep reading!

      Did this summary help you?

      In other languages

      Spanish

      Chinese

      • Print
      • Send fan mail to authors

      Thanks to all authors for creating a page that has been read 1,198,894 times.

      Reader Success Stories

      • 4 Ways to Calculate Mortgage Payments - wikiHow (48)

        Daniel G.

        Oct 5, 2018

        "My boss asked me if I knew how to calculate a mortgage. I said I could look it up and found this article. I made a..." more

        Rated this article:

      More reader storiesHide reader stories

      Did this article help you?

      Advertisement

      4 Ways to Calculate Mortgage Payments - wikiHow (2024)

      FAQs

      How to easily calculate mortgage payment? ›

      For example, if your interest rate is 6 percent, you would divide 0.06 by 12 to get a monthly rate of 0.005. You would then multiply this number by the amount of your loan to calculate your loan payment. If your loan amount is $100,000, you would multiply $100,000 by 0.005 for a monthly payment of $500.

      What 4 factors affect the amount of a person's mortgage payment? ›

      It's not just the cost of the home parceled out over months and years. In fact, your monthly mortgage payment is made up of four main parts: the Principal, the Interest, the Taxes and the Insurance, altogether known as PITI.

      What are the 3 methods utilized to calculate loan payments? ›

      Principal, interest rate, and loan term are used to determine the monthly payment made when repaying a loan. Principal is the money you originally agreed to pay back on a loan. It is often referred to as the amount of money you borrowed.

      What are the four parts of the mortgage payment? ›

      Your monthly mortgage payment typically has four parts: loan principal, loan interest, taxes, and insurance.

      What is the rule of thumb for mortgage payments? ›

      The 28% rule

      To determine how much you can afford using this rule, multiply your monthly gross income by 28%. For example, if you make $10,000 every month, multiply $10,000 by 0.28 to get $2,800. Using these figures, your monthly mortgage payment should be no more than $2,800.

      What is the formula to calculate monthly payments on a loan? ›

      The formula is: M = P [ i(1 + i)^n ] / [ (1 + i)^n – 1], where M is the monthly payment, P is the loan amount, i is the interest rate (divided by 12) and n is the number of monthly payments.

      How to manually calculate loan repayment? ›

      Step 1: Convert your annual interest rate to a monthly rate by dividing by 12. Step 2: Multiply your loan amount by your monthly interest rate to get your monthly interest payment. Step 3:To calculate your monthly principal payment, subtract your monthly interest payment from your total monthly payment.

      What are the 3 methods of payment? ›

      What are the three main types of payment options? The three most common types of payment in today's market are credit cards, debit cards, and cash. Credit and debit card transactions involve fees paid by merchants to the card companies, but they tend to involve larger purchase amounts than cash transactions.

      What are three different methods of calculating interest? ›

      There are three different interest calculation methods you can choose from for your loan product:
      • Fixed Flat.
      • Declining Balance.
      • Declining Balance (Equal Installments)
      Mar 29, 2023

      What are the four C's of mortgage lending? ›

      Meet the Fantastic Four - the 4 C's: Capacity, Credit, Collateral, and Capital. These titans hold the power to make or break your dream of homeownership. They're the guardians of mortgage approval, keeping a watchful eye on every aspect of your financial life.

      How are mortgages calculated? ›

      Your mortgage interest rate is calculated as a percentage of your loan. If you're charged 3% interest per year, you'll need to pay 3% of the value of your loan each year to your lender.

      Which formula should be used to correctly calculate the monthly mortgage payment? ›

      Expert-Verified Answer

      The correct formula to calculate the monthly mortgage payment is: m = p * (r * (1 + r)^n) / ((1 + r)^n - 1).

      How much house can I afford if I make $70,000 a year? ›

      With a $70,000 annual salary and using a 50% DTI, your home buying budget could potentially afford a house priced between $180,000 to $280,000, depending on your financial situation, credit score, and current market conditions. This range is higher than what you might qualify for with more traditional DTI limits.

      How do I calculate how much of a mortgage I can afford? ›

      With a FHA loan, your debt-to-income (DTI) limits are typically based on a 31/43 rule of affordability. This means your monthly payments should be no more than 31% of your pre-tax income, and your monthly debts should be less than 43% of your pre-tax income.

      What is the formula for calculating mortgage payments in Excel? ›

      Use the PMT function in Excel to create the formula: PMT(rate, nper, pv, [fv], [type]). 1 This formula lets you calculate monthly payments when you divide the annual interest rate by 12, for the number of months in a year.

      References

      Top Articles
      Latest Posts
      Recommended Articles
      Article information

      Author: Nicola Considine CPA

      Last Updated:

      Views: 5843

      Rating: 4.9 / 5 (69 voted)

      Reviews: 84% of readers found this page helpful

      Author information

      Name: Nicola Considine CPA

      Birthday: 1993-02-26

      Address: 3809 Clinton Inlet, East Aleisha, UT 46318-2392

      Phone: +2681424145499

      Job: Government Technician

      Hobby: Calligraphy, Lego building, Worldbuilding, Shooting, Bird watching, Shopping, Cooking

      Introduction: My name is Nicola Considine CPA, I am a determined, witty, powerful, brainy, open, smiling, proud person who loves writing and wants to share my knowledge and understanding with you.