Master Excel PMT Skill Practice – Practice Problems Excel Free Download
Practice problems Excel free download for PMT function are essential for mastering loan and investment calculations in Excel. These practice problems Excel free download for PMT function include 20 real-world exercises with solutions to help you calculate repayments, plan cash flows, and compare financing options. By working through these examples, you’ll gain confidence in using the Excel PMT function professionally.
Explore More Excel Functions
Want to master all essential Excel formulas in one place?
Check out our complete guide —
👉 Mastering Excel Formulas: Free Excel Practice Problems for Accounting and Finance
What is the PMT Function in Excel? – Learn with Practice Problems Excel Free Download
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 – Practice Problems Excel Free Download
| Loan | Principal (PV) | Annual Interest Rate | Periods (Nper) | Payment Frequency |
|---|---|---|---|---|
| A | $50,000 | 8% | 60 months | Monthly |
| B | $250,000 | 6% | 20 years | Monthly (Mortgage) |
| C | $15,000 | 10% | 36 months | Monthly (Car Loan) |
| D | $40,000 | 12% | 4 years | Quarterly |
| E | $100,000 | 7% | 10 years | Annual |
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 – Practice Problems Excel Free Download
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 – Practice Problems Excel Free Download
Conclusion – Strengthen Your Excel Skills with Practice Problems Excel Free Download
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.
