Introduction

When learning finance in Excel, PMT skill practice is essential for mastering loan payment calculations. The PMT function helps you determine monthly or periodic payments on loans, mortgages, or investments with fixed interest rates and terms.

By using PMT in Excel, you can determine how much you need to pay monthly, quarterly, or annually, given a principal amount, an interest rate, and a repayment period.

This article provides a complete guide to the PMT function, including its formula structure, real-world use cases, an example dataset, and 20 Excel PMT practice problems with solutions. By the end, you’ll have the confidence to apply Excel PMT calculations in professional scenarios and even automate your loan analysis using advanced tools like PivotXL.

Download PMT Excel Skill Practice Excel File

What is the PMT Function in Excel?

The PMT function calculates the fixed periodic payment required to repay a loan or investment. It assumes a constant interest rate and equal payment intervals.

PMT Function Syntax

=PMT(rate, nper, pv, [fv], [type])
  • rate → Interest rate per period. For monthly payments, divide annual interest by 12.
  • nper → Total number of payment periods (e.g., 60 months for a 5-year loan).
  • pv → Present value, or the total loan amount (principal).
  • fv (optional) → Future value, or the balance left after the last payment (default = 0).
  • type(optional) → Payment timing:
    • 0 → End of the period (default).
    • 1 → Beginning of the period.

Example:
=PMT(8%/12, 60, 50000) → Monthly payment for a $50,000 loan at 8% annual interest over 5 years.

Why PMT Skill Practice Matters for Finance Professionals

Learning the Excel PMT skill gives you the ability to:

  • Calculate loan repayment amounts accurately.
  • Compare financing options such as car loans, personal loans, and mortgages.
  • Plan cash flows for business loans.
  • Adjust loan models for balloon payments, down payments, or residual values.
  • Save time by automating calculations instead of manual trial-and-error.

For finance professionals, Excel’s PMT function is a must-have tool when evaluating borrowing costs and repayment schedules.

Example Dataset for PMT Practice

LoanPrincipal (PV)Annual Interest RatePeriods (Nper)Payment Frequency
A$50,0008%60 monthsMonthly
B$250,0006%20 yearsMonthly (Mortgage)
C$15,00010%36 monthsMonthly (Car Loan)
D$40,00012%4 yearsQuarterly
E$100,0007%10 yearsAnnual

This dataset will be used across our practice problems to demonstrate how PMT works in real-world cases.

20 Excel PMT Skill Practice Problems with Solutions

1. Monthly payment for Loan A ($50,000, 8% annual, 60 months)

Formula: =PMT(8%/12, 60, 50000)
Solution: -1,013.78

2. Mortgage payment for Loan B ($250,000, 6% annual, 20 years)

Formula: =PMT(6%/12, 240, 250000)
Solution: -1,791.76

3. Car loan payment for Loan C ($15,000, 10% annual, 36 months)

Formula: =PMT(10%/12, 36, 15000)
Solution: -484.01

4. Quarterly loan payments ($40,000, 12% annual, 4 years)

Formula: =PMT(12%/4, 16, 40000)
Solution: -3,226.06

5. Annual payment for Loan E ($100,000, 7% annual, 10 years)

Formula: =PMT(7%, 10, 100000)
Solution: -14,237.75

6. Loan with future value ($50,000, 5% annual, 10 years, FV=10,000)

Formula: =PMT(5%, 10, 50000, 10000)
Solution: -6,472.58

7. Payment at beginning of period ($20,000, 9% annual, 5 years, type=1)

Formula: =PMT(9%, 5, 20000, 0, 1)
Solution: -5,142.82

8. Credit card repayment ($5,000, 18% annual, 24 months)

Formula: =PMT(18%/12, 24, 5000)
Solution: -249.62

9. Compare annual vs. monthly payments ($10,000 loan, 6% annual, 5 years)

Annual: =PMT(6%, 5, 10000) → -2,374.11
Monthly: =PMT(6%/12, 60, 10000) → -193.33

10. Loan with balloon payment ($50,000, 8% annual, 5 years, FV=20,000)

Formula: =PMT(8%, 5, 50000, 20000)
Solution: -6,529.73

11. Short-term loan ($2,000, 12% annual, 6 months)

Formula: =PMT(12%/12, 6, 2000)
Solution: -345.54

12. Investment repayment ($75,000, 10% annual, 15 years)

Formula: =PMT(10%, 15, 75000)
Solution: -9,864.65

13. Loan with no interest ($10,000, 0%, 10 years)

Formula: =PMT(0%, 10, 10000)
Solution: -1,000

14. Compare different interest rates ($20,000 loan at 5%, 10%, 15%)

5%: =PMT(5%, 5, 20000) → -4,621.13
10%: =PMT(10%, 5, 20000) → -5,274.11
15%: =PMT(15%, 5, 20000) → -5,951.65

15. Monthly mortgage ($500,000, 5.5% annual, 30 years)

Formula: =PMT(5.5%/12, 360, 500000)
Solution: -2,838.99

16. Loan with irregular repayment FV ($100,000, 6%, 8 years, FV=20,000)

Formula: =PMT(6%, 8, 100000, 20000)
Solution: -14,312.11

17. Car loan with down payment ($25,000, 8%, 5 years, down $5,000)

Adjusted PV = $20,000
Formula: =PMT(8%, 5, 20000)
Solution: -5,009.65

18. Home equity loan ($50,000, 7%, 15 years, monthly)

Formula: =PMT(7%/12, 180, 50000)
Solution: -449.04

19. Personal loan ($10,000, 15%, 3 years, monthly)

Formula: =PMT(15%/12, 36, 10000)
Solution: -346.65

20. Compare PMT vs. simple division ($12,000 loan, 3 years, 0% interest)

PMT: =PMT(0%, 36, 12000) → -333.33
Simple division: 12000 Ă· 36 = 333.33

Download PMT Excel Skill Practice Excel File

Conclusion

The PMT Excel skill practice is a must-have for anyone working with finance, loans, or investment modeling. From mortgages and car loans to business financing, Excel’s PMT function helps you plan repayments, compare options, and make informed decisions.

To learn more about the PMT function, visit Investopedia’s PMT Guide.

Download the PMT Excel Skill Practice Template to test these 20 problems and master the function step by step.