calculate-loan

01 Loan payments

Student Loan Interest Calculator Excel

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

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

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.

This calculator works out your monthly student loan payment and total interest for a fixed-rate loan, using the same standard amortization formula you would set up in Excel. You enter the loan amount, the annual interest rate (APR), and the term in years, and you get the level monthly payment and the total interest paid over the life of the loan.

Formula

The monthly payment for a fixed-rate loan is calculated with the standard amortization formula:

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

Where:

  • M = monthly payment (in dollars)
  • P = principal, the original loan amount (in dollars)
  • r = monthly interest rate, equal to the annual percentage rate (APR) divided by 12 (as a decimal, so APR% / 1200)
  • n = total number of monthly payments, equal to the term in years multiplied by 12

Total interest is simply the monthly payment times the number of payments, minus the principal:

Total Interest = M × n − P

Worked Example

Using the calculator defaults: a $20,000 loan at 8% APR for 5 years.

  1. Convert APR to a monthly rate: r = 8 / 1200 = 0.0066667
  2. Number of payments: n = 5 × 12 = 60
  3. Plug into the formula: M = 20000 × 0.0066667 × (1.0066667)^60 / ((1.0066667)^60 − 1)
  4. Calculate (1.0066667)^60 ≈ 1.4898457
  5. M = 20000 × 0.0066667 × 1.4898457 / (1.4898457 − 1) = 20000 × 0.0066667 × 1.4898457 / 0.4898457
  6. M ≈ 20000 × 0.020276 = 405.52 (rounded to the cent)
  7. Total interest = 405.52 × 60 − 20000 = 24331.20 − 20000 = 4331.20

The calculator shows a monthly payment of $405.52 and total interest of $4,331.20, matching the example exactly.

Assumptions

  • The interest rate is fixed for the entire term (no variable-rate changes).
  • Payments are made monthly at the end of each period.
  • Interest compounds monthly at APR / 12, which is the standard for most student loans.
  • No fees, loan origination charges, late penalties, or prepayment penalties are included.
  • No grace period, deferment, or forbearance is modeled.

Why Use Excel Instead of a Calculator?

Many borrowers prefer Excel because it gives full control and transparency. You can see every formula, adjust assumptions, and build a complete amortization schedule that shows the balance after each payment. Excel also lets you compare multiple scenarios side-by-side, which is helpful when you are considering different loan amounts or terms. A web calculator like this one is faster for a single estimate, but Excel is better for deeper analysis.

To replicate this in Excel, you would use the built-in PMT function:

=PMT(rate, nper, pv)

Where rate is the monthly rate (APR/12), nper is the number of months, and pv is the loan amount (as a negative number). For the example, =PMT(0.08/12, 60, -20000) returns $405.53 (slight rounding difference).

How to Build Your Own Student Loan Calculator in Excel

If you want to create a reusable spreadsheet, follow these steps:

  1. Open a new workbook and label cells for Loan Amount (B1), APR (B2), and Term in Years (B3).
  2. In B4, enter the monthly rate formula: =B2/1200.
  3. In B5, enter the number of payments: =B3*12.
  4. In B6, enter the payment formula: =PMT(B4, B5, -B1).
  5. In B7, enter total interest: =B6*B5 - B1.
  6. For an amortization schedule, create columns for Payment Number, Payment, Interest Paid, Principal Paid, and Remaining Balance. Use the IPMT and PPMT functions to break down each payment.

This approach lets you see how much of each payment goes to interest versus principal, and how the balance declines over time.

Common Mistakes and Misconceptions

A frequent error is using the annual rate directly in the PMT function without dividing by 12. That gives a wildly wrong result. Another is forgetting to convert the term to months. Always use the monthly rate and total months.

A common misconception is that a lower monthly payment always means less total interest. In reality, extending the term lowers the payment but increases total interest because you pay interest for a longer period. For example, a $20,000 loan at 8% for 10 years has a monthly payment of about $242.66, but total interest rises to $9,119.20—more than double the 5-year interest.

Another misunderstanding is that the APR equals the interest rate. APR includes certain fees and costs, so it can be slightly higher than the nominal rate. This calculator uses the APR as the annual rate, which is the standard for comparing loans, but your actual interest rate might be lower if fees are included in the APR.

Limitations of the Standard Formula

The fixed-rate amortization formula assumes level payments and no changes. Real student loans can have variable rates, income-driven repayment plans, or interest that capitalizes (is added to the principal) during deferment. Those situations require a more complex model. Also, if you make extra payments, the formula does not apply directly—you would need to recalculate each period.

For federal student loans, there are multiple repayment plans (standard, graduated, extended, income-driven) with different formulas. This calculator only models the standard fixed-payment plan. It does not include loan forgiveness, subsidies, or tax implications. For those details, you must check with your loan servicer or the official program documentation.

Frequently Asked Questions

How do I calculate student loan interest in Excel?

Use the PMT function for the monthly payment: =PMT(rate/12, term*12, -loan_amount). For total interest, multiply the payment by the number of months and subtract the principal. For a full schedule, use IPMT and PPMT functions for each period.

What is the difference between simple interest and compound interest on student loans?

Most student loans use daily simple interest during the billing period: interest = principal × rate × (days/365). That is different from the monthly compounding used in the amortization formula. However, when you make regular monthly payments, the amortization formula accurately reflects the total interest because the unpaid interest is added to the balance each month.

Can I use this calculator for variable-rate loans?

No. The calculator assumes a fixed rate for the entire term. For variable rates, you would need to model each rate change separately, which is beyond the scope of this simple tool. Use it only for fixed-rate loans.

Does the calculator include loan fees?

No. Origination fees and other charges are not included. If your loan has an origination fee, you can add it to the principal to see the effect on payments, but the APR might already reflect it. Check your loan documents for the exact APR.

What if I make extra payments?

The formula gives the payment for a fixed schedule. If you make extra payments, you will pay off the loan faster and pay less interest. To model that, you would need an amortization schedule that recalculates each month after the extra payment is applied.

The result from this calculator is an estimate for educational purposes. Your lender’s official disclosure statement provides the exact payment and total cost for your specific loan.

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

The whole Loan payments section →