SQL/Compound Interest data type example.sql at master · lf-novelt/SQL · GitHub

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
29
30
31
32
33
34
35
36
37
/* An example of real v float rounding */

DECLARE @rate real = 0.05,
	@InterestAddedPerYear real = 12.0, -- monthly
	@InitialContibution real = 5000,
	@MonthlyContributions real = 100,
	@TimeYears real = 10

DECLARE 	@InterestPerPeriod real = (@rate / @InterestAddedPerYear),
	@InterestPeriods real = (@InterestAddedPerYear * @TimeYears)

SELECT
(POWER(1 + @InterestPerPeriod, @InterestPeriods) * @InitialContibution)
	, ((@MonthlyContributions * (POWER(1 + @InterestPerPeriod, @InterestPeriods) - 1) / @InterestPerPeriod)) -- * (1 + @InterestPerPeriod))

GO
DECLARE @rate float = 0.05,
	@InterestAddedPerYear float = 12.0, -- monthly
	@InitialContibution float = 5000,
	@MonthlyContributions float = 100,
	@TimeYears float = 10

DECLARE 	@InterestPerPeriod float = (@rate / @InterestAddedPerYear),
	@InterestPeriods float = (@InterestAddedPerYear * @TimeYears)

select (POWER(1 + @InterestPerPeriod, @InterestPeriods) * @InitialContibution)
	, ((@MonthlyContributions * (POWER(1 + @InterestPerPeriod, @InterestPeriods) - 1) / @InterestPerPeriod)) -- * (1 + @InterestPerPeriod))

GO
DECLARE @rate float = 0.05,
	@InterestAddedPerYear float = 12.0, -- monthly
	@InitialContibution float = 5000,
	@MonthlyContributions float = 100,
	@TimeYears float = 10

--P = A / ( 1 + r/n ) ^ (nt).
-- Initial = Target / POWER(1 + @InterestPerPeriod, @InterestPeriods)