Website's listing student loan calculator excel formula

Blog

Want to Calculate Loan Payments Offline?

We have offered a downloadable Windows application for calculating mortgages for many years, but we have recently had a number of people request an Excel spreadsheet which shows loan amortization tables.

Our Simple Excel loan calculator spreadsheet offers the following features:

  • works offline
  • easily savable
  • allows extra payments to be added monthly
  • shows total interest paid & a month-by-month amortization schedule

Video

Amortization of the Loan

The prior formulas allow us to create our schedule period by period, to know how much we will pay monthly in principal and interest, and to know how much is left to pay.

Run Your Calculations

Next, you’ll need a row for each payment as part of your data table. In the far left column of your spreadsheet (below the “Period number” column described above), add one number on each row: The first row is “1,” then move down a row for “2,” and so on. Each row is one payment. For a 30-year loan, you’d have 360 monthly payments—for large numbers like that it’s easiest to fill in the first few periods and use Excel’s “fill handle” to fill in all of the remaining rows.

Now, have Excel fill in and calculate values for you. Remember to use the "$" before any row number in a formula in your calculations except the Period—otherwise, Excel will look in the wrong row.

  1. Use the PMT function to calculate your monthly payment (using information from your “input area”). This payment generally does not change over the life of the loan, so this function would be the same all the way down. (The Excel function is: “=PMT(‘Loan amount,’ ‘Interest Rate,’ ‘Periods’)”)
  2. Use the IPMT function to show the amount of each payment that goes to interest. (Same formula as above, just with IPMT at the beginning)
  3. Subtract the interest amount from the total payment to calculate how much the principal you paid in that month.
  4. Subtract the principal you paid from your loan balance to arrive at your new loan balance.
  5. Repeat for each period (or month).

Note that after the first row of your data table, you’ll refer to the previous row to get your loan balance.

If your loan uses monthly payments, make sure you set up each period correctly in the formulas. For example, a 30-year loan has 360 total periods (or monthly payments). Likewise, if you’re paying an annual rate of 6% (0.06), you should make the periodic interest rate of 0.5% (or 6% divided by 12 months).

If you don't want to do all the work of working in spreadsheets, there's an easier way. Use an online Loan Amortization Calculator. These are also helpful for double-checking your spreadsheet’s output.

Calculate the Monthly Payment

First, here’s how to calculate the monthly payment for a mortgage. Using the annual interest rate, the principal, and the duration, we can determine the amount to be repaid monthly.

The formula, as shown in the screenshot above, is written as follows:

=-PMT(rate;length;present_value;[future_value];[type])

The minus sign in front of PMT is necessary as the formula returns a negative number. The first three arguments are the rate of the loan, the length of the loan (number of periods), and the principal borrowed. The last two arguments are optional, the residual value defaults to zero; payable in advance (for one) or at the end (for zero) is also optional.

The Excel formula used to calculate the monthly payment of the loan is:

= PMT((1+B2)^(1/12)-1;B4*12;B3)=PMT((1+3,10%)^(1/12)-1;10*12;120000)

Explanation: For the rate, we use the monthly rate (period of rate), then we calculate the number of periods (120 for 10 years multiplied by 12 months) and, finally, we indicate the principal borrowed. Our monthly payment will be $1,161.88 over 10 years.

Find Out What You Qualify For

Check your refinance options with a trusted local lender.

Answer a few questions below and connect with a lender who can help you refinance and save today!

Tags