---
title: "Loan payments in Excel: PMT and NPER practice | ExcelSavvy"
description: "Practice PMT and NPER in a live spreadsheet. Monthly loan payments, months to pay off and total interest, checked as you type."
canonical_url: "https://excelsavvy.com/practice/loan-payments"
meta:
  "og:description": "Practice PMT and NPER in a live spreadsheet. Monthly loan payments, months to pay off and total interest, checked as you type."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~TG9hbiBwYXltZW50cyBpbiBFeGNlbDogUE1UIGFuZCBOUEVSIHByYWN0aWNl%2Cdescription_~UHJhY3RpY2UgUE1UIGFuZCBOUEVSIGluIGEgbGl2ZSBzcHJlYWRzaGVldC4gTW9udGhseSBsb2FuIHBheW1lbnRzLCBtb250aHMgdG8gcGF5IG9mZiBhbmQgdG90YWwgaW50ZXJlc3QsIGNoZWNrZWQgYXMgeW91IHR5cGUu%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiXSwicm93cyI6W3sibiI6MSwiY2VsbHMiOlsiTG9hbiIsIlZhbHVlIl19LHsibiI6MiwiY2VsbHMiOlsiQW1vdW50IGJvcnJvd2VkIiwiMjQwMDAiXX0seyJuIjozLCJjZWxscyI6WyJJbnRlcmVzdCByYXRlIHBlciB5ZWFyIiwiMC4wNiJdfSx7Im4iOjQsImNlbGxzIjpbIlllYXJzIiwiNCJdfSx7Im4iOjUsImNlbGxzIjpbIk1vbnRobHkgcGF5bWVudCIsIiJdfV0sInRhcmdldCI6WzQsMV19fQ%2Cp_Ii9wcmFjdGljZS9sb2FuLXBheW1lbnRzIg%2Cs_gIVj0Br5KtpEwGNM.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "Loan payments in Excel: PMT and NPER practice | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~TG9hbiBwYXltZW50cyBpbiBFeGNlbDogUE1UIGFuZCBOUEVSIHByYWN0aWNl%2Cdescription_~UHJhY3RpY2UgUE1UIGFuZCBOUEVSIGluIGEgbGl2ZSBzcHJlYWRzaGVldC4gTW9udGhseSBsb2FuIHBheW1lbnRzLCBtb250aHMgdG8gcGF5IG9mZiBhbmQgdG90YWwgaW50ZXJlc3QsIGNoZWNrZWQgYXMgeW91IHR5cGUu%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiXSwicm93cyI6W3sibiI6MSwiY2VsbHMiOlsiTG9hbiIsIlZhbHVlIl19LHsibiI6MiwiY2VsbHMiOlsiQW1vdW50IGJvcnJvd2VkIiwiMjQwMDAiXX0seyJuIjozLCJjZWxscyI6WyJJbnRlcmVzdCByYXRlIHBlciB5ZWFyIiwiMC4wNiJdfSx7Im4iOjQsImNlbGxzIjpbIlllYXJzIiwiNCJdfSx7Im4iOjUsImNlbGxzIjpbIk1vbnRobHkgcGF5bWVudCIsIiJdfV0sInRhcmdldCI6WzQsMV19fQ%2Cp_Ii9wcmFjdGljZS9sb2FuLXBheW1lbnRzIg%2Cs_gIVj0Br5KtpEwGNM.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **Loans: PMT and NPER practice exercises**

Work out the monthly payment on a loan, how many months it takes to pay one off and how much interest it costs in total.

