This is part of an ongoing series of posts about managing finances and financial planning as they relate to student loans. We cover a series of topics to help bolster understanding of student loans, how they work, and how to make a plan to pay them off.
One of the most common questions for grad students before, during, and after grad school is, how long will it take to pay off my loans? News articles and memes poke fun at how it will take decades and you’ll still be paying them off. It can seem like it will take an eternity to even make a dent in those monumental balances. The future is also unpredictable, so you never know when you might get set back or leap forward in your ability to repay.
A great way to visualize a path to paying off student loans is to calculate what your monthly payment would have to be in order to pay off your loans within certain time periods. With a spreadsheet and payment formula, you can map your strategy to eliminating your student debt.
Prep: Gather the Numbers You Need
There are some key variables to know that will factor into this particular calculation:
- Your interest rate (in Excel, “rate”) — Excel will use a fixed interest rate, so if you’re on a variable rate loan, you’ll have to use an assumption for a fixed rate; learn the differences between fixed and variable interest rates here [link to Tommy’s fixed vs variable post].
- How many years/months you want to target for paying off your loans (in Excel, “nper”)
- How much you (will) owe (in Excel, “pv”)
The only Excel/spreadsheet function you will need is the payment (“PMT”) function, which will also require the three variables above as inputs to the formula.
Excel Pro Tip: Using the Payment Function
With the answers to the above questions, you are armed to begin to use the PMT formula to calculate a monthly payment for any given a) interest rate, b) payoff period, and c) debt amount.
There is one tricky element to this formula: the interest rate you use has to match up with the time period you use. Because student loan payments are made monthly, the interest rate AND payoff period have to be expressed in months.
So let’s say you owe or will owe $150,000 at a fixed 6.8% (annual) interest rate and aim to pay it off in 10 years. Your payments will be monthly, so both the interest rate and payoff period need to be expressed in terms of months, too. 10 years is 120 months, and to convert a simple annual interest rate to a simple interest rate, divide by 12:
Therefore, the Excel calculation for a) 6.8% annual interest, b) 10 years (120 months), c) $150,000 debt is:
Note that we used -$150,000 for the total debt (“pv”) and we did this because the amount total and payment result will have opposite signs, and we want to yield a positive number for the payment. This yields, $1,726.20, meaning that this debt will be paid off after 120 monthly payments of $1,726.20.
Playing with the Numbers
The beauty of Excel is the ability to create sensitivity tables that vary your result when you change different variables. How will my monthly payment change if I shoot to pay it off in 5 years instead of 10? What if I only end up owing $100,000 instead of $150,000? What if my interest rate is reduced to 4% instead of 6.8%?
Data tables can answer all of these questions. I’ve created a simple Excel tool that already has a table built in (sample below). You can download it here.
As useful as it is to know what you should target paying off each month to meet your own particular goal, this is only one part of the equation. The next step is taking a hard look at after-tax income, budgeting, and prioritizing where to put your cash.
