how do i calculate monthly mortgage payment in excel

There are designed to adjustment cycle continued in excel spreadsheet later we cannot guarantee their lower. Adjustable Rate Mortgage Calculator. Some Mortgage Calculators - Excel files. Monthly Payment Mortgage Calculator - No Amortization Table This spreadsheet file allows you to compare up to five. Looking to buy a house? See how much you can afford with this free mortgage payment calculator in Excel.
how do i calculate monthly mortgage payment in excel

Excel PMT Function


The PMT function calculates the payment for a loan that has constant payments and a constant interest rate. Also see the Loan Payment Schedule Template page

How Could You Use PMT?

The PMT function returns a payment amount, so you can use it to:

  • Calculate the monthly payment due on a personal loan
  • Calculate the payment due for a Canadian mortgage loan, with interest west valley police department compounded bi-annually

PMT Syntax

The PMT function has the following syntax:

  • PMT(rate, nper, pv, [fv], [type])
    • Rate is the interest rate for the loan.
    • Nper is the total number of payments for the loan.
    • Pv is the present value; also known as the principal.
    • Fv is optional. It is the future value, or the balance that you want to have left after the last payment. If fv is omitted, the fv is assumed to be zero.
    • Type is optional. If omitted, it is assumed to be zero, and payments are due at the end of the period. Use 1 in this argument if payments are due at the beginning of the period.
PMT syntax

PMT Traps

  • The payment calculated by PMT includes principal and interest but does not include taxes, or other fees that might be associated with the loan.
  • Canadian mortgage payments have the interest compounded bi-annually, even if the payments are made monthly. The Rate argument must be adjusted to account for this

Example 1: Calculate Payment on Personal Loan

To see the steps for calculating a simple loan payment with the PMT function, watch this short video. The written instructions are below the video.

With the PMT function, you can return a payment amount, based on loan information. In this example:

  • The loan amount is $10,000
  • The interest rate is 5% annually
  • The loan is for a 4 year term, with 48 monthly payments

In cell C6, the PMT function calculates the monthly payment, based on the annual rate, which is divided by 12 to get the monthly rate, the number of payments (periods) and the loan amount (present value):


PMT loan calculation

The payment, -230.29, is calculated as a negative amount, because you are paying that amount out of your bank account.

If you would prefer to see the result as a positive number, you can use a minus sign before the PMT function:


Example 2: Calculate the Payment on a Canadian Mortgage

For Canadian mortgage loans, the interest is compounded semi-annually, rather than monthly, even if the payments are monthly. To calculate the payments, you need a different rate calculation, instead of the simple Rate/12.

Note: Visit your bank's website, or check with your banker, to confirm how your bank will calculate the payments.

In this example:

  • The mortgage loan amount is $100,000
  • The interest rate is 5% annually, compounded semi-annually
  • The loan is for a 20 year term, with 240 monthly payments

In cell C6, the PMT function calculates the monthly payment, based on the annual rate, the number of payments (periods) and the loan amount (present value):


Instead of simply dividing the rate by 12, the rate calculation is: (Rate/2+1)^(1/6)-1

  • (Rate /2 +1) is the semi-annual interest as a proportion of the annual rate. In this example, the rate is 5/2 = 2.5% each 6 months. So at the end of 6 months you owe 1.025 of what you owed at the how do i calculate monthly mortgage payment in excel beginning.
  • Payments are monthly, and there are 6 months in a half year, so the proportional rate is raised to the power of 1/6. In this example, the monthly rate is 1.025 ^(1/6)=1.00412391547
  • The 1, that was added for the rate calculation, is subtracted

PMT loan calculation

The payment, -657.13, is calculated as a negative amount, because you are paying that amount out of your bank account.

If you would prefer to see the result as how do i calculate monthly mortgage payment in excel positive number, you can use a minus sign before the PMT function:


Example 3: Enhanced Loan Calculator

In the previous examples, you had to enter the total number of payments icici direct login page due, after calculating that number -- number of years in the loan how do i calculate monthly mortgage payment in excel term, times the number of payments per year.

To make things easier, this Excel loan payment calculator lets you select the payment frequency from a drop down list of options.


In the sample file, the Lists sheet has a lookup table of frequencies and number of payments per year, for each frequency.


