Fonction "Délai de récupération

[Arnaud.C]

Bonjour,

Après maintes vaines tentatives de recherche sur la toile ou d’essai avec mes maigres connaissances en VBA, je m’en remets aux experts.

Je vous explique : j’ai besoin d’une formule assez souple pour calculer un délai de récupération (DR, un indicateur financier) qui n’existe pas dans les fonctions Excel. Le résultat à l’arrivée est de la forme « 3 an(s) 234 jour(s) ».

Le principe, c’est de pouvoir sélectionner des cases en lignes, par exemple A1 : D1, il s’agit de la seule variable (cumul de cash flows, actualisés ou non) mais il faut pouvoir sélectionner en ensemble de cellules en ligne et, rien que ça, ça dépasse mes compétences en VBA…

La formule doit effectuer ces actions :

On part du principe DR = 0.
Si la première cellule de l’ensemble est inférieure à 0, on passe à la deuxième cellule, si cette 2ème cellule est négative, on fait DR + 1 … et l’opération se répète à la 3ème cellule, etc. Si la 1ère cellule est 0 ou >0, on a DR = « immédiat ».
Dès que la fonction atteint la première cellule positive, elle enregistre DR temporairement sous X

Ex : Si on a sélectionné les cellules A1 : D1 comme cash flows, et que B1 est positive, alors DR= 0. Si c’est D1 la première positive, on a DR=1.

Pour la précision en jour, on souhaite une estimation linéaire donc la fonction fait ensuite :
(abs(dernière cellule négative)/(abs(dernière cellule négative)+première cellule positive))*365
Elle enregistre le résultat sous Y

Ex : toujours avec le même, si B1 première positive, on aura
abs(A1)/(abs (A1)+B1),
si C1 première positive, on aura
abs (B1)/(abs(B1)+C1)

Résultat, dans le case à la fin, on voit apparaître X an(s) Y jour(s)
Ouf, voilà, c’est compliqué… c’est pour ça que je le fais à la main pour le moment mais ça me prend bien 2 minutes à chaque fois.

Je n’ai aucune idée de comment faire… s’il y a ici des petits génies de la macro, je vous soumets mon problème…

Merci de votre aide,

Arnaud.

 

[mutzik]

Re : Fonction "Délai de récupération

bonjour,

c'est faisable, mais il faudrait un fichier exemple avec plusieurs lignes de données et le résultat correspondant
sinon dans :
(abs(dernière cellule négative)/(abs(dernière cellule négative)+première cellule positive))*365
dernière cellule neg correspond au nr de colonne : par ex si C1 = -8 alors dernière cell negative = 3 (pour colonne 3) ?

 

[ROGER2327]

Re : Fonction "Délai de récupération

Bonjour à tous

Pas sûr d'avoir tout compris. Un petit classeur comme support eût été le bienvenu... À tout hasard, voyez le classeur joint.​

ROGER2327
#3690

Lundi 2 Gidouille 137 (Saint Lucullus, amateur (Bloosday), SQ)
28 Prairial An CCXVIII
2010-W24-3T09:51:55Z

 

[Arnaud.C]

Re : Fonction "Délai de récupération

(abs(dernière cellule négative)/(abs(dernière cellule négative)+première cellule positive))*365
dernière cellule neg correspond au nr de colonne : par ex si C1 = -8 alors dernière cell negative = 3 (pour colonne 3) ?

Non, dernière cellule négative correspond au contenu de la dernière cellule négative (-8 dans ton exemple). La conversion de la différence entre le dernier contenu négatif et le premier contenu positif se fait donc en linéaire et est converti en jours.

Ex: ( abs(-35)/(abs(-35)+10) ) * 365 = 284 jours

Quant au commentaire précédent, le fichier temp est sur la bonne voie mais il y a un hic: le décompte du DR ne commence qu'à la deuxième année (donc deuxième cellule). Si la deuxième cellule est toujours négative, alors DR = 1... en effet, on considère en finance que la première année est en fait l'année 0 et que le calcul du payback commence en fait au 1er janvier de l'année suivante (la deuxième cellule en somme).

Je joins un fichier avec un exemple. Comme vous le constatez, j'ai trouvé un ersatz de formule mais je dois la modifier à chaque nouveau projet car il y a plein de variables liées au projet que je dois prendre en compte... alors qu'avec une fonction macro je n'aurais pas ce souci !

 

[ROGER2327]

Re : Fonction "Délai de récupération

Re...

J'ai modifié la pièce jointe...

ROGER2327
#3693

Lundi 2 Gidouille 137 (Saint Lucullus, amateur (Bloosday), SQ)
28 Prairial An CCXVIII
2010-W24-3T13:05:26Z

 

[Arnaud.C]

Re : Fonction "Délai de récupération

Voilà, merci Roger, c'est exactement ce qu'il me faut, la formule est juste.

