We're paying off $124,000 in student loans using this handy spreadsheet

Travis Hornsby

Early in our relationship, my girlfriend let me know she had $124,000 in medical school loans.

I never had to deal with student loans myself, as I had an undergraduate scholarship and did not have to go to graduate school.

While helping her navigate through a myriad of options, I realized that choosing the right student loan repayment plan is confusing and complex.

There was some information out there to help with the process, such as student loan repayment calculators, but none accounted for details important to income-based repayment programs, such as family size, marital status, and income growth.

I steeped myself in the federal student loan program rules and studied the growing private loan refinancing market so we could make a plan. As an ex-bond trader, I decided I needed to create a spreadsheet in May 2016 to navigate the bureaucratic maze that is the federal student loan program.

Once I finished the tool, I realized the need is so great to understand the world of student loan repayments that I started doing consultations for people on the side. We decided that we want to pay off her loans as quickly and cheaply as possible.

Our goal was to find the lowest cost option among the various federal income-based repayment and private refinancing plans to repay her debt. All the numbers I will use in this article are real. The only thing I am anonymizing is her salary information. I will use $200,000 as an estimate for the starting salary of a new doctor at an academic teaching hospital.

I enter all the relevant information on the first tab, as seen below.

I only need to input information into the 'Student Loan Inputs' tab to see the results of the analysis. I enter all of my girlfriend's information relevant to calculating her costs under the different repayment plans available. I will zoom in on each section to explain why each part matters and what to enter.

First, I need to enter in her total loan balance.

For the purposes of this analysis, I only include what she owes under the federal student loan repayment program. After all, if she had loans with a private lender, the analysis would be much simpler.

She could just apply to other lenders to see if they would give her a better deal. Then I estimate her average interest rate across all her loans and enter that information in as well. Typically, anyone with grad school debt usually has an average interest rate of 6% to 7%.

Next, I enter the income for a starting doctor at an academic hospital and future salary growth expectations.

This information determines the size of her monthly payments under the income-based repayment plans.