La fonction LOI.LOGNORMALE.N calcule la distribution log-normale, fondamentale pour modéliser les variables strictement positives en finance, économie et assurance. Si tu travailles avec des prix d'actions, des salaires ou des montants de sinistres, cette distribution est ton outil de référence.
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 de la fonction LOI.LOGNORMALE.N
=LOI.LOGNORMALE.N(x; moyenne; écart_type; cumulative)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.
Astuce : Pour calculer la probabilité que X soit supérieur à une valeur seuil s, utilise =1-LOI.LOGNORMALE.N(s; mu; sigma; VRAI). Par exemple, la probabilité qu'un prix dépasse 120 euros.
Exemples pratiques pas à pas
Gérant de portefeuille : modélisation d'un prix d'action (Black-Scholes)
Tu es gérant de portefeuille et tu veux estimer la distribution du prix d'une action dans un an pour calibrer tes positions. Le modèle Black-Scholes suppose que les prix suivent une distribution log-normale avec un rendement attendu (drift) et une volatilité.
Pour une action à 100 euros avec un rendement annuel attendu de 8% et une volatilité de 20%, les paramètres log-normaux sont : mu = ln(100) + 0,08 = 4,69 et sigma = 0,20. Avec ces paramètres, il y a 22% de chances que le prix soit inférieur à 90 euros (perte de 10%), et 33% de chances qu'il dépasse 120 euros (gain de 20%). Le prix médian attendu est 109 euros, légèrement supérieur au prix actuel grâce au drift positif.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Horizon | Prix (euros) | Formule Excel | Résultat |
| 2 | T0 (actuel) | 100 | Prix initial | - |
| 3 | Paramètres T+12 mois | ? | mu = ln(100) + 0,08 = 4,69 | sigma = 0,20 |
| 4 | P(Prix < 90) | =LOI.LOGNORMALE.N(90;4,69;0,2;VRAI) | 0,221 | |
| 5 | P(Prix > 120) | =1-LOI.LOGNORMALE.N(120;4,69;0,2;VRAI) | 0,335 | |
| 6 | Médiane attendue | =EXP(4,69) | 109 euros | |
| 7 | Moyenne attendue | =EXP(4,69+0,2^2/2) | 111 euros |
RH : analyser la distribution des salaires
Tu travailles au service RH d'une entreprise de 500 employés et tu veux analyser la distribution des salaires pour vérifier l'équité et planifier les augmentations. Les salaires suivent typiquement une distribution log-normale : la majorité gagne proche de la médiane, mais quelques hauts salaires tirent la moyenne vers le haut.
En prenant le logarithme des salaires et en calculant moyenne et écart-type sur ces logs, tu obtiens les paramètres de la distribution. La médiane de 36 315 euros représente le salaire typique. 27,3% des employés gagnent plus de 50 000 euros, et 38,4% sont dans la tranche 30k-45k euros. Pour une plage entre deux valeurs a et b, la formule est =LOI.LOGNORMALE.N(b;mu;sigma;VRAI) - LOI.LOGNORMALE.N(a;mu;sigma;VRAI).
| A | B | C | |
|---|---|---|---|
| 1 | Indicateur | Valeur | Formule / Résultat |
| 2 | Salaires observés (n=500) | Collectés | Base de données RH |
| 3 | mu = MOYENNE(LN(salaires)) | 10,50 | Paramètre de position |
| 4 | sigma = ECARTYPE(LN(salaires)) | 0,45 | Paramètre de dispersion |
| 5 | Médiane théorique | 36 315 euros | =EXP(10,50) |
| 6 | P(Salaire > 50 000) | 0,273 | =1-LOI.LOGNORMALE.N(50000;10,5;0,45;VRAI) |
| 7 | P(Salaire entre 30k et 45k) | 0,384 | =LOI.LOGNORMALE.N(45000;...)-LOI.LOGNORMALE.N(30000;...) |
Astuce de pro : Pour calculer mu et sigma à partir de tes données, utilise =MOYENNE(LN(A2:A501)) et =ECARTYPE(LN(A2:A501)). Ces deux cellules suffisent à paramétrer toutes tes formules LOI.LOGNORMALE.N.
Actuaire : provisionnement de sinistres en assurance
Tu es actuaire et tu dois calibrer les provisions pour sinistres d'une compagnie d'assurance. Les montants de sinistres suivent généralement une distribution log-normale : beaucoup de petits sinistres et quelques sinistres majeurs qui dominent le coût total.
Pour les sinistres auto (mu=7,2, sigma=1,1), la médiane est 1 339 euros mais la moyenne est 2 421 euros (1,8 fois plus élevée) à cause des sinistres graves. La provision au quantile 95% de 8 103 euros garantit une couverture suffisante dans 95% des cas. 6,3% des sinistres dépassent 10 000 euros, ce qui oriente le niveau des franchises et des réassurances. Pour le quantile, utilise la fonction inverse =LOI.LOGNORMALE.INVERSE.N(probabilité; mu; sigma).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Type sinistre | mu (log) | sigma (log) | Médiane | Moyenne |
| 2 | Sinistres automobiles | 7,2 | 1,1 | 1 339 euros | 2 421 euros |
| 3 | Sinistres habitation | 7,8 | 1,3 | 2 440 euros | 5 294 euros |
| 4 | P(sinistre > 10 000 euros) auto | =1-LOI.LOGNORMALE.N(10000;7,2;1,1;VRAI) | 0,063 | 6,3% | |
| 5 | Provision 95% auto | =LOI.LOGNORMALE.INVERSE.N(0,95;7,2;1,1) | 8 103 euros | Quantile 95% |
Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
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 sur la fonction LOI.LOGNORMALE.N
Pourquoi utiliser la distribution log-normale plutôt que la distribution normale ?
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.
Comment calculer les paramètres mu et sigma à partir de données observées ?
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).
Quelle est la relation entre les paramètres et les statistiques de X ?
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.
Comment la distribution log-normale est-elle utilisée en finance ?
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.
Quelle différence entre LOI.LOGNORMALE et LOI.LOGNORMALE.N ?
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
Les fonctions similaires : LOI.LOGNORMALE.INVERSE.N, LOI.NORMALE.N, LOI.NORMALE, LN, EXP
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois