Mortgage calculator Excel tutorial | Download free template

Create a mortgage / loan calculator in Excel to calculate payments and loan time. Find out monthly payments with PMT function and analyze alternative scenarios with Excel's data analyzing tools.

Monthly payments

To figure out our monthly payments, we need to know a few things:
  1. Amount of loan needed.
  2. Annual interest rate for the loan.
  3. Loan time 
  4. Number of payments in year 
  5. Total number of payments. Example: 20 years is 240 months, because 20 x 12 = 240.

I have set up the data into an Excel sheet and calculated monthly payments with PMT function as can be seen the image below.

Function syntax:
  1. RATE: divide the yearly interest rate in cell B4 with number of payments in year B6 so that we get the interest rate for each payment.
  2. NPER: Total number of payments we can see in cell B7 (which can be calculated: B5 x B6).
  3. PV: present value, total amount that series of future payments is worth now.
 If you want to loose the negative value, add a minus infront of the PMT function:
Calculate the total cost of loan: monthly payments x total number of payments.
Calculate the cost of interest: total cost of loan - loan amount

What if analysis


Let's say we have an absolute ceiling of 900 euros for the payments. How long would the loan time have to be with in this case? If we want to analyze the loan / mortgage like this, we can use the What if analysis > Goal Seek under Data tab (see image below).

Select B9 to "Set cell", type 900 into "to value" and select B7 for the "By changing cell" field. Note: The value in B7 has to be in number format, functions do not work.

Click OK and you should get 270,78 to cell D5.

You can also do goal seeking for the interest rate by selecting it to the "By changing cell" field.

Download finished  Excel spreadsheet from this link (file hosted by mediafire)


  1. This is a perfect way of making our lives more simple and easy.. Thanks for this piece, via this have learned more about the latest tools and techniques.

  2. Hi Admin,
    Your post on excel advanced training is really useful. I agree with your thoughts, knowledge on excel is mandatory for everyone. Excel Training in Chennai | Advanced Excel Training in Chennai

  3. Likewise extraordinary web journal here with the important's majority data you have.I truly appreciate basically perusing the greater part of your weblogs. Dallas FHA Loan Pros

  4. Many people around the globe have the same desire. However for some this dream does not materialize into reality while others taste the sweet fruits of success very quickly. Payday loan for bad credit

  5. This comment has been removed by the author.

  6. There will likewise be forthright charges you should pay. Along these lines you ought to ensure that you comprehend the home loan process and pick both your credit program and your bank admirably. read the full info here

    1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru ASP.NET Essential Training Online . Nowadays Dot Net has tons of job opportunities on various vertical industry.
      or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

  7. Wisconsin Mortgage Corporation has been providing quality home loans across Wisconsin.Meet our loan officers. Please choose the loan officer that you were referred to view their personal page and apply online.Wisconsin Mortgage Each loan officer's page includes the following services.

  8. I have read your blog its very Interesting. Thanks for sharing. ERP Providers in Chennai | ERP Software Company Chennai. We provide best quality cost effective ERP software applications and end to end enterprise solutions to all the industry verticals with the affordable cost.

  9. Thank you for this valuable information. I have got some important suggestions from it. Get your business to the next level in simple steps.
    ERP Solutions in Chennai | ERP Software Solutions in Chennai.

  10. Thanks very much for this great article;this is the stuff that keeps me going through out these day.


  11. I like it when individuals get together and share opinions. Great website, continue the good work! gmail email login

  12. Mortgage debt includes first mortgages and second mortgages such as a home equity line of credit or home equity loans. Non-mortgage debt would be credit cards, medical bills, student loans, auto loans, other consolidation loans, and personal loans.guarantor loans

  13. I found a lot of interesting information here. A really good post
    office 2010 professional plus key deutsch

  14. Good post and I like it very much. By the way, anybody try this increase app downloads? I do not how to use.

  15. The website is looking bit flashy and it catches the visitors eyes. A design is pretty simple .
    online marketing lüdenscheid

  16. Your good knowledge and kindness in playing with all the pieces were
    very useful. I don’t know what I would have done if I had not
    encountered such a step like this.

    java training in chennai

    java training in bangalore

    java training in bangalore


  17. The website is looking bit flashy and it catches the visitors eyes. A design is pretty simple .
    regionale stellenbörse