Question

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)

Let’s 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, we’ll 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?

Answer 1

Part 1

Loan Amount	-15000
Interest Rate Monthly	0.005
Period or Term	48

Monthly payment	352.28	Loan amount*Interest rate*Period (PMT fun)
Total spending	-16,909.22	Motnhly Payment *Total period
Total interest paid	-1,909.22	Total amount spend-total loan amount

Part 2

Using Goal seek function in Excel ,

A.Loan amount to be borrowed will be 13838 to get the Monthly installment of 325.

with Interest rate of 6%p.a (0.005 per month)

Please refer the calculation below :

Loan Amount	-13838.6033	(Using Goal seek function - this is Balancing fig)
Interest Rate Monthly	0.005
Period or Term	48
Monthly payment	₹ 325.00
Total spending	₹ -15,600.00	Motnhly Payment *Total period
Total interest paid	₹ -1,761.40	Total amount spend-total loan amount

B. When Loan amount is same as 15000 and Installent is $325 then interest rate is 2%

Loan Amount	-15000	Given
Interest Rate Monthly	0.16%	2% p.a (0.16*12 months)	(Goal seek function, this is Balancing fig)
Period or Term	48
Monthly payment	₹ 325.00	Given
Total spending	₹ -15,600.00	Motnhly Payment *Total period
Total interest paid	₹ -600.00	Total amount spend-total loan amount