Part 2

In part 1 we computed the AAGR & Std Dev of a series of fictitious returns to show that the Std Dev goes up with the volatility while the AAGR or mean average remains the same. Big deal! If I'm getting 6% it's 6%; what do I care about the Std Dev. Well, it's only 6% over the so called long run and you incur the risk of actually earning 6% +/- the Std Dev in any one period. The larger the Std Dev the more you could earn or lose, and it's the lose we are concerned about here.

Now for the real kicker. You aren't really earning 6%. You see, there's more than one type of average and you aren't earning the AAGR. You probably guessed that by now from puzzling over that last column oftable 1labeled CAGR where the percentages actually decrease with increasing Std Dev. Yep, that's the average you're getting. That is the Compound Annual Growth Rate and it is the equivalent return you would have earned if you earned the same amount in each period. By the way, that is why the AAGR is the same as the CAGR for Example 1. Now your head is spinning, but it's not really that hard. In fact you knew this intuitively all along. I'll show you with a common example.

It's the end of 2008. You started the year with let's say $100 in your TSP account but after losing 50% you are down to $50. You pull your money out of the market for the 1st quarter of 2009 while you drown your sorrows, but as you sober up you discover that you timed your trades just right for the rest of 2009 and you earned 50%. Sounds great! You made up your losses from the previous year, right? -50% + 50% averages to 0%. That is the AAGR, so why do you only have $75 in your TSP account. It's because the 50% gain is on a smaller amount, not the original amount you started with and lost. That is why losses hurt more than gains help. That is why a large negative Std Dev is not made up for by an equivalently large positive Std Dev. Turning the loss/gain around to a gain/loss doesn't help you either. In that case even though your initial gain is on the original amount, your subsequent loss is now on the newer larger amount. You lose the same amount either way. You know this. You've been there just like me. So let's compute what we really got, shall we? The formula for the CAGR is relatively simple and is given by:

What? You don't thinks that's all that simple. How about we use another built-in function instead. We can use the RRI function which has the following syntax:Code:CAGR = [(FV/PV)^(1/number of periods)] - 1

If your spreadsheet doesn't have the RRI function, you can substitute the RATE function in its place. It has the following syntax:Code:= RRI(NPER, PV, FV)

The RATE function is set up to calculate the interest rate on paying off a debt (PV) down to (FV) amount in (NPER) equal payments of (PMT). In our case we don't hold a debt but own the principle so we have to negate the value of PV. We also won't be making any payments or withdrawing any principle so our PMT is 0. Plugging in 2 years for NPER and $75 for FV gives us:Code:= RATE(NPER, PMT, PV, FV)

Now that looks like what happened to us! Going from $100 to $75 in two years is like losing 13.4% a year. That makes a lot more sense than breaking even at 0%.Code:CAGR = RATE(2,0,-100,75) = RRI(2,100,75) = -13.4%

We can use this function to compute the CAGR intable 1but things are a bit more complex because we don't have PVs and FVs but a series of gains and losses. It turns out we can use another function, "FVSCHEDULE", to generate a FV for a dummy PV, of say 1. Plugging that into the RRI or RATE equation in cell J2 gives us:

Drag this entry down the column as before and you should see the values in the last column ofCode:= RRI(COUNT(B2:G2), 1, FVSCHEDULE(1, B2:G2)) or = RATE(COUNT(B2:G2), 0, -1, FVSCHEDULE(1, B2:G2))table 1. Note that instead of just entering a 6 for the count I used another built-in function to compute that. That may seam like an unnecessary complexity in a simple example like this, but with real world data sets it helps preventchange errors. If I change the range B2:G2 in any way I need to change the 6 to the new value. That is too easy to forget or even to miscount. Setting it up with a function like this I only need to make sure the ranges for COUNT match the ranges for FVSCHEDULE.

As an aside, the function FVSCHEDULE is what we typically use to calculate our total return over all periods. You could add column K to your spreadsheet to show the total return over 6 period by typing the following into cell K2:

You Should get something like this in column K.Code:= FVSCHEDULE(1, B2:G2) - 1

Table2Total Return 41.85% 41.81% 40.49% 36.47% 41.74%

This looks like a good place to stop for now. In part three of the article we'll used what we learned here on the annual returns of our TSP funds and see what that tells us. Stay tuned.

## Bookmarks