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:
=PMT(B4/B6;B7;B3)
Explanation:
  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:
=-PMT(B4/B6;B7;B3)
Calculate the total cost of loan: monthly payments x total number of payments.
=B9*B7
Calculate the cost of interest: total cost of loan - loan amount
=B11-B3

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)

34 comments:

  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.

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  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

    ReplyDelete
    Replies
    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.

      Delete
  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.

    ReplyDelete
  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.

    ReplyDelete
  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.

    ReplyDelete
  10. Thanks very much for this great article;this is the stuff that keeps me going through out these day.http://quorse.com/it-computing/Microsoft

    ReplyDelete

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

    ReplyDelete
  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

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

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

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

    ReplyDelete
  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

    ReplyDelete

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

    ReplyDelete
  18. Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks; from every one of us.
    Hadoop Training Institute In chennai

    amazon-web-services-training-in-bangalore

    ReplyDelete
  19. You are doing a great job. You inspire me to write for other. Thank you very much. I would like to appreciate your work for good accuracy and got informative knowledge from here.
    Best Summer Courses for Business Administration in Chennai | No.1 Vacation Classes in Velachery

    ReplyDelete
  20. Great article your blog design and title is innovative and impress..update more information..Summer Courses in Chennai | Summer Courses in Thiruvanmiyur Chennai

    ReplyDelete
  21. Excellent information with unique content and it is very useful to know about the information based on blogs... Digital Marketing Summer Camp in Velachery | Digital Marketing Summer Classes in Chennai | Digital Marketing Summer courses in Taramani

    ReplyDelete
  22. www.mortgage-advice-online.org is the best online platform to calculate monthly mortgage and payments. Really liked it. www.mortgage-advice-online.org

    ReplyDelete
  23. Awesome post. Really you are shared very informative concept... Thank you for sharing. Keep on updating...
    Adobe Photoshop Vacation Classes in Chennai | Best Technical Boot Camp in Chennai

    ReplyDelete
  24. Thanks for ones marvelous posting! I genuinely enjoyed reading it, you are a great author. I will be sure to bookmark your blog and may come back very soon.

    hadoop training in chennai

    angularjs training in chennai

    devops training in chennai

    digital marketing training in chennai

    ReplyDelete
  25. You're the best particularly for the high caliber and results-situated help. I won't reconsider to underwrite your blog entry to anyone who needs and needs bolster about this region.

    aws training in chennai

    selenium training in chennai

    python training in chennai

    ReplyDelete
  26. In the event that ones home loan understanding is that has a governmentally represented monetary foundation such being a bank, the moneylender must supply you with a recharging presentation no less than 21 days before the complete of the current term. https://www.adlist24.com/

    ReplyDelete
  27. Thanks for this blog. provided great information. All the details are explained clearly with the great explanation. Thanks for this wonderful blog.
    ISTQB Certifications Course in Chennai | QA Testing in Medavakkam

    ReplyDelete
  28. Really cool post, highly informative and professionally written and I am glad to be a visitor of this perfect blog, thank you for this rare info!
    MS office course in chennai

    ReplyDelete