Calcul d'interets composés

**Erwan1978** · 17/06/2009, 19h01

Bonjour,

Je ne parviens pas à trouver une méthode satisfaisante pour gérer en transact la problématique d'intérêts composé.
Je m'explique ; supposons que je place un capital en début d'année à un taux mensuel variable, je souhaiterais que
ma requête me retourne pour chacun des mois de l'année le montant de mon capital actualisé des interets perçus.
Voila ce que cela donnerait dans un tableur :

capital initial = 100

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
date		rendement	interet + capital 
31/01/09	0,05		105,00
28/02/09	0,03		108,15
31/03/09	-0,02		105,99
30/04/09	0,01		107,05
31/05/09	0,08		115,61
30/06/09	-0,06		108,67
31/07/09	0,07		116,28
31/08/09	0,1		127,91
30/09/09	-0,12		112,56
31/10/09	0,06		119,31
30/11/09	0,03		122,89
31/12/09	0,09		133,95

J'ai bien essayé différentes méthodes (CTE, self-join...) mais je bute systématiquement sur un problème de syntaxe...
J'imagine qu'il existe une autre méthode que le passage par un curseur, non ?

Ma config :

Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)
Feb 26 2008 18:15:01
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Merci de votre aide !

Le jeu de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE dbo.tf_interest_rates
	(id INT IDENTITY(1,1) NOT NULL,
	date DATETIME NOT NULL,
	pct	 DECIMAL (8,6) NOT NULL)
GO
 
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090131',	0.05)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090228',	0.03)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090331',	-0.02)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090430',	0.01)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090531',	0.08)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090630',	-0.06)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090731',	0.07)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090831',	0.1)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20090930',	-0.12)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20091031',	0.06)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20091130',	0.03)
INSERT INTO dbo.tf_interest_rates (date, pct) VALUES ('20091231',	0.09)

**elsuket** · 17/06/2009, 19h18

Bonjour,

Essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
;WITH
	CTE (id, Date, Rendement, InteretsPlusCapital) AS
	(
		-- Calcule les intérêts à la première date
			SELECT id,
					date,
					pct AS Rendement,
					CAST(100.0 * (1.0 + pct) AS FLOAT) AS InteretsPlusCapital
			FROM dbo.tf_interest_rates
			WHERE id = 1
		UNION ALL
		-- Calcule les intérêts aux dates suivantes
			SELECT IR.id,
					IR.date,
					IR.pct AS Rendement,
					CAST(C.InteretsPlusCapital * (1.0 + IR.pct) AS FLOAT)
			FROM dbo.tf_interest_rates AS IR
			JOIN CTE AS C ON C.id + 1 = IR.id
	)
SELECT CONVERT(CHAR(10), Date, 3) AS Date,
		CAST(Rendement AS NUMERIC(4, 2)) AS Rendement,
		InteretsPlusCapital
FROM CTE

Merci bien pour le jeu de données, impeccable

En revanche c'est dommage que nous n'ayez pas posté votre code, nous aurions pu regarder où vous avez buté ...

@++

**Erwan1978** · 18/06/2009, 09h30

Bravo pour ta réponse ! Je constate que j'ai encore du chemin à faire avant de maitriser comme comme toi la syntaxe des CTE !

J'apporte une légère modification à ton script car tel quel, il sous entend que les rendements mensuels sont insérés dans l'ordre croissant dans la table (cad que la date associée à l'id = 1 est nécessairement inférieure à la date associée à l'id = 2), ce que je ne peux garantir lors du peuplement de ma table.

L'idée est de modifier la jointure entre la CTE et la table dbo.tf_interest_rates en posant une égalité du type CTE.[mois précédent] = dbo.tf_interest_rates.[mois courant] (mes données sont nécéssairement mensuelles). Pour cela j'ai utiliser une fonction perso :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
	/*******************************************************************
Renvoi la date d'input ramenée à la fin du mois après l'application éventuel d'un décalage (exprimé en mois).
*******************************************************************/
 
 
CREATE FUNCTION [dbo].[fn_get_end_of_month]
 (@date_initial DATETIME,
  @month_shift INTEGER)
 
RETURNS DATETIME
 
AS
 
BEGIN
	DECLARE @date_final DATETIME
	SET @date_final = DATEADD(MONTH,@month_shift+1,@date_initial)
	SET @date_final = DATEADD(DAY,-DAY(@date_final),@date_final)
 
 
	RETURN @date_final
END

Ce qui donne au final :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
;WITH
	CTE (id, Date, Rendement, InteretsPlusCapital) AS
	(
		-- Calcule les intérêts à la première date
			SELECT id,
					date,
					pct AS Rendement,
					CAST(100.0 * (1.0 + pct) AS FLOAT) AS InteretsPlusCapital
			FROM dbo.tf_interest_rates
			WHERE id = 1
		UNION ALL
		-- Calcule les intérêts aux dates suivantes
			SELECT IR.id,
					IR.date,
					IR.pct AS Rendement,
					CAST(C.InteretsPlusCapital * (1.0 + IR.pct) AS FLOAT)
			FROM dbo.tf_interest_rates AS IR
			JOIN CTE AS C ON dbo.fn_get_end_of_month(C.date,1) = dbo.fn_get_end_of_month(IR.date,0)
	)
