How to create mortgage calculator in Excel



In this tutorial I'm going to show you a step by step guide on how to create a mortgage calculator in Excel. Creating this mortgage calculator doesn't require any demanding skills, as long as you remember the formulas It's quite easy to create one.

   1. Step one, under A1 write down "mortgage calculator" and continue writing down the cells as in the picture below:
     
     A4: House Price                                   A12: Breakdown of Payments
     A5: Down Payment                              A13: Payment Period
     A6: Amount Borrowed                          A14: Principal Paid
     A8: Monthly Payment                           A15: Interest Paid
     A9: Total Amount Paid                          D6: Interest Rate
     D7: Years                                              D8: Payment Periods
     E4: Terms of                                         E5: Loan
     E9: (months)
    




   2. Step two, select cells A4 to A9, right click > format cells > Currency select the dollar symbol with 0 decimal places and click OK. Also do the same for the cells B14 and B15.




   3. Next step, on the cell E6 format the cells to Percentage with 0 decimal spaces and click OK.




   4. Once, you've formatted all the necessary cells write down the numbers or costs as seen on the picture below.

   A4: 85000                     E6: 9%
   A2: 2500                       E7: 15
   A13: 120 




   5. Final step, in order for this mortgage calculator to be complete we have to write down the formulas that is going to enable us to use this function as a calculator. So, write down these formulas:
  
   E8: =E7*12
   B6: =B4-B5
   B8: =pmt(E6/12,E8-B6)
   B9: =B8*E8
   B14: =ppmt(E6/12,B13,E8,-B6)
   B15: =ipmt(E6/12,B13,E8,-B6)


 Once you've written down all the formulas your mortgage calculator should be ready and functional. At this stage you can change the numbers or costs to your personal needs where you can use it to calculate your future financial decisions.




Here's also a video showcase on how to create a mortgage calculator in excel.


Comments

Popular posts from this blog

How to create a barcode using Excel

How to create a simple database in Excel

How to create an actual cell vs target graph in Excel