Geometric Annual Return in SQL
Finance / Investing sql
Published: 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).

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