Farm Fresh Spreadsheets

So someone brought it to my attention that while I discussed the virtues of getting organized with spreadsheets in a much earlier post, I did not actually show any useful spreadsheets.  Duh.

I offer the following disclaimers:

  1. I was feeling much more shy at the beginning of this blog effort, and also did not realize you could upload non-photo files.  So disclosing any sort of personal tools or information did not initially cross my mind.
  2. Some of these information-gathering efforts commenced at a much earlier time when I was totally oblivious about my loans and the associated finance implications.  That is why I gathered information, to get my head out of the sand.  So this might look rudimentary, but I suppose people have to start somewhere.  I certainly did.
  3. I have to give credit to Student Loan RAGE for putting together (and candidly sharing) totally ace and comprehensive loan and expense spreadsheets which look at personal finance at a higher level than I tend to.  RAGE is a self-proclaimed excel nut while I am not.  So you may laugh at my spreadsheets and skip straight to those.  I was trying to answer more fundamental questions at one point in time like “So how many loans do I have?  And what day of the month are they due?” Etc.

With the disclaimers out of the way, I will get down to business.  I am providing some stuff that is partially scrubbed, mostly outdated, and somewhat just filled with dummy data.  A girl’s got to leave something to the imagination!

I do not know where you are in your journey, but when I started right out of college, I literally just waited for loan bills to arrive in the mail.  I set up auto-payments for the loans and forgot about them while fretting about the rest of my poverty.  Then I “cleverly” deferred all of these during graduate school, and went back to waiting for new colors of loan bills to arrive in the mail after grad school.  But since I was a little older and had to apply for these myself with a FAFSA I had filled out myself, I had a better idea of what was coming.

If you have no freaking clue what is going on with your loans, I recommend going to the National Student Loan Data Center which is the Department of Education’s big loan database.  But it does not include private loans.  You will need to grab those loan details separately.

Then, and I only did this step after grad school for some reason, it is nice to make a spreadsheet that shows you:

  1. How many loans you have
  2. Source (private or federal)
  3. Original amount
  4. Current balance
  5. Interest rate
  6. Monthly payment
  7. Monthly payment due date

This just gets you oriented.  So here is Spreadsheet #1, which includes 3 tabs of different loan/expense topics.

student loans and expenses for blog

The first tab shows the first time I collected information about my loans in one place years ago.  I just took all of the little data points in the loan statements and pivoted them into a spreadsheet for all the loans.  I didn’t particularly know the difference between capitalized interest and accrued interest, so this quickly showed me.  Capitalized interest is what I did to myself by deferring my loans during graduate school.  The accrued interest is usually in your loan statement, and shows you how much bogus money you are paying out each month just in interest, in exchange for stretching out the payments.  The interest rates in column G helped me understand how to prioritize.  And the payment amounts and dates were helpful at the time.

This was back before Mint.com had integrated loan capability, so seeing loan details sent me to multiple websites, and understanding when payments were going out was a matter of scanning through my online bank statement.  But consolidating to one civilized place is motivating, so I have downloaded the data from the loan websites to this tab every month since then.  That was also before I learned about amortization schedules, which is the topic of the 2nd spreadsheet in this post.

The 2nd tab of this current spreadsheet is where I note all accelerated payments or savings goals that I make.  I left an old credit card payment from 2010.  That was during a rough time (essentially rock bottom for me) when I had costly dental bills and not enough savings to cover.  I got a 2nd job two weeks after starting that tab, to help make all of those accelerated payments.  And I switched to a tight cash allowance (based on the next tab) until I was out of the woods.  Trust me when I say those austerity measures work.  Plotting your progress also works.

The 3rd tab in that spreadsheet is a “monthly costs” accounting in my funky style.  I don’t put much effort into spreadsheet decor.  I just needed to know what the heck all my costs are, and when they happen in the month so I could plan all my accelerated payments better.  And I did not feel like stopping to categorize the uncategorized costs in Mint to plot it out there.  Anything that is “$0” is something I either cancelled or paid off as a result of making and reviewing this chart.

These are the tools with which I went from being in the hole with holes in my mouth to having a positive net worth.  Not the fanciest, but it gave me the information I needed to orient myself, and the history I needed to motivate.

Spreadsheet 2: Kickass amortization table

Amortization Table for blog upload

So this is a gem I discovered much more recently.  It had been bothering me that every month I had to grab the exact new balance of a loan when calculating how much I was paying off that month.  That is because loans accrue interest on an amortization schedule.  I always thought that was magical math nonsense that only accountants could decipher.  But it is just a series of formulas that even I could set up with the help of Microsoft online help!  I don’t see why MS did not distribute this as a template, but who knows.  It is reproduced elsewhere on the internet as well.

You just plug in your original loan amount into B1, your interest rate in decimal format in B2, the period (usually somewhere in the loan statement or terms of agreement) in B3, and then the monthly payment auto-calculates in B4.  You will need to adjust the number of rows.  This example is a loan for 179 months, so it won’t look right for you unless you copy or delete the right number of rows.

This tool shows how much interest accrues each month, and how that amount decreases throughout the life of the loan.  You can add extra payments (make sure to use negative value) in column E to see how that would affect the bottom line.  Literally: add an extra payment and scroll down to the bottom to see how many payments get taken off the end.  And you can delete some of the regular payments from column D to see the effect that deferring your loan would have.

Finally, you can add up all the cells from B6 down to wherever to see how much interest you will pay over time if you let the whole period run out.  This is the data the loan companies will never share with you.  Because it is too disgusting to put up with once you face it.

This is a truly powerful tool that has made the difference of night and day in my understanding of loan paydown.  I still update tab 1 of my original spreadsheet for fun on the 1st of every month, but everything I need to know at this point is in the amortization table.  If you make a tab for each of your loans, you get to play the banker and review what I previously thought only the loan companies could see.  If you had any doubts about paying off the highest interest rate loan first, this schedule will allow you to play out whatever accelerated loan payoff fantasy you want.  All graduating college kids should be given a copy of this and quizzed on it as part of exit counseling.

So there you have it: my spreadsheets along with another blogger’s slicker spreadsheets for comparison.  What is important if you feel disorganized is just to collect the basic information you need to get organized asap and get stronger about taking control of your financial situation.

Advertisements

2 thoughts on “Farm Fresh Spreadsheets

    • Hey thanks for the comments Amanda! Yes, amortization tables are totally ace. Only way to really know what’s going on with your loans.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s