Mortgage Loan Calculator Using Excel

Updated on November 3, 2016
The completed Mortgage Calculator with additional columns for Property Tax, PMI and Property Insurance.
The completed Mortgage Calculator with additional columns for Property Tax, PMI and Property Insurance. | Source

The PMT function within Excel is a financial function that is used to calculate loan payments (it can also be used to calculate the value of investments over time); with the associated IPMT and PPMT functions it is easy to create a worksheet that will calculate an amortization schedule for a mortgage or other loan.

These functions give you the ability to model different loan situations, including additional principal payments, different loan amounts, different interest rates etc. and calculate monthly total payments as well as payments over the life of the mortgage.

This article will provide a step by step guide on how to create a mortgage loan calculator using Microsoft Excel. While the worksheet has been designed in Microsoft Excel 2007, the functionality will be the same on Microsoft Excel 2003.

Creating the Format for the Mortgage Calculator

Click thumbnail to view full-size
Setting up the initial format of the Mortgage CalculatorAdding the formula =-PMT(C3/12,C4,C2) to calculate the Total Monthly Payment
Setting up the initial format of the Mortgage Calculator
Setting up the initial format of the Mortgage Calculator | Source
Adding the formula =-PMT(C3/12,C4,C2) to calculate the Total Monthly Payment
Adding the formula =-PMT(C3/12,C4,C2) to calculate the Total Monthly Payment | Source

Explanation of financial functions used in Mortgage Calculator:

PMT, IPMT & PPMT Functions - for a loan using a constant interest rate and payment:

  • PMT calculates the total payment.
  • IPMT calculates the interest payment for a given period.
  • PPMTcalculates the principal payment for a given period.

Syntax of these functions:

  • =PMT(rate, number of payments, present value, [Future Value], [type])
  • =IPMT(rate, period, number of payments, present value, [Future Value], [type])
  • =PPMT(rate, period, number of payments, present value, [Future Value], [type])

Where:

  • Rate: the monthly interest rate for the loan.
  • Period: the period for which you want to calculate the interest or principal.
  • Number of payments: the total number of payments of the loan.
  • Present value: the current valuethat the future payments are worth now
  • Future Value: the future value you want to attain after last payment. Leaving this out will assume the last value is zero.
  • Type: indicates when the payments are due. (0 is end of period, 1 is beginning of period)

The process to create a mortgage schedule in Microsoft Excel is relatively simple although it does use some complex financial functions.

Start off by creating a simple worksheet where the data will be entered and the amortization will be calculated. In this example add the basic variables such as loan amount, number of months, interest rate etc. and the headings for the actual amortization schedule. To do this:

  • Create a range with the main loan details: Total Loan, Interest Rate, Months. These are the variables that will allow for modelling of different loan terms.
  • Add Total Monthly Payment, Total Payments and Total Interest Paid to this range – these will be calculated later.
  • Create the actual schedule by adding heading: Month, Opening Balance, Principal, Interest, Extra Payment, Closing Balance and Total Payment. Once formulas are added, this section will automatically calculate the amortization schedule based on the variables input in the main loan range.
  • Add a loan amount, and interest rate and the number of months to the range. This is done to ensure that the formulas are working and can be any value.
  • Format the headings and range.
  • Add the formula =-PMT(C3/12,C4,C2) to cell C5 (See explanation of PMT on the right) IMPORTANT NOTE: the interest in the main range is an annual interest. Divide this value by 12 to get the monthly interest rate used in the formula)

The basic format is complete. Other items that could be added (see above screenshot for an example of a more complex final worksheet):

  • PMI payment
  • Monthly Insurance Payment
  • Property Taxes

Adding the initial formulas to the Mortgage Calculator

Click thumbnail to view full-size
Adding the formula =SUM(H10:H500) to the Total Interest Paid field in the summary.Adding the formula =SUM(E10:E500) to the Total Payments column.Adding the formula =1 to the first row of the Month column.Adding the formula =+C2 to the first row of the Opening Balance column.Adding the formula =-PPMT($C$3/12,B10,$C$4,$C$2) to the first row of the Principal column.Adding the formula =-IPMT($C$3/12,B10,$C$4,$C$2) to the first row of the Interest column.Adding the formula =+C10-D10-F10 to the first row of the Closing Balance column.Adding the formula =+D10+E10+F10 to the first row of the Total Payment column.
Adding the formula =SUM(H10:H500) to the Total Interest Paid field in the summary.
Adding the formula =SUM(H10:H500) to the Total Interest Paid field in the summary. | Source
Adding the formula =SUM(E10:E500) to the Total Payments column.
Adding the formula =SUM(E10:E500) to the Total Payments column. | Source
Adding the formula =1 to the first row of the Month column.
Adding the formula =1 to the first row of the Month column. | Source
Adding the formula =+C2 to the first row of the Opening Balance column.
Adding the formula =+C2 to the first row of the Opening Balance column. | Source
Adding the formula =-PPMT($C$3/12,B10,$C$4,$C$2) to the first row of the Principal column.
Adding the formula =-PPMT($C$3/12,B10,$C$4,$C$2) to the first row of the Principal column. | Source
Adding the formula =-IPMT($C$3/12,B10,$C$4,$C$2) to the first row of the Interest column.
Adding the formula =-IPMT($C$3/12,B10,$C$4,$C$2) to the first row of the Interest column. | Source
Adding the formula =+C10-D10-F10 to the first row of the Closing Balance column.
Adding the formula =+C10-D10-F10 to the first row of the Closing Balance column. | Source
Adding the formula =+D10+E10+F10 to the first row of the Total Payment column.
Adding the formula =+D10+E10+F10 to the first row of the Total Payment column. | Source

