Category Archives: Tools

Spreadsheet 101 – Debt Pay-down Projection

February 22, 2016 cb3 Leave a comment

Class in Session

I’ll be the first to admit that there are several aspects of life where I really let my nerd show. Personal finance has rapidly become one of those and I especially enjoy formulating and applying spreadsheets to the tasks of managing finances.

Here and there I will do a post about a particular spreadsheet that I’ve found useful and describe how I put it together and why. Also, for those that subscriber to the blog (I promise not to fill up your inbox!), you will get an email with links to template versions of the spreadsheets that I use.

The Original

The first spreadsheet that I did for my personal finance journey was one that I originally call ‘Financial Projection’. This was a little bit of a misleading title since it was a bit more focused on tracking outstanding debts and projecting the year-end balances on those debts.

I’ve made some enhancements over the past couple of years, but I still use this sheet today.

The Layout

The spreadsheet contains multiple sheets with one sheet representing each year.

Each sheet uses the columns to represent months in the year. There are fourteen columns for data. The first data column at the beginning of the sheet shows the previous year-end balances for each debt. The last columns shows the year-end balances for the current year.

At the top of the sheet there are some rows for meta-data. The top row shows the month and the row below it contains the number of days in that month. The only update made here is for February on leap years.

The next two rows show the total outstanding debt at the beginning of the month and the percentage of debt that has been paid down so far this year.

These are sums based on the next set of rows which contain the details for each outstanding debt.

The Debt Section

Each outstanding debt in the spreadsheet consists of five rows. I used the top row to show the projected balance for the beginning of the month (I have noticed that there are sometimes slight variances between my projected balance and the statement balance but I haven’t spent any time tracking down the difference).

The next row contains the statement balance for that month. This is a value that I manually enter when I go to pay the bill.

The third row is for the payment amount. Here is where I manually record the amount of money put toward the debt.

The fourth column contains the daily interest rate for the debt. Not all loans calculate interest daily, but this is used to give me a rough idea of how much money I’m losing by having the debt each month.

The daily interest rate is used to calculate the amount of interest accrued for the month. This can be a rather eye-opening figure for some items.

Finally, I subtract the payment amount from the statement balance and add back the accrued interest. This is the value that gets put in the next month’s projected balance box.

Putting it Together

Once I have all of my debts added to the sheet, I use the statement balance from each debt and add them all together to arrive at the total outstanding debt value at the top of the sheet.

The percentage of reduction sheet is calculated by dividing that month’s total debt by the beginning of the year total debt and then subtracting the result from 1.

Since the calculations are all cascading and update when the row values are changed, I am able to project my year-end total outstanding debt and year-end debt reduction percentage.

I’ve thought about adding some fancy graphs for a more visual look at the data, but I haven’t gotten motivated to do it yet. Besides, I also have the Personal Capital dashboard for visual reference.

It really helped me to see how much progress I was making and the amount of interest that each debt was costing me. Hopefully a tool like this will help you too. If you’d like a copy of the template, subscribe to the blog.

Cheers!

-cb3

Expenses, Personal Finance, Tools

Drawing a Picture – Part 2

November 5, 2015 cb3 Leave a comment

The next part of the picture is just as important, if not more so, as the balance sheet. It wasn’t until early this year that the concept really clicked with me. Sure, I had used a budget for years but this put things in a new perspective.

Cash Flow

The cash flow statement is the simple picture of the amount of money moving in to your finances (income) versus the amount moving out (expenses).

First we list our income for the month with one type or source per line and add them up. That gives us Gross Income.

Then we take all of our expenses and list them with one category or source per line and add them.

Finally we subtract the expenses total from gross income and we arrive at our Net Income for the month. If this number is negative, it means we spent more money than we made. Warning! If the number is positive, then we’ve made money for the month. If we have positive cash flow we should look at how we’re going to put the surplus to work.

Always have a plan for allocating surplus cash. Otherwise, it can disappear. For example, right now my plan is to put any surplus cash to work paying down outstanding debt.

A Note on Credit Cards

It can be especially easy to spend more than you make with credit cards. They’re easily available and credit limits can creep up beyond what you can handle.

Before I looked at my cash flow, I didn’t realize that I was falling farther into the whole because I was always able to make more than my minimum payment each month. What I wasn’t seeing was the fact that I put more on the card that month than I paid. Floating along like this for five or six years with regular credit limit raises led to quite a deep hole. At the worst of it, I think we had around $28k on cards. All generating interest!

Looking at the Categories

Now that I look at all of my expenses each month by category, it’s much easier for me to see what I can do to improve my situation. I can now see if we’re spending too much and where we need to cut back. Of course, some expenses are fixed. I actually break my expense list down into two sections: fixed expenses (mortgage, loans) and variable expenses (groceries, restaurants, fuel).

