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:
- 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,"
the "shift" and "ctrl" keys and then hit "enter."
I credit my friend Neil Riddles for educating me on this.
.png)
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...
ReplyDeleteThis 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.
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
ReplyDeletefollowed by Shift/Ctrl and Enter!
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.
ReplyDeleteI'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.
yes, please id yourself!
ReplyDeleteGood idea about a class...maybe we'll do a webinar on this. We are awaiting additional aids which might also prove helpful.
May I suggest that for content for your webinar you poll your blog's readers and those you know in the industry to find out which techniques they currently find most valuable. Compiling this "best stuff" will provide great value. I think you will help to put performance staff into the "sweet spot" of decreased effort/increased effectiveness by helping to remove the redundancies and manual efforts that are resource intensive and error prone. Looking forward to this.
ReplyDeleteAre you kidding me? That is one of the most common techniques in Excel and although useful for a quick calculation is still far less powerful than other functions in Excel that can be used to link returns without the explicit use of array syntax.
ReplyDeleteAnonymous, thanks for your post.
ReplyDeleteI don't know how "common" it is, as I have found that a small percentage of firms know about it, but am also aware that some do. If you are aware of others, please share! If you'd like, submit it as an article for The Journal of Performance Measurement, as a "practitioner's helper" or "idea" area, as I am sure many would find such tools of value. Thanks!