Discussion :

[amayze]

Bonjour à tous,
J'ai réalisé dans excel un tableau qui à partir de certains input (montant prêt, taux d'intérêt, durée, durée à la restitution anticipée, etc...) calcule un tableau d'amortissement et en déduis une répartition des indemnités de résiliation entre Capital et Intérêts.
ça marche sous excel.
Seulement j'aimerais le transformer en fonction pour pouvoir effectuer des calculs de masse dans une base access.
Quelqu'un pourrait il m'aider?
Merci beaucoup
PS: j'ai joint le fichier excel

[random]

Si on nomme
Mt le montant du capital 37000
Taux le taux du leasing 6.5%
Dur le nombre de loyers mensuels 24
Valresi la valeur résiduelle 18500
Tauxrefi le taux de refinancement 4%
Restit la période de remboursement anticipé 21
Tauxpen le taux appliqué au remboursement restant 6%

On commence par calculer

Pmt=

Code :

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

VPM(taux/12;dur;-mt;valresi;1)

Puis la marge sur interet totale mintot

Code :

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

=((pmt*dur)-(mt-valresi))*(taux-tauxrefi)/taux

Puis la marge sur interet de la periode mintper

Code :

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

=((VC(taux/12;restit;pmt;-mt;1)-mt)+(pmt*restit))*(taux-tauxrefi)/taux

Puis la penalite penal

Code :

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

=((VC(taux/12;restit;pmt;-mt;1)))*tauxpen

Part Marge Intérêts IR pmint=

Code :

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

SI((mintot-mintper)>=penal;penal;mintot-mintper)

Part Capital IRA =

Code :

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

penal-pmint

En visual basic cela donnerait

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
Option Explicit
Function fmintot(taux As Double, dur As Integer, mt As Double, valresi As Double, tauxrefi As Double) As Double
Dim loyer As Double
loyer = Pmt(taux / 12, dur, -mt, valresi, 1)
fmintot = ((loyer * dur) - (mt - valresi)) * (taux - tauxrefi) / taux
End Function
 
Function fmintper(taux As Double, dur As Integer, mt As Double, valresi As Double, tauxrefi As Double, restit As Integer) As Double
Dim loyer As Double
loyer = Pmt(taux / 12, dur, -mt, valresi, 1)
fmintper = ((FV(taux / 12, restit, loyer, -mt, 1) - mt) + (loyer * restit)) * (taux - tauxrefi) / taux
End Function
 
Function fpenal(taux As Double, dur As Double, mt As Double, valresi As Double, restit As Double, tauxpen As Double) As Double
Dim loyer As Double
loyer = Pmt(taux / 12, dur, -mt, valresi, 1)
fpenal = ((FV(taux / 12, restit, loyer, -mt, 1))) * tauxpen
End Function

Nb à mon sens ce calcul est affecté d’une grave erreur de raisonnement, en effet faire marge=interet –(interet/taux*tauxrefi) c’est considérer que la structure d’amortissement du refinancement est identique à celle du leasing ce qui est faux

[amayze]

Merci beaucoup mais qu'est ce que tu me propose alors?
Merci

[random]

je te propose de faire 2 tableaux de mise en loyer

un au taux contractuel
un au taux de refinancement

et de calculer les marges périodes et cumul par différence entre ces deux tableaux et non par règle de trois

évidemment cela ne va pas changer la marge finale mais sa répartition dans le temps sera altérée

[amayze]

Merci
Mais donc du coup le code n'est plus bon alors?
Puisqu'il faut générer les remboursements totaux
Comment faire?
Merci

[random]

j'ai refait les calculs tu peux conserver ta méthode les différences sont infimes
tu peux utiliser mes formules

[philben]

bonjour random,
Mis à part de retourner à mes chères études , quel livre de chevet ou site me conseilles-tu pour que je puisse potentiellement espérer un jour, décrypter ce jargon et ces formules financières ?

amicalement,

Philippe

[random]

En ce qui concerne les formules financières, je te conseille l’aide d’excel qui est très bien faite.

Ceci étant toutes les mathématiques financières en univers certain reposent sur une seule formule, nommée théorème du transport (le joli nom qui évoque le déduit) à savoir vf=vp(1+t)^n que je te fournis gracieusement, pour les univers incertains on ajoute un nuage de calcul probabiliste, mais ajouter un nuage avec ce temps pourri est sacrilège.

Pour ce qui concerne le crédit bail (leasing) le jargon est limité on parle de loyer, (annuité en matière de crédit) qui est économiquement composé de deux flux (le remboursement de la dette et celui de l’intérêt).

Le problème ici posé se compliquait d’un refinancement, pour schématiser le banquier (ou loueur) emprunte au marché financier le montant du bien qu’il va louer.

Comme tu vois, tout ceci est assez basique, mais les financiers masquent cette simplicité par un jargon qui permet de mieux justifier leurs tarifs, ainsi as-tu deviné qu’ici le jeu consistait à demander de façon injustifiée 6% du montant prêté en cas de remboursement anticipé ?

[philben]

re bonjour Random et merci pour ton temps.

ainsi as-tu deviné qu’ici le jeu consistait à demander de façon injustifiée 6% du montant prêté en cas de remboursement anticipé ?

Avec le jargon d'un béotien ,
si j'ai bien compris, un taux de remboursement anticipé qui entraine une <Part Capital IRA> supérieure à zéro, indique que le banquier gagne encore plus d'argent à l'arrêt anticipé du prêt que si celui-ci était arrivé à terme.

Le taux de refinancement interne indique le taux d'achat pour le banquier de l'argent prêté à son client à un taux plus élevé bien sûr

J'espère que je ne suis pas totalement à côté de la plaque...

Faut dire que depuis que mon ex-conseiller financier (mon coiffeur en faite) m'avait conseillé d'acheter des SICAV en août 2001, la finance me donne des boutons.
Je parle de mon ex-conseiller financier, non pas que j'ai changé de coiffeur ou que j'ai décidé de prendre en main personnellement le destin de mes finances et de mon cuir chevelu , mais depuis cette date mémorable il s'est reconverti spontanément en météorologue du quartier, sans beaucoup plus de succès quant à la qualité de ces prédictions...

Bon j'arrête là sinon je vais me faire délester par les modérateurs, un comble après ce je viens de raconter !

amicalement,

Philippe