How to Create a Mortgage Calculator With Microsoft Excel

PDF download Download Article
PDF download Download Article

One of the best features of Excel is its ability to calculate your mortgage-related expenses like interest and monthly payments. Creating a mortgage calculator in Excel is easy, even if you're not extremely comfortable with Excel functions. This tutorial will teach you how to create a mortgage calculator and amortization schedule in Microsoft Excel.

Method 1
Method 1 of 2:

Creating a Mortgage Calculator

PDF download Download Article
  1. Open Microsoft Excel . If you don't have Excel installed on your computer, you can use Outlook's online Excel extension in its place. You may need to create an Outlook account first.
  2. This will open a new Excel spreadsheet . [1]
    Advertisement
  3. This will go in the "A" column. To do so, you should first click and drag the divider between columns "A" and "B" to the right at least three spaces so you don't run out of writing room. You will need eight cells total for the following categories:
    • Loan Amount $
    • Annual Interest Rate
    • Life Loan (in years)
    • Number of Payments per Year
    • Total Number of Payments
    • Payment per Period
    • Sum of Payments
    • Interest Cost
  4. Enter your values . These will go in your "B" column, directly to the right of the "Categories" column. You'll need to enter the appropriate values for your mortgage.
    • Your Loan Amount value is the total amount you owe.
    • Your Annual Interest Rate value is the percentage of interest that accrues each year.
    • Your Life Loan value is the amount of time you have in years to pay off the loan.
    • Your Number of Payments per Year value is how many times you make a payment in one year.
    • Your Total Number of Payments value is the Life Loan value multiplied by the Payments Per Year value.
    • Your Payment per Period value is the amount you pay per payment.
    • Your Sum of Payments value covers the total cost of the loan.
    • Your Interest Cost value determines the total cost of the interest over the course of the Life Loan value.
  5. Since this is your Life Loan value multiplied by your Payments Per Year value, you don't need a formula to calculate this value.
    • For example, if you make a payment a month on a 30-year life loan, you would type in "360" here.
  6. Calculate the monthly payment . To figure out how much you must pay on the mortgage each month, use the following formula: "= -PMT(Interest Rate/Payments per Year,Total Number of Payments,Loan Amount,0)". [2]
    • For the provided screenshot, the formula is "-PMT(B6/B8,B9,B5,0)". If your values are slightly different, input them with the appropriate cell numbers.
    • The reason you can put a minus sign in front of PMT is because PMT returns the amount to be deducted from the amount owed.
  7. Calculate the total cost of the loan . To do this, simply multiply your "payment per period" value by your "total number of payments" value.
    • For example, if you make 360 payments of $600.00, your total cost of the loan would be $216.000.
  8. Calculate the total interest cost. All you need to do here is subtract your initial loan amount from the total cost of your loan that you calculated above. Once you've done that, your mortgage calculator is complete.
    9. Advertisement
Method 2
Method 2 of 2:

Making a Payment Schedule (Amortization)

PDF download Download Article
  1. Since the Payment Schedule uses the Mortgage Calculator to give you an accurate evaluation of how much you'll owe/pay off per month, these should go in the same document. You'll need a separate column for each of the following categories:
    • Date - The date the payment in question is made.
    • Payment (number) - The payment number out of your total number of payments (e.g., "1", "6", etc.).
    • Payment ($) - The total amount paid.
    • Interest - The amount of the total paid that is interest.
    • Principal - The amount of the total paid that is not interest (e.g., loan payment).
    • Extra Payment - The dollar amount of any extra payments you make.
    • Loan - The amount of your loan that remains after a payment.
  2. This will go in the first empty cell at the top of the "Loan" column.
  3. Set up the first three cells in your " Date " and "Payment (Number)" columns. In the date column, you'll input the date on which you take out the loan, as well as first two dates upon which you plan to make the monthly payment (e.g., 2/1/2005, 3/1/2005 and 4/1/2005). For the Payment column, enter the first three payment numbers (e.g., 0, 1, 2).
  4. Use the "Fill" function to automatically enter the rest of your Payment and Date values . To do so, you'll need to perform the following steps: [3]
    • Select the first entry in your Payment (Number) column.
    • Drag your cursor down until you've highlighted to the number that applies to the number of payments you'll make (for example, 360). Since you're starting at "0", you'd drag down to the "362" row.
    • Click Fill in the top right corner of the Excel page.
    • Select Series .
    • Make sure "Linear" is checked under the "Type" section (when you do your Date column, "Date" should be checked).
    • Click OK .
  6. Enter the Payment per Period formula . The formula for calculating your Payment per Period value relies on the following information in the following format: "Payment per Period<Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year)),Payment per Period,Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year)))". [4]
    • You must preface this formula with the "=IF" tag to complete the calculations.
    • Your "Annual Interest Rate", "Number of Payments per Year", and "Payment per Period" values will need to be written like so: $letter$number. For example: $B$6
    • Given the screenshots here, the formula would look like this: "=IF($B$10<K8+(K8*($B$6/$B$8)),$B$10,K8+(K8*($B$6/$B$8)))" (sans the quotation marks).
  7. This will apply the Payment per Period formula to your selected cell.
    • In order to apply this formula to all subsequent cells in this column, you'll need to use the "Fill" feature you used earlier.
  9. The formula for calculating your Interest value relies on the following information in the following format: "Total Loan*Annual Interest Rate/Number of Payments per Year". [5]
    • This formula must be prefaced with a "=" sign in order to work.
    • In the screenshots provided, the formula would look like this: "=K8*$B$6/$B$8" (without quotation marks).
  10. This will apply the Interest formula to your selected cell.
    • In order to apply this formula to all subsequent cells in this column, you'll need to use the "Fill" feature you used earlier.
  12. For this formula, all you need to do is subtract the "Interest" value from the "Payment ($)" value.
    • For example, if your "Interest" cell is H8 and your "Payment ($)" cell is G8, you'd enter "=G8 - H8" without the quotations.
  13. This will apply the Principal formula to your selected cell.
    • In order to apply this formula to all subsequent cells in this column, you'll need to use the "Fill" feature you used earlier.
  14. This should be directly below the initial loan amount you took out (e.g., the second cell in this column).
  15. Calculating the Loan value entails the following: "Loan"-"Principal"-"Extra".
    • For the screenshots provided, you'd type "=K8-I8-J8" without the quotations.
  16. This will apply the Loan formula to your selected cell.
    • In order to apply this formula to all subsequent cells in this column, you'll need to use the "Fill" feature you used earlier.
  17. Your payment should be the same all the way down. The interest and loan amount should decrease, while the values for the principal increase.
  18. At the bottom of the table, sum the payments, interest, and principal. Cross-reference these values with your mortgage calculator. If they match, you've done the formulas correctly. [6]
    • Your principal should match up exactly with the original loan amount.
    • Your payments should match the total cost of the loan from the mortgage calculator.
    • Your interest should match the interest cost from the mortgage calculator.
    19. Advertisement

