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)

No comments:

Post a Comment