Calculating a Loan With the PMT Function in Excel

Everyone needs to borrow money some time. Every time you use your credit card you are taking out a loan. The key question for most people is What will my monthly payment be? Fortunately, Excel has a special function just for this calculation, called PMT. The basic syntax for PMT is as follows:

=PMT(rate, nper, pv)

Lets break down the ARGUMENTS you have to input:

The rate input is the amount of interest collected per period. Important: This is NOT the APR! The APR is an annual rate, but (most) loans are paid monthly. Because of this, you need to divide the APR by 12. So for a loan that is paid monthly it would be: rate/12. If the loan is paid back quarterly it would be rate/4

The nper input is the number of periods the loan will be paid back over. Most car loans are 3-5 years. My loan term is already specified in months. My input for nper is going to be 48 or 4years.

The pv input stands for present value, which is finance code for how big the loan is. Excel likes to assume that the loan amount is negative. Since we usually like to see the loan as a positive number, well change the sign right in the function. My loan amount is in $15000, so my input will be -$15000.

	A	B
1	Loan Amount	-15,000
2	Interest Rate	6%
3	Period or Term	48

Professor Cokkinos want to purchase a used car for $15,000 but he has no money. He needs to take out a loan to buy the car. The local bank offers him a rate of 6% on a four year loan. His wife has some questions:

ASSIGNMENT: Set up a spreadsheet with the above given data and answers these questions:

1. What is the Monthly Payment on this loan?
2. What is the Total Payment over the life of this loan?
3. What is the Total Interest we have to pay to the bank on this loan?

Part 2 - WHAT IF ANALYSIS: Professor Cokkinos can only afford to pay up to $325 per month for this car loan.

Set up a WHAT IF ANALYSIS - GOAL SEEK

Go to DATA> WHAT IF ANALYSIS > GOAL SEEK and follow the prompts

If he can only afford to pay $325 per month

A How much should he borrow instead of 15,000?

B What interest rate could give him a $325 pay per month?