A payment schedule calculator shows how each regular loan payment is split between principal and interest, and how the balance changes over time. Enter the loan amount, annual percentage rate (APR), and term to estimate the monthly payment and total interest; a spreadsheet can then show the same figures payment by payment.
Formula
The calculator uses the fixed-payment loan formula. It first calculates the monthly payment, rounded to the nearest cent, then subtracts the original principal from the sum of all scheduled payments to estimate total interest.
- p is the starting loan principal in dollars.
- apr is the annual percentage rate entered as a percentage, such as 8 for 8% per year.
- r is the monthly rate as a decimal: r = apr / 1200.
- n is the number of monthly payments: n = yrs × 12.
- pmt is the monthly payment in dollars: pmt = round[p × r / (1 − (1 + r)−n), 2].
- int is estimated total interest in dollars: int = pmt × n − p.
The total uses the rounded monthly payment, so it reflects the displayed payment multiplied by the scheduled number of months. Actual loan statements can differ slightly because of payment dates, lender rounding, fees, or a final payment adjustment.
Worked Example
Using the calculator’s default inputs: principal p = $20,000, APR = 8%, and term = 5 years.
- Convert the APR to a monthly decimal rate: r = 8 / 1200 = 0.0066666667.
- Convert the term to monthly payments: n = 5 × 12 = 60.
- Substitute into the payment formula: pmt = round[$20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60), 2] = $405.53 per month.
- Calculate scheduled payments: $405.53 × 60 = $24,331.80.
- Subtract principal: $24,331.80 − $20,000 = $4,331.80 estimated total interest.
These are estimates under the calculator’s assumptions. The payment is rounded to cents before calculating the total, so the displayed total agrees with 60 payments of $405.53.
Assumptions
- The interest rate stays fixed for the entire term.
- Payments are equal, made monthly, and timed at the end of each monthly period.
- Interest is modelled as monthly at APR / 12.
- Fees, taxes, insurance, PMI, escrow, late charges, and optional extra payments are not included.
What a Payment Schedule Spreadsheet Shows
A payment schedule, often called an amortization schedule, expands the monthly-payment estimate into one row per payment. A typical Excel sheet includes the payment number or date, opening balance, scheduled payment, interest portion, principal portion, and closing balance. The opening balance on a later row is usually the previous row’s closing balance.
For a fixed-rate loan, the scheduled payment generally stays level while the allocation changes. Interest for a period is calculated on the outstanding balance. The remainder of the payment reduces principal. As the balance falls, the interest portion usually shrinks and the principal portion grows. For example, an early payment on a $20,000 loan at 8% APR has about $133.33 in interest for the first month ($20,000 × 0.08 / 12); from a $405.53 payment, about $272.20 reduces principal. Later rows use the lower remaining balance.
In a spreadsheet, the first row can refer to the loan amount as its opening balance. The interest cell can multiply that balance by the periodic rate, and principal paid can be calculated as payment minus interest. The next closing balance is opening balance minus principal paid. Spreadsheet functions and cell references vary by workbook, so check how the sheet treats dates, rounding, and any final adjustment before relying on its output.
How to Build or Check the Sheet
Start with the loan terms shown in the lender’s disclosure or account information. Enter the principal, APR as a percentage, term, and payment frequency. A schedule based on monthly payments should use monthly periods; converting a weekly or biweekly arrangement into twelve payments per year would not represent the actual timing.
- Set the opening balance to the principal.
- Calculate each period’s interest from the opening balance and periodic rate.
- Allocate the scheduled payment first to the interest amount; the remainder reduces principal.
- Subtract principal paid from the opening balance to obtain the closing balance.
- Carry the closing balance into the next row and repeat for the number of periods.
For a basic monthly model, the periodic rate is APR divided by 12, then divided by 100 when the APR is entered as a whole-number percentage. If APR is stored as an Excel percentage value (for example, 8% rather than 8), the equivalent monthly-rate expression is APR/12. Mixing these conventions is a frequent source of errors.
Choosing a Template and Checking Results
A simple template is suitable for a fixed-rate loan with regular payments and no extra activity. A more detailed schedule may be needed when payments vary, the rate changes, fees are financed, or additional principal is paid. Check that the template’s stated assumptions match the loan rather than assuming every downloadable spreadsheet uses the same method.
- Confirm the principal, APR, term, and payment frequency against the loan documents.
- Check whether the APR is entered as 8 or 8%, and whether the formula expects a percentage or decimal.
- Look for the rounding method. Rounding every period can create a small difference from carrying extra decimal places and rounding only the displayed values.
- Verify that the closing balance approaches zero after the final scheduled payment. A small residual may be caused by rounding; a large residual can indicate a wrong rate, period count, or payment.
- Check whether the last payment is adjusted rather than treated as identical to earlier payments.
A payment schedule is not necessarily the same as a lender’s payoff quote. A payoff figure may account for interest accrued through a particular date, outstanding charges, or other account-specific items. The schedule is a planning model, not a substitute for the lender’s current balance or payoff information.
Common Problems and Limits
One common misconception is that the entire monthly payment reduces the loan balance. In an amortizing loan, interest is part of the payment, so only the principal portion lowers the balance. Another mistake is multiplying the original principal by the APR to estimate total interest; that ignores the changing balance and the way payments are allocated over time.
Results can also diverge when the loan has a variable rate, irregular payment dates, a payment holiday, extra payments, or fees that affect the balance. This calculator assumes a fixed rate and equal monthly payments, so it does not model those features. It also uses a stated APR as a monthly rate divided by 12; lender-specific interest accrual conventions may differ. For a detailed forecast, use the schedule and terms supplied for the specific loan and compare its assumptions with the spreadsheet.
Frequently Asked Questions
What is a payment schedule Excel sheet?
A payment schedule spreadsheet lists each planned payment and shows how much is assigned to interest and principal, along with the balance after that payment. It is commonly used to understand how a fixed-payment loan amortizes.
How do I calculate interest in a monthly payment schedule?
For this basic fixed-rate model, multiply the opening balance for the month by APR divided by 12. If APR is entered as a whole-number percentage, divide by 1,200 to convert it to a decimal monthly rate.
Why does the principal portion increase over time?
With a fixed payment and rate, interest is calculated on the remaining balance. As principal is paid down, less interest accrues each month, leaving more of the same scheduled payment to reduce principal.
Why does my Excel schedule not match the lender’s figures?
Differences may come from rounding, payment dates, daily interest accrual, fees, a variable rate, or a different final-payment treatment. Compare the spreadsheet’s assumptions with the loan documents and account figures.
Can I use this schedule for extra payments?
The calculator does not include extra payments. A spreadsheet can model them by applying each additional amount to principal in the relevant period, but the result depends on how the loan handles extra payments and any payment changes.
This result is an estimate, and the lender’s disclosure gives the exact figures.
Related
Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000 in apr | Interest rate (APR) | % | 8 | 0.01 | 100 in yrs | Loan term | years | 5 | 0.0833 | 50 out pmt | Monthly payment | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2 out int | Total interest | $ | pmt * yrs * 12 - p | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- June Balance Date: What It Means for Loan Balances$405.53
- L&T Finance Gold Loan: How It Works and What to Check
- Journal Entry to Correct a Beginning Balance
- JS Bank Gold Loan: Eligibility, Process and Costs
- Journal Entry for a Bank Loan: Debit and Credit$20,000.00
- Journal Entry to Adjust Beginning Balance