A loan payment schedule calculator in Excel uses the loan amount, annual percentage rate (APR), and term to calculate a level monthly payment and show how each payment is divided between interest and principal. Enter the three figures below to estimate the payment and total interest; an Excel amortization table can then show the changing balance month by month.
Formula
The calculator uses the standard fixed-payment amortization formula. It first calculates the payment that would repay the starting balance over the number of monthly periods:
Payment = P × r / (1 − (1 + r)−n)
Here, P is the principal, or starting loan balance, in dollars; r is the monthly interest rate as a decimal; and n is the number of monthly payments. Convert the APR entered as a percentage to a monthly decimal with r = APR / 1200. Convert the term in years to months with n = years × 12. The payment is rounded to the nearest cent. Total interest is estimated as the rounded monthly payment multiplied by the number of payments, less the original principal.
The payment formula assumes a positive APR. The input range begins at 0.01% because the formula divides by the monthly rate. A zero-interest loan has a different, simpler payment calculation: principal divided by the number of payments.
Worked Example
Using the calculator’s defaults, the principal is $20,000, the APR is 8%, and the term is five years.
- Convert the APR to a monthly decimal:
r = 8 / 1200 = 0.0066667. - Convert the term to payments:
n = 5 × 12 = 60. - Apply the payment formula:
$20,000 × 0.0066667 / (1 − (1.0066667)−60), which rounds to $405.53 per month. - Calculate total interest using the rounded payment:
$405.53 × 60 − $20,000 = $4,331.80.
These are estimates under the stated assumptions. A lender’s actual schedule can differ because of payment dates, rounding conventions, fees, or other loan terms.
Assumptions
- The interest rate stays fixed for the full term.
- Payments are equal, monthly, and made at the end of each monthly period.
- Interest compounds monthly at APR divided by 12.
- Fees, taxes, insurance, PMI, escrow, and extra payments are not included.
How to Build the Schedule in Excel
The calculator provides the payment and total interest. To create a month-by-month schedule, put the original principal, APR, term in years, and calculated payment in separate cells. Use cell references in formulas so changing an input updates the schedule. For instance, if principal is in B1, APR as a percentage is in B2, years are in B3, and payment is in B4, a payment formula is:
=ROUND(B1*(B2/1200)/(1-(1+B2/1200)^-(B3*12)),2)
Then add columns for period, opening balance, payment, interest, principal paid, and closing balance. In the first schedule row, calculate interest as opening balance times APR divided by 12. Principal paid is the payment minus interest, and closing balance is opening balance minus principal paid. For the next row, carry the previous closing balance into opening balance. Fill the formulas down for the total number of monthly payments.
- Interest: opening balance × APR / 1200.
- Principal paid: scheduled payment − interest.
- Closing balance: opening balance − principal paid.
Keep full precision in intermediate calculations and format displayed values as currency. If you round interest and principal separately in every row, small rounding differences can accumulate. The final payment may need adjustment to clear a small remaining balance; confirm the actual payoff amount and schedule with the lender.
How to Read the Results
At the beginning of a standard amortizing loan, the outstanding balance is largest, so the interest portion of each payment is also usually larger. As principal is repaid, later payments generally allocate more toward principal and less toward interest. The scheduled payment can remain constant while its components change.
Total interest is the sum of interest charges implied by this model over the full schedule. It is not the total amount paid: total payments include repayment of the original principal as well. For this example, total payments are $24,331.80, consisting of $20,000 principal and $4,331.80 estimated interest.
Common Errors and Limits
- Entering APR incorrectly: Enter 8 for 8%, not 0.08. The formula divides the percentage by 1200 to get a monthly decimal.
- Mixing years and months: This calculator takes the term in years and multiplies it by 12. If building a spreadsheet directly from a months input, use the month count without multiplying again.
- Confusing payment with principal: The monthly payment includes both interest and principal; it is not the amount by which the balance falls.
- Assuming every loan is fixed-rate: A variable rate, changing payment, irregular payment date, or payment holiday is not represented by this level-payment formula.
- Expecting fees and extra payments to be included: They are not inputs here. Additional charges or principal payments can change the schedule and total cost.
A spreadsheet is useful for exploring a stated loan amount, APR, and term, but it cannot determine the lender’s exact daily interest, payment allocation, or payoff quote. Use the figures and payment schedule supplied for the specific loan when reconciling an account.
Frequently Asked Questions
How do I calculate a monthly loan payment in Excel?
Use the fixed-payment formula above, converting APR to a monthly rate and years to months. Excel’s PMT function is another option: =-PMT(APR/12, years*12, principal). The minus sign makes the payment display as a positive number when the principal is entered as a positive value.
How do I make an amortization schedule in Excel?
Calculate the fixed payment, then create monthly rows for opening balance, interest, principal paid, and closing balance. Each row’s closing balance becomes the next row’s opening balance. Fill the formulas down through the term and check the final balance for rounding differences.
Does the monthly payment include interest?
Yes. For a standard amortizing loan, each scheduled payment includes an interest portion and a principal portion. The split changes over time even when the scheduled payment stays level.
Why does my Excel result differ from the lender’s payment?
Check that the APR, loan amount, term, and payment frequency match. Differences can also result from fees, variable rates, payment timing, lender rounding, or a first payment period that is not exactly one month.
Can this calculator include extra payments?
No. The estimate assumes the same scheduled payment each month and no additional principal payments. An extra-payment schedule requires another input and a row-by-row balance calculation.
The 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
- Cash-Out Refinance a Car? Calculator and Costs$405.53
- Case Total Cost of Ownership Calculator$405.53
- Cash-Out Auto Refinance: Calculate Your New Loan$405.53
- Car Cost of Ownership Calculator and Key Expenses$650.00
- Car Total Cost of Ownership Calculator: Full Cost
- Car Refinance Cash Out Options and How They Work$405.53