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).

/* 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”:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s