What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
The best Excel formula depends on what you mean by “interest.” Use IPMT for the interest in one payment, CUMIPMT for a range of payments, PMT to calculate the payment and total scheduled interest, or an amortization schedule for a complete, adjustable model. For a conventional fixed-rate loan with monthly payments, convert the annual rate to a monthly rate and the term to a number of monthly periods.
This example uses a $20,000 loan at 8% annual interest, repaid monthly over five years. Assuming payments are made at the end of each month and there are no fees, the payment is approximately $405.53 and total scheduled interest is approximately $4,331.67.
As an Amazon Associate I earn from qualifying purchases.
Set up the loan inputs
Enter the assumptions once so every formula refers to the same inputs:
Free tools Windows power users keep installed
One-click scans. No signup required.
| Cell | Label | Value or formula |
|---|---|---|
| B2 | Loan amount | 20000 |
| B3 | Annual interest rate | 8% |
| B4 | Term in years | 5 |
| B5 | Payments per year | 12 |
| B6 | Total payments | =B4*B5 |
| B7 | Periodic rate | =B3/B5 |
| B8 | Payment | =-PMT(B7,B6,B2,0,0) |
For monthly payments, the periodic rate is normally annual rate/12 and the number of periods is years*12. For quarterly, weekly, or biweekly payments, use the corresponding number of payment periods: annual rate/4, annual rate/52, or annual rate/26. This assumes the quoted rate is a nominal annual rate divided by the payment frequency. A lender that accrues interest daily or uses a different compounding convention may produce different results.
#1 Best Overall
- Compact Mouse: With a comfortable and contoured shape, this Logitech ambidextrous wireless mouse feels great in either right or left hand and is far superior to a touchpad
- Durable and Reliable: This USB wireless mouse features a line-by-line scroll wheel, up to 1 year of battery life (2) thanks to a smart sleep mode function, and comes with the included AA battery
- Universal Compatibility: Your Logitech mouse works with your Windows PC, Mac, or laptop, so no matter what type of computer you own today or buy tomorrow your mouse will be compatible
- Plug and Play Simplicity: Just plug in the tiny nano USB receiver and start working in seconds with a strong, reliable connection to your wireless computer mouse up to 33 feet / 10 m (5)
- Better than touchpad: Get more done by adding M185 to your laptop; according to a recent study, laptop users who chose this mouse over a touchpad were 50% more productive (3) and worked 30% faster (4)
Excel accepts both 8% and 0.08. If a cell contains the number 8, rather than a percentage value, use =B3/100/12. Do not divide a cell already stored as 8% by 100 again.
Why Excel returns negative payments and interest
Excel financial functions use cash-flow signs. Money received by the borrower and money paid back by the borrower are opposite cash flows, so PMT, IPMT, and CUMIPMT commonly return negative values when the loan principal is entered as positive.
To display borrower payments or interest as positive expenses, put a minus sign before the function:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitches=-PMT(rate,nper,pv)
=-IPMT(rate,period,nper,pv)
=-CUMIPMT(rate,nper,pv,start_period,end_period,type)Keep the cash-flow signs consistent rather than changing only one argument arbitrarily.
Method 1: Calculate one period’s interest manually
When you know the balance at the start of a period, the basic calculation is:
=BeginningBalance*PeriodicRateFor the first month of this example:
=B2*B7The result is approximately $133.33:
=20000*(8%/12)This is the clearest way to understand loan interest: the period’s interest is calculated from the opening balance, not necessarily from the original loan amount. As the balance declines, the interest portion normally declines too.
Use this method to check the first payment, calculate interest when a balance is already known, or model a loan with irregular balance changes. It may not match the lender exactly if the loan uses daily simple interest, an actual/365 or actual/360 day-count convention, an irregular first or last period, deferred interest, a variable rate, or capitalized fees. Check the loan agreement for the accrual method.
Rank #2
- Pair and Play: With fast, easy Bluetooth wireless technology, you’re connected in seconds to this quiet cordless mouse —no dongle or port required
- Less Noise, More Focus: Silent mouse with 90% reduced click sound and the same click feel, eliminating noise and distractions for you and others around you (1)
- Long-Lasting Battery Life: Up to 18-month battery life with an energy-efficient auto sleep feature, so you can go longer between battery changes (2)
- Comfortable, Travel-Friendly Design: Small enough to toss in a bag; this slim and ambidextrous portable compact mouse guides either your right or left hand into a natural position
- Long-Range: Reliable, long-range Bluetooth wireless mouse works up to 10m/33 feet away from your computer (3)
Method 2: Use IPMT for interest in one payment
IPMT returns the interest portion of a specified payment in a fixed-rate annuity or loan. Its syntax is:
=IPMT(rate, per, nper, pv, [fv], [type])| Argument | Meaning |
|---|---|
rate |
Interest rate per payment period |
per |
Payment number being examined |
nper |
Total number of payment periods |
pv |
Present value, or loan principal |
fv |
Balance remaining at the end; usually 0 for a fully repaid loan |
type |
0 for end-of-period payments; 1 for beginning-of-period payments |
Interest in the first monthly payment:
=-IPMT($B$7,1,$B$6,$B$2,0,0)Interest in payment 12:
=-IPMT($B$7,12,$B$6,$B$2,0,0)If payment numbers are in column A, place this in the first schedule row and copy it down:
=-IPMT($B$7,A12,$B$6,$B$2,0,0)For monthly payments, do not combine an annual rate and a number of years in a monthly formula. This is incorrect for the example:
=IPMT(8%,1,5,20000)Use the matching monthly units instead:
=IPMT(8%/12,1,5*12,20000)See Microsoft’s IPMT documentation for the function’s supported arguments and behavior.
Method 3: Calculate total scheduled interest with PMT
PMT calculates the total periodic payment, not interest alone. For a standard fixed-rate, fully amortizing loan:
=-PMT($B$7,$B$6,$B$2,0,0)Then calculate the amount paid over the full schedule and subtract the original principal:
Total paid: =B8*B6
Total interest: =B8*B6-B2For the example, the calculation is approximately:
=(-PMT(8%/12,5*12,20000))*60-20000Result: approximately $4,331.67 in scheduled interest. This is useful for comparing loan terms or getting a quick total without building every row.
Rank #3
- 【Dual Mode Wireless Bluetooth Mouse】: Switch easily between two devices—connect one via Bluetooth (BT5.2/3.0) and the other using a 2.4G USB receiver. No drivers needed; just plug and play. Enjoy a reliable connection up to 33 feet. Note: You can't use both modes simultaneously; the USB receiver is stored in the mouse.
- 【Rechargeable Wireless Mouse】: Equipped with a 500mAh lithium-ion battery, it charges in 2 hours for over 7 days of use and 30 days on standby. The mouse sleeps after 5 minutes of inactivity to save power and can be woken with any click.
- 【Colorful LED Breathing Light】: Features 7 colorful LED lights that change randomly, adding a fun atmosphere to your workspace.
- 【Portable Mouse】Compact size (4.4 x 2.3 x 1.1 inches) makes it easy to fit in your laptop bag. Lightweight and ergonomic, it's perfect for travel. Contact us anytime for support.
- 【Wide Compatibility】: Works with laptops, PCs, tablets, and smartphones across various operating systems, including Android, Windows, and Mac. Ideal for home, office, and travel.
The result assumes a constant rate, constant payments, regular intervals, no extra payments, and no fees. It is scheduled interest under the model—not the loan’s complete cost. PMT includes principal and interest but does not automatically include taxes, reserve payments, insurance, or fees, as explained in Microsoft’s PMT documentation.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteMethod 4: Use CUMIPMT for interest over a range
Use CUMIPMT when you need interest over several payments rather than one specific period:
=CUMIPMT(rate,nper,pv,start_period,end_period,type)Interest during the first year:
=-CUMIPMT($B$7,$B$6,$B$2,1,12,0)Interest during months 13 through 24:
=-CUMIPMT($B$7,$B$6,$B$2,13,24,0)Payment periods begin at 1. Use 0 for end-of-period payments and 1 for beginning-of-period payments. The function is useful for summarizing interest in a tax year, before refinancing, or during the first several years of a mortgage.
CUMIPMT is designed for a constant-payment annuity. It is not automatically suitable for a variable-rate loan, irregular dates, daily accrual, or a schedule with extra principal payments. Microsoft lists the function’s syntax and restrictions in its CUMIPMT documentation.
Method 5: Build an amortization schedule
An amortization schedule is the most transparent method because it shows the opening balance, payment, interest, principal, and ending balance for every period.
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 →| Column | Heading |
|---|---|
| A | Payment number |
| B | Beginning balance |
| C | Payment |
| D | Interest |
| E | Principal |
| F | Ending balance |
For payment 1 in row 12:
A12: 1
B12: =$B$2
C12: =$B$8
D12: =B12*$B$7
E12: =C12-D12
F12: =B12-E12For payment 2 in row 13:
A13: =A12+1
B13: =F12
C13: =$B$8
D13: =B13*$B$7
E13: =C13-D13
F13: =B13-E13Copy row 13 down through payment 60. The first payment contains approximately $133.33 of interest; later payments contain less interest and more principal.
You can also calculate the components with Excel’s functions:
Rank #4
- Your hand can relax in comfort hour after hour with this ergonomically designed mouse. Its contoured shape with soft rubber grips, gently curved sides and broad palm area give you the support you need for effortless control all day long.
- You’ve got the control to do more, faster. Flipping through photo albums and Web pages is a breeze, especially for right-handers—with three standard buttons plus Back/Forward buttons that you can also program to switch applications, go full screen and more. And side-to-side scrolling plus zoom gives you the power to scroll horizontally and vertically through your music library, maps and Facebook feeds, and zoom in and out of photos and budget spreadsheets with a click.* * Requires Logitech SetPoint software (Windows) or Logitech Control Center software (Mac OS X)
- Two years of battery life practically eliminates the need to replace batteries. ** The On/Off switch helps conserve power, smart sleep mode extends battery life and an indicator light eliminates surprises. ** Battery life may vary based on user and computing conditions.
- The tiny Logitech Unifying receiver stays in your laptop. There’s no need to unplug it when you move around, so there’s less worry of it being lost. And you can easily add compatible wireless mice and keyboards to the same wireless receiver.
D12: =-IPMT($B$7,A12,$B$6,$B$2,0,0)
E12: =-PPMT($B$7,A12,$B$6,$B$2,0,0)
C12: =D12+E12PPMT returns the principal portion for a specified period. Add totals below the schedule:
Total interest: =SUM(D12:D71)
Total principal: =SUM(E12:E71)
Total payments: =SUM(C12:C71)The final balance should be zero or very close to zero. A small residual can result from displayed rounding. Keep full precision in the formulas and format the cells as currency instead of rounding every intermediate result. If the lender rounds interest or payments to cents each period, its final row may differ by a few cents; adjust only the final payment when you need to reconcile the schedule to an actual statement.
Recommended Free Tools
Bonus: Work backward from a payment with RATE
RATE estimates the interest rate per period when you know the number of periods, payment, and principal:
=RATE(nper,-payment,principal)For the example:
=RATE(5*12,-405.53,20000)To annualize the nominal monthly rate:
=RATE(5*12,-405.53,20000)*12To calculate the effective annual rate:
=(1+RATE(5*12,-405.53,20000))^12-1RATE returns a rate per period and uses iteration. It can return #NUM! if it does not converge; supplying a reasonable optional guess may help:
=RATE(60,-405.53,20000,,0,0.01)This is useful for checking a quoted rate, but fees must be included in the cash flows if you want to estimate a broader borrowing cost. A rate derived only from principal, payment, and term is not automatically the APR.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How to model extra payments
The standard functions assume regular payment behavior. For extra principal payments, use a row-by-row schedule. For each period:
Interest = BeginningBalance*PeriodicRate
ScheduledPrincipal = ScheduledPayment-Interest
TotalPrincipal = ScheduledPrincipal+ExtraPayment
EndingBalance = BeginningBalance-TotalPrincipalTo prevent the last row from paying more than the remaining balance:
Best Value
- 【Plug and Play for Home/Office/School】The wireless computer mouse features 2.4GHz connectivity, delivering a stable, interference-free connection up to 32ft. Designed for 𝐦𝐞𝐝𝐢𝐮𝐦 𝐭𝐨 𝐥𝐚𝐫𝐠𝐞 𝐬𝐢𝐳𝐞𝐝 𝐡𝐚𝐧𝐝𝐬, it ensures comfortable use all day. Simply plug in the USB-A receiver for instant pairing—no drivers needed. 📌📌 If the mouse isn’t suitable, place the USB receiver in the battery compartment and return both.
- 【3 Levels Adjustable DPI】This travel USB mouse offers 3 adjustable DPI settings (800, 1200, 1600), allowing you to customize sensitivity for precise design work. Effortlessly switch to match your task and elevate your productivity. 📌 Please remove the film at the bottom of the mouse before use.
- 【Effortless Browsing】Equipped with forward and backward buttons, this computer mice streamlines your workflow, making it easy to navigate through web pages and files with a simple click. 📌Side button does not work on Mac.
- 【Visible Indicator Light】 The pc mouse features a visual indicator for DPI levels and low battery alerts. The red light flashes once for 800 DPI, twice for 1200 DPI, and three times for 1600 DPI. When the battery level is below 10%, the light flashes red until the mouse is completely out of power.
- 【Click to Wake】With smart sleep mode, it saves power by standby after 10 inactive minutes, just 2-3 clicks to wake. This efficient design delivers 3x longer battery life than motion-wake mice. Engineered for durability, its buttons and scroll wheel are tested for 10 million clicks, ensuring long-term reliability and consistent performance.
=MIN(ScheduledPrincipal+ExtraPayment,BeginningBalance)Then calculate the actual payment:
=Interest+TotalPrincipalExtra payments reduce future interest only if the lender applies them to principal promptly and the loan does not restrict or delay prepayment. The exact savings depend on the contract.
When standard Excel loan formulas are not enough
Variable-rate loans
A single PMT formula models one assumed rate. For an adjustable-rate loan, store the applicable rate for each period, calculate interest from that period’s beginning balance, and recalculate payments when the contract requires it. A realistic model may also need reset dates, caps, floors, interest-only periods, and a final balloon balance.
Irregular payments or daily interest
Standard annuity functions assume periodic payments. For irregular payment dates, build a date-based schedule, calculate the actual day count, and apply the lender’s day-count convention. Excel’s date-based functions, including XIRR and XNPV, are useful for analyzing cash flows with different dates; see Microsoft’s financial-functions reference.
Balloon or interest-only loans
Enter a nonzero final value in the optional fv argument where appropriate. A schedule that assumes a zero ending balance will understate the amount due if the contract contains a balloon payment.
Interest rate versus APR
The interest rate is the rate used to calculate periodic interest. APR is generally a broader borrowing-cost measure that may include certain finance charges. A formula using only PMT, IPMT, or CUMIPMT uses the rate you enter; it does not automatically add origination fees, points, insurance, taxes, or other charges.
Therefore, total payments minus principal equals scheduled interest under your assumptions—not necessarily the loan’s APR or total cost of borrowing. Include fees in a complete cash-flow analysis, or compare the lender’s disclosed APR and payment schedule with your Excel model.
Troubleshooting Excel loan calculations
#NUM!
- Confirm that the rate, number of periods, and principal are valid positive inputs where required.
- For
CUMIPMT, ensurestart_periodis at least 1,end_periodis not below the start period, and both periods are within the schedule. - Use only
0or1fortype. - For
RATE, try a realisticguessif the iterative calculation does not converge.
#VALUE!
One or more inputs may be text rather than numbers. Re-enter the value as a number or convert it with:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →=VALUE(A1)Unexpected negative results
Negative values often indicate a correct cash-flow model. Use =-IPMT(...), =-PMT(...), or =-CUMIPMT(...) when you want borrower costs displayed as positive amounts.
The result does not match the lender
Check the nominal rate versus APR, payment timing, daily versus monthly accrual, actual day counts, financed fees, first-payment date, rounding rules, escrow, extra payments, balloon balances, and variable-rate resets. Excel can model the assumptions you enter; it cannot infer rules that are not included in the worksheet.
Which method should you use?
| Need | Best method |
|---|---|
| Interest in one payment | IPMT |
| Interest during a year or range | CUMIPMT |
| Quick payment and total scheduled interest | PMT plus subtraction |
| Full visibility, extra payments, or custom terms | Amortization schedule |
| Interest on a known opening balance | Beginning balance × periodic rate |
| Implied rate from payment and principal | RATE |
For documented function behavior, see Microsoft’s pages for IPMT, PPMT, CUMIPMT, PMT, and RATE.
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.
