Help with numbers formula to average the previous 12 months of financial investments returns

I think there are a couple of possible misconceptions here:

1) Investment performance is not the percentage difference between book value and market value.

It typically is measured as the change in Market Value expressed as a percentage of Beginning Market Value, or EMV-BMV divided by BMV expressed as a percentage.

This simplifies to EMV/BMV-1. This is what most analysts use.

2) Because of compounding effects (hugely significant) one can't just take an average of percentage period returns. To account for compounding you need to derive what is sometimes called the "geometric" average, calculated by this general formula: EMV/BMV^(1/n)-1, where n is the number of periods.

Applying this to your example gives you something like this:

In G13, which you could put up in the table header or wherever:

=(F15/F2)^(1/12)−1

In E3 filled down the column:

=D3/C3−1

In F3 filled down the column:

=F2/(1+E3)

Is is usually convenient to have a "growth of $100" index type column such as F in my example because that simplifies calculations between time periods. You can just apply the general formula.

Note that you can get the cumulative return (as opposed to monthly) by looking at the 152.02 in the index column. If you started with 100 that means the return over that period was 52%. If you want the return for a different period just divide the relevant index values.

If you add money or take money out of the account then you have to adjust the return for the months such contributions or withdrawals occur as they have a large effect on returns. If you have that problem you can post.

SG

Hi emt,

For multiple comparisons, the function AVERAGEIFS may be what you need

AVERAGEIFS – Apple Support (AU)

Call back with questions.

Regards, Ian.

To piggyback on SGIII's statement that you cannot simply average the monthly results and get a meaningful result, here is an example for a 3 month period:

Initial investment = $100

September it drops precipitously to $1. The monthly return is -99%

October it rises to $10. The monthly return is 900%

November it rises to $100. The monthly return is 900%. You are back to the $100 you started with.

Average(-99%,900%,900%) =567%

You did not make a 567% return on this investment. Over those 3 months you broke even with a 0% return

Another example is an investment that drops by 50% one month then rises by 50% the next. A lot of people think that is break even but it is far from it.

Start with $100

$100*(1-0.5) = $50

$50*(1+0.5) = $75

End result is $75, a loss of $25.

emt6388 wrote:

The formula in column F =F2/(1+E3) gives me a different result.

As well it should! My apologies.

Unfortunately I have a significant typo in the formula I put in the post (but not my table). It should be:

=F2*(1+E3)

My apologies.

Post if that doesn't work for you or you have further questions. Calculating investment performance can be confusing. But the basic formulas are quite straightforward.

Badunit's example contains a classic lesson about investing. If you incur a 50% loss you can't make up for it by a 50% gain. Just to get back to where you started you have to double your money (i.e. gain 100%).

For many people, that is not intuitive. That is one of the reasons setting up an index (growth of $100) column like F in this example can be so helpful.

SG