A new view on your data: Pivot Tables

looking around cornersExcel’s pivot tables reorganize, categorize, and summarize your data dynamically however you want. Look at it one way, then in seconds, look at it another way. It has a lot of features, but the basics alone are powerful. Today I’ll show you how to use it on a simple example.

Let’s take the example from last time, a spending log.spending

 

 

Pivot table dialog box

Click anywhere in the data. Then select Data > PivotTable or Insert > Table > PivotTable, depending on your version of Excel. You’ll get a Create PivotTable dialog box. Click OK.

 

Blank Pivot Table Builder

Now you’ll get a new sheet with a blank table, marked only Column area, Row area, and Values area. You’ll also get the PivotTable Builder. This is where you tell Excel what you want for rows and columns and what you want in the body of the table.

 

Start by deciding what you want for Values. This is the data you’re going to total by categories, so it only makes sense if it’s numerical, something you can sum. Here, the only field name like that is Amount. Of course, that’s exactly what you want to look at, your spending. Drag the Amount field name into the lower right Values box. You will now see a tiny pivot table showing your total spending. You’d really like to break it down by category, so drag the Category field name into the Column Labels box. Now you have your total spending by category. Break that down by month by dragging the Month field name into the Row Labels box. (You may have to scroll in the Field name to find Month.) Here’s what the PivotTable Builder looks like when it’s filled in.

Completed Pivot Table Builder

Completed Pivot Table Builder

 

It looks complicated, but this just took a few steps. Now you can slice and dice your data any way you want. Drag Category out of the window and you immediately get a new table, summarized only by month. Drag Paid by into the Column Labels box to get your spending by month and payment method.

This feature does more, but these simple steps are all you need to slice and dice on the fly. I love Excel for its flexibility. You don’t need to pre-define reports the way you do in a database program. Try it on your data.

Final pivot table

Final pivot table

Dealing with data: Sort and Filter

 

alternativesHave you ever been overwhelmed with data? How can you organize it? How can you make sense of it all? I’ve tried a lot of databases, and I keep coming back to Excel. The main reason is that databases are designed to give you fixed reports, and the whole thing is structured to give you those reports. Well, when I have a lot of data, I’ll look at it from multiple views, each of which will trigger other questions. I can’t predict what I’ll want to look for next month or the month after that. Excel allows you to look into the data dynamically. The two major techniques for doing this are Sort/Filter and Pivot Tables. Let’s explore Sort and Filter. I’ll show you how to use Pivot Tables next time.

However you plan to look into the data, the first thing you need to do is organize your data into rows and columns. The rows represent the individual items and the columns are the categories and amounts. For example, for tracking your spending, each row will represent a payment and the columns might be the date, amount, payee, and any categories you might be interested in.

spendingIt also works best if your rows represent things at the lowest, most detailed level. For example, if you are tracking the costs, diagnoses, and insurance payments of your doctor visits, each row will represent one visit to one doctor. If you visit the same doctor twice, that’s two different rows. If you see two different doctors on the same day, that’s two rows. Then these Excel tools will let you look at things from a higher level if you want to, say, look at a single doctor or specialty.

It’s easy to turn on Sort and Filter. Click anywhere in your table and then find Sort and Filter. It might be called simply “Filter” in the Data menu, or may be an icon that looks like a funnel. Try Home > Editing > Sort & Filter > Filter. It depends on your Excel version, and it may appear multiple places. When you select it, little arrows appear at the top of the columns. Notice that Excel figures out how big your table is, even if you don’t formally make it a table.

filterNow when you click on that little arrow, it opens a window that allows you to sort or filter the whole table based on the values in that column. For example, if you open the drop-down on Category, deselect “(Select All)” and then click on Clothing, You’ll see just your clothing purchases.

sort filter dialogClick “Clear Filter” to put it all back. You can sort your purchases by amount, simply by opening the drop-down on Amount and clicking Descending (to put your biggest purchases on top). You can even use multiple sorts and filters at once. For example, see all your January Visa purchases, or list your entertainment purchases in order of cost. There are much more complex filtering options that you can explore.

Next month I’ll show you how to do basic Pivot Tables. I’m now going to a monthly blog, the second Monday of the month. Thanks for reading.

Which investment is better?

compare investmentsI have $10,000 to invest over the next six years. I have a chance to buy equipment that will save me $2000 a year for five years and after that I could sell it for $3000. Looks like $3000 ahead! But before I jump into this, I should compare it with other investment opportunities. Maybe I have a chance to invest it at 6% for the six years, and I figure the risks are comparable for the two choices.

Here’s a spreadsheet to look at investments like this. Fill in the interest rate you’re using for comparison and the amount you’re investing. Then fill in how much you will get each of years 1 through 6. (If you have to put in more money any year, enter that as a negative.) The cumulative column shows how much you’ll have total (positive in black or negative in red) at the end of each year, taking into account what you’ll give up by not investing the money.

IRR1The Cumulative column shows how much I’ll be ahead (black) or behind (red) relative to investing the money at 6%. I’m in the hole until Year 6, but I end up $765.45 ahead. All else being equal, this is a better than the 6% investment.

But how much better is it? Try various rates. Try 7%. The bottom line is still positive, so this is better than a 7% investment. Try 8%. Negative. Not as good. Now I can use Goal Seek to find the rate that gives comparable results. I tell Goal Seek to make the bottom line exactly zero by changing the rate. Here’s what I get.

IRR2

The answer is that this investment is exactly as good as investing the money at 7.6%.

Here’s another way I could use this spreadsheet and Goal Seek. I’m looking for a 10% return on my investment since I think it’s risky. How much should I offer to pay for the equipment?

