Qu'est-ce que la fonction LOI.
LOI.LOGNORMALE.INVERSE.N (LOGNORM.INV en anglais) te sert dès que tu dois transformer un niveau de confiance abstrait en un chiffre concret sur lequel t'engager : un seuil de prix, une réserve de capital, une perte maximale à ne pas dépasser. C'est la fonction à sortir quand une probabilité seule ne suffit plus et qu'il te faut la valeur qui va avec.
Si tu travailles en finance, en assurance ou dans l'analyse des revenus et des prix, cette fonction répond à des questions comme : "Quel est le prix en dessous duquel se situent 95 % des transactions ?" ou "Quelle est la perte maximale avec 99 % de confiance ?" La distribution log-normale est omniprésente partout où les variables sont strictement positives et présentent une asymétrie vers la droite : prix d'actions, revenus des ménages, montants de sinistres, durées de vie de composants.
Syntaxe
=LOI.LOGNORMALE.INVERSE.N(probabilité; moyenne; écart_type)Clique sur un argument pour aller à son explication.
Comprendre chaque paramètre de la fonction LOI.LOGNORMALE.INVERSE.N
Les trois arguments s'enchaînent dans un ordre fixe et sont tous obligatoires : d'abord la probabilité visée, puis la moyenne, enfin l'écart-type. La probabilité doit rester strictement entre 0 et 1 (jamais les bornes), et l'écart-type strictement positif, sinon tu tombes sur #NOMBRE!.
Garde en tête que moyenne et écart_type décrivent ln(X), pas ta variable brute : c'est le détail qui fait basculer un résultat correct vers un chiffre aberrant.
probabilité
: la probabilité associée à la distribution log-normale, c'est-à-dire P(X ≤ x)Cette valeur doit être strictement comprise entre 0 et 1 (les valeurs 0 et 1 exactes provoquent une erreur car elles correspondraient respectivement à 0 et à l'infini).
En finance, tu utiliseras souvent des probabilités comme 0,01, 0,05 ou 0,10 pour les calculs de Value at Risk. Une probabilité de 0,01 te donne le 1er percentile : la valeur en dessous de laquelle se situent seulement 1 % des observations. Pour les analyses de revenus, tu utiliseras plutôt 0,50 (médiane), 0,90 ou 0,95.
Attention : Les valeurs exactes 0 et 1 provoquent #NOMBRE!. Si tu obtiens ces valeurs à partir de calculs, utilise 0,0001 et 0,9999 comme approximations pratiques.
moyenne
: la moyenne de ln(X), c'est-à-dire la moyenne du logarithme naturel de ta variableAttention : ce n'est pas la moyenne de X directement. Ce paramètre est souvent noté mu dans la littérature statistique. Il peut être positif, négatif ou nul.
Pour estimer ce paramètre à partir de données réelles, calcule : =MOYENNE(LN(A1:A100)). La médiane de la distribution log-normale est simplement =EXP(mu), ce qui te donne une interprétation intuitive : si mu = 10,5, la médiane est e puissance 10,5, soit environ 36 300.
écart_type
: l'écart-type de ln(X), c'est-à-dire l'écart-type du logarithme naturel de ta variableCe paramètre doit être strictement positif. Il est souvent noté sigma et mesure la dispersion des log-valeurs. Un sigma plus grand signifie une distribution plus étalée avec une queue droite plus longue.
Pour estimer ce paramètre à partir de données réelles : =ECARTYPE.STANDARD(LN(A1:A100)). En finance, sigma représente souvent la volatilité des log-rendements, un indicateur clé du risque.
Attention : Un écart_type nul ou négatif provoque #NOMBRE!. Si =ECARTYPE.STANDARD(LN(A:A)) retourne 0, tes données ne sont pas assez dispersées pour justifier un modèle log-normal.
Exemples pratiques pas à pas
Comment calculer un quantile de risque avec la fonction LOI.LOGNORMALE.INVERSE.N
Tu dois dimensionner la réserve de capital d'une position et annoncer la perte que tu ne dépasseras qu'exceptionnellement, faute de quoi ta réserve sera arbitraire. Ton tableau fixe pour chaque position le seuil de probabilité retenu, avec une dérive quotidienne de 0,0005 et une volatilité de 0,02 estimées sur le logarithme des rendements. La fonction LOI.LOGNORMALE.INVERSE.N rend le multiple du cours correspondant à ce seuil.
- 1Dans une cellule, écris
=LOI.LOGNORMALE.INVERSE.N(. - 2En 1er argument : clique sur la cellule du seuil de risque retenu,
B2. Elle affiche0,01sur la première position, un seuil qui correspond à une lecture à 99 % de confiance puisqu'il désigne le pire centième des séances. - 3En 2ᵉ argument : saisis la dérive quotidienne,
0,0005. Elle porte sur le logarithme du rendement et non sur le rendement lui-même, ce qui explique entièrement sa valeur minuscule à côté du résultat. - 4En 3ᵉ argument : saisis la volatilité quotidienne,
0,02. Elle doit rester strictement positive, et c'est elle qui commande la largeur du risque, ce que l'exemple 3 mesure en détail. - 5Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas. Plus le seuil est exigeant, plus le multiple descend, de
0,9681à0,9405entre les positions les plus tolérantes et les plus prudentes.
=LOI.LOGNORMALE.INVERSE.N(B2; 0,0005; 0,02)0,9550 de la première ligne se lit ainsi : dans 99 % des séances, la position conserve au moins 95,50 % de sa valeur. Le résultat reste toujours strictement positif, ce qui traduit une hypothèse forte du modèle, celle d'un cours qui ne peut pas s'annuler en une séance.
Comment convertir un quantile en perte avec la fonction LOI.LOGNORMALE.INVERSE.N
Ton comité des risques ne raisonne pas en multiples du cours mais en pourcentage de perte, et c'est ce chiffre qui doit figurer dans ta note. Le résultat brut de la fonction n'est pas lisible tel quel, parce qu'un 0,9550 ne dit pas immédiatement qu'il s'agit d'une baisse de 4,50 %. La conversion tient en une soustraction, encore faut-il la faire.
- 1Dans une cellule, écris
=1-LOI.LOGNORMALE.INVERSE.N(. Le1-de tête convertira en baisse le multiple que la fonction renvoie. - 2En 1er argument : clique sur la cellule du seuil de risque retenu,
B2. C'est le même seuil qu'aux exemples précédents, et seule la lecture du résultat change ici. - 3En 2ᵉ argument : saisis la dérive quotidienne,
0,0005. Elle porte sur le logarithme du rendement, comme à l'exemple 1. - 4En 3ᵉ argument : saisis la volatilité quotidienne,
0,02. Elle reste strictement positive et commande la largeur du risque. - 5Ferme la parenthèse et appuie sur Entrée. Le complément à
1n'est légitime que parce que le résultat s'exprime en multiple du cours de départ, et il n'aurait aucun sens sur une distribution dont les valeurs ne tourneraient pas autour de 1. Recopie ensuite vers le bas, puis applique ces pourcentages au montant réel de la position. Sur une position de 100 000 €, la ligne0,01demande ainsi 4 500 € de réserve.
=1-LOI.LOGNORMALE.INVERSE.N(B2; 0,0005; 0,02)1 traduit ce multiple en baisse effective. La ligne 0,001 chiffre le coût de la prudence : exiger de couvrir la pire séance sur mille au lieu de la pire sur cent fait passer la réserve de 4,50 % à 5,95 %, soit à peine un tiers de plus pour un risque dix fois plus rare.
Comment mesurer l'effet de la volatilité avec la fonction LOI.LOGNORMALE.INVERSE.N
Tes positions n'ont pas toutes la même agitation, et couvrir une valeur calme comme une valeur nerveuse conduit soit à immobiliser trop de capital, soit à en manquer le jour difficile. Le seuil de confiance ne suffit pas à trancher, parce que c'est la volatilité qui commande vraiment l'ampleur du risque. Passer cette volatilité en colonne permet de comparer les scénarios sur une base identique.
- 1Dans une cellule, écris
=LOI.LOGNORMALE.INVERSE.N(. - 2En 1er argument : clique sur la cellule du seuil de probabilité,
B2, maintenue à0,01sur toutes les lignes. Figer ce seuil est ce qui rend la comparaison honnête, puisqu'un seul facteur varie à la fois. - 3En 2ᵉ argument : saisis la dérive quotidienne commune à tous les scénarios,
0,0005. Son influence reste marginale ici, très loin derrière celle de la volatilité. - 4En 3ᵉ argument : clique cette fois sur la cellule de volatilité,
C2, au lieu d'écrire une valeur dans la formule. Ce renvoi permet de tester plusieurs volatilités sans réécrire la formule ligne à ligne. - 5Ferme la parenthèse et appuie sur Entrée, puis recopie vers le bas et lis la colonne de haut en bas. Le multiple conservé s'effondre à mesure que la volatilité grimpe, parce que la distribution s'étire d'autant plus vite que son logarithme se disperse.
=LOI.LOGNORMALE.INVERSE.N(B2; 0,0005; C2)0,01 sur toutes les lignes, donc l'écart entre elles ne vient que de la volatilité, passée en colonne. Multiplier la volatilité par huit, de 0,01 à 0,08, fait tomber le multiple conservé de 0,9775 à 0,8306, soit une perte à couvrir qui passe de 2,25 % à 16,94 %. La réserve de capital dépend donc bien plus de la volatilité que du seuil de confiance retenu, ce que l'exemple 2 laissait déjà deviner.
Les erreurs fréquentes avec la fonction LOI.LOGNORMALE.INVERSE.N
Deux symptômes reviennent sans cesse ici. Le plus visible est #NOMBRE! : il apparaît dès que ta probabilité vaut exactement 0 ou 1, ou que l'écart-type est nul ou négatif. Le second est sournois car aucune erreur ne s'affiche : tu glisses la moyenne et l'écart-type de X au lieu de ceux de ln(X), et le résultat sort dans un ordre de grandeur complètement faux.
Erreur #NOMBRE! avec des paramètres invalides
Cette erreur survient quand la probabilité est exactement 0 ou 1, ou quand l'écart-type est négatif ou nul. Excel ne peut pas calculer un quantile pour une probabilité de 0 (qui donnerait 0) ou de 1 (qui donnerait l'infini).
Solution : Vérifie que ta probabilité est strictement entre 0 et 1, par exemple 0,001 au lieu de 0, ou 0,999 au lieu de 1. Vérifie aussi que l'écart_type est strictement positif. Si =ECARTYPE.STANDARD(LN(A:A)) retourne 0, tes données ne sont pas assez dispersées.
Résultat aberrant : confusion entre paramètres de X et de ln(X)
Une erreur très courante est d'utiliser la moyenne et l'écart-type de X directement au lieu de ceux de ln(X). Les paramètres attendus sont ceux de la distribution normale sous-jacente.
Solution : Calcule toujours les paramètres à partir des logarithmes de tes données : mu = MOYENNE(LN(A1:A100)) et sigma = ECARTYPE.STANDARD(LN(A1:A100)). Si tu as la moyenne mu_X et l'écart-type sigma_X de X, utilise les formules de conversion : mu = LN(mu_X² / RACINE(mu_X² + sigma_X²)).
Questions fréquentes
LOI.LOGNORMALE.INVERSE.N est la version moderne introduite dans Excel 2010, qui remplace LOI.LOGNORMALE.INVERSE. Les deux fonctions donnent des résultats identiques, mais Microsoft recommande la version .N pour les nouveaux classeurs car elle offre une meilleure cohérence avec les autres fonctions statistiques modernes. La version sans .N est conservée uniquement pour la compatibilité avec les anciennes feuilles de calcul.
Si les prix d'un actif suivent une distribution log-normale, calcule la VaR avec la probabilité correspondant au niveau de confiance inversé : pour une VaR à 99 %, utilise la probabilité 0,01. =LOI.LOGNORMALE.INVERSE.N(0,01; mu; sigma) te donne le prix en dessous duquel l'actif ne tombera qu'avec 1 % de probabilité. Les paramètres mu et sigma se calculent à partir des log-rendements historiques avec MOYENNE(LN()) et ECARTYPE.STANDARD(LN()).
Pour estimer mu et sigma, calcule d'abord le logarithme naturel de tes données. Ensuite : mu = MOYENNE(LN(A1:A100)) et sigma = ECARTYPE.STANDARD(LN(A1:A100)). Ces paramètres sont ceux de la distribution normale sous-jacente, pas de la distribution log-normale directement.
La loi log-normale garantit des valeurs strictement positives (impossible d'avoir un prix négatif), elle est asymétrique vers la droite (beaucoup de petites valeurs, quelques très grandes), et elle modélise bien le fait que les variations sont souvent proportionnelles à la valeur actuelle. En finance, l'hypothèse classique est que les rendements logarithmiques suivent une loi normale, ce qui implique que les prix suivent une loi log-normale.
Il existe une relation directe : LOI.LOGNORMALE.INVERSE.N(p; mu; sigma) = EXP(LOI.NORMALE.INVERSE.N(p; mu; sigma)). Autrement dit, le quantile log-normal est simplement l'exponentielle du quantile normal correspondant. Tu peux utiliser cette formule alternative pour vérifier tes calculs.
LOI.LOGNORMALE.INVERSE.N est disponible depuis Excel 2010. Pour les versions antérieures, utilise LOI.LOGNORMALE.INVERSE qui donne les mêmes résultats. Les deux sont disponibles dans Excel 365 et Excel pour Mac.
Pour aller plus loin
Continue sur ta lancée après la fonction LOI.LOGNORMALE.INVERSE.N : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