Based on the frequency that you select, a number of payments per year is calculated in cell E5, using a VLOOKUP formula.



The payment amount is calculated with the PMT function:


In this workbook, there is a minus sign before the present value variable, so the monthly payment is shown as a positive number. You can omit the minus sign, to show the payment as a negative number.


Example 4: Payment Date Calculations

In this example, the PMT function is used at the top of the worksheet, to calculate the monthly payment amount. Here is the formula in cell E2, which is named LoanPmt.

  • =-PMT(LoanRate/12,LoanMths,LoanAmt)

PMT function calculates payment amount

Payment Date Table

The first payment date is also entered at the top of the sheet, in cell A2, and a payment table calculates all the payment days, plus the interest and principal amounts each month.

NOTE: There are 48 rows in the table, and you can add more rows if needed. The formulas should fill in automatically.

Table Formulas

Here are the formulas used in row 7 of the payment date table:

  • Pay Date: =IF(G7="","", DATE(YEAR(LoanStart), MONTH(LoanStart)+G7-1, DAY(LoanStart)))
  • Outstanding: =IF(G7="","",LoanAmt-SUM(E$6:E6))
  • Mth Pmt: =IF(G7="","",LoanPmt)
  • Interest: =IF(G7="","",-IPMT(LoanRate/12,G7,LoanMths,LoanAmt))
  • Principal: =IF(G7="","",C7-D7)
  • Total Princ Paid: =IF(G7="","",SUM(E$6:E7))
  • Pmt Num: =IF(MAX(G$6:G6)<LoanMths,SUM(G6,1),"")

payment date formulas

Last Payment Highlighting

In the table, the latest payment row is highlighted, based on a Conditional Formatting rule

  • =$A7=INDEX($A$7:$A$54,MATCH(TODAY(),$A$7:$A$54,1))

In the Conditional Formatting rule:

  • MATCH looks for the current date (TODAY function) in the payment date list
  • If the current date is not found, MATCH returns the location of the latest date before the current date
  • Then, the INDEX function returns the date from that location in the list of payment dates
  • If the date in the current row matches that date, the row is highlighted with light orange

condtional formatting rule

Get the PMT Function Files

  • Examples 1 & 2: To see the formulas used in examples 1 and 2, get the PMT function sample workbook. The file is zipped, and is in xlsx file format.

Excel Functions Tutorials

Payment south florida state college panther central Calculation Table

Loan Payment Schedule Template

Cost Calculator, Annual

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Function


Excel Function Video Tutorials

Last updated: July 12, 2021 11:47 AM


Differences Between American and Canadian Mortgage Calculation

As our tool Zilculator provides investment calculations not only to American, but also Canadian and other investors world-wide, we optimized it to include country-specific differences. In Canada, mortgages are by law compounded semi-annually as opposed to the monthly compounding in the US and in most other countries around the globe. The only exceptions to this are variable rate mortgages. This makes them a bit harder to grasp for an investor who wants to do their own calculations.

Let's use an example of a mortgage with 12% interest per annum (yearly) and a loan amount of $100,000.

ww bank of america online banking In the USA, investors can simply divide this rate by 12 (months) and use a rate of 1% with monthly payments and compounding in their calculations. In the end of the first month borrower in the USA owns $1,000 for interest to the lender (12% 12 * $100, 000).

However, a Canadian investor pays $975.88 after the first month for the interest part of the mortgage. Now that's confusing, right? In Canada we have to differentiate the Nominal annual interest which is the one quoted from the bank, in our case 12% and the effective annual interest rate, which is in fact 12.36% based on 6% semi-annually. The payments are made monthly, and therefore we have to use a monthly effective rate converted from an annual rate. We find a rate that compounded monthly will result in the effective annual rate of 12.36%. Once we know this rate, we can calculate the rest of the mortgage payments the same way.

philippines to usa flight hours If you like the Math, here is the actual formula and calculation:

(1+rM)12-1 = 0.0609

rM = (1.0609)1/12

rM = 0.493862%

If you are not a fan of Math, we prepared an excel sample with the calculation, where you can input your numbers and play around. There is also a comparison with the calculation of American (monthly compounded) mortgage.

