[XL-2007] Calcul de la valeur d'un crédit à 0% avec un autre crédit [Résolu]

[uffo86]

bonjour

après plusieurs jours de recherche et de prise de tête je capitule

je suis artisan en photovoltaïque et j'aurai besoin de vos lumière pour un problème de rentabilité via 2 prêts

en premier lieu ma région fait bénéficier les particuliers veillant s'équiper de panneaux d'un prêt a taux 0% d'une valeur maximum de 8000€ remboursable en 60 mois maximum, ce qui fait 133.33€ par mois pendant 5 ans, or la vente d'électricité rapporte environ 120€ par mois, de plus une installation coûte environ 12500€ (crédit d'impôt déduit) donc il y a un manque a gagner

ensuite le client devra faire un prêt supplémentaire pour la totalité du chantier

voila pour l'explication d'un projet

donc

le gain de la vente d'électricité est d'environ 120€/mois (somme qui changera selon les devis)
une installation coûte environ 12500€ (somme qui changera selon les devis)
les clients ont droit a un crédit à taux 0% sur 60 mois d'un maximum de 8000€ (8000€ pour 60 mois = 133.33€, trop par rapport au gain de la vente)
le reste de l'installation devra être payer via un prêt a taux fixe (variable selon les banques) pour une période la plus courte possible

mon problème est de connaitre la valeur maximum du prêt a 0% qu'il faudra demander pour que les mensualités du prêt a 0% + les mensualités du prêt a taux fixe ne dépasse pas le gain de la vente d'électricité par mois

les valeurs variables seront prisent via un tableau et par liste déroulante

merci d'avance

PS : si l'on devait passer obligatoirement par un code VBA, merci de me préciser la marche a suivre car je n'ai jamais réussi dans le passé

[Pierre Fauconnier]

Bonjour.

mon problème est de connaitre la valeur maximum du prêt a 0% qu'il faudra demander pour que les mensualités du prêt a 0% + les mensualités du prêt a taux fixe ne dépasse pas le gain de la vente d'électricité par mois

Je ne comprends pas bien l'énoncé de ton problème. Ce qui est mis en gras est impossible à résoudre, sauf à décaler dans le temps le remboursement du prêt avec intérêts, ce qui ne me semble pas envisageable.

Si le prêt à 0% ne couvre pas l'entièreté du coût de l'installation, il faudra soit compléter par un emprunt avec intérêts, soit compléter sur fonds propres... Le seul cas où le montant de remboursement pourra être inférieur à la valeur du gain espéré est si tu empruntes moins que 8000 à 0% et que tu complètes sur fonds propres (ce qui, entre nous, a aussi un coût)...


Donc, sauf apport de fonds propres, tu auras TOUJOURS un montant de remboursement plus important que le gain espéré, et il est plus intéressant de prendre l'entièreté du prêt à 0%, et de le compléter pour la différence (12500 - 8000 = 4500) par un emprunt avec intérêts...

[uffo86]

je vais donner un exemple

si je prend 4200€ a taux 0% pendant 60 mois j'ai bien des remboursement de 70€ par mois
il me reste donc 12500-4200=8300 à taux de 3.8% par exemple, ce qui me fait des mensualité d'environ 50€ pendant 20 ans
70+50=120€

donc il reste 125-120=5€ par mois pendant 5 ans soit 3600€ de gagner pendant ces 60 premier mois
puis durant les 15 dernières années il reste 70+5=75€ par mois soit 13500€

donc sur 20 ans on gagne 13500+3600=17100€ en ayant rien déboursé et sachant qu'a la base on aurait du payer 15500€ (sans le crédit d'impôt)
cela fait un rapport de 110%

mais comme le gain change et que certain client voudront peut etre moins de mensualité ou avoir un taux moins important ca ce complique

[Pierre Fauconnier]

OK

Le solveur d'Excel permet d'optimiser cela.

D'abord, poser le décor.

Ensuite, préparer le solveur. La résolution est simple, mais tu peux ajouter des contraintes à l'envi (ou presque)

