Mortgage Loan Calculator using Excel

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
Microsoft Office Home and Student 2010 Family Pack, 3PC (Disc Version)
Microsoft Office Home and Student 2010 Family Pack, 3PC (Disc Version)

An excellent set of Office tools that includes Microsoft Excel and Microsoft Word.

 

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.


More by this Author


Comments 2 comments

chrissieklinger profile image

chrissieklinger 4 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.


Efficient Admin profile image

Efficient Admin 4 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).

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working