calculate-loan

01 Loan payments

Student Loan Payoff Sheet: How to Build One

Calculator No. 01-21449 · Updated · Formula and assumptions shown below

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A student loan payoff sheet is a simple spreadsheet or table that tracks your loan balance, payment schedule, and progress toward paying off your debt. You enter your loan amount, interest rate, and payment, and the sheet shows your monthly payment breakdown, total interest, and projected payoff date. This guide explains how to build one, what the numbers mean, and how to use it to see your progress clearly.

Formula

The standard fixed-payment loan formula calculates your monthly payment based on three inputs: the principal balance, the annual interest rate, and the loan term. The formula is:

M = P × (r × (1 + r)^n) / ((1 + r)^n − 1)

Where:

  • M = monthly payment (in dollars)
  • P = loan principal, or the amount you borrowed (in dollars)
  • r = monthly interest rate, which is the annual percentage rate (APR) divided by 12, then divided by 100 (e.g., 6.5% APR → 0.065 / 12 = 0.0054167)
  • n = total number of monthly payments (loan term in years × 12)

This formula assumes a fixed interest rate and equal payments every month. It is the same formula used for most auto loans, personal loans, and many private student loans. Federal student loans with income-driven repayment plans use different calculations, which this sheet does not model.

Worked Example

Using the default values from the calculator above: a $35,000 loan at 6.5% APR for 10 years.

  1. Convert the annual rate to a monthly rate: 6.5 ÷ 1200 = 0.0054167
  2. Find the number of payments: 10 × 12 = 120
  3. Calculate (1 + r)^n: (1.0054167)^120 ≈ 1.9115
  4. Plug into the formula: M = 35000 × (0.0054167 × 1.9115) / (1.9115 − 1)
  5. Numerator: 35000 × 0.010354 = 362.39
  6. Denominator: 0.9115
  7. Payment: 362.39 ÷ 0.9115 = 397.58

Your monthly payment is $397.58. Over 120 payments, you pay $397.58 × 120 = $47,709.60 total. The total interest is $47,709.60 − $35,000 = $12,709.60. The calculator rounds the payment to the nearest cent, and all totals use that rounded figure.

Assumptions

  • Fixed interest rate for the entire loan term
  • Payments are made monthly, at the end of each period
  • Interest compounds monthly at APR ÷ 12
  • No fees, origination charges, late penalties, or prepayment penalties are included
  • No extra payments, deferment, forbearance, or loan forgiveness are modeled

How a Payoff Sheet Works

A payoff sheet is more than just the monthly payment. It typically includes a full amortization schedule: a row for each payment showing the payment number, payment amount, interest portion, principal portion, and remaining balance. Each month, the interest is calculated on the current balance, and the rest of your payment reduces the principal. Early in the loan, most of your payment goes to interest; later, most goes to principal.

To build one yourself, create columns for: payment number, payment date, payment amount, interest paid, principal paid, and remaining balance. Start with your initial balance. For each row, interest = balance × monthly rate, principal = payment − interest, and new balance = old balance − principal. Repeat until the balance reaches zero.

You can also add a column for cumulative interest paid to see the total cost over time. Many people find this motivating because the principal balance visibly drops faster in later years.

How to Choose the Right Inputs

Before building your sheet, gather accurate numbers from your loan servicer or lender. You need:

  • Current principal balance – not the original amount if you have made payments
  • Current interest rate – check if it is fixed or variable
  • Remaining term – how many payments are left, or the original term if you are starting fresh
  • Your actual monthly payment – this may differ from the minimum if you pay extra

If you have multiple loans, make one sheet per loan or combine them by summing the balances and using a weighted average rate. The weighted average is calculated by multiplying each loan’s balance by its rate, summing those products, and dividing by the total balance. This gives a single rate for a combined payment estimate.

Common Mistakes to Avoid

Several errors can make a payoff sheet misleading:

  • Using the APR as a monthly rate – always divide by 12 (and by 100 if the APR is a percentage).
  • Ignoring rounding – real payments are rounded to the cent, and small rounding differences accumulate over years.
  • Forgetting the last payment – due to rounding, the final payment may be slightly different from the regular payment. Many sheets adjust the last row to clear the remaining balance exactly.
  • Assuming simple interest – student loans compound monthly, so interest accrues on unpaid interest if you miss payments.
  • Mixing up biweekly and monthly payments – if you pay every two weeks, you make 26 half-payments per year, which equals 13 full payments, not 12. This extra payment shortens the term and reduces interest.

Using Extra Payments

One of the most useful features of a payoff sheet is modeling extra payments. If you pay an additional $50 or $100 each month, the extra amount goes entirely to principal, reducing the balance faster and cutting total interest. To model this, simply set your monthly payment input to your regular payment plus the extra amount. The sheet will show a shorter payoff time and lower total interest.

For example, on the $35,000 loan above, increasing the payment from $397.58 to $450 per month would shorten the term from 10 years to roughly 8.5 years and reduce total interest by about $2,000. You can test different amounts to see the trade-off between monthly cash flow and long-term savings.

Limits of the Calculator

This calculator and the payoff sheet method work well for fixed-rate, level-payment loans. They do not handle variable rates, which change over time, or income-driven repayment plans, where payments are based on your income and may not cover the accruing interest. They also ignore fees, such as origination charges, which are typically added to the principal before payments begin. If your loan has a variable rate, you would need to recalculate the payment each time the rate changes.

Frequently Asked Questions

Should I include all my student loans in one sheet?
You can, but it is clearer to track each loan separately because they may have different rates and terms. If you combine them, use a weighted average rate and a combined balance. The result is an estimate of your total monthly payment, not an exact schedule for any single loan.

What if my payment is less than the interest?
If your payment does not cover the monthly interest, the unpaid interest is added to your principal, and your balance grows. This can happen with income-driven repayment plans. The formula will show a negative amortization, meaning the balance increases each month. In that case, the payoff sheet cannot predict a payoff date because the loan never gets paid down.

Does paying extra always reduce total interest?
Yes, as long as the extra payment is applied to principal and there is no prepayment penalty. The earlier you make the extra payment, the more interest you save because the principal is reduced sooner. Some lenders apply extra payments to future payments instead of principal, so check your servicer’s policy.

How do I find my current balance and rate?
Log into your loan servicer’s website or call them. Your monthly statement shows the current principal balance, interest rate, and payment due. Use those numbers for the most accurate sheet. The original loan documents also list the rate and term.

What is the difference between APR and interest rate?
The interest rate is the cost of borrowing the principal. APR includes certain fees and costs spread over the loan term, so it is usually higher. For loan payoff calculations, use the interest rate that your payment is based on, not the APR, unless the APR equals the interest rate.

Can I use this for a federal student loan?
Yes, for the standard repayment plan, which uses a fixed payment over 10 years. Income-driven plans, graduated repayment, and loan forgiveness programs have different rules and are not modeled here. Check with your servicer for the specifics of your plan.

This result is an estimate based on the inputs you provide; your lender’s disclosure gives the exact figures for your loan.

Related

Formula used
in p | Loan amount | $ | 35000 | 1 | 100000000
in apr | Interest rate (APR) | % | 6.5 | 0.01 | 100
in yrs | Loan term | years | 10 | 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
out tot | Total paid | $ | pmt * yrs * 12 | 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

The whole Loan payments section →