Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00610 009549 10439438 na godz. na dobę w sumie
Zaawansowane modele finansowe z wykorzystaniem Excela i VBA - książka
Zaawansowane modele finansowe z wykorzystaniem Excela i VBA - książka
Autor: , Liczba stron: 320
Wydawca: Helion Język publikacji: polski
ISBN: 83-7361-340-4 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook, audiobook).

Zastosowania Excela wykraczają poza sporządzanie prostych zestawień i wykonywanie trywialnych obliczeń. W rękach specjalisty Excel staje się potężnym narzędziem przydatnym w analizie skomplikowanych zagadnień finansowych.

Ta wyjątkowa książka dowodzi, że Excel i Visual Basic for Applications mogą odgrywać istotną rolę w objaśnianiu i wdrażaniu metod ilościowych w dziedzinie finansów. Dysponując wydajnym kodem i funkcjami VBA w ciągu kilku sekund, a nawet ułamków sekund, możemy wykonywać w Excelu obliczenia, które dotąd były przeprowadzane jedynie przy użyciu specjalnych pakietów i języków.

Wszystkie modele opracowano zarówno w postaci arkuszy kalkulacyjnych pomocnych w nauczaniu finansów, jak również w formie zdefiniowanych przez użytkownika funkcji napisanych w VBA, a stanowiących bibliotekę przenośnych funkcji gotową do zastosowania w Excelu. Książka przeznaczona jest zarówno dla magistrantów, jak i dla studentów ostatnich lat studiów licencjackich.

Książka opisuje:

Zaawansowane obliczenia dla finansistów i menedżerów

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

IDZ DO IDZ DO PRZYK£ADOWY ROZDZIA£ PRZYK£ADOWY ROZDZIA£ SPIS TREĎCI SPIS TREĎCI KATALOG KSI¥¯EK KATALOG KSI¥¯EK KATALOG ONLINE KATALOG ONLINE ZAMÓW DRUKOWANY KATALOG ZAMÓW DRUKOWANY KATALOG TWÓJ KOSZYK TWÓJ KOSZYK DODAJ DO KOSZYKA DODAJ DO KOSZYKA CENNIK I INFORMACJE CENNIK I INFORMACJE ZAMÓW INFORMACJE ZAMÓW INFORMACJE O NOWOĎCIACH O NOWOĎCIACH ZAMÓW CENNIK ZAMÓW CENNIK CZYTELNIA CZYTELNIA FRAGMENTY KSI¥¯EK ONLINE FRAGMENTY KSI¥¯EK ONLINE Wydawnictwo Helion ul. Chopina 6 44-100 Gliwice tel. (32)230-98-63 e-mail: helion@helion.pl Zaawansowane modele finansowe z wykorzystaniem Excela i VBA Autorzy: Mary Jackson, Mike Staunton T³umaczenie: Daniel Kaczmarek ISBN: 83-7361-340-4 Tytu³ orygina³u: Advanced Modelling in Finance using Excel and VBA Format: B5, stron: 320 Zastosowania Excela wykraczaj¹ poza sporz¹dzanie prostych zestawieñ i wykonywanie trywialnych obliczeñ. W rêkach specjalisty Excel staje siê potê¿nym narzêdziem przydatnym w analizie skomplikowanych zagadnieñ finansowych. Ta wyj¹tkowa ksi¹¿ka dowodzi, ¿e Excel i Visual Basic for Applications mog¹ odgrywaæ istotn¹ rolê w objaġnianiu i wdra¿aniu metod iloġciowych w dziedzinie finansów. Dysponuj¹c wydajnym kodem i funkcjami VBA w ci¹gu kilku sekund, a nawet u³amków sekund, mo¿emy wykonywaæ w Excelu obliczenia, które dot¹d by³y przeprowadzane jedynie przy u¿yciu specjalnych pakietów i jêzyków. Wszystkie modele opracowano zarówno w postaci arkuszy kalkulacyjnych pomocnych w nauczaniu finansów, jak równie¿ w formie zdefiniowanych przez u¿ytkownika funkcji napisanych w VBA, a stanowi¹cych bibliotekê przenoġnych funkcji gotow¹ do zastosowania w Excelu. Ksi¹¿ka przeznaczona jest zarówno dla magistrantów, jak i dla studentów ostatnich lat studiów licencjackich. Ksi¹¿ka opisuje: • Zaawansowane funkcje i procedury Excela • Podstawy programowania w VBA • Tworzenie w³asnych funkcji w VBA • Optymalizacjê portfela akcji • Wycenê aktywów • Mierzenie efektywnoġci • Zagadnienia zwi¹zane z opcjami na akcje • Drzewa dwumianowe i formu³ê Blacka • Scholesa • Zagadnienia zwi¹zane z opcjami na obligacje Zaawansowane obliczenia dla finansistów i mened¿erów • Poznaj zaawansowane funkcje Excela • Naucz siê pisaæ programy w jêzyku Visual Basic for Applications • Zarz¹dzaj portfelem przy pomocy Excela i analizuj efektywnoġæ zarz¹dzania • Poznaj metody analizy zwi¹zane z opcjami na akcje i obligacje Spis treści Przedmowa......................................................................................... 9 Rozdział 1. Wprowadzenie .................................................................................. 11 1.1. Geneza finansów jako dziedziny nauki...................................................i...................12 1.2. Założenia do wyceny aktywów ...................................................i...............................12 1.3. Problemy matematyczne i statystyczne...................................................i...................13 1.4. Metody ilościowe ...................................................i...................................................i.13 1.5. Rozwiązania w Excelu ...................................................i............................................14 1.6. Przedstawione zagadnienia ...................................................i.....................................14 1.7. Zamieszczone arkusze Excela...................................................i.................................17 Część I Zaawansowane modelowanie w Excelu ...........................19 Rozdział 2. Zaawansowane funkcje i procedury Excela ....................................... 21 2.1. Korzystanie z funkcji Excela...................................................i...................................21 2.2. Funkcje matematyczne...................................................i............................................23 2.3. Funkcje statystyczne ...................................................i...............................................24 2.3.1. Zastosowanie funkcji CZĘSTOŚĆ ...................................................i................25 2.3.2. Zastosowanie funkcji KWARTYL ...................................................i................27 2.3.3. Zastosowanie funkcji Excela rozkładu normalnego .........................................28 2.4. Funkcje wyszukiwania ...................................................i............................................29 2.5. Inne funkcje...................................................i...................................................i..........32 2.6. Narzędzia inspekcji ...................................................i.................................................33 2.7. Tabele danych ...................................................i...................................................i......34 2.7.1. Tworzenie tabel danych z jedną zmienną wejściową .......................................34 2.7.2. Tworzenie tabel danych z dwiema zmiennymi wejściowymi ..........................35 2.8. Wykresy XY...................................................i...................................................i.........37 2.9. Udostępnianie analizy danych i Solvera ...................................................i.................40 2.10. Stosowanie nazw zakresów...................................................i...................................40 2.11. Regresja...................................................i...................................................i..............42 2.12. Narzędzie Szukaj wyniku...................................................i......................................44 2.13. Algebra macierzy i związane z nią funkcje...................................................i...........46 2.13.1. Wprowadzenie do teorii macierzy ...................................................i...............46 2.13.2. Transpozycja macierzy ...................................................i................................46 2.13.3. Dodawanie macierzy...................................................i....................................47 4 Zaawansowane modele finansowe z wykorzystaniem Excela i VBA 2.13.4. Mnożenie macierzy...................................................i......................................47 2.13.5. Odwracanie macierzy...................................................i...................................49 2.13.6. Rozwiązywanie układów równoważnych równań liniowych.........................50 2.13.7. Podsumowanie funkcji macierzowych w Excelu ...........................................51 Podsumowanie ...................................................i...................................................i............51 Rozdział 3. Wprowadzenie do VBA ...................................................................... 53 3.1. Korzyści ze znajomości VBA ...................................................i.................................53 3.2. Zorientowane obiektowo cechy VBA ...................................................i.....................55 3.3. Zaczynamy pisać makra w VBA...................................................i.............................57 3.3.1. Kilka przykładowych procedur VBA ...................................................i............57 3.3.2. Interakcja z zastosowaniem MsgBox...................................................i.............58 3.3.3. Edytor kodu źródłowego...................................................i................................59 3.3.4. Wpisywanie kodu i wykonywanie makr...................................................i........60 3.3.5. Rejestrowanie naciśnięć klawiszy i edytowanie kodu ......................................61 3.4. Elementy programowania ...................................................i.......................................63 3.4.1. Zmienne i typy danych...................................................i...................................63 3.4.2. Zmienne tablicowe VBA ...................................................i...............................64 3.4.3. Struktury sterujące ...................................................i.........................................66 3.4.4. Sterowanie procedurami powtarzalnymi ...................................................i.......67 3.4.5. Stosowanie w kodzie funkcji Excela oraz funkcji VBA...................................69 3.4.6. Ogólne uwagi na temat programowania ...................................................i........69 3.5. Komunikacja między makrami a arkuszem ...................................................i............70 3.6. Przykładowe procedury...................................................i...........................................74 3.6.1. Wykresy ...................................................i...................................................i......74 3.6.2. Wykres prawdopodobieństwa normalnego...................................................i....77 3.6.3. Generowanie granicy efektywności za pomocą Solvera ..................................79 Podsumowanie ...................................................i...................................................i............82 Lektury ...................................................i...................................................i........................83 Dodatek 3A. Edytor Visual Basic ...................................................i..................................83 Krokowe wykonywanie makra i korzystanie z innych narzędzi testujących .............86 Dodatek 3B. Rejestrowanie naciśnięć klawiszy w trybie „odwołań względnych”...........88 Rozdział 4. Tworzenie funkcji VBA zdefiniowanych przez użytkownika.................. 91 4.1. Prosta funkcja obliczająca prowizję od sprzedaży...................................................i..92 4.2. Wstawianie funkcji Commission(Sales) do arkusza ..................................................93 4.3. Dwie funkcje z wieloma danymi wejściowymi służące do wyceny opcji .................94 4.4. Manipulowanie tablicami w VBA...................................................i...........................97 4.5. Funkcje wartości oczekiwanej i wariancji z tablicami wejściowymi ........................98 4.6. Funkcja wariancji portfela posiadająca tablice wejściowe ......................................101 4.7. Funkcje zwracające tablice...................................................i....................................103 4.8. Stosowanie funkcji Excela i VBA w funkcjach zdefiniowanych przez użytkownika...................................................i......105 4.8.1. Stosowanie funkcji VBA w funkcjach zdefiniowanych przez użytkownika ... 105 4.8.2. Dodatki...................................................i...................................................i......106 4.9. Zalety i wady tworzenia funkcji VBA ...................................................i..................106 Podsumowanie ...................................................i...................................................i..........107 Dodatek 4A. Funkcje ilustrujące obsługę tablic...................................................i...........108 Dodatek 4B. Funkcje wyceny opcji z zastosowaniem drzewa dwumianowego .............110 Ćwiczenia w pisaniu funkcji ...................................................i........................................115 Rozwiązania ćwiczeń w pisaniu funkcji ...................................................i......................117 Spis treści 5 Część II Zaawansowane modele akcji ........................................121 Rozdział 5. Wprowadzenie do akcji ................................................................... 123 Rozdział 6. Optymalizacja portfela .................................................................... 125 6.1. Średnia i wariancja portfela...................................................i...................................125 6.2. Reprezentacja portfeli za pomocą ryzyka i zwrotu ..................................................128 6.3. Zastosowanie Solvera do znajdowania portfeli efektywnych ..................................129 6.4. Wyznaczanie granicy efektywności (podejście Huanga i Litzenbergera) ...............132 6.5. Portfele efektywne z warunkami ograniczającymi ..................................................134 6.6. Łączenie aktywów ryzykownych i pozbawionych ryzyka.......................................136 6.7. Problem pierwszy: łączenie aktywa pozbawionego ryziyka z aktywem ryzykownym...................................................i............................................137 6.8. Problem drugi: łączenie dwóch aktywów ryzykownych..........................................139 6.9. Problem trzeci: łączenie aktywa wolnego od ryzyka z portfelem ryzykownym......141 6.10. Funkcje zdefiniowane przez użytkownika w Module1..........................................143 6.11. Funkcje w Module1 dla trzech ogólnych problemów konstrukcji portfela ...........145 6.12. Makra z modułu ModułM ...................................................i...................................146 Podsumowanie ...................................................i...................................................i..........148 Lektury ...................................................i...................................................i......................148 Rozdział 7. Wycena aktywów............................................................................ 149 7.1. Model jednoindeksowy ...................................................i.........................................150 7.2. Szacowanie współczynników beta ...................................................i........................151 7.3. Model wyceny aktywów kapitałowych...................................................i.................154 7.4. Macierze wariancji-kowariancji...................................................i............................154 7.5. Value-at-Risk ...................................................i...................................................i.....156 7.6. Horyzont zysków...................................................i...................................................i159 7.7. Momenty rozkładów powiązanych ze sobą na przykładzie rozkładów normalnego i logarytmiczno-normalnego ..........................161 7.8. Funkcje zdefiniowane przez użytkownika w Module1............................................162 Podsumowanie ...................................................i...................................................i..........163 Lektury ...................................................i...................................................i......................164 Rozdział 8. Mierzenie efektywności i jej przypisywanie ..................................... 165 8.1. Tradycyjny pomiar efektywności...................................................i..........................166 8.2. Zarządzanie aktywne-pasywne ...................................................i.............................168 8.3. Wprowadzenie do analizy stylu ...................................................i............................170 8.4. Prosta analiza stylu...................................................i................................................172 8.5. Analiza stylu dla kolejnych okresów ...................................................i....................173 8.6. Przedziały ufności dla udziałów stylu...................................................i...................175 8.7. Funkcje zdefiniowane przez użytkownika w Module1............................................178 8.8. Makra w ModuleM...................................................i................................................179 Podsumowanie ...................................................i...................................................i..........180 Lektury ...................................................i...................................................i......................181 Część III Opcje na akcje ...................................................w..........183 Rozdział 9. Wprowadzenie do opcji na akcje...................................................... 185 9.1. Geneza formuły Blacka-Scholesa ...................................................i.........................186 9.2. Formuła Blacka-Scholesa...................................................i......................................187 9.3. Portfele zabezpieczające ...................................................i.......................................188 9.4. Wycena niezależna od ryzyka ...................................................i...............................190 9.5. Proste jednokrokowe drzewo dwumianowe z wyceną niezależną od ryzyka..........191 6 Zaawansowane modele finansowe z wykorzystaniem Excela i VBA 9.6. Parytet put-call ...................................................i...................................................i...192 9.7. Dywidendy ...................................................i...................................................i.........193 9.8. Cechy opcji amerykańskiej ...................................................i...................................194 9.9. Metody ilościowe ...................................................i..................................................194 9.10. Zmienność i stopy zwrotu z akcji mające rozkład inny od normalnego ................195 Podsumowanie ...................................................i...................................................i..........196 Lektury ...................................................i...................................................i......................197 Rozdział 10. Drzewa dwumianowe....................................................................... 199 10.1. Wprowadzenie do drzew dwumianowych ...................................................i..........200 10.2. Uproszczone drzewo dwumianowe...................................................i.....................201 10.3. Drzewo dwumianowe JR ...................................................i....................................203 10.4. Drzewo CRR ...................................................i...................................................i....207 10.5. Przybliżenia dwumianowe a formuła Blacka-Scholesa .........................................208 10.6. Zbieżność drzew dwumianowych CRR ...................................................i..............210 10.7. Drzewo LR ...................................................i...................................................i.......211 10.8. Porównanie drzew CRR i LR...................................................i..............................212 10.9. Opcje amerykańskie oraz amerykańskie drzewo CRR ..........................................214 10.10. Funkcje zdefiniowane przez użytkownika w Module0 i Module1 ......................216 Podsumowanie ...................................................i...................................................i..........218 Lektury ...................................................i...................................................i......................218 Rozdział 11. Formuła Blacka-Scholesa ................................................................ 219 11.1. Formuła Blacka-Scholesa...................................................i....................................219 11.2. Formuła Blacka-Scholesa w arkuszu ...................................................i..................220 11.3. Opcje na waluty i towary ...................................................i....................................222 11.3. Obliczanie „greckich” parametrów opcji ...................................................i............223 11.5. Portfele zabezpieczające ...................................................i.....................................224 11.6. Formalne wyprowadzenie formuły Blacka-Scholesa.............................................227 11.7. Funkcje zdefiniowane przez użytkownika w Module1..........................................229 Podsumowanie ...................................................i...................................................i..........230 Lektury ...................................................i...................................................i......................231 Rozdział 12. Inne metody ilościowe dla opcji europejskich.................................. 233 12.1. Wprowadzenie do symulacji Monte Carlo...................................................i..........234 12.2. Symulacja przy użyciu przeciwnych zmiennych losowych...................................236 12.3. Symulacja przy użyciu próbkowania quasi-losowego ...........................................237 12.4. Porównanie metod symulacji ...................................................i..............................238 12.5. Obliczanie parametrów greckich w symulacji Monte Carlo..................................240 12.6. Całkowanie numeryczne ...................................................i.....................................240 12.7. Funkcje zdefiniowane przez użytkownika w Module1..........................................242 Podsumowanie ...................................................i...................................................i..........244 Lektury ...................................................i...................................................i......................244 Rozdział 13. Rozkłady inne niż normalny oraz zmienność wewnętrzna.................. 245 13.1. Zastosowanie w formule Blacka-Scholesa alternatywnych założeń dotyczących rozkładów...................................................i.......246 13.2. Zmienność wewnętrzna...................................................i.......................................248 13.3. Uwzględnianie skośności i kurtozy...................................................i.....................249 13.4. „Uśmiech zmienności”...................................................i........................................252 13.5. Funkcje zdefiniowane przez użytkownika w Module1..........................................254 Podsumowanie ...................................................i...................................................i..........257 Lektury ...................................................i...................................................i......................257 Spis treści 7 Część IV Opcje na obligacje...................................................w.....259 Rozdział 14. Wprowadzenie do wyceny opcji na obligacje ................................... 261 14.1. Struktura czasowa stóp procentowych ...................................................i................263 14.2. Przepływy pieniężne z obligacji kuponowych i rentowność w momencie wykupu ...................................................i...........................264 14.3. Drzewa dwumianowe...................................................i..........................................265 14.4. Formuła Blacka na wycenę opcji na obligację...................................................i....266 14.5. Trwałość i wypukłość...................................................i..........................................267 14.6. Sposób zapisu...................................................i...................................................i...269 Podsumowanie ...................................................i...................................................i..........269 Lektury ...................................................i...................................................i......................270 Rozdział 15. Modele stóp procentowych ............................................................. 271 15.1. Model Vasicka struktury czasowej ...................................................i.....................272 15.2. Wycena opcji europejskich na obligacje zerokuponowe — model Vasicka .........274 15.3. Wycena europejskich opcji na obligacje kuponowe — model Vasicka ................276 15.4. Model CIR struktury czasowej...................................................i............................276 15.5. Wycena europejskich opcji na obligacje zerokuponowe — model CIR................277 15.6. Wycena europejskich opcji na obligacje kuponowe — model CIR.......................278 15.7. Funkcje zdefiniowane przez użytkownika w Module1..........................................279 Podsumowanie ...................................................i...................................................i..........281 Lektury ...................................................i...................................................i......................281 Rozdział 16. Dopasowywanie struktury czasowej................................................ 283 16.1. Drzewa ze stopami procentowymi o rozkładzie logarytmiczno-normalnym.........284 16.2. Drzewa ze stopami procentowymi o rozkładzie normalnym .................................287 16.3. Drzewo BDT ...................................................i...................................................i....288 16.4. Wycena opcji na obligacje z wykorzystaniem drzewa BDT..................................290 16.5. Funkcje zdefiniowane przez użytkownika w Module1..........................................292 Podsumowanie ...................................................i...................................................i..........294 Lektury ...................................................i...................................................i......................294 Dodatki ...................................................w....................................295 Dodatek Inne funkcje VBA ............................................................................ 297 Prognozowanie ...................................................i...................................................i..........297 Modele ARIMA ...................................................i...................................................i........299 Krzywe sklejane ...................................................i...................................................i........301 Wartości własne i wektory własne ...................................................i...............................302 Lektury ...................................................i...................................................i......................303 Skorowidz...................................................................................... 305 Rozdział 2. Zaawansowane funkcje i procedury Excela Celem tego rozdziału jest zapoznanie czytelnika z niektórymi funkcjami i procedurami Excela zastosowanymi w dalszej części książki. Chodzi przede wszystkim o matematycz- ne, statystyczne lub wyszukiwawcze funkcje Excela, a także o najczęściej stosowane procedury, takie jak tworzenie tabel z danymi oraz wyświetlanie wyników na wykresach typu XY. Przedstawione zostaną również metody podsumowywania zestawów danych, przeprowadzania analiz regresji oraz uruchamiania narzędzi Excela Szukaj wyniku i Solver. Postaramy się jak najlepiej objaśnić prezentowany materiał, by nie sprawiał on czytelnikowi żadnych trudności. Bardziej zaawansowani użytkownicy Excela mogą ograniczyć się do pobieżnego przeglądnięcia tego rozdziału bądź odwoływać się do niego tylko wtedy, gdy odczują taką potrzebę. W celu uprzyjemnienia i zwiększenia efektywności lektury opracowano skoroszyt o nazwie ZMFExcel.xls, zawierający opi- sywane przykłady i umożliwiający sprawdzenie swoichz umiejętności. 2.1. Korzystanie z funkcji Excela Excel udostępnia wiele funkcji arkuszy kalkulacyjnych, będących zasadniczo zaimple- mentowanymi procedurami obliczeniowymi. Funkcje te ułatwiają wykonywanie obli- czeń przeprowadzanych w arkuszu, a także mogą być dołączane do makr VBA i funkcji zdefiniowanych przez użytkownika (te zagadnienia zostaną przedstawione w rozdziale 3. i 4.). Funkcje udostępnia przycisk Wstaw funkcję (oznaczony jako fx) znajdujący się na standardowym pasku narzędzi (wcześniej służył do tego tak zwany kreator funkcji.) Na rysunku 2.1 widać, że funkcje są pogrupowane w kilka różnych kategorii: matema- tyczne, statystyczne, logiczne, wyszukiwania i adresuz itd. Jak widać na rysunku, zaznaczona została funkcja -1/$+0# , , co spowodowało wyświe- tlenie krótkiego opisu jej danych wejściowych i wyjściowych. Pełniejszy opis można uzyskać, naciskając przycisk Pomoc (oznaczony jako ?). 22 Część I ♦ Zaawansowane modelowanie w Excelu Rysunek 2.1. Okno dialogowe Wstawianie funkcji przedstawiające funkcję KOMBINACJE należącą do kategorii Matematyczne Po kliknięciu przycisku OK wyświetlony zostanie formularz Argumenty funkcji zawie- rający pola, w których należy wprowadzić odpowiednie dane wejściowe, tak jak na rysunku 2.2. Dane wejściowe można wpisać w pola tekstowe (jak na rysunku) lub „wy- brać” je, odwołując się do komórek arkusza (klikając wcześniej przyciski służące do zwijania formularza Argumenty funkcji). Zwróćmy uwagę, że formularz można prze- sunąć na inną niż standardowa pozycję. Kliknięcie przycisku OK na formularzu lub przycisku zatwierdzenia w wierszu edycji spowoduje wprowadzenie formuły do arku- sza kalkulacyjnego. Rysunek 2.2. Tworzenie formuły KOMBINACJE w formularzu Argumenty funkcji Oprócz formularza Argumenty funkcji, zawierającego dane wejściowe dla funkcji -1/$+0# , , na rysunku 2.2 widać również wiersz Edycja przedstawiający taką postać formuły, w jakiej pojawi się ona w komórce arkusza, oraz wciśnięty przycisk Wstaw funkcję. Zwróćmy też uwagę na przycisk Wklej nazwy (oznaczony jako =ab), ułatwiający wklejanie do formuły komórek, którym nadano nazwę (nadawanie nazw zakresom komórek oraz odwoływanie się do zakresów za pomocą nazw zostanie przedstawione w punkcie 2.10.). Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 23 Podobnie jak funkcje Excela przycisk Wstaw funkcję umożliwia dostęp do kategorii funkcji zdefiniowanych przez użytkownika, opisanych wz rozdziale 4. Znamy już sposób korzystania z funkcji, a zatem w następnych punktach przedstawimy niektóre funkcje matematyczne i statystyczne. 2.2. Funkcje matematyczne Spośród funkcji należących do kategorii Matematyczne zastosujemy funkcje :2 Z , .0 Z , 2+ 49+#56 - Z , .15 , 5+.0+# Z oraz -1/$+0# , NKEDCNKEDCAY[DTCPC . Funkcja :2 Z zwraca wartość funkcji potęgowej exp(x) lub ex. Na przykład: :2  zwróci wartość liczby e (2,7183, jeśli sformatujemy ją do czterech miejsc po przecinku); :2  zwróci wartość e2 (7,3891 — z czterema cyframi po przecinku); :2 Ō zwróci wartość 1/e, czyli e–1 (0,36788 — z pięcioma cyframi po przecinku).    W obliczeniach finansowych przepływy pieniężne zachodzące w różnym czasie prze- kształca się w wartości przyszłe (lub teraźniejsze), przykładając czynniki kapitalizacji (lub dyskontowe). W przypadku kapitalizacji ciągłej stopą procentową równą r czyn- nik kapitalizacji w jednym roku ma wartość exp(r), a jeśli kapitalizacja odbywa się raz do roku, wówczas odpowiadającą jej roczną stopę procentową ra oblicza się za pomocą wyrażenia: exp( − 1) r r a = Kapitalizacja ciągła oraz zastosowanie funkcji :2 zostanie zilustrowane w punkcie 2.7.1 dotyczącym tabel z danymi. Funkcja .0 Z zwraca wartość logarytmu naturalnego liczby Z. Liczba ta musi być dodatnia, bo w przeciwnym razie funkcja zwróci wartość 07/ oznaczającą przepeł- nienie liczbowe. Na przykład:     .0  zwróci wartość Ō, .0  zwróci wartość , .0  zwróci wartość , .0 Ō zwróci wartość 07/. W finansach często wykorzystuje się logarytmy (naturalne) zysków, stosując funkcję .0 do przekształcania kwoty zysków w ich logarytmy. Funkcja 2+ 49+#56 - Z zwraca wartość pierwiastka kwadratowego liczby Z. Oczy- wiście Z musi być liczbą dodatnią, w przeciwnym razie funkcja zwróci wartość 07/ oznaczającą przepełnienie liczbowe. 24 Część I ♦ Zaawansowane modelowanie w Excelu Funkcja .15 generuje losową liczbę o rozkładzie jednostajnym, większą lub równą zero i mniejszą od jeden. Liczba ta zmienia się po każdym przeliczeniu arkusza. .15 można zastosować do wprowadzania probabilistycznej zmienności w symulacji Monte Carlo wartości opcji. Funkcja 5+.0+# NKEDC zwraca silnię liczby, równą ∗∗∗ŗ∗NKEDC. Na przykład:  5+.0+#  zwróci wartość równą . Funkcja -1/$+0# , NKEDC NKEDCAY[DTCPC zwraca liczbę kombinacji (podzbio- rów o rozmiarze NKEDCAY[DTCPC), jakie można utworzyć z podanej liczby elementów (NKEDC). Podzbiory mogą mieć dowolny porządek. Na przykład, jeśli cena udziału zmienia się w kierunku „góra” lub „dół” w czterech odrębnych okresach, wówczas liczba sekwencji z trzema wzrostami (i jednym spadkizem) wynosi: -1/$+0# ,   lub równoważnie -1/$+0# ,  , czyli mogą wystąpić cztery sekwencje: „góra-góra-góra-dół”, „góra-góra-dół-góra”, „góra-dół-góra-góra” i „dół-góra-góra-góra”. W ujęciu statystycznym -1/$+0# ,  oznacza liczbę kombinacji trzech spośród czterech elementów, co zwykle zapisuje się jako 4C3 (lub w postaci ogólnej nCr). Excel udostępnia funkcje służące do transponowania macierzy, mnożenia macierzy oraz do odwracania macierzy kwadratowych. Są to następującze funkcje:    64#052107, VCDNKEC , która zwraca transpozycję tablicy; /# + 4 +.1 ;0 VCDNKECVCDNKEC , która zwraca iloczyn dwóch tablic; /# + 4 1 9 VCDNKEC , która zwraca macierz odwrotną podanej tablicy. Wszystkie należą do kategorii funkcji matematycznych. Być może przed poznaniem tych funkcji niektórym czytelnikom przyda się krótkie wprowadzenie do teorii macie- rzy, dlatego zamieściliśmy je na końcu tego rozdziałzu (patrz punkt 2.13). 2.3. Funkcje statystyczne Excel udostępnia kilka funkcji umożliwiających szybkie podsumowywanie cech zesta- wu danych (czyli „tablicy”, jeśli zastosujemy terminologię Excela). Są to funkcje Ħ4  0+# VCDNKEC zwracająca wartość średnią, 1 *56#0 #4 19 VCDNKEC , która zwraca wartość odchylenia standardowego, oraz /#: VCDNKEC i /+0 VCDNKEC zapewne znane już czytelnikowi. Istnieje kilka użytecznych funkcji służących do rozpoznawania rozkładu zestawów danych o umiarkowanych rozmiarach, które warto poznać. Na przykład funkcja -9#46;. wyznacza wartości kwartyli na podstawie wartości percentyli zestawu danych, a funkcja ù561Ħç zwraca pełen rozkład częstości pogrupowanego zestazwu danych. Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 25 W Excelu istnieją również funkcje dotyczące różnych teoretycznych rozkładów praw- dopodobieństwa, w szczególności są to funkcje dotyczące rozkładu normalnego: 41  -Đ# 014/#.0;5 i 41 -Đ# 014/#.0;51 9 dla standardowego rozkładu normalnego, w którym średnia ma wartość 0, a odchylenie standardowe wynosi 1, oraz 41 -Đ#  014/#.0; i 41 -Đ# 014/#.0;1 9 dla dowolnego rozkładu normalnego.    Innymi użytecznymi funkcjami z kategorii funkcji statystycznych są te operujące na dwóch zmiennych, zwracające wiele różnych wartości wykorzystywanych w analizie korelacji i regresji. Na przykład: 1 +ù6# PCPGA[PCPGAZ , 0# *;. 0+ PCPGA[PCPGAZ , 4-9# 4#6 PCPGA[PCPGAZ , 4 )$Đ56 PCPGA[PCPGAZ , 952-14 .# ,+ VCDNKECVCDNKEC , -19#4+#0 ,# VCDNKECVCDNKEC .    Istnieje również mało znana funkcja 4 ).+02 PCPGA[ PCPGAZ , która zwraca pod- stawowe statystyki regresji w postaci tablicy. Większość wymienionych funkcji zosta- nie bardziej szczegółowo przedstawiona w punkcie 2.11 opisującym regresję. Wyniki ich działania porównamy z danymi wyjściowymi regreszji, zwracanymi przez procedurę Analiza danych Regresja. W następnym punkcie na podstawie arkuszy Częstość i SNorm z pliku ZMFExcel.xls wyjaśnimy, w jaki sposób należy stosować ù561Ħç, -9#46;. oraz inne funkcje roz- kładu normalnego. 2.3.1. Zastosowanie funkcji CZĘSTOŚĆ Funkcja ù561Ħç VCDNKECAFCPGVCDNKECARTGFKCđ[ oblicza częstość, z jaką war- tości z zestawu danych występują w określonych przedziałach, i zwraca je w postaci pionowej tablicy. Parametr VCDNKECARTGFKCđ[ jest zbiorem przedziałów, w jakie pogrupowano wartości. Funkcja zwraca dane wyjściowe w postaci tablicy, dlatego koniecznie należy zaznaczyć zakres komórek arkusza, w których zostaną wyświetlone dane wyjściowe, przed wprowadzeniem funkcji. Sposób stosowania funkcji ù561Ħç objaśnimy na podstawie przykładowego arkusza Częstość z pliku ZMFExcel.xls. Jak widać na rysunku 2.3, w wierszach od czwartego do siódmego podsumowano miesięczne stopy zwrotu z kolumn D10:D71 oraz loga- rytmy stóp zwrotu (obliczone za pomocą funkcji .0) z kolumn E10:E71. Załóżmy, że naszym celem jest otrzymanie rozkładu częstości logarytmów stóp zwrotu (E10:E71), czyli tak zwanej „tablicy_danych”. Chcielibyśmy w ten sposób sprawdzić, czy rozkład tych stóp zwrotu jest zbliżony do normalnego. Najpierw musimy zdefiniować przedziały grupowania danych. Analiza maksymalnej i minimalnej wartości logarytmu stóp zwrotu wskazuje, że zakres liczb –0,16 do +0,20 najlepiej jest podzielić na 10 – 12 przedzia- łów. Wartości wpisane w komórkach G5:G14 stanowią górne granice „przedziałów”, na jakie dzielimy logarytmy stóp zwrotu. 26 Część I ♦ Zaawansowane modelowanie w Excelu Rysunek 2.3. Arkusz służący do obliczania rozkładu częstości logarytmów stóp zwrotu Aby prawidłowo wprowadzić funkcję ù561Ħç, należy zaznaczyć zakres komórek H5:H15. Następnie należy wpisać znak  i kliknąć przycisk Wstaw funkcję (oznaczony jako fx), aby uzupełnić składnię funkcji:  ù561Ħç  )) Po wpisaniu nawiasu zamykającego „)” i pozostawieniu kursora w wierszu edycji Excela, należy wprowadzić funkcję do arkusza i, trzymając wciśnięte klawisze Ctrl i Shift, naci- snąć klawisz Enter. (Konieczne będzie użycie trzech palców, gdyż w przeciwnym razie funkcja nie zostanie wprowadzona. Jeśli mimo tego operacja się nie powiedzie, należy pozostawić zaznaczenie zakresu komórek wyjściowych, nacisnąć klawisz Edycja (F2), wyedytować formułę, jeśli zajdzie taka konieczność, po czym jeszcze raz nacisnąć Ctrl+Shift+Enter.) Teraz w komórkach G5:G15 powinna być widoczna formuła zamknięta w nawiasach klamrowych (]_) oraz tablica częstości. Wyniki przedstawione są na rysunku 2.4. W komórce H17 zastosuj funkcję 57/#, aby sprawdzić, że częstości sumują się do . Rysunek 2.4. Rozkład częstości logarytmów stóp zwrotu z rozkładami częstości procentowych i skumulowanych Interpretując wyniki, można powiedzieć, że nie istnieją logarytmy stóp zwrotu mające wartość poniżej –0,16, istnieje sześć wartości z przedziału od –0,16 do –0,12 i nie ma wartości przekraczających 0,20 (dolna komórka tablicy CZĘSTOŚĆ, czyli G15, zawiera liczbę wartości przekraczających górną granicę przedzziału, wynoszącą 0,20). Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 27 Ponieważ funkcja ù561Ħç zwraca tablicę, nie ma możliwości zmiany pojedynczych komórek. Jeśli zajdzie konieczność zdefiniowania innej liczby przedziałów, niezbędne będzie usunięcie tablicy wyjściowej i wprowadzenie fzunkcji na nowo. Pomocne może okazać się przekształcenie częstości w częstości procentowe (względem rozmiaru zestawu danych, zawierającego 62 wartości), a następnie obliczenie skumulo- wanych częstości procentowych tak, jak w kolumnach I oraz J na rysunku 2.4. Formuły częstości procentowych i skumulowanych częstości procentowych można przeanali- zować w arkuszu Częstość. Najlepszym sposobem zaprezentowania skumulowanych częstości procentowych jest zamieszczenie ich na wykresie XY i połączenie punktów danych linią bez znaczników. Aby utworzyć wykres jak na rysunku 2.5, należy zaznaczyć jako dane źródłowe zakresy G5:G14 i J5:J14. W celu jednoczesnego zaznaczenia zakresów komórek, które nie sąsiadują ze sobą, należy najpierw zaznaczyć pierwszy zakres, a następnie trzymając wciśnięty klawisz Ctrl, zaznaczyć drugi i kolejne zakresy. Rysunek 2.5. Wykres skumulowanych częstości procentowych (dane rzeczgywiste oraz wartości ścisłego rozkładu normalnego) Jeśli stopy zwrotu mają rozkład normalny, rozkład skumulowany powinien mieć kształt zbliżony do litery S (jak linia przerywana). Rzeczywiste wartości logarytmów stóp zwrotu odbiegają nieco od wartości rozkładu normalnego, co może wynikać ze skośności. 2.3.2. Zastosowanie funkcji KWARTYL Funkcja -9#46;. VCDNKECMYCTV[N zwraca kwartyl zestawu danych. Druga dana wejściowa, kwartyl, jest liczbą całkowitą określającą, który kwartyl ma zostać zwró- cony: jeśli będzie mieć wartość 0, wówczas zwrócona zostanie najmniejsza wartość z tablicy; jeśli 1, zwrócony zostanie kwartyl pierwszy (czyli 25. percentyl tablicy); jeśli 2, zwrócona zostanie mediana (percentyl 50.); jeśli 3, kwartyl trzeci (percentyl 75.); jeśli 4, zwrócona zostanie wartość maksymalna. Dzięki kwartylom można szybko i stosunkowo łatwo uzyskać skumulowany rozkład zestawu danych. Na przykład wpisanie w komórce H22 na rzysunku 2.6 formuły: -9#46;.  ) 28 Część I ♦ Zaawansowane modelowanie w Excelu Rysunek 2.6. Kwartyle logarytmów stóp zwrotu z arkusza Częstość gdzie G22 zawiera wartość całkowitą 1, spowoduje zwrócenie wartości pierwszego kwartyla. Wyświetlona zostanie liczba –0,043 informująca, że 25 pozycji z zestawu danych ma wartość od niej niższą. Drugi kwartyl o warztości 0,028 wyznacza medianę, a kwartyl trzeci, równy 0,075, wyznacza wartość, poniżej której znajduje się 75 po- zycji zestawu danych. Rysunek 2.6 przedstawia wykres XY utworzony na podstawie zakresu H21:I25, na którym zaznaczono punkty danych. Linia wartości skumulowa- nych, wyznaczona na podstawie tylko pięciu punktów danych, jest bardzo zbliżona do jej dokładniejszej wersji z rysunku 2.5. Funkcję -9#46;. zastosujemy w punkcie 3.5 jako przykład obsługi tablic w VBA. Powiązana z nią funkcja 2 4 06;. VCDNKEC M , zwracająca wartość M-tego percen- tylu zestawu danych, zostanie wykorzystana w punkcie 4.7, w przykładzie kodowania funkcji tablicowej. 2.3.3. Zastosowanie funkcji Excela rozkładu normalnego Funkcje statystyczne Excela dotyczące rozkładu normalnego noszą nazwy rozpoczy- nające się od słów 41 -Đ# 014/#.0;, a niektóre z nich zawierają dodatkowo literę S wskazującą, że domyślnie przyjmuje się w nich założenie o standardowym rozkładzie normalnym. Funkcja 41 -Đ# 014/#.0;5 zwraca funkcję rozkładu skumulowanego dla standar- dowego rozkładu normalnego. Funkcja 41 -Đ# 014/#.0;51 9 RTCYFQRQFQDKGēUVYQ zwraca wartości dla podanych prawdopodobieństw. Nieco bardziej uniwersalna funkcja 41 -Đ# 014/#.0; ZħTGFPKCQFEJ[NGPKGAUVF UMWOWNQYCP[ dotyczy dowolnego rozkładu normalnego. Jeśli parametr wejściowy UMWOWNQYCP[ będzie miał wartość  (lub 24#9 #), funkcja zwróci wartości dla funkcji rozkładu skumulowanego; jeśli UMWOWNQYCP[ będzie miał wartość  (lub (#Đ5 ), zwró- cona zostanie funkcja gęstości prawdopodobieństwa. Rysunek 2.7 przedstawia arkusz Norm zawierający formułę dla gęstości prawdopodobień- stwa w komórce C5 oraz formułę dla prawdopodobieństwa lewostronnego w komórce D5. W obydwu formułach zastosowano funkcję 41 -Đ# 014/#.0; ze średnią i odchyle- niem standardowym zdefiniowanymi odpowiednio jako 0 i 1. W komórce C5 ostatnia Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 29 Rysunek 2.7. Funkcje Excela rozkładu normalnego z arkusza SNorm dana wejściowa (UMWOWNQYCP[) ma w przypadku formuły dla gęstości prawdopodo- bieństwa wartość 0, a w komórce D5 zawierającej formułę na prawdopodobieństwo lewostronne dana ta ma wartość 1. Wartości rzędnych odpowiadające prawdopodobieństwu lewostronnemu można odczytać z komórki F5 za pomocą funkcji 41 -Đ# 014/#.0;1 9. Aby zapoznać się z przedstawionymi funkcjami, wpisz fzormuły i sprawdź ich wyniki. W poprzednim punkcie wyznaczyliśmy rozkład skumulowanych częstości procento- wych logarytmów stóp zwrotu. Jego normalność można sprawdzić, stosując funkcję 41 -Đ# 014/#.0; wywoływaną z otrzymanymi wartościami średniej i odchylenia stan- dardowego i obliczając w ten sposób teoretyczne częstości procentowe. Takie działanie wykonano w kolumnie K arkusza Częstość. Otrzymane w jej wyniku częstości widnieją na wykresie na rysunku 2.5 — zostały one nałożone na rozkład rzeczywistych stóp zwrotu. Na wykresie można zaobserwować pewne odchylenia od rozkładu normalnego. Excel udostępnia bogaty zbiór funkcji służących do podsumowywania danych oraz modelowania różnorodnych rozkładów teoretycznych. Będziemy je często stosować w częściach książki dotyczących akcji i opcji. 2.4. Funkcje wyszukiwania Funkcje wyszukiwania pozwalają na znajdowanie określonych pozycji na podstawie podanych parametrów wejściowych w tabelach zawierających powiązane ze sobą informacje. Na przykład na rysunku 2.8 pokazano efekt zastosowania funkcji 9;5 7 -#,2+10191, która dla podanej zmienności zwraca wartość opcji kupna z tabeli zawie- rającej wartości zmienności i odpowiadające im wartości opcji kupna (tło teoretyczne przedstawimy w rozdziale 11. dotyczącym formuły Blackza-Scholesa). W ogólnym ujęciu funkcja: 9;5 7-#,2+10191 UWMCPCAYCTVQħèVCDGNCAVCDNKECPTAKPFGMUWAMQNWOP[ åRTGUWMKYCP[ACMTGU 30 Część I ♦ Zaawansowane modelowanie w Excelu Rysunek 2.8. Układ arkusza Wyszukiwanie służącego do wyszukiwania wartości opcji kupna odpowiadających podanej zmienności wyszukuje wartość w skrajnej lewej kolumnie tabeli (VCDGNCAVCDNKEC), a następnie zwraca wartość z tego samego wiersza we wskazanej kolumnie (PTAKPFGMUWAMQNWOP[). Domyślnie pierwsza kolumna tabeli musi być posortowana w porządku rosnącym (co oznacza, że RTGUWMKYCP[ACMTGU będzie miał wartość  (albo 24#9 #)). Jeśli tak rze- czywiście jest, to ostatni parametr wejściowy możnaz tak naprawdę zignorować. Przykłady wyszukiwania znajdują się w arkuszu Wyszukiwanie. Aby sprawdzić, czy przedstawione informacje zostały dobrze zrozumiane, można zastosować funkcję 9;5 7 -#,2+10191 do wyznaczania wysokości prowizji w zależności od wartości sprzedaży, opierając się na tabeli wskaźników prowizji w komórkach z zakresu F5:G7. Następnie należy przewinąć arkusz w dół, do tabeli Tabela wyszukiwania wartości Blacka-Scholesa opcji zakupu przedstawionej na rysunku 2.8. 5WMCPCAYCTVQħè (dla zmienności) znajduje się w komórce C17 (wartość 20 ), VCDG NCAVCDNKEC to zakres komórek F17:G27, przy czym zmienności posortowane są rosnąco, a wartości opcji kupna znajdują się w drugiej kolumnie tabeli VCDGNCAVCDNKEC. Zatem wpisana w komórce D18 formuła: 9;5 7-#,2+10191 () zwróci wartość opcji kupna równą , odpowiadającą zmienności na poziomie 20 . UWMCPCAYCTVQħè jest dopasowywana w przybliżeniu (lub dokładnie) do wartości w pierw- szej kolumnie tabeli, na tej podstawie wybierany jest wiersz i zwracana jest wartość ze wskazanej kolumny. Spróbuj poeksperymentować, wpisując w komórce C17 różne war- tości zmienności, na przykład  czy , i sprawdź, w jaki sposób działa ta funkcja. Parametr wejściowy RTGUWMKYCP[ACMTGU ma wartość logiczną (24#9 # lub (#Đ5 ), która wskazuje, czy funkcja ma dopasowywać wartości w sposób dokładny czy przy- bliżony. Jeśli parametr ten będzie mieć wartość 24#9 # lub zostanie pominięty, dopa- sowywanie będzie miało charakter przybliżony. Jeśli wartość dokładnie odpowiadają- ca szukanej wartości nie zostanie znaleziona, funkcja zwróci wartość największą, lecz nie większą od wartości UWMCPCAYCTVQħè. Jeżeli natomiast RTGUWMKYCP[ACMTGU będzie mieć wartość (#Đ5 , wówczas 9;5 7-#,2+10191 wyszuka wartość dokładnie odpowiadającą szukanej wartości lub zwróci wartość błzędną 0 . Istnieje również pokrewna funkcja 9;5 7-#,21 +1/1 wyszukująca wartości w górnym wierszu tabeli i odczytująca wartości ze wskazanegoz wiersza. Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 31 Kolejnymi funkcjami wyszukiwania są 21 #,21 ; ,ù oraz +0 -5, również przedsta- wione na rysunku 2.8. Funkcja 21 #,21 ; ,ù UWMCPCAYCTVQħè RTGUWMKYCPCAVCD V[RARQTÎYPCPKC zwraca względną pozycję takiej danej z tabeli zawizerającej jedną kolumnę (lub wiersz), która pasuje do podanej wartości w podanej kolejności (V[RARQ TÎYPCPKC). Należy zwrócić uwagę, że funkcja zwróci położenie w tabeli, a nie samą wartość. Jeśli wartością parametru V[RARQTÎYPCPKC będzie , funkcja zwróci położenie danej dokładnie odpowiadającej szukanej wartości (UWMCPCAYCTVQħè), bez względu na po- rządek tablicy. Jeśli jego wartością będzie , zwrócone zostanie położenie wartości w przybliżeniu odpowiadającej szukanej wartości przy założeniu, że tablica jest upo- rządkowana rosnąco. Jeżeli natomiast V[RARQTÎYPCPKC będzie równy Ō, wówczas funk- cja zwróci położenie wartości w przybliżeniu odpowizadającej wartości UWMCPCAYCTVQħè zakładając, że tablica jest posortowana w porządku mzalejącym. Na rysunku 2.8 wartości opcji kupna znajdujące się w kolumnie G są uporządkowane rosnąco. Aby znaleźć pozycję wartości z tablicy, która odpowiada wartości 9,73, formuła w komórce D22 powinna mieć postać: 21 #,21 ; ,ù )) Zwróci ona wartość  wskazującą na szóstą pozycję w tablicy G17:G27. Funkcja +0 -5 VCDNKECPTAYKGTUCPTAMQNWOP[ zwraca wartość z tablicy, położoną w wierszu i kolumnie o podanych numerach. Numery kolumny i wiersza znajdujące się w komórkach C25 i C26 zapewniają, że wyrażenie +0 -5, przedstawione na rysunku 2.9, zwróci wartość z szóstego wiersza drugiej kolumnyz tablicy F17:G27. Rysunek 2.9. Formuły funkcji i ich wyniki w arkuszu Wyszukiwanie Jeśli tablica posiada tylko jedną kolumnę (lub tylko jeden wiersz), wówczas PTAMQNWOP[ (albo PTAYKGTUC) jest niepotrzebny i zostawia się go pustym. Można sprawdzić, jak zadziała +0 -5 w przypadku takich tablic, zmieniając dane wejściowe w formule znaj- dującej się w komórce D27. Funkcje 9;5 7-#,2+10191, 21 #,21 ; ,ù i +0 -5 będziemy stosować w części książki dotyczącej akcji. 32 Część I ♦ Zaawansowane modelowanie w Excelu 2.5. Inne funkcje Opracowując formuły w arkuszach tam, gdzie było to możliwe, staraliśmy się tworzyć formuły „ogólne”, których składnia uwzględniałaby pokrewne lecz różne przypadki. Na przykład wartość przepływu pieniężnego w którymś roku dla którejś z obligacji z rysunku 2.10 mogłaby być zerowa, równa wartości kuponu lub równa wartości obli- gacji powiększonej o wartość kuponu. Rysunek 2.10. Formuła ogólna zawierająca różne adresy i zagnieżdżone funkcje JEŻELI z arkusza Obligacje Funkcja , Ľ .+ zwróci odmienne wyniki dla każdego z dwóch warunków. Można rów- nież napisać zagnieżdżoną instrukcję , Ľ .+ w taki sposób, by mogła zwracać trzy różne wyniki (a nawet więcej, jeśli zagnieżdżenie zostanie rozwinięte na dalsze poziomy). For- muła przepływu pieniężnego w komórce C11 zawierająca jeden poziom zagnieżdżenia: , Ľ .+ $  , Ľ .+ $   zwróci wartości przepływów pieniężnych dla każdego typu obligacji i dla każdego roku, jeśli zostanie skopiowana do komórek z zakresu C11:H13. W przypadku obligacji typu 1. wartość przepływu pieniężnego będzie zależeć od roku (komórka B11) oraz terminu wykupu (C6). Jeśli rok będzie wcześniejszy niż termin wykupu ($ ), wartość przepływu pieniężnego będzie równa wartości kuponu z komórki C5; jeśli osiągnięty zostanie termin wykupu ($ ), wartość przepływu pieniężnego będzie równa wartości obligacji powiększonej o wartość kuponu ( ); w pozostałych przypadkach ($ ) przepływ pieniężny będzie miał wartość 0. Zagnież- dżona instrukcja , Ľ .+ obsługuje przypadki, gdy termin wykupu obligacji został osią- gnięty lub przekroczony, a pierwszy warunek w zewnętrznej instrukcji , Ľ .+ obsłu- guje przypadki płatności kuponowych. W formule zastosowano „adresowanie mieszane”, aby zapewnić, że po jej skopiowaniu zmienią się odpowiednio znajdujące się w niej adresy komórek. Napisaliśmy  i , aby zapewnić, że po wkopiowaniu formuły kolumna C i wiersze 5. i 6. nadal będą odwo- ływać się do odpowiedniego terminu wykupu i wartości kuponu. Jednak $ zmieni się na $ oraz $ dla innych lat. Napisaliśmy $, dzięki czemu po skopiowaniu formuły do kolumny D rok nadal będzie odczytywany z kolumny B, natomiast  i  zmienią się na  i . Dokładne przemyślenie i napisanie tej formuły zwróci się z nawiązką, jeśli weźmiemy pod uwagę czas, jaki zaoszczędzimy w trakcie powielaniaz jej w ramach większego modelu. Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 33 2.6. Narzędzia inspekcji Pracując z formułami o dowolnej złożoności, warto jest mieć cały czas pod ręką przy- ciski uruchamiające narzędzia Inspekcji, umieszczone na przykład na pasku narzędzi. Można je uruchomić poprzez menu, wybierając pozycję Widok, a następnie Paski narzędzi i Dostosuj. Na ekranie pojawi się okno dialogowe Dostosuj przedstawione na rysunku 2.11, na którym należy zaznaczyć pasek narzędzi Inspekcja formuł — stanie się on wówczas widoczny. Rysunek 2.11. Udostępnianie paska narzędzi Inspekcja formuł zawierającego najważniejsze przyciski Najważniejsze przyciski widoczne są na rysunku 2.11 i noszą nazwy, poczynając od strony lewej, Śledź poprzedniki, Usuń wszystkie strzałki oraz Śledź zależności. Wróćmy jednak do arkusza. Zaznacz w nim komórkę C11 i kliknij przycisk Śledź poprzedniki, aby znaleźć te komórki, których wartości wykorzystywazne są w komórce C11. Efekt jest widoczny na rysunku 2.12, na którym widać również komórki zasilające komórkę F13. Aby usunąć wszystkie linie, kliknij przyciszk Usuń wszystkie strzałki. Rysunek 2.12. Efekt działania narzędzia Śledź poprzedniki zastosowanego w arkuszu Obligacje W oknie dialogowym Dostosuj można dostosowywać paski narzędzi według własnych upodobań. Po kliknięciu zakładki Polecenia i wybraniu odpowiedniej kategorii można przenieść wybrane narzędzia znajdujące się na liście poleceń — w tym celu należy zaznaczyć dany przycisk i przenieść go na pasek narzędzi. Możliwe jest również działa- nie odwrotne: zaznaczenie i przeniesienie przycisku poza pasek narzędzi doprowadzi do przeniesienia go do okna narzędziowego. 34 Część I ♦ Zaawansowane modelowanie w Excelu 2.7. Tabele danych Tabele danych pozwalają na wykonywanie sekwencji powtarzalnych obliczeń wartości formuł znajdujących się w komórkach bez konieczności ponownego ich wpisywania czy kopiowania. W skoroszycie ZMFExcel można znaleźć kilka przykładowych tabel danych. Wykorzystując obliczanie czynnika dyskontowego i kapitalizacji w arkuszu KapitalTabD, przedstawiamy tabelę danych z jedną zmienną wejściową, jak również tabelę z dwiema zmiennymi wejściowymi. Kolejny arkuszz, noszący nazwę BSTabD, zawiera inne przykłady zastosowania tabel danych, które jeszcze bardziej powinny przybliżyć ich rolę. 2.7.1. Tworzenie tabel danych z jedną zmienną wejściową Rysunek 2.13 przedstawia arkusz, w którym obliczany jest czynnik kapitalizacji odpo- wiadający kapitalizacji ciągłej dla stopy nominalnej równej 5 w okresie jednego roku (wynik widoczny jest w komórce C10). Czynnik dyskontowy odpowiadający stopie nominalnej w wysokości 5 dla jednego roku znajduje się w komórce D10. Na arkuszu przedstawiono również formuły znajdujące się w komórkach, służące do obliczania obu czynników. Rysunek 2.13. Układ tabeli danych z jedną zmienną wejściową, znajdującej się w arkuszu KapitalTabD Załóżmy, że chcemy zbudować tabelę czynników kapitalizacji i dyskontowych dla okresów o różnej długości, na przykład t = 1, 2, aż do 10 lat. Aby wykorzystać do tego celu tabelę danych, należy najpierw w odpowiedni sposób rozmieścić dane, tak jak w wierszu 16. i następnych. Formuła (formuły) służące do wykonywania obliczeń znajdują się w górnym wierszu tabeli (wiersz 16.). Zatem formuła w komórce D16 ma postać  , czyli w komórce stosowana jest po prostu formuła z komórki C10. Analogicznie, w komórce E16 wpisano Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 35 formułę  . Lista wymaganych długości okresów dla danej wejściowej t znajduje się w kolumnie C i zaczyna się w wierszu następnym po wierszu, w którym widnieją formuły. Zwróćmy uwagę, że komórka C16, znajdująca się na przecięciu wiersza z for- mułami i kolumny z wartościami, pozostała pusty. W przykładzie przedstawionym na rysunku 2.13 tak zwanym zakresem tabeli jest zakres C16:Ez26. Teraz arkusz jest już przygotowany do przeprowadzania obliczeń na tabeli danych. Wystarczy więc:    Zaznaczyć zakres tabeli, czyli komórki z zakresu C16:E26. W menu głównym wybrać pozycję Dane, a następnie Tabela. W oknie dialogowym w polu Kolumnowa komórka wejściowa wpisać:  następnie kliknąć OK. Wyniki tej operacji są pokazane na rysunku 2.14, na którym dla zwiększenia czytelno- ści odpowiednio sformatowano niektóre komórki. W komórkach tabeli widnieją warto- ści liczbowe, choć w rzeczywistości zawierają one formuły tablicowe. Są to wartości dynamiczne, co oznacza, że będą obliczane na nowo za każdym razem, gdy zmianie ulegnie któryś z parametrów, na przykład stopa procentowa r, lub gdy zmieniona zosta- nie jedna lub więcej wartości zmiennej t. Można się o tym przekonać, nadając stopie procentowej w komórce C5 wartość 6 i obserwując zmianę wartości w komórkach tabeli. Aby kontynuować lekturę, należy ustawić stopę procentową z powrotem na 5 . Rysunek 2.14. Tabela danych zawierająca wartości czynników kapitalizacji i dyskontowych dla okresów o różnej długości 2.7.2. Tworzenie tabel danych z dwiema zmiennymi wejściowymi Załóżmy, że chcielibyśmy obliczyć czynniki dyskontowe (za pomocą formuły z komór- ki C11) odpowiadające nie tylko różnym okresom, ale również różnym stopom pro- centowym. Ponownie pierwszą czynnością będzie odpowiednie ułożenie obu zmien- nych wejściowych, zanim wywołana zostanie procedura Tabela. Jeden z możliwych do zastosowania układów przedstawiono na rysunku 2.15.z Jak widać na rysunku, obszarem tabeli jest zakres komórek C30:I40. W pierwszej kolumnie znajdują się długości okresu t (kolumnowej zmiennej wejściowej), dla któ- rych należy obliczyć współczynniki dyskontowe (dane kolumnowe). W wierszu 30. znajduje się pięć wartości stopy procentowej r (dane wierszowe). Komórka w lewym 36 Część I ♦ Zaawansowane modelowanie w Excelu Rysunek 2.15. Układ tabeli danych z dwiema zmiennymi wejściowymi, znajdującej się w arkuszu KapitalTabD górnym rogu tabeli (C30) zawiera formułę, na podstawie której zostaną obliczone war- tości współczynnika dla wszystkich kombinacji wartości stopy procentowej i okresu. Formuła w komórce C30 ma postać  , a więc stanowi odniesienie do formuły obli- czania współczynnika dyskontowego. Aby zakończyć tworzenie tabeli danych, należy:    Zaznaczyć zakres tabeli, czyli komórki C30:I40. W menu wybrać pozycje Dane i Tabela. W oknie dialogowym w polu: Kolumnowa komórka wejściowa wpisać  Wierszowa komórka wejściowa wpisać a następnie kliknąć OK. Wyniki tych czynności są przedstawione na rysunku 2.16. Można sprawdzić, że wartości otrzymane dla stopy równej 5 zgadzają się z wartościami otrzymanymi wcześniej, widocznymi na rysunku 2.14. Rysunek 2.16. Tabela danych zawierająca czynniki dyskontowe dla różnych stóp procentowych i okresów o różnej długości Tabele danych nadają się przede wszystkim do przeprowadzania analiz typu „co jeśli” w niezwykle prosty sposób. Dobrą wiadomością jest to, że tabele automatycznie uwzględ- niają zmiany wprowadzone w modelu. Jest jeszcze druga, zła wiadomość: jeśli w arku- szu znajduje się większa liczba tabel z danymi, ich ciągłe przeliczanie może znacznie zmniejszyć szybkość, z jaką będą uwzględniane zmiany polegające na dodawaniu nowych pozycji lub modyfikacji wartości już obecnych w arkuszu. Z tego właśnie powo- du możliwe jest wyłączenie automatycznego przeliczaznia tabel. Konstruując tabele danych, należy pamiętać o kilku rzezczach:  Obecnie Excel wymaga, by komórki wejściowe tabeli danyzch znajdowały się w tym samym arkuszu co tabela. Rozdział 2. ♦ Zaawansowane funkcje i procedury Excela 37  Komórki tabeli danych zawierają formuły tablicowe, czyzli mają one na przykład postać ]6#$ .#   _, gdzie C5 i C7 są komórkami wejściowymi. Z tego względu nie istnieje możliwość edzytowania pojedynczej formuły znajdującej się w tabeli.  W celu przebudowania lub rozszerzenia tabeli danych nazleży zaznaczyć wszystkie komórki zawierające formułę ]6#$ .#_, a następnie z menu Edycja wybrać pozycję Wyczyść wszystko lub nacisnąć Delete.  Każda zmiana wartości wejściowych lub wartości zmiennzych spowoduje ponowne przeliczenie tabeli danych, chyba że wyłączonaz zostanie domyślna metoda automatycznego przeliczania. W przypadku dużych modeli, w których przeliczanie tabeli po każdorazowej zmianie zabiera dużo czasu, może zajść konieczność wyłączenia opcji automatycznego przeli- czania tabel danych. W tym celu należy:  z menu wybrać pozycję Narzędzia oraz Opcje,  wybrać zakładkę Przeliczanie, po czym zaznaczyć Automatyczne z wyjątkiem tabel. Po wyłączeniu automatycznego przeliczania wszystkie tabele będą ponownie przeliczane po naciśnięciu klawisza F9. Jeśli znasz już formułę Blacka-Scholesa wyceny opcji, możesz utrwalić wiedzę na temat tabel danych, konstruując trzy tabele proponowane w arkuszu BSTabD. Dzięki nim możliwe będzie przeanalizowanie wrażliwości wartości opcji zakupu z formuły Blacka- Scholesa na zmiany bieżącej ceny akcji S oraz na zmiany wartości innych zmiennych wejściowych. 2.8. Wykresy XY W Excelu można tworzyć wykresy różnych typów, lecz dla celów matematycznych, naukowych i finansowych zaleca się stosowanie wykreszu XY (Punktowego). Tam, gdzie nie spowoduje to dwuznaczności, będziemy o tym wykresie mówić po prostu jako o wykresie XY. Ważną rzeczą jest to, że na wykresie XY obie osie, czyli X i Y, są skalowane liczbowo. We wszystkich pozostałych wykresach posiadających dwie osie (w tym w wykresie liniowym) skalowane liczbowo są tylko osie pionowe, natomiast na osiach X wyświetlane są etykiety. By utworzyć wykres XY, korzysta się z Kreatora wykresów, który przeprowadza użyt- kownika przez cztery kroki noszące nazwy Typ wykresu, Źródło danych, Opcje wykresu oraz Położenie wykresu. Zakładając, że niemal za każdym razem będziemy tworzyć wykres XY osadzony w arkuszu, najważniejszym spośród tych czterech kroków będzie krok drugi — Źródło danych. Kolejne kroki przedstawimy, opierając się na wartościach z tabeli danych z jedną zmienną wejściową opisaną w zpunkcie 2.7.1 i przedstawioną na rysunku 2.14. Wartości z tabeli danych, które chcemy zamieścić na wykresie, 38 Część I ♦ Zaawansowane modelowanie w Excelu znajdują się w arkuszu KapitalTabD w komórkach z zakresu C17:E26. Kolumna C za- wiera wartości x, a na wykresie mają znaleźć się odpowiadające im wartości z ko- lumn D i E. Po zaznaczeniu danych, które chcemy zobaczyć na wykresie, należy wy- konać następujące czynności: 1. Na g
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Zaawansowane modele finansowe z wykorzystaniem Excela i VBA
Autor:
,

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: