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 problemsTo create a fixed-rate amortization calculator in Excel, calculate the regular payment with PMT, then build one schedule row for each payment period showing interest, principal, and the remaining balance. The result is an estimate based on the rate, timing, and other assumptions you enter—not a lender’s official payoff quote.
Choose a blank workbook or a Microsoft template
A formula-led workbook makes the assumptions and calculations visible, and is easier to tailor when you understand the loan’s terms. A template can save setup time, but check its formulas and payment assumptions before relying on it.
| Approach | Best for | What to check |
|---|---|---|
| Build a schedule from a blank workbook | Seeing how each payment is split and customizing the inputs or schedule | Use matching rate and payment periods, and add separate logic for loan features beyond the basic fixed-rate case. |
| Adapt a Microsoft template | Starting with a prepared mortgage calculator or amortization schedule | Confirm that the workbook’s assumptions match your loan; do not assume it supports special features without inspecting it. |
Microsoft’s Excel template catalog lists mortgage calculators for estimating monthly payments, amortization schedules, and payoff scenarios. You can choose a template and download it for Excel.
Set up the loan inputs
In a new worksheet, create a clearly labeled input area. Use one cell for each value so the formulas can reference the inputs rather than hard-coded numbers.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
- Classic Office Apps | Includes classic desktop versions of Word, Excel, PowerPoint, and OneNote for creating documents, spreadsheets, and presentations with ease.
- Install on a Single Device | Install classic desktop Office Apps for use on a single Windows laptop, Windows desktop, MacBook, or iMac.
- Ideal for One Person | With a one-time purchase of Microsoft Office 2024, you can create, organize, and get things done.
- Consider Upgrading to Microsoft 365 | Get premium benefits with a Microsoft 365 subscription, including ongoing updates, advanced security, and access to premium versions of Word, Excel, PowerPoint, Outlook, and more, plus 1TB cloud storage per person and multi-device support for Windows, Mac, iPhone, iPad, and Android.
- Principal: the amount borrowed.
- Quoted annual interest rate.
- Payments per year, such as 12 for monthly payments.
- Loan term in years.
- Payment timing: at the end or beginning of each period.
- Optional future balance, if the loan is not expected to amortize to zero.
Use the rate per payment period and the total number of payment periods in the payment formula. For monthly payments on a four-year loan at 12%, Microsoft’s example uses 12%/12 for the periodic rate and 4*12 for the number of payments. If payments are annual, use an annual rate and a count of annual payments instead.
Calculate the regular payment with PMT
Microsoft documents the function as PMT(rate, nper, pv, [fv], [type]): rate is the rate per period, nper is the total number of payments, and pv is the present value or principal. The optional fv is the desired balance after the final payment and defaults to zero. type is 0 or omitted for payments at the end of each period, and 1 for payments at the beginning. See Microsoft’s PMT function reference.
For a loan with end-of-period payments and no remaining balance, the formula pattern is:
Rank #2
- Fully compatible with Microsoft Office documents, Office Suite is the number 1 affordable alternative. It is compatible with Word, Excel and PowerPoint files allowing you to create, open, edit and save all your existing documents in an easy-to-use professional office suite. Suitable for home, student, school, family, personal and business use, it includes comprehensive PDF user guides to help you get started, plus a dedicated guide for university students to help with their studies. Multilingual - English, Spanish (Español) and more languages supported.
- Professional premier office suite includes word processor, spreadsheet, presentation, graphics, database and math apps! It can open a plethora of file formats including doc, docx, odt, txt, xls, xlsx, xlsm, ppt, pptx and many more, making it the only office suite you will ever need. You can use the ‘Save as’ feature to ensure your files remain compatible with Word, Excel and PowerPoint, plus you can convert and export your documents to PDF with ease.
- Full program included that will never expire! Free for life updates with lifetime license so no yearly subscription or key code required ever again! Unlimited users allow you to install to both desktop and laptop without any additional cost, and everything you need is provided on USB; perfect for offline installation, reinstallation and to keep as a backup. Compatible with Microsoft Windows 11, 10, 8.1, 8, 7, Vista, XP (32/64-bit), Mac OS X and macOS.
- PixelClassics exclusive extras include 1500 fonts, 120 professional templates, 1000's of clip art images, PDF user guides, over 40 language packs, easy-to-use PixelClassics installation menu (PC only), email support and more! Each USB comes complete with our quick start install guide, plus a fully comprehensive PDF guide is provided on USB.
- You will receive the USB (not a disc) exactly as pictured, in protective sleeve (retail box not included). Our slimline USB is 100% compatible with ALL standard size USB ports. To ensure you receive exactly as advertised including all our exclusive extras, please choose PixelClassics. All our USBs are checked and scanned 100% virus and malware free giving you peace of mind and hassle-free installation, and all of this is backed up by PixelClassics friendly and dedicated email support.
=PMT(annual_rate/payments_per_year, years*payments_per_year, principal)
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Replace the named inputs with cell references or defined names in your workbook. Excel may return a negative payment when you enter the principal as a positive cash inflow; that is a cash-flow sign convention. To display the borrower’s payment as a positive number, use =-PMT(...) consistently.
Payment timing changes the calculation. Use type 0 for an end-of-period payment or 1 for a beginning-of-period payment. In Microsoft’s example with an 8% rate, 10 monthly payments, and $10,000 principal, the returned payments are ($1,037.03) for end-of-period timing and ($1,030.16) for beginning-of-period timing. Those example amounts are negative because of Excel’s cash-flow sign convention.
PMT calculates principal and interest for regular payments at a constant periodic rate. It does not include taxes, reserve payments, or fees that may be associated with a loan. For a mortgage, do not treat the result as the full housing payment or total borrowing cost unless you model those other amounts separately.
Build the amortization schedule
Create a schedule with one row per payment period. A simple end-of-period, fixed-rate schedule can use these columns:
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 reinstallCrashes, 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 minute| Column | What it represents | Calculation |
|---|---|---|
| Period | Payment number | Count from 1 through the total number of payments. |
| Due date | Scheduled payment date, if dates are in scope | Generate dates from the first due date using the loan’s payment frequency. |
| Beginning balance | Amount owed before the period’s payment | For the first row, use the principal; thereafter, link to the prior row’s ending balance. |
| Payment | Regular scheduled payment | Use the PMT result, with the chosen sign convention. |
| Interest | Interest accrued for the period | Beginning balance multiplied by the periodic rate. |
| Principal | Scheduled payment applied to principal | Payment minus interest, assuming both are displayed as positive borrower amounts. |
| Extra principal | Optional additional amount paid toward principal | Enter separately if you are modeling extra payments. |
| Ending balance | Amount remaining after the payment | Beginning balance minus scheduled principal and any extra principal. |
For the basic schedule, the next period’s beginning balance equals the previous period’s ending balance. Keep extra principal separate from the regular payment so the scheduled payment’s interest-and-principal split remains clear.
Rank #4
- THE ALTERNATIVE: The Office 9 Package is the perfect alternative to MS Office. It offers you word processing as well as spreadsheet analysis and the creation of presentations.
- Excellent word processing - Powerful spreadsheet processing - Stunning presentations
- Adjustable user interface: classic look or ribbon style
- Office at home, you can run it on up to 5 PCs! A single license is enough to provide your entire family with a powerful office suite! If you use it commercially though, it's one license per installation.
- FULL COMPATIBILITY: ✓ Compatible with Office Word, Excel and PowerPoint ✓ Suitable for Windows 11, 10 (32 and 64-bit versions) ✓ Fast and easy installation ✓ Easy to navigate
Use IPMT and PPMT for payment components
Instead of calculating each row’s interest and principal from the balance, you can use Excel’s period-specific functions. IPMT(rate, per, nper, pv, [fv], [type]) returns interest for a specified period; PPMT(rate, per, nper, pv, [fv], [type]) returns principal for that period. Use the same periodic rate, payment count, principal, future balance, and timing assumptions as in PMT. See Microsoft’s IPMT reference and PPMT reference.
Check the schedule before relying on it
- Under fixed-rate, equal-payment assumptions, the scheduled payment should stay constant.
- Before any separate extra principal, interest plus principal should equal the scheduled payment.
- Each row’s ending balance should become the next row’s beginning balance.
- The balance should approach zero and reach zero after the final payment, subject to rounding.
Choose a rounding policy deliberately. If you round interest or principal to cents in every period, small rounding differences can affect the final balance. If the schedule ends with a residual amount, review the rounding and final-payment treatment rather than assuming the lender will use the same method.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Add summary totals if useful
To calculate cumulative interest across a range of payment periods, Excel provides CUMIPMT(rate, nper, pv, start_period, end_period, type). Its period numbering begins at 1. CUMPRINC calculates cumulative principal. These functions can support summary figures, while the row-by-row schedule shows how each payment is allocated. Microsoft lists both in its financial functions reference and documents CUMIPMT.
Best Value
- THE ALTERNATIVE: The Office Suite Package is the perfect alternative to MS Office. It offers you word processing as well as spreadsheet analysis and the creation of presentations.
- LOTS OF EXTRAS:✓ 1,000 different fonts available to individually style your text documents and ✓ 20,000 clipart images
- EASY TO USE: The highly user-friendly interface will guarantee that you get off to a great start | Simply insert the included CD into your CD/DVD drive and install the Office program.
- ONE PROGRAM FOR EVERYTHING: Office Suite is the perfect computer accessory, offering a wide range of uses for university, work and school. ✓ Drawing program ✓ Database ✓ Formula editor ✓ Spreadsheet analysis ✓ Presentations
- FULL COMPATIBILITY: ✓ Compatible with Microsoft Office Word, Excel and PowerPoint ✓ Suitable for Windows 11, 10, 8, 7, Vista and XP (32 and 64-bit versions) ✓ Fast and easy installation ✓ Easy to navigate
When the basic calculator needs more logic
The PMT/IPMT/PPMT approach models equal periodic payments and a constant periodic interest rate. It is not enough by itself for every loan contract.
- Extra principal: Add the extra amount to the schedule and calculate the next balance from both scheduled and extra principal. The payment amount or loan end date may change depending on the loan terms and how the borrower handles the extra amount.
- Variable rates: Use the rate applicable to each period and recalculate the payment when the loan terms require it. A single PMT result assumes the rate stays constant.
- Irregular dates, late or skipped payments: These may require date-specific interest and rules for payment application rather than a simple period count.
- Balloon balance or actual-day interest: Enter an appropriate future balance where applicable, and add the loan’s specific interest conventions and timing rules.
Corporate Finance Institute’s Excel amortization resource, published March 12, 2024, discusses additional payments and variable rates as extensions to a schedule. For any nonstandard feature, use the loan documents to determine the assumptions and do not treat a generic spreadsheet as the lender’s payoff calculation.
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.
