You Are Your Own Financial Planner

financial planningDo you think a financial planner will give you the answers you need? Possibly, but only if you understand and contribute. Don Shaughnessy addressed this recently in his daily and thoughtful MoneyFYI blog, The Client Is the Planner and the Planner Has Responsibilities. Addressing financial planners, he writes,

The key point is a client must be better informed than they typically are.  The client is the planner.  They must not abdicate responsibility for the outcome.  They must be an informed participant.  Even the leader.

Besides, people make more mistakes when they don’t understand what they are doing and no mistake is cost free.

He advises individuals, “Once you understand a little about how things connect and work together, we can have a productive conversation aimed at creating an adjustable process that will tend to get you where you need to be.”

This is where future-focused spreadsheets come in. Visualize your possible future year-by-year. What happens if I spend less and save more? How does compound interest work? Watch the alternatives play out. Understand the trade-offs.

I had a financial planner in one of my classes and we built a spreadsheet from basic principles to look at saving and investing for a future goal. She said that her company had programs to do this type of calculation and produce an answer, but this was the first time she really understood it, seeing how each year influences the next.

Visualize your financial future. Watch it unfold. Then plan for it.

How much faster will I pay off my mortgage if I add $100 a month?

house moneyCan you believe that you can answer this question using nothing but simple arithmetic in Excel? Addition, subtraction, multiplication. That’s all. No functions, and Excel writes the formulas for you. Yes, I know, you think you have to do some complicated formulas that nobody knows. Seriously, here is all you need to know:

  • The interest payment is the interest rate multiplied by the amount owing.
  • Whatever is left over from your payment after the interest is paid goes to pay off the amount you owe.
  • The monthly interest rate is 1/12 the yearly interest rate.

Let’s say you just got a 30-year home loan for $300,000 at 4% with a payment of $1432 a month. Here’s the house payment spreadsheet to answer that question.house payment

We start with the assumptions at the top. Create names for them so they play nice. I made them blue to remind you that you can change them.

Now we’ll look at what happens month-by-month. This is much more enlightening than just getting an answer from some formula. Each month you’ll see how much you’ll owe, how much interest you’ll pay that month, and how much you’ll put toward paying down the loan that month. Once you get the first two rows, you can AutoFill the whole thing down for the full 360 months of the loan.

The amount owing is the full amount the first month. After that, you’ll owe whatever you owed the previous month minus the amount you paid down the previous month. Get Excel to write the formula for you by typing “=” and then clicking on the numbers you want to use with a “-“ in between. To get the amount of interest paid that month, multiply the amount owing that month by the monthly interest rate, which is the interest rate divided by 12. The paydown is just your monthly payment plus the additional payment minus the interest paid. That’s all there is to it.

You see that in March of 2041, the amount owing becomes red. That means it’s negative; you’ve paid it all off and then some, 3 ½ years early. It turns out that you save over $61,000 by doing this, at a cost (at $100 a month) of about $31,000, a net savings of $30,000. Try $200 a month or more and see what that does.

Bonus: Set the additional payment to zero and the spreadsheet turns into a nice payment table for your loan. See, it really does go to 0 in September 2044. In fact, if you want to figure out the monthly payment on a loan, you can use Goal Seek to find the monthly payment value that makes the last amount owing equal to 0. And you thought you needed some complicated formula to do that!