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 →A monthly amortization schedule shows how each loan payment is divided between interest and principal, and how the balance falls over time. The free Excel workbook described below is designed for fixed-rate loans with monthly payments. It includes a regular-payment schedule, summary figures, a chart, and a version that allows extra payments.
Download the Excel monthly amortization schedule from ExcelDemy. The file is listed as compatible with Excel 2007 or later and licensed for private use.
What the free workbook includes
The main worksheet is named Payoff Calc. Its blue-shaded Input Values section is where you enter the loan assumptions. The workbook then calculates the scheduled monthly payment and generates a payment-by-payment table.
| Input | What to enter |
|---|---|
| Original Loan Terms (Years) | The original repayment period, such as 15 or 30 |
| Loan Amount | The amount borrowed |
| Annual Percentage Rate (APR) | The annual rate stated by the lender |
| First Payment Date | The date the first scheduled payment is due |
| Payment Type | Whether payments occur at the beginning or end of each period |
| Interest Compounding Frequency | How often interest compounds under the loan terms |
Outputs include the interest rate per period, total amount paid, total interest, number of payments, and total time. The schedule itself shows the balance declining as each payment is applied.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →#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
How to use the downloaded schedule
- Download and open the .xlsx file in desktop Excel. Save a working copy before changing anything.
- Go to the Payoff Calc worksheet.
- Enter the loan details in the blue Input Values area. Do not enter the annual rate as a monthly rate unless the workbook specifically asks for that.
- Check the calculated monthly payment against the payment quoted by your lender.
- Review the first several rows of the amortization table. The interest portion should generally be larger at the beginning of a standard loan, while the principal portion grows over time.
- Use the summary and chart to review total interest and the expected payoff timeline.
If the cells are locked
If Excel will not let you type into the input area, the worksheet is probably protected. The publisher says the included sheet is protected but not password-protected. In Excel, select Review > Unprotect Sheet. The download page also provides a version with protection removed.
Using the extra-payment version
The download page includes a separate worksheet named Payoff Calc. (Extra Payments). It supports recurring extra payments as well as individual amounts entered in the Extra Payments (Irregular) column.
Use this version to test questions such as:
- How much interest might an additional $100 per month avoid?
- How much sooner could a loan be paid off?
- What happens if a one-time payment is made in a particular month?
Do not assume that an extra payment automatically lowers your required monthly bill. Depending on the loan agreement, it may shorten the term, reduce interest, trigger a formal re-amortization, or be applied under another lender-specific rule. Confirm how your lender applies additional principal payments, and check whether there is a prepayment restriction.
Build a simple monthly schedule yourself
You do not need a specialized template for a basic fixed-rate schedule. Suppose the loan amount is in B2, the annual rate is in B3, and the term in years is in B4.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Calculate the regular monthly payment with:
=PMT(B3/12,B4*12,-B2)Using a negative loan amount makes the payment display as a positive number. If you instead use B2 as a positive present value, Excel will normally return the payment as a negative cash outflow.
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.
For a schedule with beginning balance, payment, interest, principal, and ending balance, use this logic:
Interest = BeginningBalance * MonthlyRate
Principal = Payment - Interest
EndingBalance = BeginningBalance - PrincipalFor example, if the beginning balance is in B10, the monthly rate is in $B$3/12, and the payment is in $B$6:
Interest: =B10*($B$3/12)
Principal: =$B$6-C10
Ending balance: =B10-D10Copy the next row’s beginning balance from the previous row’s ending balance. For a fixed-rate loan, the balance should reach approximately zero after the planned number of payments. A final small difference can occur because of rounding.
Free tools Windows power users keep installed
One-click scans. No signup required.
Using IPMT and PPMT
Excel also has functions for calculating the interest and principal portions for a particular payment. If the monthly rate is in B3/12, the payment number is in A10, the total number of payments is B4*12, and the loan amount is in B2, the formulas are:
=IPMT($B$3/12,A10,$B$4*12,$B$2)
=PPMT($B$3/12,A10,$B$4*12,$B$2)IPMT returns the interest for a specified period and PPMT returns the principal. The period number must be between 1 and the total number of payments. These formulas use the same cash-flow sign conventions as PMT, so you may need a negative present-value argument if you want positive displayed components.
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.
Common errors that make a schedule unreliable
Using the annual rate as the monthly rate
For monthly payments, a nominal annual rate is commonly divided by 12. A 6% annual rate is entered as 6% and used as 6%/12, not as 6% for every month. Typing 6 instead of 6% is also a major error: Excel interprets 6 as 600% unless the cell is deliberately formatted and calculated otherwise.
Mixing payment and rate units
The rate and number of periods must use matching units. For monthly payments over four years at 12% annually, the standard PMT structure is:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitches=PMT(12%/12,4*12,-LoanAmount)Do not use 48 payments with an annual rate, or 12 periods with a four-year loan.
Choosing the wrong payment timing
In Excel’s financial functions, type=0 means payment at the end of the period and is the usual assumption for an ordinary loan. type=1 means payment at the beginning of the period. Changing this setting changes both the payment and the interest allocation.
Expecting PMT to include the entire loan bill
PMT calculates principal and interest only. It does not include property taxes, homeowners insurance, escrow, reserve contributions, servicing fees, origination charges, or other costs. A lender’s monthly draft can therefore be higher than the spreadsheet’s scheduled payment.
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
Applying fixed-rate formulas to a non-fixed loan
PMT, IPMT, and PPMT assume a constant periodic rate and regular payments. They do not automatically model adjustable-rate changes, daily simple interest, 365/360 conventions, irregular payment dates, or a lender’s special interest rules. For an adjustable-rate or otherwise unusual loan, update the rate and payment assumptions for each applicable period or use the lender’s official schedule.
Outdated 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 matchWindows 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 reinstallIgnoring rounding
A spreadsheet may retain several decimal places while a lender rounds interest and principal to cents at every payment. That difference can leave a small residual balance or make the final payment differ from the template. Use the lender’s official amortization statement or payoff quote when making a payoff decision.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Excel’s built-in alternative
Desktop Excel may include a ready-made loan workbook. Open File > New, search the templates for Loan Amortization Schedule, choose the result, and click Create. Reported fields include loan amount, annual interest rate, loan period in years, payments per year, and start date. The resulting workbook can calculate scheduled payment, principal, interest, ending balance, and cumulative interest.
Template galleries vary by Excel version, account, and platform, so not every installation will show the same result. In Excel for the web, use Excel’s web template page, select Excel from the drop-down, choose a template, and click Edit. Avoid relying on older Microsoft Create links that may redirect to a newer Microsoft 365 page.
Save your customized schedule as a reusable template
After removing personal loan data and leaving only the structure and formulas:
Best 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
- In Windows desktop Excel, select File > Options > Save.
- Set Default personal templates location to a folder such as
C:Users[UserName]DocumentsCustom Office Templates. - Choose File > Export > Change File Type > Template.
- Enter a file name and select Save.
- To reuse it, go to File > New > Personal and double-click the template. Some Excel installations call this area Custom instead.
Keep the original .xlsx workbook separately. A template is best used as a clean starting point, while the original working file preserves your formulas and formatting if you need to troubleshoot later.
FAQ
Is the Excel monthly amortization schedule free?
The ExcelDemy download is offered as a free .xlsx workbook for private use and is listed as compatible with Excel 2007 or later. Download it from the publisher’s monthly amortization schedule page.
Why is my PMT result negative?
Excel uses cash-flow signs. If the loan amount is entered as a positive present value, PMT commonly returns a negative payment. Use a negative loan amount, such as =PMT(rate/12,years*12,-loan_amount), to display the payment as positive.
Can I use this schedule for extra mortgage payments?
Yes, the extra-payment version supports recurring additions and irregular amounts. It estimates the effect on interest and time, but your lender may apply extra money differently and may not reduce the required payment without re-amortization.
Why does the spreadsheet balance differ slightly from my lender’s balance?
Common causes include cents-level rounding at every payment, different compounding or day-count conventions, fees or escrow excluded from the spreadsheet, payment-date differences, and lender-specific treatment of extra payments. Compare the result with an official amortization statement or payoff quote.
The Bottom Line
For a conventional fixed-rate loan with regular monthly payments, the ExcelDemy workbook is a practical starting point: enter the six loan inputs on Payoff Calc, inspect the payment breakdown, and use the extra-payment sheet for scenarios. Treat the result as an estimate unless its rate, timing, compounding, rounding, fees, and prepayment rules match your 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.