SELECT CONVERT(CHAR(10), Date, 3) AS Date,
		CAST(Rendement AS NUMERIC(4, 2)) AS Rendement,
		InteretsPlusCapital
FROM CTE

Et qui semble bien tourner

Merci infiniment de ton aide !

**Erwan1978** · 18/06/2009, 10h34

J'ajouterai une autre modification : l'utilisation de l'option MAXRECURSION. Par défaut le nombre de récursions autorisées est 100. Il faut donc dans un premier temps déterminer le nombre de ligne dans notre table des taux d'intérêts et l'enregistrer comme paramètre. Puis utiliser ce paramètre dans l'option maxrecursion au moment du SELECT sur la CTE. Il semblerait que cela ne puisse se faire qu'en SQL dynamique...

Voici ce que ça donne :

Création de la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE dbo.tf_interest_rates
	(id INT IDENTITY(1,1) NOT NULL,
	date DATETIME NOT NULL,
	pct	 DECIMAL (8,6) NOT NULL)
GO

Peuplement aléatoire (104 lignes) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SET NOCOUNT ON
DECLARE @i INTEGER
DECLARE @pct DECIMAL(8,6)
DECLARE @date DATETIME
SET @i=1
SET @date = '19960101'
WHILE @i < 105
	BEGIN
 
		SET @pct = RAND()/100
		SET @date =  dbo.fn_get_end_of_month(@date,1)
		INSERT INTO dbo.tf_interest_rates
		(date, pct)
		VALUES(@date,@pct)
		SET @i = @i+1
	END

Détermination du nombre de lignes et construction de la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
DECLARE @number_of_rows INT
SELECT @number_of_rows = COUNT(*)
FROM tf_interest_rates
 
DECLARE @str NVARCHAR(MAX)
SET @str = '
;WITH
	CTE (id, Date, Rendement, InteretsPlusCapital) AS
	(
		-- Calcule les intérêts à la première date
			SELECT id,
					date,
					pct AS Rendement,
					CAST(100.0 * (1.0 + pct) AS FLOAT) AS InteretsPlusCapital
			FROM dbo.tf_interest_rates
			WHERE id = 1
		UNION ALL
		-- Calcule les intérêts aux dates suivantes
			SELECT IR.id,
					IR.date,
					IR.pct AS Rendement,
					CAST(C.InteretsPlusCapital * (1.0 + IR.pct) AS FLOAT)
			FROM dbo.tf_interest_rates AS IR
			JOIN CTE AS C ON dbo.fn_get_end_of_month(C.date,1) = dbo.fn_get_end_of_month(IR.date,0)
	)
 
SELECT CONVERT(CHAR(10), Date, 3) AS Date,
		Rendement,
		InteretsPlusCapital
FROM CTE OPTION (MAXRECURSION ' + CAST(@number_of_rows AS VARCHAR(25)) + ')'

Exécution de la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC sp_executesql @str

Je sens que je vais placer tout cela dans une fonction, moi !

**Waldar** · 18/06/2009, 10h51

Si votre volumétrie le permet :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE @Capital INT;
SELECT @Capital=100;
 
select
    t1.date,
    t1.pct,
    round(exp(sum(log(1+t2.pct)))*@Capital,2) as CplusI
from
    dbo.tf_interest_rates as t1
    inner join dbo.tf_interest_rates as t2
      on t2.date <= t1.date
group by
    t1.date,
    t1.pct
order by
    t1.date asc;

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
date                    pct     CplusI
----------------------- ------- --------
2009-01-31 00:00:00.000	0.05	105
2009-02-28 00:00:00.000	0.03	108,15
2009-03-31 00:00:00.000	-0.02	105,99
2009-04-30 00:00:00.000	0.01	107,05
2009-05-31 00:00:00.000	0.08	115,61
2009-06-30 00:00:00.000	-0.06	108,67
2009-07-31 00:00:00.000	0.07	116,28
2009-08-31 00:00:00.000	0.10	127,91
2009-09-30 00:00:00.000	-0.12	112,56
2009-10-31 00:00:00.000	0.06	119,31
2009-11-30 00:00:00.000	0.03	122,89
2009-12-31 00:00:00.000	0.09	133,95

Dommage que SQL Server ne sache pas encore gérer les aggrégats analytiques cumulatifs, on aurait pu économiser l'auto-jointure !

**elsuket** · 18/06/2009, 11h18

Bravo à vous deux

@++

Calcul d'interets composés

Développement SQL Server

Discussions similaires

Partager

Partager