When reviewing historical returns, there are several barriers to entry for new financial professionals. After jumping the hurdle of actually acquiring the investment data, the next hurdle many professionals must jump is understanding how to turn the monthly return data they have into average annual return data.

The calculation required is the annualized rate of return calculation.

When you download monthly return data from Morningstar, it places the data in column B starting in row 6, so I will use B6:B225 as the monthly return data in my examples.

Note that Morningstar gives you the data as whole numbers, meaning 5.42039 instead of 0.0542039, which before you can use the data you must divide by 100.

There are at least three options.

Option 1: Future Value

The main formula for an annualized rate of return is: The quotient of the ending value divided by beginning value raised to the exponent of the quotient of one divided by the number of years minus one.

The Excel function FVSCHEDULE calculates the future value of its first input when grown according to the array in its second input. In this way, you can use FVSCHEDULE(1,B6:B225) to calculate the ending value of a $1 investment.

The next step in the formula for annualized return is the ending value of the investment divided by the beginning value of the investment. Since we used a beginning investment value of $1, we have already completed that step.

The next step is to raise this ratio to the exponent of 1 divided by the number of years and then subtract 1.

Because we have a number of months in COUNT(B6:B225), to make this the number of years we would use 12/COUNT(B6:B225)

This makes the final formula for returns expressed as decimals:

=FVSCHEDULE(1,B6:B225)^(MIN(1,12/COUNT(B6:B225)))-1

If your numbers are expressed as whole numbers, you will need to divided the return numbers by 100 to get the right answer. To do this, you will Array Enter (Ctrl-Shift-Enter) the following formula, which includes the step of dividing by 100:

{=FVSCHEDULE(1,B6:B225/100)^(MIN(1,12/COUNT(B6:B225)))-1}

Option 2: Array

Another way to annualize a return is to use the product of, for each month in turn, one plus the month’s return. This can be achieved with the array-entered formula:

{=PRODUCT(1+B6:B225/100)^(12/COUNT(B6:B225))-1}

This formula assumes you need to divide by 100 to get your returns into decimals. If you do not, you can use:

{=PRODUCT(1+B6:B225)^(12/COUNT(B6:B225))-1}

Option 3: Large Table

Sometimes, you may find that you are already in need of creating a table where you grow the assets by the monthly amounts. If that is the case, set up the table with a starting value and then multiply the cell above by that month’s monthly return divided by 100. For example, the first cell might be:

=C5*(1+B6/100)

where C5 has a starting value of say $10,000.

Then after growing all the returns all the way down to growing C224 by B225 into a C225, you can calculate the annualized return by using ending value divided by beginning value, like so:

=(C225/$C$5)^(12/COUNT($C$5:C225))-1

This has the added benefit that you can count the annualized return over time, easily calculate a rolling return, or create a “Growth of the Assets Over Time” graph without any additional effort.

However, it uses a lot of cells to accomplish something you could have done in one cell with one of the options above.

Hopefully, these three strategies empower you to run some of your own investment analysis. If you enjoy running investment analysis in Microsoft Excel, you may want to consider applying for a job at Marotta Wealth Management!

We are an independent, fee-only, comprehensive wealth management firm offering a complete range of investment management and financial planning services. The work we do for clients is valuable, fun, and fulfilling. As fee-only fiduciaries, our only concern is to help clients meet their goals. And as comprehensive wealth managers, we are always seeking to advance the financial planning profession.

You can read more about the benefits of working for us here.

Photo by Tyler Franta on Unsplash