The good news is that if you use Zilculator and analyze your Canadian investment properties, simply select Semi-annual compounding in the Financing section of the property form (see the snapshot below), and the software takes care of the rest for you. You how do i calculate monthly mortgage payment in excel the exact results you need! christmas tree in the park san jose

Zilculator: Canadian Mortgage calculation

Zilculator helps real estate professionals generate break even analysis quicker. Never use a spreadsheet again! Analyze your own property or create investment reports for your clients.

  • Professional-grade branded investment reports
  • Loading data from MLS®, Zillow®, and Rentometer Pro®
  • Sales and Rental comps
Create your report in 2 minutes
Rental analysis report

How to Calculate Canadian Mortgage

  1. Determine the effective monthly rate using the formula below: American vs Canadian Mortgage Calculation
  2. Use this monthly rate in the formula for monthly mortgage payment or other calculations.

Excel Spreadsheet Example

We prepared a simple example and calculation of a canadian mortgage with semi-annual compounding in an excel spreadsheet file. You can download the file, input your own numbers and calculate results in no time. The only thing we ask in return is for you to like our facebook page or follow us on twitter.

Download Excel Spreadsheet Example

Thank you for supporting Zilculator on social networks! Click the button below to download your Excel file.


How do I Calculate Mortgage Payments in Excel?

When you take out a fixed-rate mortgage to buy or refinance a home, your lender takes three numbers and plugs them into a formula to calculate your monthly payment. Those three numbers are your principal, or the amount of money you're borrowing; your interest rate; and the number of months in your loan term. You can quickly create a spreadsheet in Microsoft Excel to perform the calculation for you--and, in the process, gain a greater understanding of just how a mortgage loan works.

  1. Launch Microsoft Excel. Open a new workbook by pressing "Ctrl" and "N."

  2. Type "Principal" into cell A1 on the Excel worksheet. Type "Rate" mtb preferred stock cell A2. Type "Months" into cell A3.

  3. Enter the amount of the mortgage principal in cell B1.

  4. Enter the interest rate in cell B2. Just enter the number; don't use the percent sign. So, if your rate is 7 percent, just enter 7. If it's 5.75 percent, enter 5.75.

  5. Enter the number of months in how do i calculate monthly mortgage payment in excel loan term in cell B3. Most mortgages are for either 15 or 30 years. Enter 180 for a 15-year mortgage or 360 for a 30-year loan. If your loan is for some other number of years, simply multiply that number by 12 and enter the result in cell B3.

  6. Enter the following formula in cell A4, beginning with the "equals" sign:

  7. This converts your annual interest rate to a decimal figure by dividing it by 100, then breaks it down into a monthly rate by dividing it by 12.

  8. Enter the following formula in cell A5, beginning with the "equals" sign:

  9. This step takes into account the compounding of the interest over the life of the loan.

  10. Enter the following formula in cell A6, beginning with the "equals" sign:

  11. This takes all the data and boils it down to a multiplier that's applied to your principal to determine your monthly payment.

  12. Enter the following formula in cell A7, beginning with the "equals" sign:

  13. This applies the multiplier to your loan principal.

  14. Right-click on cell A7 and select "Format Cells." Set the formatting to "Currency." Set "Decimal Places" to 2. Set the "Currency Symbol" to the dollar sign. Click "OK." This cell now gives you the amount of your mortgage payment based on your principal, interest rate and loan term.

  15. Tip

    Experiment with different principal amounts, interest rates and loan terms just by changing the values in cells B1, B2 and B3. The total payment in cell A7 will change to reflect the new figures.


    Most lenders require that you pay your property taxes and homeowners' insurance premiums on a monthly basis, with 1/12 of the total tacked on to each mortgage payment. Those amounts are not included in this calculation. The result in cell A7 includes only the amount that goes to your lender--the total principal and interest due each month.


How to create an amortization table using the Pmt function

This lesson guides you through the process of creating an amortization table with Microsoft Excel. It's a useful example because an amortization table can help you make wise decisions about borrowing money or repaying loans. An amortization table shows for each payment of a loan how much of the payment is going toward the interest on the unpaid balance and how much is going toward the principal.

Creating an amortization table is a 3 step process:

  1. Use the =PMT function to calculate the monthly payment.
  2. Create the first two lines of your table using formulas with the correct relative and absolute references.
  3. Use the Fill Down feature of Excel to create the rest of the table.

