HomeDebtCalculating Interest On Loans

Calculating Interest On Loans

My father was gracious enough to use his Home Equity Line of Credit to pay my 6.8% student loan, and instead let me pay him for his variable HELOC loan, which currently sits at 2.4%. We decided that I would pay $100 each month toward the HELOC, and if interest rates rise, it would be beneficial for me to make higher monthly payments.

What we didn’t do well, however, is plan out the specifics of how to handle interest rate changes. We figured out how much it would be at the current rates, but didn’t have a good formula for how to make changes. I was hoping to find an easy student loan calculator online to manage our payments and balance information, but couldn’t find anything useful. So I turned to excel. Well, to a spreadsheet in Google Docs.

Using the spreadsheet, I build a powerhouse of a loan schedule, showing how much I will owe each month at the given interest rate. When the interest rate changes, we’ll have to input it into the “Loan Information” tab, and the rest of the table will update to reflect any changes.

This exercise taught me a lot. First, it taught me a ton about excel. I love the functions, but I am now more familiar with dates and using multiple conditions. It also taught me just how much my loan is going to cost me. The interest rate is extremely low right now, and if I could lock it in, I most certainly would, but I also know that when I am ready, I will be able to make larger payments and instead of 9+ years, I may be ready to pay it off in 6 or 7. Of course it depends on the rates and whether I would be better off investing some of that money instead of paying off loans, but at a certain point, I will probably be ready to make larger monthly payments.

I think taking a step back and looking at how long you’ll be paying off your loans can put things in perspective. Credit card debt obviously has the highest cost, but when you realize that you’ll be paying for your car for the next 7 or 8 years, maybe you’ll think twice about whether it’s worth it or whether you’d rather have some of that money (plus all the interest you’ll be giving away) for other things over those years.

Here is a link so you can see the formulas behind the numbers: Student Loan Schedule.

Plese feel free to take a look, double click on the cells to view the formuals behind them. I’d be happy to explain how I got some of the numbers and how I did a few of the crazy formulas.



  1. Nice Dad, Daniel! And good work learning excel. It comes in handy if you’re gonna get serious about budgeting and planning.

    Try and type out your pro forma retirement plan for the future!

    • Yah, he saved me…about $3,300 over the course of almost 3 years!

      I’ve thought about that, but realized that at age 22, it might be hard to predict…anything. Salary, taxes, family, etc. I fiddled with FireCalc, which got me very excited about the possibility of retiring early if I save diligently for 25+ years. Maybe in 5 years I’ll have a little bit of a better sense of where I’m going, and at that point I’ll set aside a weekend to make another spreadsheet for that..

Comments are closed.

Most Popular

Recent Comments