Jan 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</code>
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 …
Recent Comments