[**Start the first exercise**](https://excelsavvy.com/practice/loan-payments/pmt-loan-payment)

NPER(PMT(

## **How to calculate a loan payment in Excel**

PMT works out the fixed payment on a loan. It takes the interest rate per period, the number of payments and the loan amount. NPER works the other way: from the rate, a payment and the loan amount, it returns how many payments it takes to pay the loan off.

Say B2 holds a loan of 200,000, B3 an annual rate of 6% and B4 a term of 20 years. `**=PMT(B3/12,B4*12,-B2)**` returns 1,432.86 a month, because the rate and the number of payments are both monthly. Multiply by the number of payments and subtract the loan, as in `**=PMT(B3/12,B4*12,-B2)*B4*12-B2**`, and you get the total interest, about 143,887. With payments of 1,000 on a 50,000 loan at 6%, `**=NPER(B3/12,-B4,B2)**` returns about 57.7 months.

The most common mistake is mixing units: an annual rate with monthly payments. Divide the annual rate by 12 and multiply the years by 12, so both cover the same period. PMT covers interest and principal only, not fees. The exercises below cover the monthly payment, months to pay off a loan and total interest.

1. [**1****Monthly loan payment with PMT**In B5, work out the monthly payment on the loan with PMT. The rate and the number of payments must both be monthly: PMT(B3/12,B4\*12,-B2).](https://excelsavvy.com/practice/loan-payments/pmt-loan-payment)
2. [**2****Months to pay off a loan with NPER****Premium**In B5, work out how many monthly payments of 500 it takes to pay off the loan, with NPER. Use the monthly rate and give the payment and the loan opposite signs: NPER(B3/12,-B4,B2).](https://excelsavvy.com/practice/loan-payments/nper-months-to-pay-off)
3. [**3****Total interest on a loan****Premium**In B5, work out how much interest the loan costs in total: all the monthly payments added up, minus the amount borrowed. That is PMT(B3/12,B4\*12,-B2)\*B4\*12-B2.](https://excelsavvy.com/practice/loan-payments/total-interest-on-a-loan)

## **Functions in this topic**

- [**NPER**](https://excelsavvy.com/functions/nper)
- [**PMT**](https://excelsavvy.com/functions/pmt)

## **Questions**

<details>

<summary>

### Why does PMT return a negative number?

</summary>

Excel treats money you pay as negative and money you receive as positive. A loan is money you receive, so PMT answers with the payment you owe as a negative number. Put a minus before the loan amount, as in =PMT(B3/12,B4\*12,-B2), or a minus in front of the whole formula, to get a positive payment.

</details>

<details>

<summary>

### Why does NPER return #NUM!?

</summary>

The payment does not cover the monthly interest, so the loan never gets paid off. On 100,000 at 6%, the monthly interest is 500, so a payment of 500 or less returns #NUM!. Raise the payment, and check that you gave the payment and the loan opposite signs.

</details>

<details>

<summary>

### How do I make a loan amortization schedule in Excel?

</summary>

Make one row per month. Interest is the balance times the annual rate divided by 12, principal is the payment minus the interest, and the new balance is the old balance minus the principal. Fill the rows down and the balance ends at 0. IPMT and PPMT give the interest and principal for any single payment.

</details>

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/practice/loan-payments#webpage","description":"Practice PMT and NPER in a live spreadsheet. Monthly loan payments, months to pay off and total interest, checked as you type.","name":"Loan payments in Excel: PMT and NPER practice","url":"https://excelsavvy.com/practice/loan-payments","@type":["WebPage","CollectionPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/practice/loan-payments#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/practice/loan-payments#/schema/question/1"},{"@id":"https://excelsavvy.com/practice/loan-payments#/schema/question/2"},{"@id":"https://excelsavvy.com/practice/loan-payments#/schema/question/3"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/practice/loan-payments"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/practice/loan-payments#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel practice exercises","item":"https://excelsavvy.com/practice","position":2},{"@type":"ListItem","name":"Loan payments in Excel: PMT and NPER practice","position":3}]},{"@id":"https://excelsavvy.com/practice/loan-payments#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Why does PMT return a negative number?","acceptedAnswer":{"@type":"Answer","text":"Excel treats money you pay as negative and money you receive as positive. A loan is money you receive, so PMT answers with the payment you owe as a negative number. Put a minus before the loan amount, as in =PMT(B3/12,B4*12,-B2), or a minus in front of the whole formula, to get a positive payment."}},{"@id":"https://excelsavvy.com/practice/loan-payments#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"Why does NPER return #NUM!?","acceptedAnswer":{"@type":"Answer","text":"The payment does not cover the monthly interest, so the loan never gets paid off. On 100,000 at 6%, the monthly interest is 500, so a payment of 500 or less returns #NUM!. Raise the payment, and check that you gave the payment and the loan opposite signs."}},{"@id":"https://excelsavvy.com/practice/loan-payments#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How do I make a loan amortization schedule in Excel?","acceptedAnswer":{"@type":"Answer","text":"Make one row per month. Interest is the balance times the annual rate divided by 12, principal is the payment minus the interest, and the new balance is the old balance minus the principal. Fill the rows down and the balance ends at 0. IPMT and PPMT give the interest and principal for any single payment."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