The Canvas

I like to use spreadsheets for most of these tasks. Google Sheets is awesome because it lets me access my data from anywhere without having to install software. There are also some handy Sheet Templates for budgets and cash flow. You could also track your cash flow with online tools such as Personal Capital or Mint.

Good luck and keep an eye on those expenses!

Cheers!

-cb3

Personal Finance, Strategy, Tools

Architecting the Future

October 22, 2015 cb3

The other day I was thinking about this blog and it occurred to me that it’s all well and good for me to be posting monthly progress updates and tracking how I’m doing, but what does that do for you? On the surface, it helps to show you that it’s possible to come from behind and start making progress. I want to show you how I set myself up to succeed. Let’s get started.

State the Problem

In most engineering problem solving exercises, we start by defining the problem. If we don’t have a clear idea of what it is that we’re trying to solve, then we’ll never have a way to measure if we’re successful. We don’t have to start with a lot of detail at this point. Let’s just establish a good high level statement.

For me, it would look something like this: “I am a 40 year old engineer with a good salary, a wife, and two kids. We are a single income family. We have a mortgage, car loan, student loan, and some credit card debt. We contribute to 401k, but other than that don’t save much. It often feels like we are living paycheck to paycheck.”

That’s a nice concise statement about the current situation. Now we are centered in our situation and can move on to the next step.

Draw a Picture

Getting a visual representation of the situation can be very helpful. In the case of personal finance, we’re not really talking about literal pictures but we do need to gather as much data about our situation and lay it out in an easily viewable manner.

There are tools that businesses use to view their financial situation and I believe we can adopt the same for ourselves. A budget is nice, but it shouldn’t be our only tool. Let’s take a look at building a balance sheet and a cash flow sheet. Google Sheets has some nice templates for these and I will cover each in more detail in following posts.

For now, we’ll just establish what each one is and what it can do for you. The balance sheet is a list of your assets and liabilities or quite simply the things you own that have value (assets) and the things you own that cost money (liabilities)

The cash flow sheet will show your income and expense for a period of time. I track it on a monthly basis. Where budgets establish how much you should spend on each category for the month, cash flow will show you how much you did spend. It’s important to know both.

There are also online tools available that will help you aggregate all of this data and give you visual representations. I currently like to use Personal Capital for this. You can set up your account there and link it to all of your relevant financial accounts.

List the Requirements

The requirements document lists all of the goals for the project. I like to think of it as a checklist that I can go down and when everything is marked off, I’m done. Sometimes the requirements change mid-project and we need to adjust. That’s OK. In project management terms, I feel like we should take a more agile approach to this anyway.

I started my list with the longest term goal at the top. It looked something like this:

In ten years, when I’m 50, I’d like to be financially independent.
In five years, I’d like to be able to step down a notch to less stressful work
For 2015, I want to accomplish the following:
- Reduce overall debt by 10%
- Invest 10% of my earnings
- Reduce the amount of credit card interest paid to $0

It’s a simple start. I like that. Sometimes if we make things too complex or start with too may requirements, the project will feel overwhelming and we’ll never get started. Start small and build over time.

Design a Solution

Here is the not-so-tricky tricky part. We have a problem. We have a picture of our current conditions. We have some requirements for the solution. How do we solve the problem?

I think there are a lot of approaches that could be employed here. There are really only two options to consider: Make more money and have less expense. Of course, there are a lot of ways to go about those two things but it really is just that simple.

There are a ton of other blogs with great ideas for both sides of the equation, so I won’t get into that here but maybe I will in some later posts.

It’s All So Much

This may be the longest blog post I’ve ever written. The long and the short of it is this: Understand your situation, determine what you want to achieve, then formulate a plan for getting there.

Cheers!

-cb3

Tools

Getting Organized

May 26, 2015 cb3

I wasn’t completely disorganized in my financial life, but I knew we could do better. This is an area where I’m always challenged.

Several years back, a friend had recommended Mint. Setup was easy and linking my accounts worked pretty well. This gave us a general view of things and I set up a budget and some goals for paying off debts. It was nice for a while, but I never really felt like it made an impact on our behavior. There wasn’t a clearly visible trend-line for me to track and the budget became more of a suggestion than a hard rule. Oops, we went over on groceries again!

Last Fall, I decided to try out Personal Capital. Again, setup was easy and most of the accounts worked*. This had exactly what I wanted. Net Worth and Cash Flow graphs are right there on the home screen. I now had a better graphical representation of how things are changing over time. This suits the way my brain works.

In my next post I want to talk about learning to understand cash flow and how i’m adding that to my view of our financial health.

Cheers,

-cb3

*There is an issue with Associated Credit Union 2-factor that makes me re-authenticate every time but PC is aware of the problem and stated that it will take a code update on their part to fix