### Monthly payments

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

Function syntax:

=PMT(B4/B6;B7;B3)Explanation:

- 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

=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)

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.

ReplyDeleteHi Admin,

ReplyDeleteYour 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