How to find Arithmetic Mean Return, Geometric Mean Return, Standard Deviation and Beta of a Portfolio/Market/Company in Microsoft Excel

Are you interested in investing some Portfolio/Shares/Company/Market but afraid to take a decision and get started or feeling wishy-washy when it comes to jumping off the top? Well, no more!.

Here's your easy guide to how to calculate the return before investing in a particular market or a company in Excel Spreadsheets or any other.

The First thing to calculate is Arithmetic Mean (A.M). Arithmetic mean, we all know the definitions so I don't want to bore you with that, but to give you a slight idea is that it's average of the return over a period of time. Take the sum of all returns over the period for which you intend to calculate and divide by the number of years/months in that period.

In order to illustrate the Excel commands we will use Table 1 to discuss and calculate.

Here's how you calculate Arithmetic mean returns with the ‘AVERAGE’ function. To calculate the arithmetic mean return of asset A, you type

=AVERAGE(B2:B11) Hit 'Enter' and you should obtain 5.2%.

You should know that AM does not give you the full picture of the portfolio. As in some periods the return might be negative while in others positive. AM in figure, will look like a handsome return but don't get too flattered. Go for Geometric mean.

Geometric mean return, compounds over the time period. Just like Compound interest.

Geometric mean in turn can be calculated with 'GEOMEAN' Function.

Here's how it would look like if we have to calculate GM for Asset A in the Table 1.

=GEOMEAN(1+B2:B11)-1 Hit Ctrl+Shift+Enter, you should obtain 4.9%.

If you want to go more deep and look for the dispersion and fluctuation in the data to take a smart decision you will have to calculate Standard Deviation.

Standard Deviation of returns for Asset A in Table 1 can be calculated with "STDEVP" Function.

=STDEVP(B2:B11) Hit enter and you should obtain 8.0%.

Standard deviation can also be calculated with the "STDEV" function but the finance that we use sometimes contain symbols and diverse data, so it's better to avoid this command.

The Beta of an asset can be calculated with the "LINEST" function. Because Beta measures Volatility relative to the market, assume that asset B in Table 1 represents the returns of the market. In that case, to calculate the Beta of Asset A, you type

=LINEST(B2:B11, C2:C11)

Hit 'Enter' and you should obtain 3.5%.

So, draw your own conclusions based on the figures above and jump into the market. You should know that there are many other methods used for investment decision such as NPV, IRR, PBP, PI etc.

But the one we just discussed, from a layman point of view, is easy to calculate and find relevant data for.

I hope your thoughts are cleared so far. Happy Investing!!!

For more information on investment decisions and Finance topics, please CLICK on the blue 'Follow' Button at the Right-Top.