Qu'est-ce que la fonction LOI.
LOI.LOGNORMALE.N te sert quand tes données ne peuvent physiquement jamais être négatives, un prix, un salaire, un montant de sinistre, mais que leur distribution penche fortement d'un côté avec quelques valeurs extrêmes qui tirent la moyenne vers le haut. Une loi normale classique donnerait ici des résultats aberrants, en admettant des valeurs négatives qui n'existent pas dans la réalité.
Le principe est simple : si X suit une loi log-normale, alors le logarithme naturel de X suit une loi normale de paramètres mu et sigma. Cette construction mathématique garantit que X est toujours positif et capture l'asymétrie typique des distributions économiques où la moyenne dépasse la médiane. Tu retrouves cette logique au cœur du modèle Black-Scholes pour la valorisation des options.
Syntaxe
=LOI.LOGNORMALE.N(x; moyenne; écart_type; cumulative)Clique sur un argument pour aller à son explication.
Les paramètres moyenne et écart_type caractérisent la distribution de ln(X), pas de X lui-même. La médiane de X est e^mu, la moyenne de X est e^(mu + sigma²/2). Pour sigma > 0, la moyenne de X est toujours supérieure à sa médiane.
Comprendre chaque paramètre de la fonction LOI.LOGNORMALE.N
Les quatre arguments s'enchaînent dans un ordre fixe : la valeur x que tu testes, puis moyenne (le mu de ln(X)) et écart_type (le sigma de ln(X)), et enfin cumulative qui décide entre la probabilité cumulée (VRAI) et la densité (FAUX). Aucun n'est facultatif ici, et c'est cumulative qu'on oublie le plus : sans lui, la formule ne sait pas si tu veux P(X ≤ x) ou f(x).
Attention au piège des deux arguments du milieu : moyenne et écart_type décrivent ln(X), pas tes données brutes. Tu les obtiens avec MOYENNE(LN(plage)) et ECARTYPE(LN(plage)).
x
: la valeur pour laquelle tu calcules la probabilitéDoit être strictement positive (> 0). Elle représente typiquement un prix d'actif, un salaire, un montant de sinistre ou toute variable économique positive.
Attention : Si x est nul ou négatif, Excel retourne #NOMBRE!. La distribution log-normale est définie uniquement pour des valeurs strictement positives, car ln(x) n'existe pas pour x ≤ 0.
moyenne
: la moyenne mu du logarithme de X, pas de X directementC'est le paramètre de position de la distribution normale sous-jacente de ln(X). Peut être positif, négatif ou nul.
Pour estimer mu à partir de tes données, calcule =MOYENNE(LN(plage_données)). Ne confonds pas avec =MOYENNE(plage_données) qui est la moyenne de X et non de ln(X).
écart_type
: l'écart-type sigma du logarithme de XDoit être strictement positif (> 0). Il contrôle la dispersion et l'asymétrie de la distribution log-normale : un sigma élevé produit une distribution très asymétrique avec une longue queue vers les grandes valeurs.
Pour estimer sigma à partir de tes données, calcule =ECARTYPE(LN(plage_données)).
Attention : Un écart-type nul ou négatif génère #NOMBRE!. Si tu obtiens sigma = 0, cela signifie que toutes tes données sont identiques, ce qui est rare en pratique.
cumulative
: vRAI pour la fonction de répartition P(X ≤ x) : la probabilité que X soit inférieur ou égal à xFAUX pour la densité de probabilité f(x).
Utilise VRAI dans la quasi-totalité des cas pratiques : probabilités d'un prix en dessous d'un seuil, calcul de quantiles de risque, provisionnement. FAUX est réservé à des usages actuariels ou statistiques avancés.
Exemples pratiques pas à pas
Comment calculer la probabilité cumulée avec la fonction LOI.LOGNORMALE.N
Tu prépares le pricing d'une option et tu dois chiffrer la probabilité que le sous-jacent finisse sous son prix d'exercice, sinon la prime que tu proposes ne repose sur rien. Ton tableau exprime chaque niveau en multiple du cours actuel, avec une dérive de 0,08 et une volatilité de 0,25 estimées sur le logarithme des rendements. La fonction LOI.LOGNORMALE.N rend cette probabilité directement.
- 1Dans une cellule, écris
=LOI.LOGNORMALE.N(. - 2En 1er argument : clique sur la cellule du multiple à tester,
B2. Elle vaut1,2sur la première ligne et se passe telle quelle, sans en prendre le logarithme au préalable, car la fonction s'en charge elle-même. - 3En 2ᵉ argument : saisis la moyenne du logarithme du multiple,
0,08. Elle décrit ln(X) et non le multiple lui-même. C'est la confusion classique sur cette fonction, et elle ne déclenche aucune erreur, elle se contente de fausser le résultat. - 4En 3ᵉ argument : saisis l'écart-type du même logarithme,
0,25. C'est la volatilité, qui doit rester strictement positive sous peine de#NOMBRE!. - 5En 4ᵉ argument : saisis
VRAIpour obtenir l'aire cumulée sous la courbe. C'est le seul argument que la version historique de la fonction n'accepte pas, et c'est lui qui ouvre le mode densité exploité à l'exemple suivant. - 6Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas. Tout multiple nul ou négatif renvoie
#NOMBRE!, ce qui est logique pour une grandeur qui ne peut pas passer sous zéro.
=LOI.LOGNORMALE.N(B2; 0,08; 0,25; VRAI)65,9 % de la première ligne donnent la probabilité que le sous-jacent reste sous 1,2 fois son cours actuel. La ligne 1,00 en dit long avec ses 37,4 % : la probabilité de terminer sous le cours de départ n'atteint pas la moitié, parce que la distribution penche d'un côté au lieu d'être symétrique.
Comment obtenir la densité au lieu de la probabilité avec la fonction LOI.LOGNORMALE.N
Tu dois tracer la distribution attendue du sous-jacent pour la présenter au comité des risques, et une colonne de probabilités cumulées ne montrera jamais la forme de cette distribution. Il te faut la hauteur de la courbe en plusieurs points pour alimenter un graphique. C'est le dernier argument de la fonction LOI.LOGNORMALE.N qui commande ce changement.
- 1Dans une cellule, écris
=LOI.LOGNORMALE.N(. - 2En 1er argument : clique sur la cellule du multiple,
B2. La distribution décrite reste celle de l'exemple précédent, seul le mode de lecture va changer. - 3En 2ᵉ argument : saisis la moyenne du logarithme,
0,08, inchangée par rapport à l'exemple 1. - 4En 3ᵉ argument : saisis l'écart-type du logarithme,
0,25, lui aussi inchangé. - 5En 4ᵉ argument : saisis
FAUX. C'est la seule modification par rapport à l'exemple précédent, et elle suffit à faire basculer la fonction, qui cesse d'additionner l'aire pour rendre la densité au point demandé. Ne convertis jamais cette densité en pourcentage, car elle peut dépasser 1, comme le1,5161de la ligne1. - 6Ferme la parenthèse et appuie sur Entrée, puis recopie vers le bas. La densité s'écrase à
0,0394en2, soit près de quarante fois moins qu'en1, ce qui traduit la longue traîne de la distribution vers les valeurs hautes.
=LOI.LOGNORMALE.N(B2; 0,08; 0,25; FAUX)FAUX fait rendre la hauteur de la courbe au lieu de l'aire accumulée, et cette possibilité est propre à la fonction LOI.LOGNORMALE.N : sa version historique n'accepte que trois arguments et refuse ce mode. La ligne 1 le prouve d'une façon frappante avec sa densité de 1,5161, une valeur supérieure à 1 qui serait impossible pour une probabilité. Une densité se compare d'un point à l'autre, elle ne se lit jamais comme un pourcentage.
Comment calculer la probabilité d'un intervalle avec la fonction LOI.LOGNORMALE.N
Ta stratégie ne gagne que si le sous-jacent atterrit dans une fourchette précise, ni trop bas ni trop haut, et tu dois chiffrer les chances que cela arrive avant d'engager la position. Une probabilité cumulée seule ne répond pas, parce qu'elle part toujours du bas de la distribution et non de ta borne basse. Deux appels encadrent la fourchette et leur différence donne la réponse.
- 1Dans une cellule, écris
=LOI.LOGNORMALE.N(. - 2En 1er argument : clique sur la cellule de la borne haute,
C2. Ce premier appel mesure toute l'aire située sous cette borne, c'est-à-dire la probabilité que le multiple reste en dessous. - 3En 2ᵉ argument : saisis la moyenne du logarithme,
0,08, identique pour les deux bornes. - 4En 3ᵉ argument : saisis l'écart-type du logarithme,
0,25, lui aussi commun aux deux appels. - 5En 4ᵉ argument : saisis
VRAIpour rester en probabilité cumulée, le seul mode qui donne une aire à retrancher d'une autre. - 6Ferme la parenthèse, puis complète par
-LOI.LOGNORMALE.N(B2; 0,08; 0,25; VRAI)pour retrancher l'aire sous la borne basseB2, et appuie sur Entrée. La soustraction ne conserve que la bande comprise entre les deux bornes, et l'ordre est imposé, borne haute moins borne basse, sinon le résultat devient négatif. Recopie enfin vers le bas. Aucune ligne n'atteint 100 %, puisqu'il reste toujours une part de scénarios en dehors de la fourchette, même la plus large.
=LOI.LOGNORMALE.N(C2; 0,08; 0,25; VRAI)-LOI.LOGNORMALE.N(B2; 0,08; 0,25; VRAI)99,2 % des cas entre la moitié et le double du cours, ce qui montre à quel point les scénarios extrêmes sont rares avec cette volatilité. Comparer SC-02 et SC-05 chiffre l'apport de la borne haute : élargir de 1,2 à 1,5 fait passer la probabilité de 28,4 % à 52,9 %.
Les erreurs fréquentes avec la fonction LOI.LOGNORMALE.N
Deux des ennuis ci-dessous renvoient le même code #NOMBRE! : un x à zéro ou négatif (impossible, puisque ln(x) n'existe pas pour x ≤ 0) et un écart_type nul ou négatif. À chaque fois, c'est une valeur d'entrée qui sort du domaine de la log-normale, pas une faute de syntaxe.
Les deux autres pièges ne déclenchent aucune erreur mais faussent tes résultats en silence : passer la moyenne de X au lieu de celle de ln(X), et rapporter la moyenne comme valeur typique alors qu'elle est tirée vers le haut par les extrêmes. C'est la médiane (e^mu) qu'il faut citer.
Erreur #NOMBRE! avec x négatif ou nul
La distribution log-normale est définie uniquement pour des valeurs strictement positives, car ln(x) n'existe pas pour x ≤ 0. Si x vaut 0 ou un nombre négatif, Excel retourne #NOMBRE!.
Solution : Vérifie que tes données sont strictement positives avant de les passer à la fonction. Si nécessaire, utilise =MAX(x;0,0001) comme garde-fou pour éviter l'erreur sur des valeurs proches de zéro.
Erreur #NOMBRE! avec écart-type nul ou négatif
L'écart-type doit être strictement positif. Une valeur nulle signifie qu'il n'y a aucune dispersion dans tes données (toutes identiques), ce qui est un cas dégénéré que la fonction ne peut pas traiter.
Solution : Vérifie ton calcul d'écart-type. Si tu obtiens 0, tes données d'entrée sont toutes identiques. Dans ce cas, la distribution est concentrée en un seul point et LOI.LOGNORMALE.N ne s'applique pas.
Confusion entre paramètres de X et de ln(X)
Erreur fréquente : utiliser directement la moyenne et l'écart-type des données brutes au lieu de ceux de leur logarithme. Les paramètres mu et sigma sont ceux de ln(X), pas de X.
Solution : Calcule toujours mu = MOYENNE(LN(données)) et sigma = ECARTYPE(LN(données)). Ne confonds pas avec MOYENNE(données) et ECARTYPE(données) qui sont les statistiques de X et produiront des résultats incorrects.
Utiliser la moyenne au lieu de la médiane comme valeur typique
Rapporter la moyenne comme valeur typique est trompeur pour une log-normale car elle est tirée vers le haut par les valeurs extrêmes (quelques grands salaires, quelques gros sinistres). La médiane est toujours inférieure à la moyenne pour une log-normale.
Solution : Utilise la médiane (e^mu) comme mesure centrale. Précise toujours quelle statistique tu rapportes : "le salaire médian est de 36k euros" versus "le salaire moyen est de 42k euros". Les deux chiffres sont justes mais leur interprétation est différente.
Questions fréquentes
La distribution normale peut prendre des valeurs négatives, ce qui est impossible pour des variables comme les prix, salaires ou sinistres. La log-normale est toujours positive, capture l'asymétrie typique de ces variables (queue étendue vers les grandes valeurs), et modélise naturellement les phénomènes multiplicatifs. C'est pourquoi elle est le choix standard en finance pour les prix d'actifs.
Les paramètres mu et sigma sont la moyenne et l'écart-type du logarithme des données, pas des données brutes. Calcule mu = MOYENNE(LN(données)) et sigma = ECARTYPE(LN(données)) dans Excel. À partir de ces paramètres, tu peux calculer les statistiques de X : médiane = e^mu, moyenne = e^(mu + sigma²/2).
Pour une variable X log-normale de paramètres mu et sigma : médiane de X = e^mu, moyenne de X = e^(mu + sigma²/2), mode de X = e^(mu - sigma²). La médiane est toujours inférieure à la moyenne pour une log-normale, l'écart croissant avec sigma. C'est pourquoi la moyenne des salaires est souvent significativement supérieure à la médiane.
Le modèle Black-Scholes suppose que les prix des actions suivent une distribution log-normale car les rendements logarithmiques sont normalement distribués. Cela garantit des prix toujours positifs et capture le fait que les variations sont proportionnelles au niveau de prix actuel. Les paramètres sont mu = ln(S0) + (r - sigma²/2) × T et sigma × racine(T), où S0 est le prix actuel, r le taux sans risque et T l'horizon.
LOI.LOGNORMALE est l'ancienne fonction Excel (avant 2010) qui ne calculait que la fonction de répartition cumulative. LOI.LOGNORMALE.N est la version moderne avec un paramètre cumulative permettant aussi de calculer la densité de probabilité f(x) avec FAUX. Les résultats pour le mode cumulatif (VRAI) sont identiques. La version .N suit la nouvelle nomenclature Excel standardisée.
Pour aller plus loin
Continue sur ta lancée après la fonction LOI.LOGNORMALE.N : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
