Lognormalna dystrybucja w programie Excel

W statystyce mamy termin zwany rozkładem logarytmiczno-normalnym, który jest obliczany w celu ustalenia rozkładu zmiennej, której logarytm ma rozkład normalny, oryginalna formuła jest bardzo złożoną formułą do obliczenia tego, ale w programie Excel mamy wbudowaną funkcję obliczania logarytmu dystrybucja, której funkcja Lognorm.Dist.

Co to jest dystrybucja logarytmiczna w programie Excel

Rozkład log-normalny zwraca ciągły rozkład statystyczny zmiennej losowej, który jest logarytmem o rozkładzie normalnym. Poniżej przedstawiono typy funkcji lognormalnych używanych w programie Excel: -

Formuła LOGNORM.DIST

Składnia funkcji rozkładu jest zdefiniowana jako ROZKŁAD.LOG (x, średnia, odchylenie standardowe, skumulowana) w programie Excel, która zwraca rozkład logarytmiczno-normalny x z podanymi parametrami średniej i odchylenia standardowego logarytmu naturalnego, Ln (x). Powyższa funkcja wymaga następujących parametrów lub argumentów: -

  • x: - wymagana wartość „x”, której rozkład logarytmiczno-normalny ma zostać zwrócony.
  • mean: - średnia z Ln (x)
  • standard_dev: - odchylenie standardowe Ln (x)
  • skumulowany: - Jeśli skumulowany ma wartość PRAWDA, to funkcja zwraca skumulowany rozkład, w przeciwnym razie FALSE podaje gęstość prawdopodobieństwa.

Dystrybucja skumulowana (CDF) to zmienna prawdopodobieństwa, która przyjmuje wartość mniejszą niż równa x. Natomiast funkcja gęstości prawdopodobieństwa (PDF) ciągłej zmiennej losowej wyjaśnia względne prawdopodobieństwo, że zmienna losowa x przyjmie daną wartość.

Funkcja LOGNORM.DIST jest również ogólnie użyteczna w analizie cen akcji, ponieważ normalnego rozkładu nie można zastosować do obliczenia ceny akcji. Funkcja może również służyć do obliczania wyceny opcji dla modelu Blacka Scholesa.

Obliczanie parametrów programu Excel rozkładu logarytmicznego

Przeanalizujmy kilka przykładów rozkładu logarytmiczno-normalnego używanego w programie Excel.

Możesz pobrać ten szablon Lognormal Distribution Excel tutaj - Szablon Excel Lognormal Distribution

Rozważ poniżej cenę akcji spółek notowanych na giełdzie, aby uzyskać średnie i odchylenie standardowe parametrów programu Excel.

Krok 1: - Teraz oblicz wartości logarytmu naturalnego dla odpowiednich cen akcji.

Jak widać na powyższych danych, = LN (liczba) zwraca wartość logarytmu naturalnego podanej liczby.

Krok 2: - Następnie oblicz kwadratowe wartości liczb logarytmów naturalnych, to samo pokazano w poniższej tabeli.

Krok 3: - Teraz potrzebowalibyśmy również sumy logarytmu naturalnego ceny akcji i sumy kwadratów wartości logarytmu naturalnego do obliczenia odchylenia standardowego.

Krok 4: - Następnie oblicz średnią z logarytmu naturalnego dla ceny akcji.

Średnia, µ = (5,97 + 5,99 + 6,21 + 6,54) / 4

Lub µ = 6,18

Krok 5: - Obliczenie odchylenia standardowego można wykonać ręcznie i przy użyciu bezpośredniego wzoru Excela.

Poniżej znajduje się tabela zawierająca średnie i odchylenie standardowe ceny akcji.

Odchylenie standardowe oblicza się przy użyciu funkcji = ODCH.STANDARDOWE (zakres logarytmu naturalnego w kolumnie ln (cena giełdowa)).

Jednak powyższe parametry średniej i odchylenia standardowego mogą być dalej wykorzystywane do obliczania rozkładu logarytmiczno-normalnego programu Excel dla dowolnej wartości „X” lub ceny akcji. Wyjaśnienie tego samego przedstawiono poniżej.

Krok 1: - Rozważ poniższą tabelę, aby zrozumieć funkcję LOGNORM.DIST

W powyższej tabeli przedstawiono wartości parametrów wymagane do obliczenia rozkładu logarytmiczno-normalnego programu Excel dla x, który wynosi 10.

Krok 2: - Teraz wstawimy wartości do funkcji formuły, aby uzyskać wynik, wybierając argumenty B2, B3, B4, a parametr skumulowany będzie miał opcje TRUE i FALSE do wyboru.

LOGNORM.DIST (x; średnia; odchylenie standardowe; skumulowana)

Jak pokazano na powyższym zrzucie ekranu, najpierw wejdzie w opcję TRUE, aby uzyskać funkcję dystrybucji zbiorczej.

W ten sposób dochodzimy do wartości pokazanej w komórce C19 dla funkcji dystrybucji skumulowanej (CDF).

Krok 3: - Teraz obliczmy rozkład logarytmiczno-normalny w programie Excel dla funkcji gęstości prawdopodobieństwa (PDF), wybierając ten sam argument B2, B3, B4 i FALSE w parametrze skumulowanym.

Jak widać na powyższym obrazku, dochodzimy do wyniku w komórce C20 dla funkcji gęstości prawdopodobieństwa (PDF).

Krok 4: - Jak widać w powyższej funkcji, LOGNORM.DIST jest kompatybilny z wersją programu Excel 2010 i nowszą. Możemy jednak użyć funkcji LOGNORMDIST, która wykorzystuje te same parametry, co w najnowszych wersjach. Biorąc pod uwagę te same wartości parametrów, wypełnimy funkcję LOGNORMDIST, jak pokazano poniżej.

Jak widać, wartość dała taką samą liczbę, jak ROZKŁAD.LOG dla parametru PRAWDA w argumencie skumulowanym.

Rzeczy do zapamiętania dotyczące dystrybucji logarytmicznej w programie Excel

  1. Jeśli jakikolwiek parametr lub argument jest nienumeryczny, to rozkład logarytmiczno-normalny excel funkcja zwróci #ARG! Komunikat o błędzie.
  2. Jeśli argumenty x są mniejsze i równe 0 lub jeśli odchylenie standardowe jest mniejsze i równe 0, funkcja zwróci #NUM! Komunikat o błędzie.
  3. Równoważne wyrażenie do obliczenia LOGNORM.DIST to LOGNORM.DIST (x, średnia, odchylenie_normalne) = ROZKŁAD.NORMALNY ((ln (x) -średnia) / odchylenie standardowe)
  4. Ta funkcja jest kompatybilna z wersją 2010 i nowszymi, w wersji 2007 i wcześniejszych należy użyć LOGNORMDIST (x, mean, standard_dev), który zwraca skumulowany rozkład logarytmiczno-normalny x, gdzie ln (x) jest rozkładem normalnym ze średnią parametrów / argumentów i standard_dev.