HomeApplicationOffice information

How to Create a Mortgage Calculator With Microsoft Excel

This wikiHow teaches you how to calculate your mortgage-related expenses like interest, monthly payments, and total loan amount using a Microsoft Excel spreadsheet. Once you've done this, you can also create a payment schedule that uses...
Method 1 of 2:

Creating a Mortgage Calculator

  1. How to Create a Mortgage Calculator With Microsoft Excel Picture 1How to Create a Mortgage Calculator With Microsoft Excel Picture 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. How to Create a Mortgage Calculator With Microsoft Excel Picture 2How to Create a Mortgage Calculator With Microsoft Excel Picture 2
    Select Blank Workbook. This will open a new Excel spreadsheet.
  3. How to Create a Mortgage Calculator With Microsoft Excel Picture 3How to Create a Mortgage Calculator With Microsoft Excel Picture 3
    Create your "Categories" column. 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:
    1. Loan Amount $
    2. Annual Interest Rate
    3. Life Loan (in years)
    4. Number of Payments per Year
    5. Total Number of Payments
    6. Payment per Period
    7. Sum of Payments
    8. Interest Cost
  4. How to Create a Mortgage Calculator With Microsoft Excel Picture 4How to Create a Mortgage Calculator With Microsoft Excel Picture 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.
    1. Your Loan Amount value is the total amount you owe.
    2. Your Annual Interest Rate value is the percentage of interest that accrues each year.
    3. Your Life Loan value is the amount of time you have in years to pay off the loan.
    4. Your Number of Payments per Year value is how many times you make a payment in one year.
    5. Your Total Number of Payments value is the Life Loan value multiplied by the Payments Per Year value.
    6. Your Payment per Period value is the amount you pay per payment.
    7. Your Sum of Payments value covers the total cost of the loan.
    8. Your Interest Cost value determines the total cost of the interest over the course of the Life Loan value.
  5. How to Create a Mortgage Calculator With Microsoft Excel Picture 5How to Create a Mortgage Calculator With Microsoft Excel Picture 5
    Figure out the total number of payments. Since this is your Life Loan value multiplied by your Payments Per Year value, you don't need a formula to calculate this value.
    1. For example, if you make a payment a month on a 30-year life loan, you would type in "360" here.
  6. How to Create a Mortgage Calculator With Microsoft Excel Picture 6How to Create a Mortgage Calculator With Microsoft Excel Picture 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)".
    1. 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.
    2. 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. How to Create a Mortgage Calculator With Microsoft Excel Picture 7How to Create a Mortgage Calculator With Microsoft Excel Picture 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.
    1. For example, if you make 360 payments of $600.00, your total cost of the loan would be $216.000.
  8. How to Create a Mortgage Calculator With Microsoft Excel Picture 8How to Create a Mortgage Calculator With Microsoft Excel Picture 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.
Method 2 of 2:

