Tuesday, December 14, 2021

Proper way to calculate CAGR using T-Sql for SQL Server

After reading (and attempting the solutions offered in some) several articles about SQL and CAGR,  I have reached the conclusion that none of them would stand testing in a real-world environment. For one thing, the SQL queries offered as examples are overly complex or don't use the correct math for calculating proper CAGR. Since most DBAs don't have an MBA or Finance degree, let me help. 

The correct equation for calculating Compound Annual Growth Rate (as a percentage) is: 


Some key points about CAGR: 
  • The compounded annual growth rate (CAGR) is one of the most accurate ways to calculate and determine returns for anything that can rise or fall in value over time.
  • Investors can compare the CAGR of two alternatives to evaluate how well one stock performed against other stocks in a peer group or a market index.
  • The CAGR does not reflect investment risk.
You can read a full article about CAGR here

To calculate the CAGR for an investment in a language like VB is pretty straightforward.

Dim CAGR as Decimal = ((MarketValue / Basis) ^ (1 / (DaysHeld / 365)) - 1). 

The MarketValue is the ending value, and this can be calculated as the number of shares times last share price. Basis is simply the dollar amount paid for the investment. Because the formula is expecting N to be number of years, I use the DATEDIFF function to get the actual number of days I've held the investment, then divide by 365. I do not multiple by 100, because I'll format this result as a Percentage, by using either the FormatPercent function, or the DataFormatString="{0:P2} attribute in the GridView's BoundField.

In a SQL query, you have to use the POWER function to raise the overall return rate to the exponent of 1/n. This can get a little complicated with the use of parenthetical brackets to ensure the correct outcome. 

For this example, there will be two tables, called Stocks and StockPrices. Stocks will have fields named StockID, Symbol, DatePurchased, SharesHeld (among others), and StockPrices will have StockID, QuotedDate, and StockPrice.

Here's how the query works out: 

SELECT s.symbol, s.DatePurchased, sp.stockprice, s.sharesheld,  s.basis,

/* This is the statement for CAGR */
(Power(((sp.stockprice * s.SharesHeld) / s.Basis), 1 / (Cast(DATEDIFF(DAY,s.DatePurchased,getDate()) as float) / 365))-1) as 'CAGR'

FROM tblStocks s, tblStockPrices sp WHERE s.stockid = sp.stockid 
AND sp.quotedate = (select max(quotedate) from tblStockPrices) 
order by s.Symbol

Again, I've omitted the multiplication by 100 because I'll format the result as a percentage when I render it to the web page. 

No comments:

Post a Comment

Thanks for the comment. Will get back to you as soon as convenient, if necessary.

The 2022 Federal Budget in Charts

Click on image to enlarge