Use These Simple Excel Formulas to Compare Student Loans
If you are starting out buying student loans, you may want a big picture of how much you are going to pay. If you are refinancing existing debt, you may need a tool to compare your options based on how far you have come with repayment. Anyway, check out these simple Excel formulas to compare different student loan options.
This post was originally published on ReadyForZero .
What do you need to get started? Just the basics – Microsoft Excel for PC or Mac and some important information:
1. How much do you want to borrow (principal amount of the loan)
2. What interest rates are you likely to receive
3. What loan conditions are you considering
Note that it’s definitely okay to have several different interest rates, etc., since formulas make it easy to test different numbers and combinations. It’s helpful to put each of these three elements in separate cells in Excel so you can use them in your formulas. Finally, when you enter the loan term, we advise you to record it in “periods”, that is, in the number of periods during which you will make your monthly payments. For a 10-year loan, that’s just 10 years multiplied by 12 months, or 120 periods. See example below.
Now about the formulas themselves. You are ready…
Calculate your monthly loan payment with PMT
The Payment Formula (PMT) shows you what your monthly payment will be on any student loan given the three details above. (Note that this does not take into account any commissions that you may incur, for example, Commission for the issuance of credit. If you know it, use the annual interest rate on the loan, not the interest rate, to get a more exact amount.)
Let’s take the above example and suppose you want to know your monthly payment on a 10-year $ 100,000 loan at a rate of 6%.
Enter ” = PMT ( ” in a new cell and Excel will prompt you for the following: = PMT (rate, nper, pv, [fv], [type])
You only need to fill in the first three fields and they match the information you already have.
- Rate : interest rate. (You will want to use your interest rate divided by 12 so that it spreads over the years.)
- NPER : number of periods. This example has 120 monthly billing periods.
- PV : The present value of your loan, or just the amount of $ 100,000.
Your formula should look like this: = PMT (B1 / 12, B2, B3). Press Enter and you will see that your monthly payments on this loan will be $ 1,110.21 for 10 years. Please note that since this is a payment, Excel will display this number in parentheses and in red font. To avoid this, insert a “-” sign in front of the PMT formula as shown below.
Calculate the total interest rate over the term of the loan with CUMIPMT
The Cumulative Interest Formula (CUMIPMT) will tell you how much you will pay in total in excess of the student loan principal, which is 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 will see the following: = CUMIPMT (rate, qty, pv, start_period, end_period, type)
So, in addition to the rate, number of periods, and present value that you specified for the PMT, you also need to fill out:
- Start_period : the first considered period of the loan repayment. This value is “1” if you are looking at interest over the entire life of the loan.
- End_period : The last billing period you are considering. In this case, it is “120” if you are looking at the total interest paid on your loan. (For more information on why you can change these fields, see the last section.)
- Type : This is the formula for whether to start calculating interest paid at the beginning or at the end of your loan. Always use “0” for student loans. (“1”, another “typical” meaning, applies to other financial products such as annuities rather than student loans.)
By completing these fields (and adding “-” again to remove the accounting formatting), you will see that you will pay $ 33,224.60 for a 10-year loan of $ 100,000 at 6% interest.
See how fast you repay your loan with CUMPRINC
The CUMPRINC formula allows you to see how much of the loan principal you have repaid at any given time. The variables in this formula reflect the CUMIPMT variables, so if you enter the same variables, you will see that you will pay $ 100,000 in principal over the life of the loan.
“But of course I will!” you say. “It just speaks to my loan principal.” This is true, but what if you want to see what the principal amount of the loan you would repay in the middle of the loan term, in this case year 5? Set the “end_period” value to “60” and you will see that halfway through you paid out about $ 42,500 in principal.
And if you tweak the CUMIMPT formula in a similar way, you will see that you paid about $ 24,000 in interest halfway through, for a total of $ 66,612.30 over five years.
This means that these formulas can help you predict the payment forecast for any period of time. If, for example, you are considering re-amortization , you can use these formulas to better understand how a prepayment after a few months might affect your expenses.
Those considering refinancing a hybrid loan can use these formulas to better gauge their comfort in switching to a variable interest rate in order to provide a better initial interest rate.