A simple-interest loan amortization calculator estimates a level monthly payment and total interest from the loan amount, annual rate, and term. Enter those three figures to see the payment and interest under a standard fixed-rate, monthly-payment model; an Excel schedule can then show how each payment is split between interest and principal.
Formula
This calculator uses the standard amortizing-payment formula for a fixed annual percentage rate, converted to a monthly rate. It is suitable for a conventional loan whose interest accrues on the outstanding balance and whose scheduled payments are monthly and level.
- Monthly payment: M = P × r / [1 − (1 + r)−n]
- Total interest: I = M × n − P
Here, P is the starting principal in dollars, APR is the annual rate entered as a percentage, r = APR / 1200 is the monthly rate as a decimal, and n is the number of monthly payments (term in years × 12). M is the monthly payment in dollars, rounded to cents for display. The total-interest output uses that displayed rounded payment multiplied by the scheduled number of payments, then subtracts principal. A real final payment may need adjustment for cent rounding or the lender’s day-count method.
Worked Example
Using the calculator’s default inputs—$20,000 principal, 8% APR, and five years—there are n = 5 × 12 = 60 payments. The monthly rate is r = 8 / 1200 = 0.0066667.
- Compute the payment: M = 20,000 × 0.0066667 / [1 − (1.0066667)−60].
- The unrounded result is about $405.5277, so the monthly payment rounded to cents is $405.53.
- Total interest using the rounded payment is $405.53 × 60 − $20,000 = $4,331.80.
The results are estimates from the stated formula. Excel may show slightly different last digits if intermediate values are rounded, so retain full precision in formulas and round only the displayed payment.
Assumptions
- The interest rate is fixed for the full term.
- Payments are equal monthly payments made at the end of each monthly period.
- Interest compounds monthly at APR / 12; no daily accrual or irregular payment dates are modelled.
- Fees, taxes, insurance, PMI, and escrow are not included. The lender’s quoted rate, fees, and payment schedule are the figures to enter or verify.
Build an Amortization Schedule in Excel
The calculator provides the payment and overall interest estimate. A row-by-row Excel schedule shows the changing balance and makes it possible to trace how much of each payment covers interest. Set up inputs in separate cells—for example, principal in B1, APR in B2, term in years in B3—and calculate the monthly payment in B4 with:
=ROUND(B1*(B2/1200)/(1-(1+B2/1200)^-(B3*12)),2)
Make a table with columns for payment number, beginning balance, payment, interest, principal repaid, and ending balance. In the first payment row, use these formulas, assuming the first row is row 8:
- Payment number:
1 - Beginning balance:
=$B$1 - Payment:
=$B$4 - Interest:
=B8*($B$2/1200)(if beginning balance is in B8) - Principal:
=C8-D8(payment minus interest) - Ending balance:
=B8-E8(beginning balance minus principal)
Adjust column references to match your sheet, then copy the formulas down for the number of monthly payments. Each following beginning balance should equal the previous row’s ending balance. The schedule should trend toward zero, with interest generally falling and principal repayment generally rising as the balance declines.
Simple Interest and Amortization: What the Terms Mean
“Simple interest” can refer to interest calculated on the outstanding principal rather than interest being added to principal and itself earning interest. In a typical amortizing loan, the periodic interest charge is calculated on the current balance; as principal is repaid, later interest charges decline. That does not mean every contract labelled “simple interest” uses the same timing or payment rules. Some calculate interest daily on the balance, so the exact charge can depend on the number of days between payments and when a payment posts.
The formula above is a level-payment, monthly-period approximation. It is not a universal formula for every product marketed as a simple-interest loan. Check the loan agreement or disclosure for how interest accrues, the payment due dates, and whether a late or early payment changes accrued interest. Use the lender’s schedule when exact contractual figures matter.
Reading the Schedule and Avoiding Errors
In each row, interest equals the opening balance multiplied by the periodic rate. The remainder of the payment reduces principal. For example, with an opening balance of $20,000 and a monthly rate of 0.0066667, the first month’s estimated interest is about $133.33; from a $405.53 payment, about $272.20 goes to principal. Small differences can arise because the schedule may calculate from unrounded values while displaying cents.
- Convert the APR correctly: for an APR entered as 8, the monthly decimal rate is 8 / 1200, not 8 / 12.
- Use months in the payment count: multiply years by 12. A five-year term is 60 monthly periods, not 5.
- Keep signs and references consistent: the balance falls by principal repaid, not by the interest portion.
- Do not round each intermediate calculation unnecessarily: format cells to show cents while retaining calculation precision.
- Check the ending balance: the final row may differ from zero by a few cents if the regular payment is rounded. A real loan may use a slightly different final payment.
A payment that arrives early or late can affect a daily simple-interest loan differently from this monthly model. Extra payments may also be applied under contract-specific rules; an Excel schedule that simply subtracts them from principal may not reproduce the lender’s posting method.
Frequently Asked Questions
Can I calculate simple-interest loan amortization in Excel?
Yes. Calculate the level monthly payment, then make a schedule with beginning balance, interest, principal repaid, and ending balance. Use the lender’s accrual and payment rules if they differ from monthly periods.
What Excel formula calculates the monthly loan payment?
You can use =ROUND(P*(APR/1200)/(1-(1+APR/1200)^-(Years*12)),2), replacing P, APR, and Years with cell references. APR should be entered as a percentage number such as 8 for 8%.
Is a simple-interest loan the same as a standard amortizing loan?
Not necessarily. Amortization describes repayment over time; simple interest describes an interest calculation method. A monthly amortization formula may estimate a simple-interest loan, but daily accrual and contract terms can change the exact schedule.
Why does my Excel ending balance not equal zero?
Cent rounding, payment dates, daily interest, or a different final-payment rule can leave a small difference. Keep full precision in intermediate formulas and check the lender’s schedule for the contractual final payment.
Does total interest include fees?
No. The estimate here is payment-based interest only. Fees and other charges are excluded, so consult the lender’s disclosure for the complete amount due under the loan.
This result is an estimate; 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 | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 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
- Closing Costs on a House Loan: What to Expect$405.53
- Secured Loan for Home Refinancing: Payment Calculator$405.53
- Closing Cost House Calculator: Estimate Buyer Costs$8,500.00
- Closing Cost House Estimate Calculator
- Loan Repayment Extension Request Letter: What to Include$405.53
- Refinance Calculator With PMI and Loan Term