Now that the initial format is set, the remaining formulas in the summary range and the top row in the amortization schedule can be created. In each cell referenced below, enter the relevant formula:

Cell
Formula
Comment
C6
=SUM(H10:H500)
Totals all the 'Total Payment' rows to give an overall payment made (principal and interest).
C7
=SUM(E10:E500)
Totals all the 'Interest' rows to give an overall interest payment made.
B10
1
As this is the first payment, this is set as 1.
C10
=+C2
As this is the first payment, this is set as the loan value.
D10
=-PPMT($C$3/12,B10,$C$4,$C$2)
Calculates the principal payment for this period only.
E10
=-IPMT($C$3/12,B10,$C$4,$C$2)
Calculates the interest payment for this period only.
F10
 
No value assigned yet.
G10
=+C10-D10-F10
Calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only.
H10
=+D10+E10+F10
Calculates the total monthly payment (principal plus interest).

Adding the final formulas to the Mortgage Calculator

Click thumbnail to view full-size
Adding the formula =+IF(G10 1,IF(B10="","",B10+1),"") to the Second row of the Month column.Adding the formula =+IF(B11="","",G10) to the Second row of the Opening Balance column.Adding the formula =+IF(B11="",0,-PPMT($C$3/12,B11,$C$4,$C$2)) to the Second row of the Principal column.Adding the formula =+IF(B11="",0,-IPMT($C$3/12,B11,$C$4,$C$2)) to the Second row of the Interest column.Adding the formula =+IF(B11="",0,+C11-D11-F11) to the Second row of the Closing Balance column.Adding the formula =IF(B11="",0,+D11+E11+F11)  to the Second row of the Total Payments column.
Adding the formula =+IF(G10 1,IF(B10="","",B10+1),"") to the Second row of the Month column.
Adding the formula =+IF(G10 1,IF(B10="","",B10+1),"") to the Second row of the Month column. | Source
Adding the formula =+IF(B11="","",G10) to the Second row of the Opening Balance column.
Adding the formula =+IF(B11="","",G10) to the Second row of the Opening Balance column. | Source
Adding the formula =+IF(B11="",0,-PPMT($C$3/12,B11,$C$4,$C$2)) to the Second row of the Principal column.
Adding the formula =+IF(B11="",0,-PPMT($C$3/12,B11,$C$4,$C$2)) to the Second row of the Principal column. | Source
Adding the formula =+IF(B11="",0,-IPMT($C$3/12,B11,$C$4,$C$2)) to the Second row of the Interest column.
Adding the formula =+IF(B11="",0,-IPMT($C$3/12,B11,$C$4,$C$2)) to the Second row of the Interest column. | Source
Adding the formula =+IF(B11="",0,+C11-D11-F11) to the Second row of the Closing Balance column.
Adding the formula =+IF(B11="",0,+C11-D11-F11) to the Second row of the Closing Balance column. | Source
Adding the formula =IF(B11="",0,+D11+E11+F11)  to the Second row of the Total Payments column.
Adding the formula =IF(B11="",0,+D11+E11+F11) to the Second row of the Total Payments column. | Source

The first row of formulas has been created. Now the rest of the formulas can be created. They essentially are the same but check to see if the month is blank (the month checks to see if the prior closing balance is zero and returns blank if it is, otherwise it returns the value of the prior month plus one. In each cell referenced below, enter the relevant formula:

Cell
Formula
Comment
B11
=+IF(G10>1,IF(B10="","",B10+1),"")
This checks to see if there is a Closing Balance first; then it checks to see if the month prior has a value; if both values or true then it adds one to the prior month's value.
C11
=+IF(B11="","",G10)
If the value of the Month is not blank then this uses the value from the prior month Closing Balance, otherwise it leaves the cell blank.
D11
=IF(B11="",0,-PPMT($C$3/12,B11,$C$4,$C$2))
If the value of the Month is not blank then this calculates the principal for this period only, otherwise it places zero in the cell.
E11
=IF(B11="",0,-IPMT($C$3/12,B11,$C$4,$C$2))
If the value of the Month is not blank then this calculates the interest for this period only, otherwise it places zero in the cell.
F11
 
No value assigned yet
G11
=IF(B11="",0,+C11-D11-F11)
If the value of the Month is not blank then this calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only, otherwise it places zero in the cell.
H11
=IF(B11="",0,+D11+E11+F11)
If the value of the Month is not blank then this calculates the total monthly payment (principal plus interest, otherwise it places zero in the cell.

Copy these formulas down to row 500. The basic mortgage calculator is now complete. The amortization schedule will automatically recalculate when the values in cells C2, C3 and C4 are amended.

Adding Extra Payments to the Mortgage Calculator

Adding 'Extra Payments'.
Adding 'Extra Payments'. | Source

Once the design is complete you can amend any of the loan details to model the amortization; additionally you can add Extra Payments throughout the entire loan. In the example an extra payment has been applied to the loan every month. This extra payment reduces the length of the loan, the total paid and the interest paid. This extra payment can be made at any time during the loan and doesn’t have to be monthly.


Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • Efficient Admin profile image

        Efficient Admin 

        6 years ago from Charlotte, NC

        Very useful and this looks like it took a lot of hard work to put this hub together - it looks great and thanks for sharing, voted up and across (except funny).

      • chrissieklinger profile image

        chrissieklinger 

        6 years ago from Pennsylvania

        Very helpful; I had no idea you could do that in Excel 2007. Better route to go than using online web calculators.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)