Step 1

The key to creating an amortization table is the PMT function. The PMT function is a built-in function that computes the periodic payment given the principal, rate, and term. Here is the syntax:

=PMT(rate, term, principal)

For example, if you borrow $12,000 at 8.5% for 3 years, your monthly payment would be:

=PMT(.085/12,3*12,12000) = $-378.81


  • The result is negative because it represents money you are paying.
  • The values for rate and term must how do i calculate monthly mortgage payment in excel consistent. If you are making a payment each month, the term is the number of months and the rate is the interest rate for one month.

Using the PMT function and a few simple formulas, you can generate an amortization table.

The first step is to specify the terms of the loan and use the PMT function to calculate the monthly payment.

Figure 1. Using the =PMT() function

Notice that in the above example the parameters to the PMT function are cell references. This allows you to change a value in row 4 and quickly see how your change effects the monthly payment--especially handy when you are doing what-if analysis. Also notice that the value in B6 is displayed in red and in parenthesis. This is the default format for displaying negative numbers.

Step 2

In this step you add the first two lines of the table. Using the View Formulas feature of Excel, the following figure shows the formulas in the first two lines of our amortization table:

Figure 2. First two lines of an amortization table with formulas showing

Figure 3 below shows the values you would see given the above formulas.

amort5.gif (4598 bytes)

Figure 3. First two lines with values showing

Pmt. Num (Payment Number)
starts at 0 and is incremented for each payment.
is the loan balance multiplied by the interest rate for the compounding period. The interest rate in cell B6 is the interest rate per year. We divide by 12 to get the interest owed for one month. Note the absolute reference $B$4. This keeps the reference from changing during the fill down step that's coming up shortly.
can be calculated by adding the interest paid for the current period to the value in cell B6 (the monthly payment). It might seem odd that we are adding. Take a close look at the value in cell B6. It's negative. When you add a positive number (B10) to a negative number (B6) you get a less negative number.
is the previous balance minus the principal paid. Since the principal paid is a negative number we add it to the balance.

Step 3

The relative references in row 10 refer to other terms in the amortization table so we can use the Fill Down command to fill out the other rows in the table. The avi movie below demonstrates how to use the Fill Down command. In short,

  1. Click and drag the mouse cursor across row 10 to highlight cells A10 - D10.
  2. Now click and drag down the handle at the lower right of the border surrounding the highlighted area.
  3. When you release the mouse the row you highlighted will be copied down to fill out your table.

If you would like a copy of the spreadsheet developed here, click here to download a copy.


8.1 Calculate the Terms of a Loan

Learning Objectives

  1. Evaluate the cost of borrowed money
  2. Analyze the profitability of a business enterprise
  3. Explain the benefits of placing multiple graphs on the same page.
  4. Use fixed costs and variable costs in a break even analysis
  5. Engage in “What If” data manipulation scenarios to realize business objectives
  6. Conditionally format data meeting predetermined criteria
  7. Choose and successfully employ Excel techniques to solve a complex task


Many businesses need a loan in order to cover startup costsThese are fixed costs associated with starting transfer money from checking account to prepaid card online business. Often refers to one time costs. Many entrepreneurs will turn to family for a loan. However, the danger here is that you are risking not only your family’s money, but also the relationships if the business should go under.

On the other hand, small businesses without a prior track record sometimes have trouble securing a bank loan. Fortunately, there are government agencies at both the federal and state levels that help businesses secure grants and loans.

Loan payments form part of the fixed costs of a business. Determining the payments on a loan is an important part of forecasting costs. The financial formula that calculates loan payments is fairly complex. However, Excel provides an easier way to calculate loan payments using the payment (PMT) functionA built in Excel function that calculates loan payments. Inputs to the function include the loan amount (pv), the interest rate (rate), and the number of loan payments (nper). The PMT function is one of many built in Excel functions. In this chapter we will examine functions, how they differ from formulas, and how to use them in a spreadsheet.

Where Are We in the Life Cycle?

Many information systems projects are conceived of in a life cycle that progresses in stages from analysis to implementation. The diagram below shows the stages that we touch in the current chapter:

Functions vs. Formulas

In the prior chapter we looked at Excel formulas and how to construct them. In many cases, we want to create our own formulas so we have a clear idea of how the information is constructed.