IRR3

If I could buy the equipment for $9,275, I could make 10% on the investment.

The value of 7.6% that we got is called the internal rate of return, often abbreviated as IRR. This is a useful measure of how good the investment is. Take, for example, a similar investment that pays more each year, but has no salvage value at the end, as represented in this spreadsheet.

IRR4

The internal rate of return here is 7.9%, a better investment.

Now let’s compare it to a completely different type of investment. Suppose someone borrows $10,000 and in six years (without any interest payments) pays you $15,000. That’s more than you’d get back from the other investments. But if we find the internal rate of return, it’s only 7.0%. Money sooner is more valuable than money later.

IRR5

If you’re interested in how you’d build a spreadsheet like this, it’s the same techniques used in others—(1) create names for the inputs, (2) click on cells to get Excel to write a formula for you using only the current and preceding line and named inputs, and (3) AutoFill.

Take a look at the key formula for the cumulative in Year 1.

IRR6

You’ll get this if you click on the cells of interest while thinking through what you’d compute. Start with the cumulative you had the year before (the $10,000 in the cell just above, C5) and add in a year of lost interest on it (Rate*C5). On top of that, you get back the Amount in/out, so add in the $2000 in the cell to the left (B6). Click on the cells to build this formula rather than worrying about cell numbers. Once again, Excel formulas are easier to write than to read. You may notice that this formula is exactly the same as the one we used to look at investment growth. It works just fine even if some of the numbers are negative. This formula uses only the current and previous rows and named inputs, so we can AutoFill it down to complete the spreadsheet.

By the way, Excel has a function for calculating the internal rate of return directly, IRR. In this spreadsheet, =IRR(B5:B11) gives you the same 7.6% we got before.

What if you could visualize your financial future for the coming year?

Hiker woman looking forward in the snowy mountainHappy new year! This is a time for looking back and for looking ahead. How did you do financially this past year? Was it what you expected? What do you expect in the coming year, and the years ahead? If you could magically produce a spreadsheet that could help you look ahead, what would you ask it? Here are some of the types of questions you might want answered:

  • How can I get out of debt?
  • How much do I need to save to reach my goal?
  • Can I pay off my house faster?
  • Should I refinance my house?
  • How much money do I need to retire?
  • How much can I spend each year in retirement?

You can probably think of others. The good news is that you can easily build spreadsheets to explore questions like these. Of course, there are no certainties in financial planning. That’s why you may want to play “what if” and maybe do some Monte Carlo analysis. Best wishes for a new year filled with fun and no unpleasant surprises.

Volatility is not your friend

Businessman and declining shareWhen you can visualize your financial future using one of the spreadsheets I’ve included recently, it’s fun to put in some really high rate of return and watch your future self get rich. Before the great recession a few years ago, there were a lot of people touting 18% returns, and a lot more people running the numbers and thinking they were going to be gazillionaires. We all know how that turned out. Yes, if you could guarantee 18% year-after-year, go for it. But, if fact, you may get less, a lot less, maybe more, or the whole thing could go bust. That’s volatility (on top of the risk of losing your original investment). If you want to gamble, go to Vegas. But when you’re talking about your financial future, you want some certainty.

Volatility, the uncertainty of returns, hits you even in some relatively safe investments. Take the example of the man retiring with $2,000,000 invested at 4%. A few posts ago I built a simple retirement spending spreadsheet that showed he could spend $80,000 a year and not run out for 27 years, when he’s 92.Retirement til 92

But now if you throw in some modest volatility and do a Monte Carlo analysis of it with this spreadsheet, the certainty goes away. With a standard deviation (a common measure of volatility) of only 2.5%, there are no guarantees. retirement basic mc graphThis money will last those 27 years only with a probability somewhere between 75 and 80%. He’d need to cut his spending down to $70,000 a year to get near-certainty that it would last.

Try higher volatility (standard deviation) numbers to see how much harder it is to get near-certainty in your financial future.

Investment returns are uncertain. How much savings do I really need to retire?

money growthA couple posts back, I looked at how much you could spend each year if you retired with 2 million dollars invested at 4%. (Answer: About $80,000, increasing each year for inflation). But how do you know you can get 4% year-after-year for 30 or 40 years?

You don’t. Any investment has good years and not-so-good years, and in general, the greater the return, the higher the uncertainty. One way of measuring this volatility is the standard deviation. For example, a bond fund paying 4% per year had a standard deviation of 2.5%. That means about 68% of the time your return will be with 2.5 percentage points of the average 4%, or between 1.5% and 6.5%. Here’s a spreadsheet, retirement with volatility, that gives you a sample of what might happen to your investment over the years.

retirement variabilityWhat you see when you open the spreadsheet won’t be the same as this. The reason is that it’s using random draws to represent how well the fund will do that year. We can’t predict it, but we know what might happen, so for each year it randomly picks something within the realm of possibility. The Random number is selected between 0 and 100 percent, and the Growth is calculated based on it. Every time you change something in the spreadsheet you got a completely new list of random numbers and a new outcome. Try entering something, anything, in some unused cell to get another view of how it might turn out. There’s even a tiny chance that the money won’t last the full 25 years:

retirement bad variability

The Excel secret to making all this work, if you’re interested, is the RAND function. This also uses NORMINV to calculate the growth rates. This goes beyond the basic techniques used to build most of the spreadsheets here, but you can investigate these functions yourself.

If you’ve tried a few cases, you’re probably satisfied that this investment and withdrawal plan is sound. But what if the investment were more volatile? Try 8% or 10% standard deviation. Are you comfortable with that?

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.