Sample Mortgage Payment Calculator

Community Q&A

Search
Add New Question
  • Question
    If I make extra payments on the loan, is there a formula that will recalculate the monthly payment?
    Community Answer
    Include 2 columns, one for interest component and the other with principal component. Add a new column next to principal title "additional principal". Now subtract it from the original principal.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 8 Helpful 39
  • Question
    In Excel, what's the formula to calculate a loan amount if I know the loan payment, interest rate and term?
    Community Answer
    Use the same formulas mentioned in method 1 above; however, use Goal Seek to automatically calculate the loan amount.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 30 Helpful 23
  • Question
    As I add extra payments into the loan, this adjusts the payment schedules accordingly, but is there a formula to show me how much interest or months I'm saving by making the payments?
    Community Answer
    Set up two spreadsheets, one containing your "base loan" with no extra payments, and the second containing the extra payments. The "sum of payments" will decrease as you add extra payments in the second sheet. Subtract this value from the "base loan" sum of payments to see how much you are saving by putting extra money into your loan.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 9 Helpful 17
See more answers
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      • To AutoFill the date using Google Docs spreadsheet, type the date in the first cell and then a month forward in the second cell, then highlight both cells and do the AutoFill as described above. If AutoFill recognizes a pattern, it will AutoFill in for you. [7]
        Thanks
        Helpful 0 Not Helpful 0
      • The "-" sign in front of the PMT function is necessary, or else the value will be negative. Also, the reason the interest rate is divided by the number of payments is because the interest rate is for the year, not month.
        Thanks
        Helpful 0 Not Helpful 0
      • Try building the table like the example first, inputting the example values. Once everything checks out and you're sure the formulas are right, input your own values.
        Thanks
        Helpful 0 Not Helpful 0
      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 to
      Calculate Mortgage Interest
      How to
      Calculate Mortgage Payments
      How to
      Create a Financial Calculator in Microsoft Excel: Step-by-Step Guide
      How to
      Apply Conditional Formatting in Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/create-a-new-workbook-ae99f19b-cecb-4aa0-92c8-7126d6212a83
      2. ↑ https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
      3. ↑ https://support.microsoft.com/en-us/office/fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db
      4. ↑ https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20
      5. ↑ https://support.microsoft.com/en-us/office/using-excel-formulas-to-figure-out-payments-and-savings-11cb708f-c137-4ef8-bcf3-5137aaeb4b20
      6. ↑ https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89
      7. ↑ https://support.google.com/docs/answer/75509?hl=en&co=GENIE.Platform%3DDesktop

      About This Article

      Article Summary X

      1. Open Excel .
      2. Select Blank Workbook .
      3. Add your categories to column A.
      4. Enter values for each category into column B.
      5. Figure out the total number of payments.
      6. Calculate the monthly payment.
      7. Calculate the total loan cost.
      8. Calculate the total interest cost.

      Did this summary help you?
      Thanks to all authors for creating a page that has been read 2,145,375 times.

      Reader Success Stories

      • Anonymous

        Apr 19, 2017

        "A professor wanted the class to figure out regular mortgage payments using scenarios. He said there was a built-in ..." more
      More reader stories Hide reader stories
      Share your story

      Is this article up to date?

      Advertisement