Personal Loan Amortization Schedule in Excel

A personal loan amortization schedule in Excel shows each monthly payment split between interest and principal, along with the balance left after each payment. This calculator estimates the monthly payment and total interest from a loan amount, APR, and term; use the same inputs in Excel to build the full schedule.

Formula

The fixed monthly payment is calculated from the loan principal, monthly interest rate, and number of monthly payments. The formula is:

Payment = P × r ÷ (1 − (1 + r)−n)

• P is the starting loan principal, in dollars.
• r is the monthly interest rate as a decimal: APR ÷ 12 ÷ 100.
• n is the number of monthly payments: loan term in years × 12.
• Payment is the level monthly payment, in dollars.

Total interest in this calculator is estimated as the rounded monthly payment multiplied by the number of payments, minus the original principal. The rate input must be above zero because the payment formula divides by the monthly rate. For a zero-interest loan, the payment would instead be principal divided by the number of payments.

Worked Example

Using the calculator’s default values: a $20,000 loan, 8% APR, and a five-year term. The rate and term are examples only; obtain the figures for a specific loan from its lender.

1. Convert APR to a monthly decimal: r = 8 ÷ 1200 = 0.0066667.
2. Find the number of payments: n = 5 × 12 = 60.
3. Apply the payment formula: 20,000 × 0.0066667 ÷ (1 − (1 + 0.0066667)−60).
4. The monthly payment, rounded to cents, is $405.53.
5. Total interest using that rounded payment is $405.53 × 60 − $20,000 = $4,331.80.

Assumptions

• The interest rate is fixed for the entire modeled term.
• Payments are level monthly payments, made at the end of each monthly period.
• Interest accrues monthly at APR divided by 12; this is a simplifying convention and may differ from a lender’s daily-interest method.
• Fees, taxes, insurance, and other charges are not included.

Build the Schedule in Excel

To create a row-by-row schedule, place the principal, APR, and term in separate input cells. For example, put the loan amount in B1, APR as a percentage in B2, and the number of monthly payments in B3. In B4, calculate the payment with =-PMT(B2/12,B3,B1). Excel’s PMT function expects a periodic rate and number of periods; the leading minus sign displays the payment as a positive amount when the principal is entered as positive.

Create columns for payment number, beginning balance, payment, interest, principal repaid, and ending balance. In the first schedule row, use formulas equivalent to these, adjusting cell references to match your layout:

• Beginning balance: the original principal for the first row; afterward, the prior row’s ending balance.
• Interest: beginning balance multiplied by the monthly rate, such as =BeginningBalance*$B$2/12.
• Principal repaid: payment minus interest.
• Ending balance: beginning balance minus principal repaid.

Fill the formulas down for the full number of payments. The ending balance should approach zero, but rounding each displayed or calculated amount to cents can leave a small residual. A real final payment may also differ because lenders can use exact dates, daily accrual, payment processing rules, or adjustments. Don’t alter a spreadsheet result to assume that the lender will handle the last payment in a particular way.

How to Read the Schedule

At the start of a fully amortizing loan, the balance is largest, so the interest portion of each payment is generally larger. As principal is repaid, interest for later periods is calculated on a smaller balance; more of the same level payment then goes toward principal. The scheduled payment does not usually decline just because the interest portion declines.

The balance column is an estimate of what remains after each modeled payment, not necessarily a payoff quote. A payoff amount on a particular date can include interest accrued since the last payment and other amounts specified by the lender. Check the loan disclosure or request a current payoff figure for an exact amount.

Excel Methods and Common Errors

Excel’s PMT function calculates the payment, while IPMT and PPMT can calculate the interest and principal parts for a specified period. For example, =-IPMT($B$2/12,1,$B$3,$B$1) gives the first period’s interest as a positive number under this sign convention. The schedule approach above makes the changing balance and each component visible.

• Entering APR as 8 instead of 8%: Excel may interpret 8 as 800% when used directly. If the cell contains the number 8, divide it by 100 before applying the monthly conversion; if it is formatted as 8%, use the value divided by 12.
• Mixing years and months: A five-year term means 60 monthly periods, not five.
• Using the annual rate as the monthly rate: For a nominal APR with monthly periods, use APR divided by 12. A lender’s calculation convention may vary.
• Rounding too early: Displaying cents is different from rounding every intermediate calculation. This calculator rounds its payment to cents for the total-interest estimate, while a spreadsheet may retain extra precision internally.
• Treating extra payments as part of the standard schedule: Additional principal payments change the remaining balance and future interest. Model them in separate rows or columns rather than assuming the fixed-payment schedule already includes them.

Limits and Useful Variations

This schedule fits a basic fixed-rate loan with equal monthly payments. It does not model variable rates, skipped or late payments, fees rolled into the balance, irregular payment dates, or additional payments. If the loan has one of these features, a simple PMT schedule may not match the lender’s balance calculations.

A common question is whether an amortization schedule is the same as a payoff schedule. The terms are sometimes used loosely, but a standard amortization table describes planned payments over the full term. A payoff schedule may instead show what happens when extra principal is paid or payments are made on different dates. For an extra-payment scenario, reduce the balance by the extra principal in the relevant period, then recalculate subsequent interest from that lower balance. Confirm any payment-allocation details with the lender.

Frequently Asked Questions

How do I make a personal loan amortization schedule in Excel?
Calculate the payment with PMT, then make columns for opening balance, interest, principal, and closing balance. Use monthly interest equal to the APR divided by 12 and fill the formulas down for the number of payments.

What Excel formula calculates a loan payment?
Use =-PMT(APR/12,months,principal) when APR is stored as a percentage and principal is positive. If APR is stored as a number such as 8 for 8%, use =-PMT(APR/100/12,months,principal).

Why does my Excel balance not reach exactly zero?
Rounding, full-precision calculations, payment timing, and the lender’s interest method can create a small difference. A spreadsheet estimate may not match the lender’s final payment calculation.

Does an amortization schedule include extra payments?
Not unless you add them to the model. Extra principal reduces the balance, which can change interest and the remaining payoff timeline.

Is the schedule an exact payoff amount?
No. It models planned payments using stated assumptions. The lender’s disclosure gives the exact figures, and a dated payoff quote is needed for the amount due on a particular day.

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

Related

• Jumbo ARM Refinance Rates by Loan Term
• Journal Entry for a Long-Term Loan: Example and Guide
• Jane Street Term Loan B: What It Means

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