Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchiTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To build a basic amortization schedule in Excel, calculate the fixed periodic payment with PMT, use IPMT and PPMT to split each payment into interest and principal, then roll each ending balance into the next period. The method below is for a fixed-rate loan with regular equal payments; a variable-rate or irregular-date contract needs its own accrual and payment rules.
Set up the loan inputs
Enter assumptions in a labeled input block. Keep rates, periods, and payment timing explicit so the formulas use consistent units.
| Input | Example | Meaning |
|---|---|---|
| Loan principal | 180000 | Amount borrowed, entered as a positive number. |
| Annual interest rate | 5% | Enter as a percentage, not as the number 5. |
| Payments per year | 12 | Use 12 for monthly payments. |
| Term in years | 30 | Used to derive the total number of periods. |
| Payment timing | 0 | 0 means end of each period; 1 means beginning. |
| Final balance | 0 | Normally zero for a fully amortizing loan. |
For monthly payments, calculate the periodic rate as annual rate divided by 12 and the number of payments as years multiplied by 12. The rate and period count must use the same cadence. Microsoft defines PMT(rate, nper, pv, [fv], [type]) for a constant rate and regular payments; the result includes principal and interest, not taxes, reserve payments, or fees. See Microsoft’s PMT function documentation.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Calculate the scheduled payment
With named inputs, use:
=PMT(annual_rate/payments_per_year, years*payments_per_year, principal, 0, payment_type)
#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
For example, if the annual rate is in B2, payments per year in B3, term in years in B4, principal in B1, and payment type in B5, the formula is =PMT(B2/B3,B4*B3,B1,0,B5). Excel follows a cash-flow sign convention, so a positive principal commonly produces a negative payment: the sign represents money paid out. For a borrower-facing schedule with positive payment amounts, you can use a negative principal in the financial functions or apply a consistent sign reversal when displaying results. Do not mix conventions between formulas.
As a formula illustration, Microsoft’s support example uses a $180,000 home loan at 5% for 30 years and =PMT(5%/12,30*12,180000), yielding $966.28 per month. Microsoft notes that this excludes insurance and taxes; it is not a current mortgage offer or a complete estimate of housing costs. See Microsoft’s payment and savings formula examples.
Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
Build one row for each payment period
Create columns for Period, Payment date (if known), Beginning balance, Payment, Interest, Principal, and Ending balance. Keep the period number separate from calendar dates: the functions model regular periods, and a simple monthly formula does not establish a contract’s day-count or date-adjustment method.
- Enter period numbers. Start with 1 and continue through the total number of payments.
IPMTandPPMTuse period numbers beginning at 1. - Fill the scheduled payment. Use the PMT result in every row for the standard fixed-payment case.
- Calculate interest. In the Interest column, use
=IPMT(periodic_rate, period_number, total_periods, principal, 0, payment_type). - Calculate principal. In the Principal column, use
=PPMT(periodic_rate, period_number, total_periods, principal, 0, payment_type). - Calculate the ending balance. With borrower-facing positive principal amounts, use beginning balance minus principal repaid.
- Carry the balance forward. The next row’s beginning balance equals the previous row’s ending balance.
For example, with the same named inputs as above and the period number in A10, the interest formula is =IPMT($B$2/$B$3,A10,$B$4*$B$3,$B$1,0,$B$5); the principal formula is =PPMT($B$2/$B$3,A10,$B$4*$B$3,$B$1,0,$B$5). These financial functions commonly return negative cash-flow amounts when the principal is positive. If you want positive values in the schedule, normalize the signs consistently—for example, show the magnitude of principal and subtract that amount from the balance. Microsoft documents the components and their assumptions in its IPMT documentation and PPMT documentation.
Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
Cross-check the schedule with the balance method
For a fixed payment, you can independently calculate each row directly from its beginning balance:
- Interest: beginning balance multiplied by the periodic rate.
- Principal: scheduled payment minus interest.
- Ending balance: beginning balance minus principal.
This makes the balance roll-forward visible and provides a useful check against IPMT and PPMT. Format displayed amounts to cents, but do not round intermediate calculations period by period unless the contract requires it. Repeated rounding can leave a small residual, which may require an adjusted final payment under the contract.
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
Check for common formula errors
- Rate and period mismatch: For monthly payments, pair annual rate divided by 12 with years multiplied by 12.
- Wrong payment timing: In these functions,
type0 or omitted means payment at the end of a period; 1 means payment at the beginning. The timing choice affects the result. - Unexpected negative values: A negative payment, interest, or principal amount can be correct under Excel’s cash-flow convention. Confirm signs before changing the arithmetic.
- Components do not reconcile: After normalizing signs, the period’s interest and principal should add up to the scheduled payment.
- Balance does not carry forward: Check that every period begins with the preceding period’s ending balance.
- Residual at the end: A final balance close to zero may reflect display or rounding effects. Do not conceal it; check the contract’s final-payment terms.
When the standard schedule does not fit
PMT, IPMT, and PPMT describe the constant-rate, constant-payment case. They do not by themselves model rate changes, irregular payment dates, fees, or a lender’s specific day-count and accrual rules. For those loans, use the contract’s actual rate-reset, date, interest-accrual, and payment rules rather than treating a basic annuity schedule as exact.
Equal payment and equal principal are different patterns
Some loans repay the same principal amount each period instead of keeping the total payment level. Interest then falls as the unpaid balance declines, so the total payment also declines. Microsoft documents ISPMT for calculating interest in this even-principal pattern; its period numbering starts at 0, unlike IPMT and PPMT, which start at 1. The total payment in an even-principal schedule is the equal principal amount plus that period’s interest. See Microsoft’s ISPMT function documentation.
Best Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
| Repayment pattern | Payment shape | Principal per period | Excel approach |
|---|---|---|---|
| Equal total payment | Generally level for a fixed rate and regular periods | Rises over time as interest falls | PMT for payment; IPMT and PPMT for components |
| Equal principal | Declines as interest falls | Stays level | Set equal principal amounts; use ISPMT for interest |
The schedule shows the arithmetic for the chosen repayment structure; it does not mean the borrower can select a different method from the one permitted by the loan contract.
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.
