# Geometric Annual Return in SQL

## 2008-01-30

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