Excel PMT Function: Calculate Loan Payments, Mortgage Payments, and Investment Goals Step-by-Step
Excel PMT function explained: calculate loan payments, mortgage, car loans, savings goals. Syntax, examples, sign conventions, and common mistakes to

The Excel PMT function calculates periodic loan payments — the most useful financial function for mortgages, car loans, student loans, and savings calculations. Type =PMT(rate, periods, present_value) and Excel returns the constant payment needed to pay off the loan at the given interest rate over the given number of periods. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).
What PMT does. Solves the time-value-of-money equation: how much must you pay each period to repay a present amount over N periods at rate R? The answer is constant for the life of the loan (assuming fixed rate). Used by banks, financial planners, businesses, and anyone calculating regular payments.
When to use PMT. Calculating mortgage payments. Estimating car loan payments. Personal loan and student loan payments. Investment goal calculations (how much to save monthly to reach a target). Lease payment calculations. Any periodic payment with fixed interest rate.
Sign convention quirk. PMT returns a negative value by default. Why? Excel treats money received as positive and money paid out as negative. So if you borrow $104 (positive cash flow to you), your payment is negative (cash out). To get a positive payment value for display, prefix with negative sign: =-PMT(...).
Foundation of financial calculations. PMT is the gateway to a full suite of related Excel financial functions: PV (present value), FV (future value), RATE (interest rate), NPER (number of periods). Master PMT and the others follow naturally.
This guide covers PMT syntax, examples for mortgages and loans, sign conventions, advanced uses, related functions, and common mistakes. It's for anyone using Excel for financial calculations.
Microsoft Excel: upcoming dates and official changes
- Updated Nov 22, 2026: Exam fee is now $104 per MOS exam voucher (previously $104 per exam voucher (US Certiport Store)).
- Updated Nov 22, 2026: Retake fee is now $120 for an exam voucher with one retake (previously $120 for exam voucher with one retake).
- Updated Nov 22, 2026: Delivery is now Proctored, live-in-the-application exam (previously Proctored live-in-the-application exam via Certiport Authorized Testing Centers).
Facts verified Nov 22, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
- The Microsoft Office Specialist Excel exam (Microsoft 365 Apps) has 33-37 questions. (certiport.pearsonvue.com)
- The Microsoft Office Specialist Excel exam gives candidates 50 minutes to complete it, with 60 minutes of seat time. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- A Microsoft Office Specialist (MOS) exam voucher costs $104.00 in the US Certiport Store. (store.certiport.com)
- A Microsoft Office Specialist (MOS) exam voucher with a retake costs $120.00 in the US Certiport Store. (store.certiport.com)
- Microsoft Office Specialist (MOS) certifications are valid for 5 years. (certiport.pearsonvue.com)
- Successfully renewing a Microsoft Office Specialist certification extends it by three years, and the renewal assessments begin in March 2030. (certiport.pearsonvue.com)
- Delivery: Proctored, live-in-the-application exam. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- The Microsoft Office Specialist Excel Associate (Microsoft 365 Apps) exam is proctored, and students and educators schedule it through Certiport. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Candidates for the Microsoft Office Specialist Excel Associate (Microsoft 365 Apps) certification should have approximately 150 hours of instruction and hands-on experience with Excel. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
Facts verified Nov 22, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Microsoft Excel Exam at a Glance
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel (Microsoft 365 Apps)) | 33-37 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Exam fee | $104 per MOS exam voucher | store.certiport.com |
| Retake fee | $120 for an exam voucher with one retake | store.certiport.com |
| Validity | 5 years | certiport.pearsonvue.com |
| Renewal | Renewal assessment extends certification by 3 years; available from March 2030 | certiport.pearsonvue.com |
| Delivery | Proctored, live-in-the-application exam | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Delivery (Excel Associate (MO-210)) | Proctored; students and educators schedule through Certiport | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | About 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report provided on completion; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Nov 22, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Free Excel Study Tools
Interactive Excel resources on PracticeTestGeeks, free to start:
- Free Excel Questions and Answers — 16 questions with instant scoring and answer explanations
- Excel flashcards — review key terms and concepts card by card
- Excel study guide — topic-by-topic overview of what the exam covers
- Excel cheat sheet — one-page printable summary for last-minute review

