Geometric Annual Return in SQL

Here is some quick-and-dirty SQL to calculate an geometric annual return (as a percent) from a column of monthly returns (in percents).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


/* Convert the annualized number back to a percent */
SELECT (T3.AnnHPR - 1) * 100 AS GeomAnnRet
FROM
  (
  /* Annualize the holding period return */
  SELECT POWER(T2.HPR, 12.0 / T2.NumReturns) AS AnnHPR
  FROM
    (
    /* Calculate the holding period return over the time
       period.

       POWER(10, SUM(LOG10(n))) is a simulated PRODUCT(n)
       aggregate function.

       The precision of POWER is determined by the precision
        of the first argument, so use a lot of decimals. */
    SELECT POWER(10.0000000000000000,
                 SUM(LOG10(T.MonthReturn))) AS HPR,
           COUNT(*) AS NumReturns
    FROM
      (
      /* Convert all percent returns to multipliers (1% ->
         1.01) */
      SELECT 1 + MonthPctReturn / 100 AS MonthReturn
      FROM ...
      ) AS T
    ) AS T2
  ) AS T3

Update 2008-01-30 10:52PM: Here’s the equivalent “one-liner”:

1
2
3
4


SELECT 100 * (POWER(POWER(10.000000000000000,
                          SUM(LOG10(1 + MonthPctReturn / 100))),
                    12.0 / COUNT(*)) - 1)
FROM ...