User

On présente dans ce billet un outil Access permettant de générer sur une certaine durée exprimée en mois, un tableau de remboursement , ceci en fonction du capital de départ emprunté, et du taux d'intérêt annuel.



Particularité : Si la durée en nombre de mois n'est pas remplie alors l'utilisateur est invité à saisir le montant approximatif qu'il souhaite payer par échéance,
le calculateur déterminera alors automatiquement la durée et les différents remboursements à effectuer.

Table principale pour enregistrer les remboursements :

Nom du champ	Type de données	Description
IdRemb	Entier long	Numéro du remboursement (1,2..n)
DateEcheance	Date/Heure	Date d'échéance
CapitalDu	Monétaire	Capital restant dû avant le remboursement d'indice i
InteretsRemb	Monétaire	Part du remboursement des intérêts
CapitalRemb	Monétaire	Part du remboursement de l'amortissement
MntPaye	Monétaire	Montant payé à chaque échéance

Formule mathématique donnant le montant à payer à échéance, avec comme arguments le capital de départ c, le taux t et la durée n :

Code VBA :

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

1
2
3
4
Public Function EvalRemb(c As Currency, t As Double, n As Long) As Double
    EvalRemb = c * ((t / (1 - ((1 + t) ^ (-n)))))
 
End Function

Formule mathématique donnant le capital restant dû au bout de n remboursements et en fonction du capital de départ c, du montant payé m et du taux t :

Code VBA :

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

1
2
3
4
Public Function EvalReste(c As Currency, m As Double, t As Double, n As Long) As Double
    EvalReste = ((1# + t) ^ n) * (c - (m / t)) + (m / t)
 
End Function

Fonction donnant le nombre de mois du plan de remboursement, avec comme arguments le capital de départ c, le taux t et le montant payé m :

Code VBA :

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

1
2
3
4
Public Function EvalNbMois(c As Currency, m As Currency, t As Double) As Double
    EvalNbMois = (Log(m) - Log(m - c * t)) / Log(1 + t)
 
End Function

Procédure génératrice du plan de remboursement. Elle s'exécute sur l'événement clic du bouton Cmdvalider :

Code VBA :

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
Private Sub CmdValider_Click()
    On Error GoTo err_CmdValider_Click
    Dim i As Long ' Numéro du remboursement
    Dim db As DAO.Database ' Référence à la base active
    Dim rsRemb As DAO.Recordset ' Recordset lié à la table T_Remb
    Dim c As Currency ' Variable contenant la valeur du capital de départ
    Dim n As Long ' Durée en mois
    Dim t As Double ' Taux du remboursement annuel
    Dim nbRemb As Long ' Nbre de remboursements
    Dim txRemb As Double ' Taux de remboursement de la période
    Dim mtPaye As Double ' Montant à payer à chaque échéance
    Dim ctReste As Currency ' Capital restant dû
    Dim dt As Date ' Date de l'échéance i
    Dim p As Long ' nombre de mois de la périodicité
 
        If (Nz(Me.CapitalDepart, "") <> "") And (Nz(Me.TauxRemb, "") <> "") And ((Nz(Me.DureeRemb, "") <> "") Or _
                                                (Nz(Me.MntRemb, "") <> "")) And (Nz(Me.Date1ereEcheance, "") <> "") Then ' Si tous les paramètres ont été saisis
 
            c = Me.CapitalDepart: t = Me.TauxRemb: dt = Me.Date1ereEcheance ' Capital de départ, taux de remboursement, date 1ère échéance
 
            Select Case Me.PeriodiciteRemb
 
                Case "Mensuelle"
                p = 1 ' nombre de mois de la périodicité
 
                Case "Trimestrielle"
                p = 3 ' nombre de mois de la périodicité
 
                Case "Semestrielle"
                p = 6 ' nombre de mois de la périodicité
 
                Case "Annuelle"
                p = 12 ' nombre de mois de la périodicité
 
            End Select
 
            txRemb = t * (p / 12) ' Taux pour la périodicité
 
                If Nz(Me.DureeRemb, "") = "" Then ' Si la durée n'a pas été saisie
                    n = p * EvalNbMois(c, Me.MntRemb, txRemb)  ' On affecte la durée en mois au champ DureeRemb
                    Me.DureeRemb = n ' Affectation de la durée en mois
                Else
                    n = Me.DureeRemb ' la durée en mois
                End If
 
            nbRemb = n / p ' nombre de remboursements
 
            Set db = CurrentDb ' On fait référence à la base active
 
            DoCmd.SetWarnings False
            DoCmd.RunSQL ("delete from T_Remb;") ' On vide la table T_Remb avant la simulation
            DoCmd.SetWarnings True
 
            Set rsRemb = db.OpenRecordset("T_Remb", dbOpenDynaset) ' Ouvre le jeu d'enregistrements de la table T_Remb
 
            mtPaye = 0
            ctReste = c
 
            For i = 1 To nbRemb ' On parcourt la liste des échéances
 
                rsRemb.AddNew ' Ajout d'un remboursement dans la table
                rsRemb!IdRemb = i ' Numéro du remboursement
                rsRemb!DateEcheance = dt ' Date de l'échéance
                rsRemb!MntPaye = EvalRemb(c, txRemb, nbRemb) ' Montant payé à échéance
                rsRemb!CapitalDu = EvalReste(c, mtPaye, txRemb, i - 1) ' Capital restant dû
 
                rsRemb!CapitalRemb = ctReste - EvalReste(c, rsRemb!MntPaye, txRemb, i) ' Capital remboursé
                rsRemb!InteretsRemb = rsRemb!MntPaye - rsRemb!CapitalRemb ' Intérêts remboursés
 
                rsRemb.Update
 
                rsRemb.MoveLast ' On ce déplace sur le dernier remboursement enregistré
 
                ctReste = EvalReste(c, rsRemb!MntPaye, txRemb, i) ' On affecte le reste dû à la variable ctRest
                mtPaye = EvalRemb(c, txRemb, nbRemb) ' On affecte le montant payé à sa variable
 
                dt = DateAdd("m", p, dt) ' On passe à la date d'échéance suivant (+1 mois)
 
            Next i
 
 
            Me.MntPaye = mtPaye ' Mise à jour de la zone de texte MntPaye
 
            'Me.TotalEcheances = mtPaye * nbRemb ' Total payé
            Me.NbEcheances = nbRemb ' Mise à jour du nombre d'échéances
            Me.DerniereEcheance = DateAdd("m", -p, dt) ' Mise à jour de la date de dernière échéance
 
            Me.SF_CalculateurRemb.Requery ' Rafraîchissement du sous-formulaire
 
            rsRemb.Close ' Fermeture du recordset
 
        Else ' S'il manque un paramètre
 
            MsgBox ("Saisie incomplète !") ' On affiche ce message
 
        End If
 
err_CmdValider_Click:
 
        ' Gestion d'erreur
        If Err.Number <> 0 Then ' S'il y a eu une erreur
            MsgBox (Err.Description) ' On affiche sa description
        End If
 
        On Error Resume Next
 
        ' Libération des variables
 
        'rsRemb.Close
        Set rsRemb = Nothing
 
        Set db = Nothing
 
End Sub

L'outil est disponible en pièce jointe..