SQL Compound Growth Rate Calcuations

Hello,

I'm working on a graph for a client that needs to show growth rates for a population.  I have the starting population and the date of that starting population.  I know the annual growth rate is 30%.  What I need is to take that growth and be able to calculate the growth of the population for a given date in the future.

I.e. Starting Population 100, Growth Rate 30%, Start Date 1/1/2010
Population on 1/1/2011 would be 130.

How would I calculate the population of the dataset say for 6/1/2010?
Or 6/1/2013 ?

MS SQL Function, Stored Procedure, etc

Thank you.
vegasdevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Cheating a little, and assuming that each month is of the same length, this code seems to be doing the trick:




CREATE PROCEDURE dbo.GetPopulation (@StartDate datetime, @Months int, @StartPop numeric(20, 6),
	@AnnualGrowth numeric(20, 6))

AS BEGIN

	DECLARE @tbl TABLE (AsOfDate datetime, Population numeric(15, 2))
	DECLARE @MonthRate numeric(20, 6)
	DECLARE @MonthCount int
	DECLARE @Mult numeric(20, 6)

	SET @MonthRate = POWER(1 + @AnnualGrowth, 1 / 12.0)
	SET @MonthCount = 0

	WHILE @MonthCount < @Months BEGIN
		SET @Mult = POWER(@MonthRate, @MonthCount)
		INSERT INTO @tbl (AsOfDate, Population) VALUES (DATEADD(m, @MonthCount, @StartDate), @StartPop * @Mult)
		SET @MonthCount = @MonthCount + 1
	END

	SELECT * FROM @Tbl

END

GO

EXEC dbo.GetPopulation '2010-01-01', 24, 100, 0.3;

GO


/* That code returns:

AsOfDate                Population
----------------------------------
2010-01-01 00:00:00.000	100.00
2010-02-01 00:00:00.000	102.21
2010-03-01 00:00:00.000	104.47
2010-04-01 00:00:00.000	106.78
2010-05-01 00:00:00.000	109.14
2010-06-01 00:00:00.000	111.55
2010-07-01 00:00:00.000	114.02
2010-08-01 00:00:00.000	116.54
2010-09-01 00:00:00.000	119.11
2010-10-01 00:00:00.000	121.75
2010-11-01 00:00:00.000	124.44
2010-12-01 00:00:00.000	127.19
2011-01-01 00:00:00.000	130.00
2011-02-01 00:00:00.000	132.87
2011-03-01 00:00:00.000	135.81
2011-04-01 00:00:00.000	138.81
2011-05-01 00:00:00.000	141.88
2011-06-01 00:00:00.000	145.02
2011-07-01 00:00:00.000	148.22
2011-08-01 00:00:00.000	151.50
2011-09-01 00:00:00.000	154.85
2011-10-01 00:00:00.000	158.27
2011-11-01 00:00:00.000	161.77
2011-12-01 00:00:00.000	165.34

*/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Theory

From novice to tech pro — start learning today.