A repayment schedule in Excel shows each loan payment split between principal and interest, along with the balance still owed after each payment. This calculator estimates the monthly payment and total interest from a loan amount, APR, and term; you can use those figures to build and check a month-by-month spreadsheet schedule.
Formula
The calculator uses the standard fixed-payment amortization formula. It first converts the annual percentage rate into a monthly rate, then calculates the payment that pays down the principal and interest over the term:
Monthly payment: M = round(P × r ÷ [1 − (1 + r)−n], 2)
Total interest: I = M × n − P
- P is the amount borrowed, in dollars.
- APR is the annual percentage rate entered as a percentage.
- r is the monthly rate, calculated as APR ÷ 1,200. For an 8% APR, r = 8 ÷ 1,200.
- n is the number of monthly payments, calculated as years × 12.
- M is the monthly payment, rounded to the nearest cent.
- I is estimated total interest, using that rounded monthly payment.
Worked Example
With the calculator’s default inputs—$20,000 principal, 8% APR, and a five-year term—the number of payments is 5 × 12 = 60, and the monthly rate is 8 ÷ 1,200 = 0.0066667.
- Substitute into the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60].
- The unrounded payment is about $405.53; rounded to the cent, the estimated monthly payment is $405.53.
- Total scheduled payments using the rounded payment are $405.53 × 60 = $24,331.80.
- Subtract the original principal: $24,331.80 − $20,000 = $4,331.80 estimated total interest.
Assumptions
- The interest rate stays fixed for the full term.
- Payments are equal, made monthly, and paid at the end of each monthly period.
- Interest compounds monthly at APR ÷ 12.
- The estimate excludes fees, taxes, insurance, PMI, escrow, and any charges not entered as inputs.
Build the Schedule in Excel
To create an amortization schedule, put the loan details in separate cells, then create columns for payment number, starting balance, payment, interest, principal, and ending balance. If you enter the principal in B1, APR as a percentage in B2, and the number of monthly payments in B3, a spreadsheet payment formula can use Excel’s PMT function:
=-PMT(B2/12,B3,B1)
PMT returns a negative cash flow when the principal is positive, so the minus sign displays the payment as a positive amount. The period rate is B2/12 when B2 contains a percentage such as 8%, and B3 must be the total number of monthly payments, not the number of years.
For the first schedule row, calculate interest as starting balance multiplied by the monthly rate. Principal paid is the payment minus that period’s interest. Ending balance is starting balance minus principal paid. For the next row, carry the previous ending balance forward as the new starting balance. Fill the formulas down for each month.
- Interest: beginning balance × APR ÷ 12.
- Principal: payment − interest.
- Ending balance: beginning balance − principal.
With the example above, the first month’s interest is approximately $133.33 ($20,000 × 0.08 ÷ 12). Of the $405.53 payment, about $272.20 goes to principal, leaving an estimated balance of $19,727.80. Small differences can occur if spreadsheet formulas retain extra decimal places internally while the displayed payment is rounded.
How to Read and Check the Results
In a standard amortizing loan, the payment is level, but its components change: interest generally takes a larger share early in the schedule, while principal repayment grows as the balance declines. The final balance should be close to zero. It may not be exactly zero because of cent-level rounding, a lender’s specific accrual method, or a different final payment amount.
Check that the number of rows equals the number of scheduled payments, and that each row’s ending balance matches the next row’s starting balance. Sum the interest column to compare it with the calculator’s total-interest estimate. If the spreadsheet’s final balance is slightly above or below zero, verify that the APR is entered as 8% rather than 8, and that the payment and interest use consistent rounding.
Common Excel Mistakes and Limits
- Mixing annual and monthly values: Divide an annual APR by 12 for a monthly rate, and multiply a term in years by 12 for the payment count.
- Entering a whole-number rate incorrectly: Excel interprets 8% as 0.08. If you enter 8 to mean eight percent, divide by 100 before using it as a rate.
- Sign confusion in PMT: Excel uses cash-flow signs. With a positive loan amount, PMT commonly returns a negative payment; use a leading minus sign for a positive display.
- Assuming every loan has equal payments: Variable rates, interest-only periods, irregular payment dates, fees added to the balance, or a different compounding method require a different schedule.
- Rounding every intermediate value: Displaying two decimal places does not necessarily round the stored value. Decide whether to round only the payment or also each period’s interest and principal, since the method affects the final cents.
This calculation is a general estimate, not a lender-specific payoff statement. Actual schedules can use contract terms, payment dates, fees, or rounding conventions that are not represented here. Get the applicable APR and loan details from the lender. The result is an estimate, and the lender’s disclosure gives the exact figures.
Frequently Asked Questions
How do I calculate a monthly loan payment in Excel?
Use =-PMT(APR/12, years*12, principal) when APR is stored as a percentage and the term is in years. The minus sign makes the payment display as positive.
How do I split a payment into principal and interest?
For each month, multiply the starting balance by the monthly rate to find interest. Subtract that interest from the payment to find principal, then subtract principal from the starting balance.
Why does my Excel balance not reach exactly zero?
Rounding, using a payment with fewer decimals than Excel’s internal value, or mismatched rate and term units can leave a small residual. The final payment may also need adjustment in a real schedule.
Does a repayment schedule include extra payments?
Not automatically. Add a separate extra-payment input or column and apply it to principal to model how it changes later balances and the payoff date. Confirm how extra payments are treated under the loan’s terms.
Is APR the same as the monthly interest rate?
No. APR is an annual percentage measure; this calculator converts it to a monthly rate by dividing by 12. The conversion is an assumption, and lender-specific accrual details may differ.
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