Quick Facts
- Syntax: =PMT(rate, nper, pv, [fv], [type])
- rate: Interest rate per period (e.g., monthly rate = annual/12)
- nper: Total payment periods (e.g., 360 for 30-year monthly mortgage)
- pv: Present value / loan amount (often negative for borrowing)
- fv (optional): Future value, often 0 for fully amortizing loan
- type (optional): 0 = end of period (default), 1 = beginning
- Returns: Negative number representing payment due each period
- Sign convention: Outflow negative, inflow positive
- To get positive: Use =-PMT(...) for display
- Related: PV, FV, RATE, NPER, IPMT, PPMT
Basic PMT examples. Start with the simplest case.
Example 1: $200,000 mortgage at 6.5%, 30 years. Setup: loan amount $200,000, rate 6.5% annually, term 30 years. PMT formula: =PMT(0.065/12, 30*12, 200000). Returns: -$1,264.14. This is the monthly payment (negative because it's cash out).
For positive display. =-PMT(0.065/12, 30*12, 200000) = $1,264.14. Same calculation, prefixed with negative for readable result.
Example 2: $25,000 car loan at 7%, 5 years. =-PMT(0.07/12, 5*12, 25000) = $495.03 monthly.
Example 3: $50,000 student loan at 5.5%, 10 years. =-PMT(0.055/12, 10*12, 50000) = $542.61 monthly.
Step by step. Step 1: Identify variables. Loan amount, annual rate, total term. Step 2: Convert annual to periodic rate. Divide annual rate by 12 (for monthly) or 4 (quarterly). Step 3: Calculate total periods. Years × payments per year. Step 4: Apply PMT formula. =PMT(periodic_rate, total_periods, loan_amount). Step 5: Negate for positive display. =-PMT(...).
What you're calculating. PMT assumes: constant payments throughout the term. Each payment is part principal + part interest. Early payments are mostly interest; later payments are mostly principal. The total of payments = principal + total interest.
Total interest formula. =total_payments - loan_amount. For our $200K mortgage: $1,264.14 × 360 = $455,089. Loan was $200,000. Total interest = $255,089. The borrower pays back more than double the original loan over 30 years.
PMT Building Blocks
Annual rate ÷ payments per year (e.g., 6.5%/12 for monthly)
Years × payments per year (e.g., 30 × 12 = 360 monthly)
Current loan amount (positive for what you owe)
Optional. 0 for fully paid off (default)
0 = end of period (typical). 1 = beginning of period
Negative = payment out. =-PMT() for positive display
Common mortgage and loan PMT scenarios.
30-year fixed mortgage. Standard residential mortgage. =-PMT(rate/12, 360, principal). For $300K at 7%: =-PMT(0.07/12, 360, 300000) = $1,995.91 monthly.
15-year fixed mortgage. Faster payoff, lower total interest. =-PMT(rate/12, 180, principal). For $300K at 6%: =-PMT(0.06/12, 180, 300000) = $2,531.57 monthly. Higher payment but $230,000 less in interest vs 30-year at 7%.
Adjustable rate mortgage (ARM). Initially fixed, then variable. PMT calculates initial fixed-period payment. After adjustment, recalculate with new rate. =-PMT(new_rate/12, remaining_periods, current_balance).
Refinance calculation. Compare current vs new mortgage. Current: =-PMT(current_rate/12, remaining_periods, current_balance). New: =-PMT(new_rate/12, new_term*12, new_principal). Calculate savings × remaining months.
Car loan. Typically 3-7 years. =-PMT(rate/12, years*12, principal). For $35K at 5.5% over 5 years: =-PMT(0.055/12, 60, 35000) = $668.04.
Student loan. Federal: 10-25 years typical. Private: 5-20 years. =-PMT(rate/12, years*12, principal). For $50K at 5.5% over 10 years: =-PMT(0.055/12, 120, 50000) = $542.61.
Personal loan. 1-7 years typical. Higher rates. =-PMT(rate/12, years*12, principal). For $10K at 11% over 3 years: =-PMT(0.11/12, 36, 10000) = $327.07.
Business loan. Term loan or SBA loan. 3-25 years. Similar formula. =-PMT(rate/12, years*12, principal). For $250K at 8% over 10 years: =-PMT(0.08/12, 120, 250000) = $3,033.18.
Construction loan. Often interest-only initially, then conventional. PMT for interest-only periods is simply loan × rate/payments per year. PMT for principal+interest period uses standard PMT formula.
Common Loan Types
=-PMT(rate/12, 360, principal). Standard residential mortgage. $300K at 7%: $1,995.91/month. Total payments over 30 years: $718,529. Total interest: $418,529 (more than original loan).
PMT for savings and investment goals. Reverse the logic.
Savings goal: how much to save monthly. To reach $50,000 in 10 years at 7% annual return: =-PMT(0.07/12, 120, 0, 50000) = $288.27 monthly. Negative because money out (saving). The fv=50000 means you want to end with $50K.
Retirement savings calculation. To have $1 million in 30 years at 7% return: =-PMT(0.07/12, 360, 0, 1000000) = $820.07 monthly. Modest monthly amount compounds to seven figures.
College savings. 18 years to save $200,000 at 6% return: =-PMT(0.06/12, 216, 0, 200000) = $516.74 monthly.
Emergency fund. 3 months expenses ($15,000) in 12 months at 4% savings rate: =-PMT(0.04/12, 12, 0, 15000) = $1,228.74 monthly. Short timeline means most through your savings, minimal compound growth.
What this assumes. Constant rate of return. Constant monthly savings amount. No tax considerations. Real returns vary; this is mathematical baseline.
Adjusting for inflation. To save $50,000 in today's dollars in 10 years at 5% inflation: actual target = $50,000 × 1.05^10 = $81,445. Then calculate savings rate. PMT doesn't auto-adjust; do manually.
Real vs nominal returns. Nominal return 7% with 3% inflation = ~4% real return. Use real return for inflation-adjusted goals. PMT works with whatever rate you provide.
Combining with already-saved. If you have $10,000 saved, formula adjusts. =-PMT(0.07/12, 120, -10000, 50000) — the -10000 is current savings (negative because you 'gave' that to your future self).
Tax considerations. PMT doesn't account for tax. Roth vs traditional retirement: returns differ in tax treatment. PMT calculates pre-tax savings. After-tax planning requires additional calculation.
PMT Examples

Advanced PMT scenarios.
Variable rate scenarios. PMT assumes constant rate. For variable-rate loans, recalculate at each rate change. Track interest paid (IPMT) and principal (PPMT) by period to understand balance changes.
Balloon loans. Periodic payments plus large final payment. PMT formula handles balloon when fv is non-zero. =-PMT(rate/12, periods, principal, balloon_amount). The balloon is the unpaid principal at end of regular payments.
Interest-only mortgages. No principal paid during interest-only period. Payment = principal × rate / 12. PMT formula: =-PMT(rate/12, total_periods, principal, principal). The fv=principal because you owe the full principal at end of interest-only period.
Bi-weekly mortgages. 26 payments per year (every 2 weeks). =-PMT(rate/26, years*26, principal). Slightly more payments per year reduces total interest. Equivalent to one extra monthly payment per year.
Refinance breakeven. Compare costs. Current monthly minus new monthly = monthly savings. Refinance fees ÷ monthly savings = months to breakeven. Worth refinancing if breakeven before you plan to move/refinance again.
Loan payoff with extra payments. =NPER(rate/12, -increased_payment, principal) returns months until payoff with extra payment. Compare to original NPER for time savings.
Sinking fund. Save monthly toward future expense (replacement, retirement). =-PMT(rate/12, months, 0, target_amount). Same as savings goal calculation.
Annuity calculations. Receive monthly income from invested amount. =-PMT(rate/12, months, present_value, 0). The PV is your investment; monthly payment is income; 0 means fully depleted at end.
Comparing loan offers. Lender A: $104K at 6.5%, 30 years, $104 fees. Lender B: $104K at 6.25%, 30 years, $104 fees. Compare total cost over expected term.
Advanced PMT
fv = balloon amount. Periodic payments + final lump.
fv = principal. Payments cover interest only during fixed period.
rate/26, periods*26. ~1 extra month payment per year.
Monthly savings × months to recoup fees.
Use NPER to find new payoff date with higher payment.
PV as investment. Monthly income calculated.
Related Excel financial functions.
PV (Present Value). Calculate how much something is worth today given future payments. =PV(rate, nper, pmt, [fv]). Useful for valuing future income streams, comparing loan offers, retirement planning.
FV (Future Value). Calculate future value of present amount or savings stream. =FV(rate, nper, pmt, [pv]). Useful for retirement projections, savings growth, investment forecasting.
RATE. Calculate interest rate from other variables. =RATE(nper, pmt, pv, [fv]). Useful when you know payment, principal, and term but not rate. Can be slow due to iterative calculation.
NPER. Calculate number of periods to pay off or reach goal. =NPER(rate, pmt, pv, [fv]). Useful for 'how long until I'm debt-free' or 'how long to reach savings goal.'
IPMT and PPMT. Break down each payment into interest (IPMT) and principal (PPMT) components. =IPMT(rate, period, nper, pv) — interest portion of specific payment. =PPMT(rate, period, nper, pv) — principal portion. Useful for understanding amortization.
CUMIPMT and CUMPRINC. Cumulative interest and principal between specific periods. Useful for analyzing first vs last years of a mortgage. =CUMIPMT(rate, nper, pv, start_period, end_period, type). Useful for tax calculations (mortgage interest deductible).
NPV (Net Present Value). Discount future cash flows to present. =NPV(rate, cash_flows). Useful for investment analysis, comparing projects, valuing businesses.
IRR (Internal Rate of Return). Discount rate that makes NPV = 0. =IRR(cash_flows). Useful for investment evaluation, comparing returns of different opportunities.
XNPV and XIRR. Same as NPV/IRR but handles irregular dates. Useful for real-world investments with non-uniform cash flow timing.
Practical use. Build amortization schedule using PMT, IPMT, PPMT. Show how each payment breaks down. Visualize how loan balance decreases over time. Strong understanding of how loans actually work.
Related Functions
=PV(rate, nper, pmt, [fv]). What's something worth today? If you'll receive $500/month for 10 years at 6% discount, =PV(0.06/12, 120, -500) = $45,043. Useful for valuing income streams, retirement planning.
Building an amortization schedule with PMT and friends.
Why an amortization schedule. Shows month-by-month: interest paid, principal paid, remaining balance. Reveals how loans actually work. Strongly recommended for understanding any loan.
Setup. Headers: Payment Number, Payment, Principal, Interest, Remaining Balance. Inputs: loan amount, rate, term. PMT formula calculates payment.
Payment 1 row. Payment = =-PMT(rate/12, total_periods, principal). Constant for entire schedule. Interest = principal × rate/12. Principal = payment - interest. Remaining balance = principal - principal_paid.
Payment 2 and beyond. Payment = same constant. Interest = previous_balance × rate/12. Principal = payment - interest. Remaining balance = previous_balance - principal_paid.
Copy down. Drag formulas down for full schedule (e.g., 360 rows for 30-year monthly mortgage).
What you'll see. Early payments: mostly interest (e.g., $1,083 interest + $181 principal on $200K mortgage payment 1). Mid-loan: more balanced. Late payments: mostly principal (e.g., $7 interest + $104 principal on last payment).
Total interest paid. Sum of interest column. For $200K at 6.5% over 30 years: $255,000+ total interest. Eye-opening.
Adding extra payment column. Show effect of additional payments on payoff. Add extra payment field, recalculate principal/balance row by row. See how extra payments accelerate payoff.
Visualization. Chart of principal vs interest by payment number. Bar chart of remaining balance over time. Pie chart of total paid (principal vs interest).
Practical use. Understand your loan deeply. Identify when extra payments help most. Compare loan options. Make better borrowing decisions.
Amortization Schedule
Loan amount, annual rate, term. Single-cell inputs.
=-PMT(rate/12, total_periods, principal). Constant.
Previous balance × rate/12. Changes each month.
Payment - interest. Increases over time.
Previous balance - principal paid. Decreases each period.
Chart total paid, balance over time, principal vs interest.

Common PMT mistakes and how to avoid them.
Mistake 1: Using annual rate for monthly payments. =PMT(0.065, 30*12, 200000) — wrong! Result is huge because rate not converted. Fix: =PMT(0.065/12, 30*12, 200000). Annual rate must be divided by 12 for monthly payments.
Mistake 2: Wrong number of periods. Used years instead of months. =PMT(0.065/12, 30, 200000) — wrong! Result: $4,265 instead of $1,264. Fix: =PMT(0.065/12, 30*12, 200000). Use total payment periods (years × payments per year).
Mistake 3: Forgetting sign convention. =PMT(0.065/12, 360, 200000) returns -1264.14. Display issue: shows as negative. Fix: =-PMT(...) for positive display. Underlying math is correct either way.
Mistake 4: Mixing periods. Quarterly compounding with monthly payments? Get consistent periods. If rate is annual, must convert to match payment frequency. =PMT(annual_rate/payments_per_year, total_periods, principal).
Mistake 5: Wrong PV sign. Loan amount should be positive (you receive cash, owe future payments). =PMT(rate, nper, principal) = correct. =PMT(rate, nper, -principal) would give payment in opposite direction.
Mistake 6: Including upfront fees in principal. PMT calculates payment based on full principal. If you have origination fees added to loan, those increase principal and monthly payment. Some calculations don't account for fees.
Mistake 7: Ignoring property taxes/insurance. For mortgages, PMT calculates principal+interest. Add property tax, insurance, PMI for full monthly housing cost (PITI).
Mistake 8: Confusing nominal and APR. Banks may quote nominal rate (compounded annually) but charge effective APR (compounded monthly). Slight differences in calculations. Verify which rate the lender quoted.
Mistake 9: Forgetting compound interest. PMT assumes compound interest, not simple. Some loans (especially auto) use simple interest. Calculation method differs.
Mistake 10: Treating PMT as set-in-stone. Variable-rate loans, ARMs, ballooning ARMs all change. PMT calculates given current variables. Actual payments may change over loan life.
Readers who want more detail usually continue with excel pmt function.
For a deeper look at this topic, see our guide to excel pmt function.
Readers who want more detail usually continue with excel pmt function.
Why PMT returns negative. Excel financial functions use cash flow signs: money you receive (positive), money you pay out (negative).
For loans. You receive principal (positive cash flow to you). You pay back periodically (negative cash flow out). So PMT returns negative.
For savings. You contribute monthly (negative — money leaves your checking). You receive future value (positive). PMT for savings also returns negative.
To display positive. Use =-PMT(...) — the negative sign flips the result to positive.
To enter PV negative. Some prefer: =PMT(rate, nper, -principal). Math same. Display differs.
Don't confuse with calculation error. Negative PMT is correct convention, not an error. Banking and finance industries use this standard.
Real-world applications of PMT.
Personal financial planning. Build retirement projection: =FV(7%/12, 30*12, -500, 0) shows $1M+ from $500/month over 30 years at 7%. Build budget for expected expenses (mortgage, car loan). Plan for major purchases.
Mortgage shopping. Compare lender offers. Calculate total interest paid over loan life. Decide between 15-year and 30-year. Decide between fixed and adjustable rate. Quantify trade-offs.
Refinance decision. Current monthly: $104. New offer with $104 fees: $104 monthly. Savings: $104/month. Breakeven: 20 months. Worth it if you'll stay 3+ years.
Car affordability. Want $35K car? Monthly payment at 5% over 5 years: $660. Add insurance, gas, maintenance ($200-400/month). Total transportation cost: $860-1,060/month. Decide if affordable for your income.
Investment analysis. Comparing real estate vs stock investment. Rental property cash flow analysis. ROI calculations for renovations. Lease-buy decisions for equipment.
Business financing. Loan vs lease analysis. SBA loan terms. Equipment financing decisions. Cash flow projections with debt service.
Education funding. College cost vs student loan implications. Compare federal vs private loan options. Income-based repayment vs standard.
Debt consolidation. Combine multiple high-interest debts into one lower-rate loan. PMT calculates new monthly payment. Compare to current total monthly payments.
Auto leasing vs buying. Lease payment (no principal payoff) vs loan payment (builds equity). Total cost comparison over typical ownership period.
Insurance planning. Disability insurance: how much income you need to replace. Long-term care insurance: monthly premium vs potential payout.
Use Cases
Compare lender offers. Calculate total interest. Decide 15 vs 30 year. Plan for refinancing breakeven. PMT is essential mortgage tool.
Tips for using PMT effectively.
Tip 1: Always verify your math. Spot-check PMT result against a financial calculator (Google 'mortgage calculator' for sanity check). Different sources should agree closely.
Tip 2: Use named ranges. Name cells 'rate', 'principal', 'years'. Then =PMT(rate/12, years*12, principal). Easier to read and update.
Tip 3: Build templates. Save mortgage calculator, car loan calculator, savings goal templates. Reuse for future calculations.
Tip 4: Combine with charts. Visualize amortization. Show how principal vs interest changes over time. Charts communicate better than numbers.
Tip 5: Sensitivity analysis. What if rate changes by 0.5%? What if you take a 15-year instead of 30-year? Build a what-if scenarios sheet showing payments at various rates and terms.
Tip 6: Compare to other tools. PMT in Excel. Bank online calculators. NerdWallet, Bankrate. Verify your results across sources.
Tip 7: Account for total cost. Mortgage isn't just principal + interest. Add taxes, insurance, PMI for full housing cost. Car loan isn't just monthly payment. Add insurance, fuel, maintenance.
Tip 8: Document assumptions. What rate did you use? What term? What did you exclude? Notes prevent confusion 6 months later.
Tip 9: Plan for changes. Variable rates change. Income changes. Life changes. PMT calculates point-in-time scenarios. Build flexibility into financial plans.
Tip 10: Learn related functions. Master PMT, then learn PV, FV, RATE, NPER, IPMT, PPMT. Each unlocks new analyses.
PMT Pros and Cons
- +PMT has a publicly available content blueprint — you know exactly what to prepare for
- +Multiple preparation pathways accommodate different schedules and budgets
- +Clear score reporting shows specific strengths and weaknesses
- +Study communities share current insights from recent test-takers
- +Retake policies allow recovery from a difficult first attempt
- −Tested content scope requires substantial preparation time
- −No single resource covers everything optimally
- −Exam-day performance can differ from practice test performance
- −Registration, prep, and retake costs accumulate significantly
- −Content changes between versions can make older materials less reliable
Pros and Cons at a Glance
| Pros | Cons |
|---|---|
| PMT has a publicly available content blueprint — you know exactly what to prepare for | Tested content scope requires substantial preparation time |
| Multiple preparation pathways accommodate different schedules and budgets | No single resource covers everything optimally |
| Clear score reporting shows specific strengths and weaknesses | Exam-day performance can differ from practice test performance |
| Study communities share current insights from recent test-takers | Registration, prep, and retake costs accumulate significantly |
| Retake policies allow recovery from a difficult first attempt | Content changes between versions can make older materials less reliable |
Sample Excel Practice Questions
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
Which function calculates the payment for a loan based on constant payments and a constant interest rate?
- A. FV
- B. PV
- C. NPV
- D. PMT
Answer: D. PMT
PMT(rate, nper, pv) calculates the fixed periodic payment required to pay off a loan over a set number of periods at a given interest rate.
You have a formula that calculates a loan payment. You know the desired monthly payment amount, the loan term, and the interest rate, but you need to determine the maximum loan principal you can afford. Which Excel tool allows you to work backward from a desired result to find the required input value?
- A. Solver
- B. Data Table
- C. Scenario Manager
- D. Goal Seek
Answer: D. Goal Seek
Goal Seek is the ideal tool for this situation. It is part of the What-If Analysis tools and is used to find a specific input value that will produce a desired result in a formula. In this case, you set the payment cell to your desired value and tell Goal Seek to change the loan principal cell to find the correct input.
What does the CONCATENATE function do in Excel?
- A. Joins two or more text strings into one string
- B. Splits a text string into separate cells
- C. Compares two text strings for equality
- D. Removes duplicate characters from text
Answer: A. Joins two or more text strings into one string
CONCATENATE joins multiple text strings into a single string. It has been largely replaced by CONCAT and TEXTJOIN in newer Excel versions.
What is the purpose of the LAMBDA function in Excel?
- A. Creating anonymous functions in VBA
- B. Defining custom reusable functions without VBA
- C. Generating random numbers
- D. Creating lambda expressions for Power Query
Answer: B. Defining custom reusable functions without VBA
LAMBDA lets you create custom reusable functions using Excel formulas alone, without VBA.
Take the full Excel practice test
Excel Questions and Answers
Final thoughts. The Excel PMT function is one of the most useful financial tools you can master. Whether buying a home, financing a car, planning retirement, or analyzing investments, PMT gives you the answers you need quickly.
Start with the basics. =-PMT(rate/12, years*12, principal) for any loan. Understand sign conventions (negative payment is correct). Master the related functions (PV, FV, RATE, NPER, IPMT, PPMT) as you progress.
Apply it to your life. Don't just learn the formula — use it. Calculate your mortgage payment. Plan your retirement savings. Analyze whether to refinance. Compare car loan options. The function is most valuable when applied to real decisions.
Build templates. Mortgage calculator, car loan calculator, retirement planner. Reusable spreadsheets save time and increase accuracy. Add charts, what-if scenarios, sensitivity analyses.
Avoid common mistakes. Convert annual rates to monthly. Use total periods, not just years. Understand the sign convention. Account for total cost (not just principal + interest).
The PMT function democratizes financial calculations. What used to require a banker, financial advisor, or specialized calculator now sits in any spreadsheet. Master it, and you'll make better financial decisions throughout your life. Whether borrowing or saving, planning or comparing, PMT provides the mathematical foundation for sound personal finance.
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- store.certiport.com — Fees, retake fee · verified Nov 2026
- certiport.pearsonvue.com — Renewal, score validity, score release, exam format · verified Nov 2026
- learn.microsoft.com — Test delivery, time limit, eligibility · verified Nov 2026