To prevent uneasiness, I should disclose that the attached sample spreadsheet (link provided at the bottom of the post) contains fictional data as an example/starting point for you to understand how the spreadsheet works so you can more easily use it yourself. These are not my (nor anyone elses) loans or account numbers. Do not worry :)!
Everybody has debt. And, I assume, we all want to pay it off. But how quickly should we pay it off? Did you know that on a $100,000 home mortgage at 12.0% interest, increasing your monthly payment by only $100 (from $1,100 to $1,200) will save you nearly $50,000 of interest paid over the course of the loan? Now do I have your attention?
Here’s another scenario. You’ve cancelled your credit card with a remaining balance of $3,499 (you know, from the new Mac Pro you bought). Your credit card has an interest rate of 15% and requires a minimum monthly payment of $40. In some moment of madness, you decide that making the minimum monthly payments will be a good idea. If you die in sixty years, you will not have come close to paying off that small balance. In fact, when you die, the new balance on that card will be $2,441,399.88, and you will have paid over $2.4 million in interest. Of course, the credit card company would never divulge this information to you.
In both of these situations, you only have one loan. What if you’re a recent college graduate (like my wife and I) and you have several smaller loans and would like to find the fastest, most cost-effective way to pay them off? For that, I present you with an Excel spreadsheet I created. You may even be surprised to find that you will spend less on interest just by paying off your loans in a different order.
I’ve tried to make the spreadsheet as simple as possible, with buttons and drop-downs to help you add/edit/sort your own loans. Once your loans have been added and the details specified, the spreadsheet will automatically populate a payment plan for you. It will tell you the projected date when all of your loans will be paid off, how much you will have paid in interest, and how large the loans will have gotten with accrued interest. For fun, sort your loans by different criteria (there are drop-downs for sorting at the top of the spreadsheet) and change your monthly payment. You’ll be surprised how much things like that will ultimately affect your payment plan, and it’ll be good knowledge for you to better understand your own loans.
If you don’t have any loans currently but are about to start college (or are considering getting five separate credit cards), I still strongly recommend opening this spreadsheet and putting in bogus loan values for your speculative debt. The misinformation or simply lack of understanding among my peers when it came to financial responsibility (and future debt) was immense in college. Sure, college loans generally have a better interest rate than credit card or auto loans. And, sure, you could pay your $80,000 loans off over the next thirty years … but do you know that, when all is said and done, on that $80,000 loan, including interest, you may end up paying something upwards of $150-$200,000?
I could continue with example after example of loan payments and the penalties incurred if you don’t pay attention to your interest rates and monthly payments, but my main point is that you need to stay on top of your loans, and the best way to do that is to make a plan for them. That’s where I hope my spreadsheet can help you; it has helped me come up with a simple way to quickly pay off Jess’ and my school loans in only a couple of years. Not only that, but after putting my own loans into this spreadsheet and sorting them in different ways, I found that paying them off in a different order than I originally perceived to be the best would save me about $350. That may seem like nominal savings to you now, but my wife and I (comparatively) don’t have that much school debt−so imagine how much would be saved if we did, or in the future when we have a home mortgage and maybe a car payment or two. When that time comes, knowing this information will save us thousands or tens-of-thousands (see my first example).
Let me know if you have any issues with the spreadsheet or if you have any ideas that might make it better−I’m always open to ideas! And happy savings :).
For your convenience, especially if you are a soon-to-be or current college student that may be unfamiliar with much of loan lingo, I’ve provided some guidance that will help you better understand loans and the spreadsheet.
- Status: A word or two describing the state of the loan (ex. “Open”, “Enrolled”, “In School”, “Closed”).
- Loan Name: A unique name to help you identify the loan. This is a required field, and it must be unique from all other loan names.
- Account Number: The account number given by the lender for this loan.
- Lender: Every loan has a lending institution that granted you the loan. For your reference, put the name of that institution here (ex. “The Department of Education”, “Capitol One”, “Collins Community Credit Union”).
- Type: The type of loan this is, either generically (ex. “School Loan”, “Credit Card”) or specifically (“Stafford”, “Direct”, “Perkins”).
- Interest Subsidy: Subsidization is a type of assistance you may get from the lender or another financial institution (or the government) to help you with the loan or its interest. For example, if your loan payments or interest are deferred for six months, your loan is “subsidized”. If your loan does not have any sort of assistance with it, it is “unsubsidized”. An unsubsidized loan begins accruing immediately. This is a required field.
- Interest Rate: The percentage rate at which your loan gains interest annually. This is a required field.
- Minimum Payment: Most loans require a minimum monthly payment. The payment plan will deduct minimum payments each month so your payment plan is accurately generated. This is a required field—just input $0 if your loan does not require a minimum payment.
- Due Date: The date on which payments are expected to begin for this loan. In the same of a subsidized loan, this should be the date your interest will start accruing. This field is required.
- Initial Balance: The initial amount that the loan was worth. This field is required.
- Current Balance: The current amount that the loan is worth. This will be the same as the initial balance if you haven’t already started paying toward this loan. This field is required.
For an example of how the spreadsheet will look when it’s filled in with loan values, download the sample spreadsheet above.
Download Ready-to-Use Loan Payment Plan Spreadsheet
For a ready-to-use spreadsheet that you can more easily enter your own loan information into, download the template spreadsheet above.