However, how do i calculate monthly mortgage payment in excel some cases the formula might involve more complex math where the possibility for error is greater. In these cases it is better to use a built in functionA function is similar to a stored formula. However, functions usually hide details of the formula from the user. For example, we can use the PMT function to calculate loan payments without ever knowing the mathematical formula behind the function. that has already been tested and debugged. There are also functions that avoid busywork that you could do yourself but would probably prefer not to do.

On a small scale this is analogous to the build vs. buy issue. Think of formulas as things that you build whereas functions are things that you “buy.” We put buy in quotes because many functions, including the payment function, are bundled with Excel. That is part of the way that Excel maintains its leadership in the spreadsheet marketplace.

Most functions process input to produce a result. Perhaps the most popular function in Excel is the sum (SUM) functionAlso represented by Σ, the sum function adds up a column or row of numbers., which adds up a long list of numbers. The input for the Sum function are the cells to be added together.

The example below shows the sum function compared with the equivalent formula. The formula is obviously very tedious as it involves adding all the numbers. This is expressed as


The sum function accomplishes the same task more simplistically. This is expressed as


Note that in both cases the result is the same: 1,427.

One nice advantage of the sum function is that if we were to add a row in the middle of the list, say between row 7 and row 8, how do i calculate monthly mortgage payment in excel sum function would automatically expand to accommodate the new row, but the formula would not.

The right way (above) and wrong way (below) to add up a column of numbers. Always try to use the sum function when adding numbers from more than two cells.

The Payment (PMT) Function Calculates Loan Payments Automatically

The payment (PMT) function calculates loan payments automatically. The format of the PMT function is:

=PMT(rate,nper,pv) correct for YEARLY payments
  • Rate is the interest rate, usually expressed as an annual percentage rate (APR). If payments are made once a year then just plug in the APR. However, payments are usually once a month. So you need to divide the rate by 12.
  • Nper is the number of payment periods. Again, if payments are made once a year then nper is just the number of years of the loan. However, payments are usually once a month. So you need to multiply the nper by 12.
  • Pv is the present value of the loan, in other words the loan amount today.

Adjusting for monthly payments produces this modification of the function:

=PMT(rate/12,nper*12,pv) correct for MONTHLY payments

By the way, you can use the PMT function to calculate payments on car loans and home mortgages. In case you are curious, the actual mathematical formula that the PMT function translates to looks like this:

Payment = pv* apr/12*(1+apr/12)^(nper*12)/((1+apr/12)^(nper*12)-1)

Note that it is hard to even follow a complex mathematical formula when it is written in Excel.

The payment (PMT) function in action. The wording in this illustration is taken directly from U.S. federal guidelines for loan disclosure. The PMT function is used to calculate the monthly payment—in this case $101. The function references three other numbers in the same illustration. Years is multiplied by 12 to get the number of payments (nper), APR% is divided by 12 to get the monthly interest rate, Amount Financed is the present value (pv) of the loan—the amount you are borrowing.

The Payment (PMT) Function

In the United States, the federal government places requirements on the actual wording of a loan. This wording is reflected in the illustration below. The intention is to force lenders to be honest about the terms of the loan and to allow buyers to comparison shop loans.

Note especially the finance chargeThe total interest paid over the life of the loan. The finance charge is calculated by adding up all of the loan payments and then subtracting from this total the amount originally borrowed. This number is what people normally understand to be interest on the loan. It is the cost you pay for the privilege of borrowing the money. Now you might be wondering why this number is so high. After all 8% of $5,000 is only $400 not $1,083. The interest compounds or grows because the loan has been stretched out over five years.

First time home buyers are often shocked to find that their finance charge actually exceeds the amount of the loan. In other words, over the life of the loan they end up paying around twice the closing price of the home. Home loans have higher finance charges because they are often stretched out over thirty years—which is a lot of time to compound interest. Similarly, if we were to stretch our $5,000 business loan out over thirty years, the finance charge climbs to $8,208, which exceeds the amount of the loan.

Here the same $5,000 loan is shown under different loan terms. The second loan increases the interest rate to 20%, but leaves all else unchanged from the original. The third loan increases the term of the loan to thirty years but leaves all else unchanged from the original. The lesson here is to borrow at as low an interest rate as possible and for as short a time as possible.

