Simple spreadsheets for complex questions

magic bar graphIsn’t it hard to build a spreadsheet to analyze financial issues? Don’t you need to be a math whiz? Don’t you need to write complicated formulas into all those cells? No, no, and no!

You can do a lot with just addition, subtraction, and multiplication, and Excel will do all the arithmetic for you. The formulas are actually quite simple, and Excel will write them for you. One good formula and you can fill an entire column, or even the whole spreadsheet.

As an example, some recent posts included spreadsheets for thinking about some complex financial questions including:

  • How much money do I need for retirement?
  • Am I saving enough for retirement (or any other goal)?
  • How much faster will I pay off my mortgage if I add $100 a month?

Even though these are complex questions, there are only a few formulas in each of these—the rest of the cells are just copies. The formulas use no more than addition, subtraction, and multiplication. Best of all, you just tell Excel what you want it to do and it writes the formulas for you.

Take a look at the spreadsheet for retirement spending.

formula

There are formulas in the Spending column to predict your spending each year as it rises with inflation. Take a look at one of them: =B8 + B8*Inflation. This is where people get scared off. Come back! Excel formulas are much easier to write than they are to read. You get Excel to write this just by thinking through what you want it to do. First type “=” to tell Excel to write a formula. Then think about how much you’ll spend that second year when you add in inflation, “I’ll spend the same $50,000 I spent last year, plus the inflation amount, which is 4% of the $50,000 I spent last year.” When you think “$50,000 I spent last year,” click on the $50,000 under spending. When you think “plus” type +. Follow this with the inflation amount, 4% (click on it in the inputs) of the (type * for multiplication, since you take percentages by multiplying) $50,000 I spent last year (click on it under Spending). When you enter it, you should get the answer. The same thinking will get you the funds growth, and then you just need to ask Excel to subtract the spending amount in the same row.

Here’s where the magic happens. AutoFill these two formulas down the column to complete the spreadsheet in seconds. That’s all there is to it. All of these spreadsheets are built this same way. Just be sure to name your inputs. To learn more about these Excel techniques, see my posts from September to November, 2012.

Do you have any financial issues you’d like considered? Let me know and I’ll feature them in future posts.

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