## Friday, December 3, 2010

### A cool tool!

We held the Fall meeting of the Performance Measurement Forum this week in Dallas, Texas; as usual, there was great discussion and much to learn. The highlight may have been a tour of the new Dallas Cowboys stadium.

One of the things I learned was a technique in Excel, which I suspect most people aren't aware of. Let's say you want to calculate the quarterly return in Excel based on monthly returns of 1%, 2%, and 3 percent. If you're like me, you would add one to each of these numbers, multiply them together, and then subtract one; something like:

While this clearly works, it requires you to add an additional column, which at times might not be convenient. Here's where the neat trick comes in:
• key in "=product("
• select the cells you want to multiply together (in our case, the ones with the three monthly returns)
• key in "+1)-1"
• and then, instead hitting "enter," hold down the "shift" and "ctrl" keys and then hit "enter."
Try it...it works! Pretty cool, I think. This process creates an "array," which I had never heard of before. There are apparently other neat tricks you can do with this, too.

I credit my friend Neil Riddles for educating me on this.

1. Where were you 20 years ago with this trick when I needed you? If I had a dollar for every column of (1 + Return) that I created over the years...

This is one more reason to enter return data in decimal format rather than whole number format. Unfortunately, so many of us use whole number format for its "clarity" but I'm becoming convinced that switching over to percentage format is better. You've given me one more good reason. Thanks.

2. Thanks for your comments. I agree about preferring to see returns as decimals, but with this tool, you can take whole numbers and quickly divide them! Try this: =PRODUCT(range/100+1)-1
followed by Shift/Ctrl and Enter!

3. Your second idea is even better! I can get rid of all those (now) extraneous rows of (1 + return/100)!! Thanks!!! I might even have to reveal my secret identity to express my gratitude.

I've used that curly bracket function before, but only for more involved things, like matrix multiplication for calculating portfolio risk using weights and covariances. Who would think this idea is even more useful for everyday stuff?

Perhaps you could put together a class in Excel aimed at showing such useful tips and tricks for performance people. Huge potential productivity gains.