So the bottom line is that you should borrow money for as short a time as possible to avoid large finance charges.

By the way, the situation is even worse with credit card loans because the interest rate on credit cards is so much higher—around 20%. Note how at 20% the finance charge climbs to almost $3,000 over five years. Your goal should be to pay off your credit card in full at the end of each month. If you do not have enough money to do this, then you should try to modify spending habits so that you reach this goal.

Key Takeaways

  • Businesses sometimes need loans to cover startup costs. Ideally, these should be business loans rather than personal loans.
  • At its heart, a function is simply a stored formula.
  • Functions are pretested and debugged. They sometimes simplify complex mathematical formulas and/or eliminate busywork.
  • One disadvantage of functions is that you sometimes lose a sense of how the information was derived.
  • The PMT function calculates loan payments. Since most loan payments are monthly, the function needs to be modified by dividing the interest rate by 12, but multiplying the number of payment periods by 12.
  • The United States federal government mandates that loan terms be expressed in a uniform way so that buyers are fully informed of credit terms and can comparison shop.
  • Because interest is compounded, loans should be taken out for as few years as possible. Compound interest is especially painful at high interest credit card rates.

Questions and Exercises

  1. Credit card companies sometimes refer to customers who pay off their balance in full each month as deadbeats. Why would they use wells fargo auto loan bill pay a derogatory term for responsible behavior?
  2. Do some research and find the terms of a student loan and a car loan. Which has more favorable terms? Explain.


The following techniques, found in the Excel section of the software reference, may be useful in completing the assignments for this chapter: Conditional Formatting

L1 Assignment: Calculate Loan Payments

Create a properly formatted spreadsheet that calculates the payments on a business loan.

Many businesses need to take out loans to cover startup costs. This exercise allows you to create a loan payment calculator and perform a sensitivity analysisAn analysis of how the prefab shipping container homes for sale in north carolina results vary with changes in the initial assumptions. on the terms of the loan.


Start Excel and properly title your spreadsheet. Because there are so few numbers the assumptions area and the calculations are combined.

Content and Style

  • Name each number in the As-Is scenario. Use those names in calculations.
  • Follow best practice design techniques.
  • Include a copyright symbol with your name at the bottom.
  • Perform a sensitivity analysis to see how payments change as a function of comerica bank online account rates and loan amount.


Electronic submission: Submit the workbook electronically.

Paper submission:

  • The worksheet grid lines will not appear on the printout.
  • Print out both the results and formulas. The formulas printout shows the formulas in each column. Reveal the formulas by typing CTRL+ ~. Adjust the column widths to closely crop the formulas by dragging the separator between each column in the gray header area.
  • Both printouts should use landscape orientation, which may be accessed from Page Layout > Page Setup > Orientation > Landscape. Each printout should fit on one page. Choose Page Layout > Scale to Fit > Height: 1 page; Width: 1 page.

L2 Assignment: Include Loan Payments in Forecast

Include loan payments in your previous forecast of revenues and costs.

To make the problem more realistic we bring in loan payments as an additional fixed cost. To help in decision making we conditionally format the sensitivity analysis.


Open your workbook file from the L2 assignment of the prior chapter, re—save it under a different name, and then modify it to look as below.

Content and Style

  • Add an assumption for the Loan Payment.
  • Add an assumption for the Desired Profit by Year 5.
  • Name each number in the assumptions area.
  • Use those names in calculations in the spreadsheet below.
  • Follow best practice design techniques in this chapter.
  • Only the first number how do i calculate monthly mortgage payment in excel each column gets formatted as CURRENCY. (Do not format as ACCOUNTING.) Update the format using the Number Formatting Technique. All other numbers greater than 1,000 should be in Comma style.
  • Include a copyright symbol with your name at the bottom.
  • Produce a sensitivity analysis table of total profit/(loss) as a function of growth rate and price per unit.
  • Conditionally format all profit scenarios that meet or exceed the desired minimum profit by year 5 listed in the assumptions area. Conditional formatting is located on the Home screen in Excel. Follow the prompts.


Electronic submission: Submit the workbook electronically.