Ah, juste une chose, est-il possible que la fonction indique "Jamais" si la fonction arrive au bout de la ligne des cash-flows et tombe sur une case vide (puisqu'il n'a trouvé aucune année où le cash flow devient positif, il n'y a donc pas de récupération) ?

Maintenant,si je veux en faire une vraie fonction que j'appelle DR et que l'utilisateur n'ait à sélectionner que le cash flow de l'"année 0" ( en gros ça donne DR(cash_flow) ), qu'est ce que ça donne sur VBA ?

Le problème c'est que je peux remplacer tes A1 dans la formule par cash_flow mais je sens que ça ne marchera pas aussi facilement...

 

[ROGER2327]

Re : Fonction "Délai de récupération

Re...

Peut-être ceci ?

[COLOR="DarkSlateGray"][B]=SI(NB.SI(A1:L1;">0");SI(A1;MIN(SI(A1:L1>1;COLONNE(A1:L1);255))-2+(DECALER(A1;;MIN(SI(A1:L1>0;COLONNE(A1:L1);255))-2)>0)&" a. "&ARRONDI(365*DECALER(A1;;MIN(SI(A1:L1>0;COLONNE(A1:L1);255))-2)/(DECALER(A1;;MIN(SI(A1:L1>0;COLONNE(A1:L1);255))-2)-DECALER(A1;;MIN(SI(A1:L1>0;COLONNE(A1:L1);255))-1));1)&" j.";"Immédiat");"Jamais")[/B][/COLOR]

ROGER2327
#3695

Lundi 2 Gidouille 137 (Saint Lucullus, amateur (Bloosday), SQ)
28 Prairial An CCXVIII
2010-W24-3T13:46:58Z

 

[Arnaud.C]

Re : Fonction "Délai de récupération

Aïe, non, j'ai remplacé dans ton fichier la formule par la nouvelle mais elle ne marche pas... au pire, ce n'est pas trop grave si tu n'y arrives pas.

En fait, ce que j'aimerais pouvoir faire, c'est créer une fonction en VBA où l'utilisateur inscrit le premier cash flow (ta case A1) et le dernier (la case L1), et paf, le DR apparait. Cependant, mes connaissances sont limitées en VBA (j'ai codé un peu en turbo pascal en prépa... c'est dire). Y'a t'il un convertisseur ?

Merci de ton aide en tout cas !

 

[ROGER2327]

Re : Fonction "Délai de récupération

Re...

J'ai vérifié la formule dans mon fichier : elle fonctionne... ...à condition de la valider en tant que formule matricielle. Mais passons...

Une solution en VisualBasic :

[COLOR="DarkSlateGray"][B]Function DR(r As Range)
Dim i&
   For i = 1 To r.Count
      If Not IsEmpty(r.Item(i)) Then
         If IsNumeric(r.Item(i).Value) Then
            If r.Item(i).Value >= 0 Then Exit For
         End If
      End If
   Next i
   Select Case i
      Case 1: DR = "Immédiat"
      Case Is > r.Count: DR = "Jamais"
      Case Else
         If r.Item(i).Value = 0 Then
            DR = i - 1 & " an" & IIf(i > 2, "s", "") & " 0 j."
         Else
            DR = i - 2 & " an" & IIf(i > 3, "s ", " ") & _
               Round(365 * r.Item(i - 1).Value / (r.Item(i - 1).Value - r.Item(i).Value), 1) & " j."
         End If
   End Select
End Function[/B][/COLOR]

ROGER2327
#3696

Lundi 2 Gidouille 137 (Saint Lucullus, amateur (Bloosday), SQ)
28 Prairial An CCXVIII
2010-W24-3T15:32:05Z

 

[Arnaud.C]

Re : Fonction "Délai de récupération

Ok, merci mille fois ! Ca marche du tonnerre !

Tu n'imagines pas à quel point cette formule va me simplifier la vie ainsi que celle de mon service... et, en définitive, combinée à d'autres mesure, permettre des économies de plusieurs millions d'euros à terme grâce à une plus grande rigueur d'étude de rentabilité des investissements !

S'il existe une base de données du site avec toutes les fonctions et autres macros codées par les membres, ça vaut le coup de rajouter celle-ci !

 

[Benjy]

Re : Fonction "Délai de récupération

Des économies de plusieurs millions d'euros ? ROGER le sauveur du capitalisme.. . Si c'est bien sa il à droit à sa part du butin non ? Ah mais j'oubliais.. dans se système ceux qui travaille ne sont pas ceux qui récolte les sousous. Désolé pour toi ROGER

 

[Celynec0]

Re : Fonction "Délai de récupération

Bonjour, je dois calculer le délai de récupération du capital investi avec le langage vba je ne sais pas comment faire pouvez vous m'aider ?