Buzz
If you're beginning your search for student loans, it's useful to get a broad sense of what your total payments might look like. If you're refinancing, you may also need a tool to compare your loan options based on your current repayment progress. Either way, try these easy Excel formulas to weigh different student loan choices.
This article was first published on ReadyForZero.
What’s needed to begin? You’ll only need a few essentials – Microsoft Excel (for either PC or Mac) and some key details:
1. The amount you want to borrow (your loan principal)
2. The interest rate(s) you're expected to receive
3. The loan terms you're considering
Keep in mind that it's perfectly fine to have multiple interest rates, etc., as the formulas allow you to test various figures and combinations. It’s a good idea to enter these three details into separate Excel cells so you can easily reference them in your formulas. Also, when entering your loan term, we recommend expressing it in "periods," which is the total number of months over which you'll make payments. For a 10-year loan, this means multiplying 10 years by 12 months, resulting in 120 periods. See the example below.
Now, let’s move on to the formulas themselves. You're all set to…
Calculate your monthly loan payment using PMT
The payment formula (PMT) calculates your monthly loan payment for any student loan, using the three pieces of information mentioned earlier. (Keep in mind that this formula doesn't include any fees, such as an origination fee. For more accurate results, use the loan's APR rather than the interest rate, if available.)
Let’s take the previous example and say you need to find your monthly payment for a 10-year loan of $100,000 with a 6% interest rate.
Enter "=PMT(" in a new cell, and Excel will show you the following prompt: =PMT(rate,nper,pv,[fv],[type])
You only need to fill out the first three fields, which correspond to the data you already have.
Rate: The interest rate. (Make sure to divide your interest rate by 12 to distribute it across each month.)
NPER: The total number of periods. For this example, it’s 120 monthly payments.
PV: The present value of your loan, which in this case is the $100,000 principal.
Your formula should look like =PMT(B1/12,B2,B3). Press "enter" and you’ll see that your monthly payment for this loan will be $1,110.21 over 10 years. Note that because this is a payment, Excel will display the amount in parentheses with red text. To prevent this, simply add a "-" sign before the PMT formula, like this.
Calculate the Total Interest Over the Loan's Term Using CUMIPMT
The cumulative interest formula (CUMIPMT) will calculate how much you'll pay in total beyond your original loan principal—essentially, the cost of the loan.
CUMIPMT works with the same three data points and several similar variables as the PMT formula. When you type "=CUMIPMT(" you’ll get the following prompt: =CUMIPMT(rate,nper,pv,start_period,end_period,type)
In addition to the rate, number of periods, and present value you already entered for PMT, you'll also need to specify the following:
Start_period: The first period in your loan that you're considering. This value will be "1" if you're calculating the interest over the entire life of your loan.
End_period: The final period in your loan that you're evaluating. For the total interest on your loan, this would be "120". (We'll explain why you might change these values in the last section.)
Type: This indicates whether the formula should calculate interest from the beginning or end of your loan. For student loans, always input "0". (The "1" value is used for other financial products like annuities, not student loans.)
Once you’ve filled in these fields (and added a "-" to remove the accounting format), you’ll see that your total interest paid on this 10-year $100,000 loan at 6% will be $33,224.60.
Track Your Loan Repayment Progress with CUMPRINC
The cumulative principal formula (CUMPRINC) helps you track how much of your loan principal you've paid at any given time. The variables in this formula are similar to those in CUMIPMT, so using the same values will show you that you'll pay off the full $100,000 in loan principal by the end of the loan term.
"Of course, I will!" you say. "That’s just showing me the loan principal." That’s correct, but what if you want to know how much principal you’ll have paid off halfway through the loan term, say by year 5? Set the "end_period" value to "60" and you’ll find that, by the midpoint, you’ve paid off roughly $42,500 of the principal.
If you adjust the CUMIPMT formula in the same way, you’ll find that by the halfway point, you’ve paid approximately $24,000 in interest, bringing your total to $66,612.30 after five years.
These formulas can assist in predicting your payment outlook for any given period. For example, if you're contemplating reamortization, they allow you to understand how making a prepayment a few months in could impact your overall costs.
If you're looking into refinancing into a Hybrid Loan, these formulas can help you evaluate your comfort level with switching to a variable rate to secure a more favorable initial interest rate.
