Objectifs
Une approche du crédit, en partant des fonctions mathématiques, en passant par le tableau d'amortissement et en terminant par quelques fonctions financières du tableur OpenOffice...
Nouveauté 2023 : Une approche du crédit basée sur les macros
Le deuxième fichier "info terminé.ods" est le classeur terminé avec les relations financières disponibles dans OpenOfficeClasseur. Evidemment l'intérêt est de le réaliser à partir d'une feuille vide en suivant la progression du cours. De mémoire, le tableau d'amortissement fonctionne pour un emprunt sur plus de 50 ans... ce que je ne vous conseille pas!!! Vous jouez avec les cellules sur fond jaune de la feuille 1, les feuilles sont protégées mais le mot de passe est vide.
Voir la pj en bas d'article.
Support de cours
- Lien vers le Support de cours
Table des matières
1 - Présentation de la séance
1.1 - Prérequis
1.2 - Rappel des principes du crédit
1.2.1 - Le taux périodique
2 - Mise en place simplifiée
2.1 - Les données fictives de l'exercice pour la suite
2.2 - Mise en forme de base
2.3 - Affichage des données numériques
2.3.1 - Format monétaire
2.3.2 - Format pourcentage
2.4 - Calculons les taux périodiques
2.4.1 - Linéarisation d'une formule mathématique dans un classeur
2.4.2 - Taux proportionnel
2.4.3 - Rappel
2.4.4 - Affichage et précision
2.5 - Calculons les intérêts, les échéances et le capital restant dû après la première période
2.6 - Réalisons le tableau d'amortissement
2.6.1 - But à atteindre
2.6.2 - Mise en place sur la feuille 2
2.7 - Bilan du crédit
2.8 - Autocritique de cette simulation
3 - Mise en place de la fonction SI
3.1 - Présentation de la fonction SI
3.2 - Modification du tableau d'amortissement
3.2.1 - Modification de la colonne échéance
3.2.2 - Modification des colonnes B, C, D, E et F.
3.2.3 - Vérifications
4 - Sécurisation des formules
4.1 - Format de cellule Verrouiller
4.2 - Activation de la protection des feuilles
4.3 - Message de protection
5 - Utilisation des fonctions financières d'OpenOffice
5.1 - Fonction VPM – échéances
5.2 - Fonction PRINCPER – Capital remboursé pour une période
5.3 - Fonction INTPER – Intérêt pour une périod
5.4 - Travail à faire
Classeur, crédit et macros
- Lien vers le classeur contenant la macro
test macro classeur credit pour la suite OpenOffice.ods
test macro classeur credit pour la suite Libreoffice - Source inspirante:
https://berma.pagesperso-orange.fr/Files_fr/OOo_API_intro.pdf
Autorisation des macros
Pour que les macros fonctionnent, il faut non seulement les activer mais aussi dans les options baisser le niveau de sécurité (le faire passer de Elevé à Moyen) donc vous devez me faire confiance ;) - Pour l'instant je creuse pour savoir comment devenir une source de confiance.
Fonctionnement
- Feuille 1:
Crédit avec tableau d'amortissement et utilisation des formules financières
=> Pb: Si je modifie le nombre d'année, mon tableau d'amortissement ne fonctionne plus ;-( - Feuille 2:
Présence de 2 boutons
Macro - Crédit lance Sub Credit
Macro - Effacement lance Sub Effacement
=> Plus de Pb: Si je modifie le nombre d'année, il me suffit d'effacer les données et de relancer la macro "crédit"
Principe d'utilisation
- Se rendre sur la feuille 2
- Saisir
- le montant
- le taux annuel
- la durée en année (Valeur entière >=1)
- Cliquer sur Macro - Crédit
Pour calculer le tableau d'amortissement - Cliquer sur Macro - Effacement
Pour effacer les données jusqu'à la ligne 1000
Visualiser/Modifier la macro
Via me menu Outils/Macro/Editer les macros
Développer dans le catalogue d'objet en partant de la feuille
En dessous le contenu de la macro avec des commentaires (lignes commençant par ')
REM ***** BASIC *****
' L'instruction Option explicit renvoie un message d’erreur pour toute variable non définie, ou mal orthographiée.
Option explicit
' ------------------- declaration variables du module donc pour les 2 macros Sub
Dim MonDocument As Object
Dim MaFeuille As Object
Dim MaCellule As Object
Dim MaPlage As Object
' Script lancé par le bouton Macro - Crédit
Sub Credit
' Variables specifiques au sous programme Credit
Dim montant_emprunte As Variant, taux_an As Variant, taux_periodique As Variant, duree_an As Variant, duree_mois As Variant
MonDocument = ThisComponent
MaFeuille = MonDocument.CurrentController.ActiveSheet
' ------------------ recup valeur des cellules et affectation variables
' Principe appliqué ici:
' Se positionner sur la cellule ad hoc avec getCellByPosition(col,ligne)
' Rq ici (1,3) pour B4
' Affectation de sa valeur avec la propriété .Value
'
' Les donnees sont dans les cellules des lignes de 1 à 6
'
MaCellule = MaFeuille.getCellByPosition(1,3)
montant_emprunte = MaCellule.Value
'
MaCellule = MaFeuille.getCellByPosition(1,4)
taux_an = MaCellule.Value
taux_periodique=taux_an/12
'
MaCellule = MaFeuille.getCellByPosition(1,5)
duree_an = MaCellule.Value
duree_mois =duree_an*12
' ------------------ si besoin astuce pour afficher les valeurs
' Principe :
' Utilisation du print "texte à afficher";variable
'
' Affichage pour verifier (a decommenter si necessaire)
'print "nom feuille ";MaFeuille.Name
'print "verif valeurs ";montant_emprunte;taux_an;taux_periodique;duree_an;duree_mois
' ---------------------- calculs echeance, tot credit et tot interet
' Objectif:
' Calculer puis afficher les résultats dans des cellules
' Affichage entre les lignes 10 et 12
'
' Principe:
' Calculer les valeurs et les affectées a des variables
' Saisir l'etiquette et la valeur avec les proprietes .String pour le texte et .Value pour les nombres
' Déclaration des variables puis calculs
' Currency pour format monetaire
' Propriete .NumberFormat=108 pour affichage au format monetaire
Dim echeance_maths As Currency, tot_credit As Currency, tot_interet As Currency
'
echeance_maths=(montant_emprunte*taux_periodique)/(1-(1+taux_periodique)^(-duree_mois))
tot_credit=echeance_maths*duree_mois
tot_interet=tot_credit-montant_emprunte
' Affichage pour verifier (a decommenter si necessaire)
'print "echeance=";echeance_maths;"tot credit=";tot_credit;tot_interet
' Affichage des resultats dans cellules de la feuille active
MaCellule = MaFeuille.getCellByPosition(0,9)
MaCellule.String = "Echéance"
MaCellule = MaFeuille.getCellByPosition(1,9)
MaCellule.NumberFormat=108
MaCellule.Value = echeance_maths
'
MaCellule = MaFeuille.getCellByPosition(0,10)
MaCellule.String = "Total crédit"
MaCellule = MaFeuille.getCellByPosition(1,10)
MaCellule.NumberFormat=108
MaCellule.Value = tot_credit
'
MaCellule = MaFeuille.getCellByPosition(0,11)
MaCellule.String = "Total intérêt"
MaCellule = MaFeuille.getCellByPosition(1,11)
MaCellule.NumberFormat=108
MaCellule.Value = tot_interet
' ------------------- Calcul du tableau d'amortissement
MaCellule = MaFeuille.getCellByPosition(0,16)
MaCellule.String = "Tableau d'amortissement"
' Mise en place des étiquettes des colonnes
MaCellule = MaFeuille.getCellByPosition(1,17)
MaCellule.String = "Total"
MaCellule = MaFeuille.getCellByPosition(0,18)
MaCellule.String = "Rang"
MaCellule = MaFeuille.getCellByPosition(1,18)
MaCellule.String = "Restant dû"
MaCellule = MaFeuille.getCellByPosition(2,18)
MaCellule.String = "Intérêt"
MaCellule = MaFeuille.getCellByPosition(3,18)
MaCellule.String = "Principal"
MaCellule = MaFeuille.getCellByPosition(4,18)
MaCellule.String = "Echéance"
' Mise en place des totaux de la ligne 18
' Principe:
' Propriété .FormulaLocal pour pouvoir saisir une formule comme dans le classeur
' A noter l'astuce +Ltrim(str... afin de pouvoir faire tourner la valeur de la ligne
' Ex
' "=SOMME(C20:C"+Ltrim(str(19+duree_mois))+")"
' donnera si duree_mois correspond à 24 mois soit un credit de 2 ans
' "=SOMME(C20:C43)"
' Rappel la somme doit aller jusqu'à la ligne 19+duree_mois
' Mise en place de la somme avant la boucle pour voir l'evolution en cours de calculs
' Total interet
MaCellule = MaFeuille.getCellByPosition(2,17)
MaCellule.NumberFormat =108
MaCellule.FormulaLocal = "=SOMME(C20:C"+Ltrim(str(19+duree_mois))+")"
' Total principal - doit correspondre au montant emprunte
MaCellule = MaFeuille.getCellByPosition(3,17)
MaCellule.NumberFormat =108
MaCellule.FormulaLocal = "=SOMME(D20:D"+Ltrim(str(19+duree_mois))+")"
' Total echeance
MaCellule = MaFeuille.getCellByPosition(4,17)
MaCellule.NumberFormat =108
MaCellule.FormulaLocal = "=SOMME(E20:E"+Ltrim(str(19+duree_mois))+")"
' Mise en place de la première valeur du restant dû
' Cette valeur ne peut pas être dans la boucle car decalage
MaCellule = MaFeuille.getCellByPosition(1,19)
MaCellule.NumberFormat =108
MaCellule.FormulaLocal = "=B4"
' mise en place de la boucle pour les valeurs du tableau
' Principe:
' Propriété .FormulaLocal pour pouvoir saisir une formule comme dans le classeur
' A noter l'astuce +Ltrim(str... afin de pouvoir faire tourner la valeur de la ligne
' Ex
' =INTPER(B5/12;A"+Ltrim(str(i+20))+";B6*12;B4)
' donnera pour i = 0
' =INTPER(B5/12;A20;B6*12;B4)
Dim i As Integer
for i = 0 to duree_mois-1
MaCellule = MaFeuille.getCellByPosition(0,19+i)
MaCellule.NumberFormat = 1
MaCellule.FormulaLocal=1+i
MaCellule = MaFeuille.getCellByPosition(2,19+i)
MaCellule.NumberFormat = 108
MaCellule.FormulaLocal="=INTPER(B5/12;A"+Ltrim(str(i+20))+";B6*12;B4)"
MaCellule = MaFeuille.getCellByPosition(3,19+i)
MaCellule.NumberFormat = 108
MaCellule.FormulaLocal="=PRINCPER(B5/12;A"+Ltrim(str(i+20))+";B6*12;B4)"
MaCellule = MaFeuille.getCellByPosition(4,19+i)
MaCellule.NumberFormat = 108
MaCellule.FormulaLocal="=VPM(B5/12;B6*12;B4)"
MaCellule = MaFeuille.getCellByPosition(1,20+i)
MaCellule.NumberFormat = 108
MaCellule.FormulaLocal="=B"+Ltrim(str(i+20))+"+D"+Ltrim(str(i+20))+""
Next i
End Sub
'----------------------------------------------------------------------
'----------------------------------------------------------------------
' Script lancé par le bouton Macro - Effacement
Sub Effacement
' Variable specifique au sous programme Effacement
MonDocument = ThisComponent
MaFeuille = MonDocument.CurrentController.ActiveSheet
' Efface les donnees numeriques, alphanumeriques et les formules
MaPlage = MaFeuille.GetCellRangeByName ("A10:E1000")
MaPlage.clearContents(com.sun.star.sheet.CellFlags.VALUE OR _
com.sun.star.sheet.CellFlags.STRING OR _
com.sun.star.sheet.CellFlags.FORMULA)
End Sub
 BTS cours credit.odt | [ ] | 1337 kB |
 info terminé.ods | [ ] | 88 kB |
| test macro classeur credit libreoffice.ods | [ ] | 62 kB |
| test macro classeur credit.ods | [ ] | 34 kB |
