3 Fast Ways to Compare Student Loans Using Excel

While there are a lot of great loan calculators out, it can be hard to know where to look when you just want to understand how much you’re going to be paying for a student loan. If you’re starting to shop around for loans, you may want a general picture of how much you’re going to pay, and if you’re refinancing your existing debt, you may want a tool to compare your options based on how far you’ve already come with repayment. Either way, check out these simple Excel formulas to compare different student loan options in just minutes.

What do you need to get started? Just the basics – Microsoft Excel, for PC or Mac, and some key pieces of information:

1. How much you want to borrow (your loan principal)

2. What interest rate(s) you’re likely to get

3. What loan terms you’re looking at

Note that it’s definitely okay to have a couple of different interest rates, etc., since the formulas make it easy to test out different figures and combinations. It’s helpful to put each of these three items in separate Excel cells so you can pull them into your formulas. Finally, when you’re inputting your loan term, we advise writing it in “periods,” i.e., the number of periods over which you’ll make monthly payments. For a 10-year loan, this is simply 10 years multiplied by 12 months, or 120 periods. See the example below.

Now, onto the formulas themselves. You’re ready to…

1. Calculate your loan monthly payment with PMT. The payment formula (PMT) shows what your monthly loan payment will be for any student loan, given the three piece of information above. (Note that this does not account for any fees you may incur, such as an origination fee. If you know it, use the loan’s APR, not the interest rate, for a more accurate amount.)

Let’s take the example above and say you want to know your monthly payment for a 10-year loan for $100,000 at a rate of 6%. Type “=PMT(” into a new cell , and Excel will prompt you with the following: =PMT(rate,nper,pv,[fv],[type])

You only need to fill in the first three fields, and these match up to information you already have.

Rate: Interest rate. (You’ll want to use your interest rate divided by 12 so that it’s distributed over each year.)

NPER: Number of periods. In this example, 120 monthly payment periods.

PV: Present value of your loan, or simply the $100,000 principal here.

Your formula should read =PMT(B1/12,B2,B3). Hit “enter” and you’ll see that your monthly payments on this loan will be $1,110.21 for 10 years. Note that because this is a payment, Excel will display this number in parenthesis and red font. To avoid that, insert a “-” sign in front of the PMT formula, like below.

2. See how much you’ll pay in total interest over the life of your student loan with CUMIPMT. The cumulative interest formula (CUMIPMT) will show you how much you’ll pay in total on top of your student loan principal, i.e., the cost of the loan.

CUMIPMT relies on the same three data points and many of the same variables as last time. When you enter “=CUMIPMT(“ you’ll see the following: =CUMIPMT(rate,nper,pv,start_period,end_period,type)

So besides the rate, number of periods, and present value that you inserted for PMT, you’ll also need to fill in

Start_period: The first loan payment period you’re looking at. This value is “1” if you’re looking at interest over the whole life of your loan.

End_period: The last payment period you’re looking at. In this case, it’s “120” if you’re looking at total interest paid on your loan. (More on why you may alter these fields in the last section.)

Type: This tells the formula whether to start calculating interest paid from the beginning or end of your loan. For student loans, always use “0”. (“1”, the other “type” value, applies to other financial products like annuities, not student loans.)

Filling in these fields (and again, adding a “-” to remove accounting formatting), you can see that you’ll pay $33,224.60 for this 10-year $100,000 loan at 6%.

3. Understand how quickly you’re paying down your loan with CUMPRINC. The cumulative principal formula (CUMPRINC) lets you see how much of your loan principal you’ve paid at any given point in time. This formula’s variables mirror those of CUMIPMT, so by filling in those same variables, you’ll see that you’ll pay $100,000 in loan principal over the life of your loan.

“But of course I will!” you say. “That’s just telling my loan principal.” That’s true, so what if you wanted to see how much loan principal you would have paid off halfway through your loan term, aka by year 5 in this case? Set the “end_period” value to “60” and you’ll see that at the halfway point, you’ve paid off about $42,500 in principal. (And if you similarly tweak the CUMIMPT formula, you’ll see that you’ve paid about $24,000 in interest at the halfway point, for a total of $66,612.30 in five years.)

That means these formulas can help your forecast your payment outlook over any period of time. If, for instance, you’re considering reamortization, you can use these formulas to better understand how a prepayment a few months in could change your costs. For those considering refinancing to a Hybrid Loan, you can use these formulas to better assess your comfort with transitioning to a variable rate in order to secure a better upfront interest rate.

One Response to “3 Fast Ways to Compare Student Loans Using Excel”

1. Guadalupe Arnold says:

   October 3, 2016 at 2:44 am

   Love the post. Good review of the Excel functions for any sort of loan calculation. One correction – I believe converting the 6% annual rate down to the period rate should be the (12th-root of (1 + the annual rate))-1, not division by 12. This is because the interest is actually compounding throughout the year. In this example ((1+.06)^(1/12))-1. For this example, the difference is between 0.4868% and 0.5% monthly or 6% and 6.17% annually. That extra interest adds up!