Making a Payment Schedule (Amortization)

  1. How to Create a Mortgage Calculator With Microsoft Excel Picture 9How to Create a Mortgage Calculator With Microsoft Excel Picture 9
    Create your Payment Schedule template to the right of your Mortgage Calculator template. 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:
    1. Date - The date the payment in question is made.
    2. Payment (number) - The payment number out of your total number of payments (e.g., "1", "6", etc.).
    3. Payment ($) - The total amount paid.
    4. Interest - The amount of the total paid that is interest.
    5. Principal - The amount of the total paid that is not interest (e.g., loan payment).
    6. Extra Payment - The dollar amount of any extra payments you make.
    7. Loan - The amount of your loan that remains after a payment.
  2. How to Create a Mortgage Calculator With Microsoft Excel Picture 10How to Create a Mortgage Calculator With Microsoft Excel Picture 10
    Add the original loan amount to the payment schedule. This will go in the first empty cell at the top of the "Loan" column.
  3. How to Create a Mortgage Calculator With Microsoft Excel Picture 11How to Create a Mortgage Calculator With Microsoft Excel Picture 11
    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. How to Create a Mortgage Calculator With Microsoft Excel Picture 12How to Create a Mortgage Calculator With Microsoft Excel Picture 12
    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:
    1. Select the first entry in your Payment (Number) column.
    2. 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.
    3. Click Fill in the top right corner of the Excel page.
    4. Select Series.
    5. Make sure "Linear" is checked under the "Type" section (when you do your Date column, "Date" should be checked).
    6. Click OK.
  5. How to Create a Mortgage Calculator With Microsoft Excel Picture 13How to Create a Mortgage Calculator With Microsoft Excel Picture 13
    Select the first empty cell in the "Payment ($)" column.
  6. How to Create a Mortgage Calculator With Microsoft Excel Picture 14How to Create a Mortgage Calculator With Microsoft Excel Picture 14
    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
  7. You must preface this formula with the "=IF" tag to complete the calculations.
  8. 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
  9. Given the screenshots here, the formula would look like this: "=IF($B$10
  10. How to Create a Mortgage Calculator With Microsoft Excel Picture 15How to Create a Mortgage Calculator With Microsoft Excel Picture 15
    Press ↵ Enter. This will apply the Payment per Period formula to your selected cell.
    1. In order to apply this formula to all subsequent cells in this column, you'll need to use the "Fill" feature you used earlier.
  11. How to Create a Mortgage Calculator With Microsoft Excel Picture 16How to Create a Mortgage Calculator With Microsoft Excel Picture 16
    Select the first empty cell in the "Interest" column.
  12. How to Create a Mortgage Calculator With Microsoft Excel Picture 17How to Create a Mortgage Calculator With Microsoft Excel Picture 17
    Enter the formula for calculating your Interest value. 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".
    1. This formula must be prefaced with a "=" sign in order to work.
    2. In the screenshots provided, the formula would look like this: "=K8*$B$6/$B$8" (without quotation marks).
  13. How to Create a Mortgage Calculator With Microsoft Excel Picture 18How to Create a Mortgage Calculator With Microsoft Excel Picture 18
    Press ↵ Enter. This will apply the Interest formula to your selected cell.
    1. 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. How to Create a Mortgage Calculator With Microsoft Excel Picture 19How to Create a Mortgage Calculator With Microsoft Excel Picture 19
    Select the first empty cell in the "Principal" column.
  15. How to Create a Mortgage Calculator With Microsoft Excel Picture 20How to Create a Mortgage Calculator With Microsoft Excel Picture 20
    Enter the Principal formula. For this formula, all you need to do is subtract the "Interest" value from the "Payment ($)" value.
    1. For example, if your "Interest" cell is H8 and your "Payment ($)" cell is G8, you'd enter "=G8 - H8" without the quotations.
  16. How to Create a Mortgage Calculator With Microsoft Excel Picture 21How to Create a Mortgage Calculator With Microsoft Excel Picture 21
    Press ↵ Enter. This will apply the Principal formula to your selected cell.
    1. 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. How to Create a Mortgage Calculator With Microsoft Excel Picture 22How to Create a Mortgage Calculator With Microsoft Excel Picture 22
    Select the first empty cell in the "Loan" column. This should be directly below the initial loan amount you took out (e.g., the second cell in this column).
  18. How to Create a Mortgage Calculator With Microsoft Excel Picture 23How to Create a Mortgage Calculator With Microsoft Excel Picture 23
    Enter the Loan formula. Calculating the Loan value entails the following: "Loan"-"Principal"-"Extra".
    1. For the screenshots provided, you'd type "=K8-I8-J8" without the quotations.
  19. How to Create a Mortgage Calculator With Microsoft Excel Picture 24How to Create a Mortgage Calculator With Microsoft Excel Picture 24
    Press ↵ Enter. This will apply the Loan formula to your selected cell.
    1. In order to apply this formula to all subsequent cells in this column, you'll need to use the "Fill" feature you used earlier.
  20. How to Create a Mortgage Calculator With Microsoft Excel Picture 25How to Create a Mortgage Calculator With Microsoft Excel Picture 25
    Use the Fill function to complete your formula columns. Your payment should be the same all the way down. The interest and loan amount should decrease, while the values for the principal increase.
  21. How to Create a Mortgage Calculator With Microsoft Excel Picture 26How to Create a Mortgage Calculator With Microsoft Excel Picture 26
    Sum the payment schedule. 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.
    1. Your principal should match up exactly with the original loan amount.
    2. Your payments should match the total cost of the loan from the mortgage calculator.
    3. Your interest should match the interest cost from the mortgage calculator.

Sample Mortgage Payment Calculator

How to Create a Mortgage Calculator With Microsoft Excel Picture 27How to Create a Mortgage Calculator With Microsoft Excel Picture 27 Mortgage Payment Calculator
5 ★ | 1 Vote
paymentloanpaymentstotal

You should read it

Maybe you are interested

Office information