QuickBooks Loan Amortization Schedule Calculator

A QuickBooks loan amortization schedule shows how each scheduled payment is divided between principal and interest, and how the balance declines over time. This calculator estimates a fixed-rate monthly payment and total interest from the loan amount, APR, and term; you can use its results as a reference when recording a loan in QuickBooks.

Formula

The calculator uses the standard fixed-payment loan formula. It converts the annual percentage rate to a monthly rate, then calculates a level payment that would repay the starting balance over the stated number of monthly payments.

• Monthly rate: r = APR / 1,200, where APR is entered as a percentage per year.
• Number of payments: n = years × 12.
• Monthly payment: Payment = P × r / [1 − (1 + r)⁻ⁿ].
• Total interest: Interest = (monthly payment × n) − P.

P is the original loan principal in dollars, r is the monthly interest rate as a decimal, and n is the number of monthly payments. The payment formula result is displayed to the nearest cent. Total interest uses that rounded monthly payment, so it may differ slightly from a calculation that retains unrounded payment precision throughout.

Worked Example

Using the calculator’s default values—$20,000 principal, 8% APR, and a five-year term:

1. Convert APR to a monthly decimal rate: 8 / 1,200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Apply the payment formula: $20,000 × 0.0066667 / [1 − (1.0066667)⁻⁶⁰] = $405.53 per month, rounded to cents.
4. Calculate interest from the rounded payment: ($405.53 × 60) − $20,000 = $4,331.80.

The estimated monthly payment is $405.53, and the estimated total interest is $4,331.80. These figures assume exactly 60 equal monthly payments and do not represent a lender’s final payoff or billing schedule.

Assumptions

• The interest rate is fixed for the entire term.
• Payments are equal, made monthly, and applied at the end of each monthly period.
• Interest compounds monthly at APR divided by 12.
• Fees, taxes, insurance, escrow, late charges, irregular payment dates, and any balloon payment are not included.

Reading a Loan Amortization Schedule

An amortization schedule is a period-by-period table. A typical row includes the payment number or date, amount paid, interest portion, principal portion, and remaining balance. Interest is calculated against the unpaid balance, so at the beginning of a standard amortizing loan, a larger share of each payment generally goes toward interest. As the balance falls, the interest portion usually shrinks and more of a level payment goes toward principal.

For a monthly schedule, the basic calculation for a period is interest = beginning balance × monthly rate. Principal paid is the payment minus that period’s interest, and the ending balance is the beginning balance minus principal paid. For example, on a $20,000 balance at a monthly rate of 0.0066667, the first month’s estimated interest is $133.33. With a $405.53 payment, about $272.20 goes to principal, leaving approximately $19,727.80. A real lender may use daily accrual, different payment dates, or other contract terms, so its schedule can differ.

Using the Estimate With QuickBooks

QuickBooks is accounting software, not the lender’s authoritative payment record. How a loan is entered and tracked can depend on the QuickBooks product, account setup, transaction workflow, and the loan agreement. A schedule can help you understand the expected split between interest expense and principal reduction, but the calculator does not create or import a QuickBooks schedule.

In bookkeeping, principal repayment reduces the loan liability; it is not the same account treatment as interest expense. When a payment is entered, the payment may need to be allocated between those components rather than recorded entirely as an expense. Use the lender’s statement or amortization schedule to confirm the actual amounts and dates, and follow the account setup appropriate to your records. If QuickBooks labels or available workflows differ, consult current product help or a qualified bookkeeping professional rather than relying on this estimate to determine an entry.

When This Calculator Fits—and Where It Does Not

This estimate is useful for a conventional fixed-rate loan with equal monthly payments and no unusual payment structure. It can provide a quick benchmark for payment size, total scheduled interest, or a rough comparison with a lender-provided schedule when the assumptions match.

It is not a complete amortization table: it returns a payment and total interest, not the date and principal-interest breakdown for every installment. It is also not designed for variable rates, interest-only periods, skipped or uneven payments, daily simple interest, fees financed into the balance, or a final balloon payment. For those cases, the schedule must reflect the contract’s actual rules and dates.

Keep rounding in mind. The displayed payment is rounded to cents, but a lender may calculate interest daily or adjust the final payment to clear a small remaining balance. Extra principal payments can also change the balance and reduce later interest, but their effect depends on when they are credited and how the loan applies them. Do not assume that a schedule generated from the basic formula will match a payoff quote.

Common Errors and Useful Checks

• Entering 8 instead of 8% in the wrong format: This calculator’s APR input expects 8 for 8%, not 0.08.
• Using the term in months: The term field is in years; enter 5 for five years, not 60.
• Mixing payment amount with interest cost: The monthly payment includes principal and interest. Total interest is only the estimated interest over the full schedule.
• Expecting an identical accounting entry: The calculator gives a mathematical estimate, not the exact transaction split shown by a lender.

To check a lender schedule, compare the starting principal, APR, payment frequency, term, first payment date, fees included in the balance, and the amount of each payment. Differences often arise from a mismatch in one of those assumptions, payment timing, or rounding.

Frequently Asked Questions

Does QuickBooks automatically create a loan amortization schedule?
QuickBooks features and workflows vary by product and setup. This calculator produces an estimate only; check the current help for your specific QuickBooks version to learn which loan-tracking options it supports.

How do I calculate the principal and interest in a monthly payment?
For a standard monthly schedule, multiply the beginning balance by the monthly rate to estimate that month’s interest. Subtract that interest from the payment to find the principal portion, then subtract principal from the beginning balance.

Why does my QuickBooks loan balance differ from the lender’s balance?
Possible causes include a payment recorded to the wrong account, an incorrect principal-interest split, a missed or duplicated transaction, timing differences, fees, or interest calculated using a different method. Reconcile against lender statements and the contract schedule.

Can I use this for a variable-rate loan?
No. The formula holds APR constant for the entire term. A variable-rate schedule needs the rate changes and their effective dates, along with the lender’s payment recalculation rules.

Does total interest include fees?
No. The result is scheduled interest under the stated assumptions; it excludes fees and other charges. Enter the lender’s actual loan details elsewhere as needed, and check its disclosures for the complete cost.

This result is an estimate for educational purposes, and the lender’s disclosure gives the exact figures.

Related

• Loan Amortization Sheet: Payments, Interest, and Balance
• Loan Amortization Calculator: Payments and Interest
• Loan Amortization Schedule With Extra Payments

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