Monthly paymentsTo figure out our monthly payments, we need to know a few things:
- Amount of loan needed.
- Annual interest rate for the loan.
- Loan time
- Number of payments in year
- 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.
- 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.
- NPER: Total number of payments we can see in cell B7 (which can be calculated: B5 x B6).
- PV: present value, total amount that series of future payments is worth now.
=-PMT(B4/B6;B7;B3)Calculate the total cost of loan: monthly payments x total number of payments.
=B9*B7Calculate 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).
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)