Lancer le calcul du solveur.

Si une ou plusieurs valeurs changent, tu les modifies dans les cellules et tu relances le solveur.

Tu peux bien entendu adapter la feuille si tu veux "gagner" dès le début.







Attention!!

1. Comme mentionné dans l'illustration, la formule du calcul de la mensualité du prêt bancaire doit être adaptée, celle illustrée sert uniquement à titre d'exemple ;
2. Dans le coût de l'opération, il faut bien entendu tenir compte de l'intérêt bancaire remboursé mensuellement, et donc ne pas confondre le débours (trésorerie) de la rentabilité de l'opération.
3. Avant de proposer ce schéma à un client, assure-toi qu'une banque est d'accord d'étaler sur une aussi longue période un emprunt d'un montant tel que celui calculé, car j'ai des doutes qu'une banque autorise 20 ans pour un emprunt avoisinant les 10.000 euros, mais c'est une autre histoire...

[uffo86]

je ne connaissais pas le solveur d'excel

je vais essayer de voir ce que cela donne avec votre exemple illustrer

pour les mensualité d'un prêt y'a la fonction VPM

merci

[GerardCalc]

Bonsoir,

Sans utilisation du solveur, mais avec des formules.

Je suis parti de chiffres connus :

• Montant total des prêts. (Montant)
• Mensualité maximum. (Mensualité)
• Durée du prêt à taux zéro. (Durée)
• Taux du prêt bancaire. (Taux)

Plus les égalités suivantes :

• Montant = PB + PTZ
• Mensualité = PB * Taux/12 + PTZ / Durée

De ces 2 égalités découle le montant du prêt bancaire :
PB = (Durée * Mensualité - Montant) / (Durée * Taux/12 -1)

A partir de là, d'autres formules feront le reste.

Même si cela n'est pas la politique de ce forum , plutôt que de détailler toutes les formules, voici un fichier joint.

Édition du 17/04 à 10h00 :
Je me rends compte que je n'ai pas expliqué le pourquoi des 2 égalités qui induisent le reste.
Pour la 1ère ce n'est pas vraiment utile : Montant = PB + PTZ
(Le montant total du prêt est la somme des 2 prêts)

Pour la 2è : Mensualité = PB * Taux/12 + PTZ / Durée
L'optimisation du crédit veux que on emboîte les 2 prêts, la mensualité est (quasi) constante jusqu'à la fin du 2nd.

Le montant de cette mensualité est égal au montant des intérêts du prêt bancaire + le remboursement du PTZ.

Pourquoi ?

• Si je rembourse plus que les intérêts du PB, je ne profite pas pleinement du PTZ qui est dans ce cas moins élevé.
• Si je rembourse moins que les intérêts du PB, cela conduit à un amortissement négatif et n'est pas rentable (en + je doute que le banquier voit cela d'un bon oeil).

[uffo86]

bonjour

alors la je suis bluffer

c'est exactement ce que je cherchais

non seulement ça calcul la valeur optimal du PTZ
et en plus ça calcul aussi la durée optimal du PB en réajustant les mensualités a la fin du PTZ

j'ai bon ?

par contre y'a peut être une petite erreur au niveau du calcul de la 2éme mensualité du PB car je pense qu'il faudrait déduire ce qui as dèja été remboursé lors des premier 60 mois au lieu de prendre le montant du départ

[GerardCalc]

Envoyé par uffo86

par contre y'a peut être une petite erreur au niveau du calcul de la 2éme mensualité du PB car je pense qu'il faudrait déduire ce qui as dèja été remboursé lors des premier 60 mois au lieu de prendre le montant du départ

Je ne pense pas. Car lors des 60 premiers mois, seul les intérêts sont payés donc le capital restant du est le même qu'au départ.

[uffo86]

en fait les crédits devrais ce rembourser en même temps

c'est a dire qu'on as le PTZ+PB pendant 60 mois

je ne pense pas que l'on puissent décaler de 60 mois les mensualité d'un prêt, au pire ce sera un décalage de 24 mois