Paper submission:

  • The worksheet grid lines will not appear on the printout.
  • Print out both the results and formulas. The formulas printout shows the formulas in each column. Reveal the formulas by typing CTRL+ ~. Adjust the column widths to closely crop the formulas by dragging the separator between each column in the gray header area.
  • Both printouts should use landscape orientation, which may be accessed from Page Layout > Page Setup > Orientation > Landscape. Each printout should fit on one page. Choose Page Layout > Scale to Fit > Height: 1 page; Width: 1 page.

Add assumptions and columns as necessary to accommodate loan payments.


Use the PMT function in Excel to calculate monthly loan repayments and then link your calculations to your budget so you can run scenarios to make better financial decisions.

I teach Excel and computing as a volunteer at an adult education centre. I love Excel, I love playing around with it.

Today I was doing a lesson plan on some of the inbuilt functions, and it got me thinking about the using the PMT Function in Excel to calculate monthly debt repayments.

I realise that you can use online calculators to work out repayments on mortgages and loans, but if you enjoy using excel as much as I do, then you might like to do it yourself using this function. It’s a great exercise for understanding how excel and interest rates work.

You can also use your spreadsheet to play around with the variables, like increasing the interest rate and seeing what effect that it has on the repayment amount.

You can work out how much you can afford to borrow and what affect an interest rate rise will have on your overall budget.

A quick disclaimer though. The calculations are based on the assumption that all the variables remain constant, i.e. that the interest rate always stays the same over the entire course of the loan, which we know doesn’t happen in reality.

The other thing that the repayment amount doesn’t take into account is monthly bank fees, so if you expect to have to pay fees, you might like to factor these into your rockland bakery catering menu to calculate loan repayments in excel

First you need to enter your data.

Principle = the amount you want to borrow.

The Interest Rate =  the per annum interest rate divided by 12. So if the interest rate is 6.5%pa then calculate it as:

The term =  how long you’ll have the loan in months. So if it’s a 30 year loan calculate it as:

This is what it will look like:

showing the formulas:

Now in the cell for the monthly repayment calculation, insert the PMT function (I’m on 2007, if you have 2003 it will be under the insert functions menu):

PMT Function – this is my very first video! Sorry about the fact there’s no talking.

The solution is represented as a negative number because it’s money you’re paying out.

Once this is calculated, you can run scenarios by changing some of the variables.

What is the repayment amount if you only borrow $400,000?

What is the repayment amount if the interest rate is 7%?

See how this simple yet powerful calculation can make future budget planning easier?

Plug the numbers from this spreadsheet into your budget (you can create a link, here’s how) and when you update this spreadsheet, your budget will update automatically.

taking this one step further…

Based on the input data, you can also use the CUMIPMT function to calculate the total interest paid over the whole loan (assuming the rate stays constant – it doesn’t in real life, but that’s the only way to do the calc and it gives you a ball park figure) and also the total amount paid for the asset – principle plus interest.

In the CUMIPMT dialogue box The start period is month 1 so you type in 1 in this field and the end period is month 360.

The type refers to whether the monthly repayments and interest charges occur at the beginning or the end of the month, typing in 0 means that they occur at the end of the month, which is usual.

Watch the video for a demonstration of how it works.

CUMIMPT Function – sorry it’s a bit blurry. I have a bit to learn regarding video!

To calculate the total paid over the term of the loan, you need to add the principle to the interest amount.

The only problem is that the interest is a negative so you need to change it into a positive. There are probably a number of ways to do this, but this is the way I do it and it gives you the right answer anyway.

Edited to note: Rather than using the formula to change the results from negative to positive, adjust the How to volunteer at a local food bank and CUMIPMT formulas by typing in a negative (minus sign) in front of the formula, between the equals sign and the letters PMT (or CUMIPMT).

A little bit shocking, isn’t it, the amount we really pay when borrowing to buy.

When getting into debt, especially debt as big as a mortgage, then it’s important to make informed decisions as to how much you can afford.

Using excel and running various scenarios, like changes in interest rates, gives you a good idea of your capacity to repay your debt if the interest rate goes up.


3 Replies to “How do i calculate monthly mortgage payment in excel”

  1. Is deta entry ki job ke liye kya requirenment hogi means lease hame kon se computer course aana chahiye

Leave a Reply

Your email address will not be published. Required fields are marked *