Geometric Returns - An Analyst's Perspective To Spreadsheet Modeling

Often those who work on investment performance software’s (like Sylvan, HiPortfolio, HP Calipers, First Rate Performance etc.) calculate the rate of returns with a click of a button. That’s because, everything is automatically done in such software applications. That’s the reason; many of the performance analysts fail to understand the human side of calculating those results. We are so used to automation that we hardly care about the actual methods which are generally taught mostly in course curriculums only. Those who haven’t gone through the curriculums may still don’t know the actual methods. And those who have gone may not always know the complete treatment or practical applications of their learning’s, unless they apply them everyday in real life scenarios.

Well, I have gone through the course curriculum too. However, during one of my job interviews, when the interviewer asked me to calculate the geometric return in a spreadsheet, I was blank. It’s not that I never knew the method, but I never did it on a spreadsheet. However, I had a sound understanding of spreadsheet, so I thought that I could do so because, I knew the actual method of calculating geometric returns (True Rate of Return). Totally unaware of the fact that, spreadsheets like MS Excel have a ‘single cell’ formula to calculate the geometric return from a series of input returns, I used the traditional method as taught to me in the curriculum. Although the formula does not come as a built in function in Excel, we have to create it using arrays. The method which I used was lengthy enough and consumed more cells in the spreadsheet. Obviously, one can not learn everything even from a curriculum, isn’t it? Consequently, the interviewer told me that since I catered to US markets, I might not be aware of such methods because, in US, mostly investment management companies use the arithmetic returns, that too calculated by software applications. The interviewer from London, generously gave me a benefit of doubt as I still knew how to calculate geometric returns using the traditional curriculum method. But what if, he would not have been generous enough to give me the benefit of doubt? That’s exactly the question which forced me to do some research and come up with the solution.

After communicating with numerous investment management industry experts, performance gurus, CFA charter holders and CIPM certificate holders, I finally got what I wanted. Only analysts using spreadsheet on a regular basis for such calculations can tell the exact treatment, as this calculation methodology is not even included in investment books and curriculums that I know of.

For calculating Geometric Return or True Return by chain-linking a series of input returns by a normal method, you all can refer to the below calculation. This method is often covered by many books and curriculums…

Note, that in column B, we have the input return series and we are using many cells in columns C, D , E, F and G to calculate the exact geometric return employing more work, calculations and exact procedures as mentioned in the investment books and curriculum.

Now let me introduce you to a better formula of calculating the same result in a single cell, using arrays in MS Excel.

• Just use the formula, =product(1+B5:B7)-1, in a cell where you have to calculate the geometric return
• Please do not hit enter to come out of the cell, instead press, Ctrl, Shift and Enter simultaneously.
• You will see, the same formula in braces {} which would look like {=product(1+B5:B7)-1} , which indicates that you have used arrays in your excel spreadsheet.
• Format the cell with Percentage (and required number of decimal points) and you would get the required answer.

Note, that the entire solution ramps down to only one cell.

You can use the above mentioned technique with as many portfolios as you want, with as many number of input return series.

Also, please bear the following points in your mind before calculating the geometric return in a spreadsheet like MS Excel.

• For calculating Geometric Returns, sub-period / input returns must not be calculated using Ending Market Values including Cash-flows. Cash-flows should always be deducted from Ending Market Values
• For actual Percentage Figures, always use Numerator divided by Denominator and format it to Percentage with 2 decimals. Don't multiply the fraction with 100 to convert it into percentage
• While using Geometric Return Array Functions, the input series of returns should always have % sign in the values.
• When array functions are applied to calculate Geometric Returns from a series of returns, format the result to Percentage with 2 or more decimals as required.
• Annualized returns must not be calculated where periods are shorter than a year. Extrapolating such returns might give incorrect results.
• The power term in the Annualized returns must always be in parenthesis.
• From Quarterly returns input series, Nth year Annualized return can only be calculated by having the power term as 1/N

The above mentioned technique can be successfully used to calculate Annualized (geometric) returns from a series of annual and quarterly returns as illustrated below.

The above figure illustrates, Annualized returns from various input annual returns whereas, the figure below, illustrates, Annualized returns from various input quarterly returns.

I am always open to feedback and suggestions regarding this article. The technique mentioned above is rarely presented elsewhere, but it does not mean that I have deduced it. Like mentioned above, I got this methodology after communicating with various industry experts to whom I am greatly thankful. However, errors (if any) must be attributable only and only to me.