Free tools Windows power users keep installed
One-click scans. No signup required.
Use Excel’s PMT function for the fastest reliable answer. For a $20,000 balance at a 6% nominal annual rate, paid monthly for five years, =-PMT(6%/12,5*12,20000,0,0) returns approximately $386.66 per month. The minus sign displays the payment as a positive amount; Excel itself treats it as a cash outflow.
What an annuity payment means in Excel
An annuity is a series of equal cash payments made at regular intervals. In Excel, the term applies to mortgage and car-loan installments, regular savings deposits, retirement withdrawals, lease payments and insurance premiums—not only to an insurance-company annuity.
Excel’s time-value-of-money functions assume a constant payment and a constant interest rate for each payment period. The most direct function is PMT:
=PMT(rate, nper, pv, [fv], [type])- rate: interest rate per payment period.
- nper: total number of payment periods.
- pv: present value, such as the amount borrowed or the starting balance.
- fv: balance required after the final payment; use
0for a fully repaid loan. - type:
0or omitted for payment at the end of a period;1for payment at the beginning. See Microsoft’s syntax and conventions at Microsoft’s PMT documentation.
Prepare matching inputs before calculating
The rate and number of periods must use the same frequency. For a nominal annual rate and monthly payments, divide the rate by 12 and multiply the term by 12.
#1 Best Overall
- Profitability calculations; cash flow function Calculates NPV and IRR for uneven cash flows
- Time-value-of-money and Amortization keys solve problems including: pension calculations, loans, mortgages, etc.
- Ideal calculator for students, managers and statisticians
- Built-in functionality : List-based one- and two-variable statistics with four regression options: linear, logarithmic, exponential and power
- The BA II Plus calculator is approved for use on the following professional exams: Chartered Financial Analyst exam. GARP Financial Risk Manager (FRM) exam. Certified Management Accountants exam
| Input | Meaning | Example |
|---|---|---|
| Annual interest rate | Quoted yearly rate | 6% |
| Payments per year | Payment frequency | 12 |
| Periodic rate | Annual rate ÷ payments per year | 6%/12 |
| Term in years | Length of the agreement | 5 |
| Number of periods | Years × payments per year | 5*12 |
| Present value | Starting loan or investment balance | $20,000 |
| Future value | Required ending balance | $0 |
| Payment timing | End or beginning of period | 0 |
Dividing by 12 is appropriate when the quoted rate is a nominal annual rate compounded monthly. If 6% is an effective annual rate, an equivalent monthly rate is =(1+6%)^(1/12)-1 instead.
Method 1: Calculate a loan payment with PMT
Enter the fixed-rate loan formula
For a $20,000 loan at 6% annually, five years, monthly payments, no balloon balance and payments at month-end:
=-PMT(6%/12,5*12,20000,0,0)The result is approximately $386.66 per month. This assumes no fees, taxes, insurance or other contract adjustments. PMT includes principal and interest only.
Use cell references
If B2=6%, B3=12, B4=5, B5=20000, B6=0 and B7=0, use:
=-PMT(B2/B3,B4*B3,B5,B6,B7)Estimate total paid and interest
Total paid = Payment * Number_of_Periods
Interest = Total paid - PrincipalUsing the unrounded mathematical payment of $386.6560306 for 60 periods gives approximately $23,199.36 paid and $3,199.36 interest. These figures exclude fees and assume the rate and payment remain unchanged.
Rank #2
- PROFESSIONAL FINANCIAL CALCULATOR : Built-in TVM, IRR, NPV. Engineered for business analysts, real estate investors, accountants, and finance students.
- ADVANCED CASH FLOW & AMORTIZATION : Execute time value of money, break-even analysis, depreciation schedules, and bond pricing. Trusted for professional exam prep", MBA coursework, and banking certifications.
- CATIGA CF-300 : Flip-open hard case with a snap-close design for a secure fit. Compact and portable: designed for daily professional use in office, classroom, or on-site.
- ALL-IN-ONE FOR PROFESSIONALS : From NPV/IRR for real estate analysis to statistical calculations for business analysts. Handles probability, linear regression, and complex financial formulas.
- MORTGAGE, LOAN & INVESTMENT CALCULATOR : Covers bond pricing, loan amortization, investment analysis, and exam-level computations. Your go-to accounting calculator, business calculator, and real estate calculator in one device.
Method 2: Calculate deposits for a future savings goal
Use future value as the target
To accumulate $50,000 in five years at 6%, starting from zero and depositing at month-end, enter:
=-PMT(6%/12,5*12,0,50000,0)The required deposit is approximately $716.64 per month. For deposits at the beginning of each month, change the final argument to 1:
=-PMT(6%/12,5*12,0,50000,1)The beginning-of-period deposit earns interest for one additional month, so the required contribution changes. The same PMT framework can include an existing starting balance by replacing the zero pv argument.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Method 3: Calculate the payment with the annuity equation
Present value, zero future value
For an ordinary annuity with a known present value and no ending balance:
Payment = (r * PV) / (1 - (1+r)^(-n))To display a borrower payment as a negative cash flow:
Rank #3
- HP 10BII+ FOR STUDENTS & PROFESSIONALS – This HP calculator is built for business, finance, accounting, and statistics courses. Perfect for learners and professionals who need to solve common financial problems quickly without memorizing formulas or relying on spreadsheets.
- 100+ FUNCTIONS FOR REAL WORLD MATH – Quickly solve time value of money, interest rates, loan payments, NPV, IRR, cash flows, and more. The 10bII+ also includes probability distributions for statistics courses—a feature not often found in financial calculators.
- ALGORITHMIC INPUT WITH DEDICATED KEYS – This high-school/college calculator uses algebraic and chain logic with minimal keystrokes. Layout appears the same as standard calculators for easy learning. Dedicated keys give quick access to commonly used financial and statistical functions
- APPROVED FOR MAJOR EXAMS – The HP 10bII+ algebra calculator is permitted for use on SAT, PSAT/NMSQT, and AP tests. An ideal statistics calculator and business calculator for school finance and accounting students preparing for class, coursework, or standardized exams.
- INCLUDES TRAVEL CASE, CLEANING CLOTH & BATTERIES– Slim, durable, and easy to keep on hand or store in a backpack or locker. Includes a protective case, cleaning cloth, and batteries so it’s ready out of the box. Large screen with clear contrast (non-backlit) is easy to read during exams or lectures.
=-(6%/12*20000)/(1-(1+6%/12)^(-5*12))This reproduces approximately -$386.66. Removing the leading minus displays the positive payment magnitude.
Include both present and future value
With a consistent cash-flow sign convention, a general ordinary-annuity expression is:
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →=-(PV*(1+r)^n+FV)*r/((1+r)^n-1)For example, with the inputs in the earlier table:
=-(B5*(1+B2/B3)^(B4*B3)+B6)*(B2/B3)/((1+B2/B3)^(B4*B3)-1)Check the signs of PV, FV and the payment against the perspective you are using. Excel’s PMT function is the safer choice when you do not need to expose the algebra.
Handle a zero interest rate separately
The standard equation divides by the periodic rate. For a zero-rate case, use:
=IF(rate=0,-(pv+fv)/nper,-(pv*(1+rate)^nper+fv)*rate/((1+rate)^nper-1))For a zero-interest loan with no future value, the payment magnitude is simply principal divided by the number of periods.
Rank #4
- Solves time-value-of-money calculations such as annuities, mortgages, leases, savings, and more
- Performs cash-flow analysis for up to 32 uneven cash flows with up to 4-digit frequencies
- Calculates various financial functions: Net Future Value Net present Value Modified Internal Rate of Return Internal Rate of Return Modified Duration Payback Discounted Payback
- The Texas Instruments BAII Plus Professional features an Automatic Power Down (APD) function for extended battery life
- Prompted display guides you through financial calculations showing current variable and label. Ten-digit display
Method 4: Build a payment schedule and validate it
A schedule shows whether the balance actually reaches the intended ending value and separates interest from principal.
Recommended Free Tools
| Period | Beginning balance | Payment | Interest | Principal | Ending balance |
|---|---|---|---|---|---|
| 1 | Starting balance | Fixed payment | Beginning balance × periodic rate | Payment − interest | Beginning balance − principal |
| 2 onward | Prior ending balance | Fixed payment | Beginning balance × periodic rate | Payment − interest | Beginning balance − principal |
Worksheet formulas
Assume B2 is the periodic rate, B3 the number of periods, B4 the present value and B5 the positive payment:
B5 = -PMT(B2,B3,B4)In row 10, use:
A10 = 1
B10 = $B$4
C10 = $B$5
D10 = B10*$B$2
E10 = C10-D10
F10 = B10-E10In row 11, use:
A11 = A10+1
B11 = F10
C11 = $B$5
D11 = B11*$B$2
E11 = C11-D11
F11 = B11-E11Copy row 11 down for the full term. For built-in component checks, IPMT returns the interest portion and PPMT returns the principal portion; see Microsoft’s IPMT documentation and PPMT documentation.
Ordinary annuity versus annuity due
| Type | Payment timing | Excel type |
|---|---|---|
| Ordinary annuity | End of each period | 0 or omitted |
| Annuity due | Beginning of each period | 1 |
For the loan example, end-of-month payments use:
=-PMT(6%/12,5*12,20000,0,0)Beginning-of-month payments use:
=-PMT(6%/12,5*12,20000,0,1)Use the setting that matches the contract. Mortgages commonly pay at period end; rent, some leases and some insurance premiums may be due at period beginning. Changing type changes the financial result, not merely its display.
Excel’s cash-flow signs
Excel models direction: money received is positive and money paid out is negative. With a positive loan principal, =PMT(6%/12,60,20000) returns approximately -$386.66. Prefixing the formula with a minus sign displays the payment’s positive magnitude.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsBest Value
- Brand New in box; The product ships with all relevant accessories
- Dedicated keys allow easy access to common financial and statistics functions
- Easy-to-use design provides business, finance and statistical calculations fast
- Specially designed to meet the mathematical needs
Define the perspective before entering pv and fv. Giving both balances the same sign can produce a result that appears to point in the wrong direction.
When PMT is not enough
Use Goal Seek for custom schedules
- Create a schedule with a payment input cell and a formula for the final balance.
- Choose Data → What-If Analysis → Goal Seek.
- Set the final-balance cell to
0. - Set the payment input cell to the amount Goal Seek should change.
- Run Goal Seek and inspect the resulting schedule.
Goal Seek is useful when payments are rounded, fees are included, extra payments occur or rates change. It is unnecessary for a standard fixed-rate annuity and depends on the worksheet design and starting value.
Use a cash-flow model for irregular cases
PMT is unsuitable for variable payments, changing rates, skipped payments or irregular dates. Build dated cash flows and consider XNPV or XIRR for irregular timing. Related present- and future-value functions are documented by Microsoft at PV and FV.
Common errors and fixes
- Annual rate used as a monthly rate: replace
=PMT(6%,60,20000)with=PMT(6%/12,5*12,20000). - Years used as periods: five years of monthly payments requires
5*12, not5. - Unexpected negative result: treat it as a cash-flow direction; use
=-PMT(...)for a positive displayed amount. - Wrong payment timing: use
type=0for period-end andtype=1for period-beginning payments. - Fees included in the wrong place: PMT calculates principal and interest, not taxes, reserves or fees; add those separately in a schedule.
- Rounding too early: retain full precision internally and format the display to two decimals. Rounding every period can leave a small final balance.
#NUM!from RATE: RATE uses iteration. Try anotherguess, such as=RATE(nper,pmt,pv,fv,type,0.05); Microsoft documents the default guess and convergence behavior at RATE function.
How to check the result
- Confirm that the periodic rate and number of periods have the same frequency.
- Confirm that payment timing matches the agreement.
- Check that the schedule’s ending balance reaches the specified
fv, allowing for rounding. - Compare total payments and total interest with the contract’s disclosures.
- Check whether fees, insurance, taxes, balloon payments or extra contributions were modeled separately.
Frequently Asked Questions
How do I calculate a monthly annuity payment in Excel?
Convert the annual rate to a monthly rate and years to months, then use =-PMT(annual_rate/12,years*12,pv,fv,type). Use type=0 for month-end payments and type=1 for month-start payments.
Why does Excel PMT return a negative number?
Excel uses cash-flow signs. A negative payment represents money leaving the borrower’s or account holder’s perspective. Prefix PMT with a minus sign to display a positive payment amount.
Can I include a balloon payment?
Yes. Enter the remaining balance as the fv argument, with its sign set consistently with your cash-flow perspective.
Can PMT handle changing interest rates?
No. PMT assumes one constant periodic rate and equal payments. Use a period-by-period schedule, and use Goal Seek or other cash-flow functions when the terms vary.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
