PMT interview question: a loan's monthly payment

Use =PMT(B2/12,B3*12,-B1): the yearly rate divided by 12, because the payments are monthly, the years times 12 for the number of payments, and the loan with a minus in front so the payment comes out positive. A loan of 40,000 at 6.9% a year over 6 years costs 680.04 a month. Multiply by the number of payments and take away the loan, and the total interest is 8,962.96.

The question

“A customer borrows 40,000 at 6.9% a year, repaid monthly over six years. What’s the monthly payment? And how much do they pay in interest over the life of the loan?”

In finance, banking and FP&A interviews this often opens the Excel part, because it shows in under a minute whether you understand periods and signs.

What they’re really testing

• Matching the rate to the period. A yearly rate with monthly payments needs dividing by 12.
• Counting payments, not years. Six years of monthly payments is 72 periods.
• The sign convention. Why PMT answers with a negative number, and what to do about it.
• Inputs in cells. A model the interviewer can change, not numbers typed into a formula.

The worked answer

The loan, rate and term are in B1:B3. The payment in B5, then the totals below it:

B5fx=PMT(B2/12,B3*12,-B1)

Row	A	B
1	Loan amount	40,000
2	Annual rate	6.9%
3	Years	6
4
5	Monthly payment	680.04
6	Total paid	48,962.96
7	Total interest	8,962.96

PMT’s required arguments are the rate per period (B2/12), the number of payments (B3*12) and the present value, the amount borrowed (-B1). The total interest in B7 is every payment added up, minus the loan:

The traps: rate and periods

Forget to divide the rate and Excel charges 6.9% every month, which makes the payment about four times too big:

The mirror image is the number of periods: =PMT(B2/12,B3,-B1) pays a 40,000 loan off in six monthly payments. Both come from the same rule: the rate and the number of periods must use the same unit, months here.

Why the answer is negative

Without the minus, PMT returns a negative payment:

That’s not an error. Excel’s finance functions follow the cash flow: you receive the loan (positive), so you pay the instalments (negative). Saying this out loud in the interview shows you know the convention instead of fighting it.

The follow-up: is a project worth it?

The next finance question is usually NPV: “This project costs 12,000 today and brings in 4,000, 5,000 and 6,000 over the next three years. At 8%, is it worth it?”

B9fx=NPV(B1,B5:B7)+B4

Row	A	B
1	Discount rate	8%
2
3	Year	Cash flow
4	0	-12,000
5	1	4,000
6	2	5,000
7	3	6,000
8
9	NPV	753.39

NPV discounts every value it’s given by at least one period, so the money spent today (year 0, in B4) is added outside the function. The NPV is positive, so the project earns more than 8% a year. Putting B4 inside NPV is the classic mistake; How to calculate NPV in Excel works through it.

Common wrong answers, and why they fail

1. The yearly rate with monthly payments. The payment comes out about four times too big.
2. Years instead of months for the periods. The loan is paid off in a handful of payments.
3. Leaving the answer negative with no comment. It isn’t wrong, but explain it or flip it.
4. Numbers typed into the formula. =PMT(6.9%/12,72,-40000) gives the right answer once and can’t be changed from the sheet.
5. Simple interest. The loan amount times the rate times the years ignores that the balance falls with each payment.

Practise it: a monthly payment

A different loan, laid out in a row the way a list of loans would be, so the cells differ from the example above. We check your payment, then change the rate, the amount and the term.

Your task: In D2, return the monthly payment on the loan in A2, at the yearly rate in B2, over the years in C2, as a positive amount (not rounded: the cell shows two decimals).

Graded live

D2fx

Row	A	B	C	D
1	Loan	Annual rate	Years	Monthly payment
2	18,000	7.2%	4

Write the formula in the bar above, then press Check or Enter.

Hint

PMT takes the rate per period, the number of payments and the loan: B2/12, C2*12 and -A2 (the minus makes the payment positive).

Solution

=PMT(B2/12,C2*12,-A2) Any formula that returns the same result passes.

Check

How to answer it in the interview

“Monthly payments, so the rate per period is the yearly rate over 12 and the number of periods is years times 12. PMT returns the payment as a negative cash flow, so I put a minus before the loan. Total interest is the payment times the number of payments, minus the loan.” Then offer the NPV or IPMT follow-up before they ask. More questions like this one: Excel interview questions.