PMT interview question: a loan's monthly payment
The finance question in Excel interviews: a loan's monthly payment and its total interest, with the rate, period and sign traps explained, an NPV follow-up, and a sheet that grades yours.
NPV function
NPV returns the present value of future cash flows at a discount rate. Excel's version discounts the very first value too, so add today's investment outside the function: =NPV(rate, CF1:CFn) + CF0.
Available in every version of Excel, Excel for the web and Google SheetsOn this page
NPV syntax
=NPV(rate, value1, [value2], ...)| Argument | Required | What it means |
|---|---|---|
rate | Yes | The discount rate for one period: yearly for yearly flows, monthly for monthly ones. |
value1 | Yes | The first cash flow, assumed to arrive at the end of period 1. |
value2, … | No | Later cash flows, evenly spaced. Empty cells and text in a range are skipped. |
Examples
A 4-year project at 10%
10,000 invested today, then four yearly inflows. Years 1 to 4 go inside NPV; year 0 is added after it.
| Row | A | B |
|---|---|---|
| 1 | Discount rate | 10% |
| 2 | Year 0 | -10,000.00 |
| 3 | Year 1 | 3,000.00 |
| 4 | Year 2 | 4,000.00 |
| 5 | Year 3 | 4,000.00 |
| 6 | Year 4 | 3,000.00 |
| 7 | NPV | 1,087.36 |
=NPV(B1,B3:B6)+B2 → 1,087.36Monthly cash flows
With monthly flows, use a monthly rate. Here 12% a year, compounded monthly, is 1% a month.
| Row | A | B |
|---|---|---|
| 1 | Yearly rate | 12% |
| 2 | Today | -2,000.00 |
| 3 | Month 1 | 700.00 |
| 4 | Month 2 | 700.00 |
| 5 | Month 3 | 700.00 |
| 6 | NPV | 58.69 |
=NPV(B1/12,B3:B5)+B2 → 58.69Common mistakes
- Year 0 inside the range. =NPV(rate, CF0:CFn) discounts today's investment by a period and understates the answer. Keep CF0 outside.
- A yearly rate on monthly flows. Convert the rate to the period of the flows: rate/12 for a nominal yearly rate.
- Blank cells for years with no cash. NPV skips blanks and shifts every later flow a period earlier. Type 0.
A full walkthrough
The guide How to calculate NPV in Excel works through the year-0 mistake step by step, compares NPV with XNPV and IRR, and has a sheet that grades your formula.
Try it with your own numbers
The NPV and IRR calculator takes your own cash flows and discount rate, and gives the NPV, the IRR and the Excel formulas for both, with today’s cash flow kept outside NPV.
FAQ
Why is Excel's NPV different from my calculator?
Excel discounts the first value by a period; calculators treat CF0 as today. Use =NPV(rate, CF1:CFn) + CF0 and they match.
What's the difference between NPV and XNPV?
NPV assumes equal periods. XNPV takes real dates and a yearly rate, and its first value isn't discounted.
Guides that use NPV
Worked problems with practice sheets.
#NUM! error in Excel: what it means and how to fix it
#NUM! means a calculation has no valid answer: an IRR with no rate to find, a square root of a negative. Each cause and its fix, with a practice sheet.
How to calculate NPV in Excel (and the year-0 mistake)
Excel's NPV discounts the first number you give it. If that number is today's investment, your answer is wrong. Here's the fix, worked through, and a sheet that grades yours.
Know NPV? Prove it on a real sheet.
Practise NPV and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install