Po co własny szablon portfolio inwestycji w Excelu i czego ma pilnować
Różnica między „ładną tabelką” a roboczym narzędziem do decyzji
Szablon portfolio inwestycji w Excelu może być tylko estetyczną tabelą z kilkoma liczbami albo narzędziem, które faktycznie pomaga podejmować decyzje. Różnica zaczyna się od pytania: czy z arkusza można od razu wyciągnąć konkretną decyzję – sprzedać, dokupić, zmienić strukturę portfela – czy trzeba jeszcze „dopowiadać” sobie resztę w głowie.
Robocze narzędzie do zarządzania portfelem powinno łączyć dane o transakcjach, aktualne notowania, zyski i straty, dywidendy oraz wykres struktury portfela. Nie chodzi o zaawansowaną matematykę, tylko o spójny obraz: ile kapitału jest w jakich aktywach, jakie generują zyski, jak zachowuje się ryzyko. Portfolio inwestycyjne w Excelu ma stać się „panelami kontrolnymi”, a nie tylko rejestrem historii.
Ładna tabelka to często jednowymiarowe zestawienie – lista akcji, ich cen, może prosty zysk/strata. Robocze narzędzie rozdziela dane na moduły (transakcje, słowniki, dywidendy, raporty), pozwala filtrować, sortować, generować wykres struktury portfela, a przede wszystkim odpowiada na proste pytanie: co aktualnie dzieje się z moim portfelem?
Jakie decyzje ma wspierać: dokupowanie, sprzedaż, rebalancing, ocena ryzyka
Excel dla inwestorów indywidualnych nie musi być „drugim Bloombergem”. Ma wspierać kilka powtarzalnych decyzji, które inwestor podejmuje co tydzień czy co miesiąc. Najczęściej są to:
Dokupowanie pozycji – czy warto powiększyć konkretną pozycję, czy jest już zbyt duża w odniesieniu do całego portfela; jak wygląda średnia cena zakupu po dokupieniu.
Sprzedaż – czy zrealizować zysk, czy ograniczyć stratę; jaki wpływ będzie miała sprzedaż na strukturę portfela i przepływy gotówkowe.
Rebalancing portfela w Excelu – porównanie docelowych udziałów (np. 60% akcje, 30% obligacje, 10% gotówka) z aktualnymi udziałami i policzenie, ile trzeba dokupić lub sprzedać, aby wrócić do celu.
Ocena ryzyka inwestycji – sprawdzenie, czy nie ma nadmiernej koncentracji na jednym instrumencie, sektorze, walucie lub koncie; identyfikacja pozycji, które ciągną portfel w dół.
Dobrze zaprojektowany szablon portfolio inwestycyjnego w Excelu powinien pozwalać przejść od obserwacji do poglądowych symulacji. Przykładowo: zmiana wartości jednej pozycji w arkuszu „Stan portfela” powinna od razu przeliczyć udział procentowy tej pozycji, pokazać wpływ na całkowitą stopę zwrotu oraz przesunąć ją na wykresie struktury portfela.
Podstawowe pytania kontrolne: co wiemy o portfelu, czego nie wiemy?
Każdy inwestor może wykonać prosty test swojego systemu raportowania. Pytanie brzmi: co wiemy o aktualnym portfelu, a czego nadal brakuje? Typowy arkusz pokazuje wartości pozycji, ale nie zawsze pozwala odpowiedzieć na bardziej szczegółowe pytania:
Jaka jest łączna wartość portfela w walucie bazowej, po przeliczeniu wszystkich walut?
Jaki jest udział poszczególnych klas aktywów (akcje, ETF, obligacje, gotówka) w portfelu?
Jak wygląda analiza zysków i strat – osobno zrealizowanych i niezrealizowanych?
Jaki był przepływ dywidend miesięcznie w ostatnich dwunastu miesiącach?
Na których instrumentach stopa zwrotu jest najwyższa, a które ciągną wynik w dół?
Jeżeli szablon portfolio inwestycji w Excelu od razu daje odpowiedzi na te pytania, można mówić o realnym narzędziu zarządzania. Jeżeli natomiast do każdej odpowiedzi trzeba dopisywać dodatkowe formuły lub przerzucać dane do innego pliku, wówczas szablon wymaga przebudowy.
Krótko o Excelu, aplikacjach brokerów i serwisach online
Brokerzy i serwisy online udostępniają rozbudowane podglądy portfela: wykresy, zestawienia zysków, raporty podatkowe. Mają jednak ograniczenia: pokazują tylko dane z jednego rachunku lub grupy rachunków, nie integrują gotówki w banku, funduszy poza domem maklerskim czy innych aktywów. Excel scala wszystko w jednym miejscu.
Różnica jest również w elastyczności. Aplikacje brokerskie narzucają sposób prezentacji danych. Własny szablon w Excelu pozwala zmieniać formuły, dodawać wskaźniki stopy zwrotu, dopinać nowe arkusze z analizą ryzyka czy specjalnymi raportami dywidend. Odpowiedzialność za poprawność obliczeń spada jednak na inwestora: trzeba pilnować formuł i struktury pliku.
Serwisy online pomagają przy notowaniach i wykresach kursów, ale z reguły nie śledzą historii indywidualnych transakcji tak precyzyjnie, jak dedykowany dziennik transakcji inwestycyjnych w Excelu. To podejście „od zewnątrz do środka”: widzimy wykres ceny, ale nie wiemy dokładnie, w jakim punkcie i za ile kupowaliśmy lub sprzedawaliśmy.
Główne moduły szablonu: dane, obliczenia, raporty, wykresy
Szablon portfolio inwestycyjnego w Excelu, który ma działać kilka lat, powinien być zbudowany modułowo. Praktyczny podział to:
Moduł danych – arkusze z danymi źródłowymi: słowniki instrumentów, lista kont, dziennik transakcji, rejestr dywidend, kursy walut i notowania.
Moduł obliczeń – arkusze, które na bazie danych liczą: stan portfela, koszt nabycia, zysk/stratę, stopy zwrotu, wpływy z dywidend.
Moduł raportowy – zestawienia zbiorcze P/L, raporty dywidend, rankingi instrumentów, przekrojowe analizy roczne i miesięczne.
Moduł wizualizacji – wykres struktury portfela (udziały procentowe), wykresy wartości portfela w czasie, słupkowe porównanie dywidend miesiąc po miesiącu.
Taki podział usprawnia pracę: dane wprowadza się zwykle ręcznie do kilku arkuszy, obliczenia i raporty aktualizują się automatycznie, a wykresy służą do szybkiej orientacji, bez zaglądania w każdy szczegół. Rozdzielenie tych warstw ogranicza chaos i minimalizuje ryzyko przypadkowego skasowania formuł.
Struktura szablonu – przegląd arkuszy i logika przepływu danych
Propozycja układu arkuszy
Spójny szablon portfolio inwestycji w Excelu dobrze zaczyna się od prostego, ale przemyślanego układu arkuszy. Praktyczny zestaw może wyglądać tak:
Ustawienia – waluta bazowa, parametry ryzyka, daty, zakresy do raportów.
Transakcje – dziennik wszystkich operacji (kupno, sprzedaż, przelewy, podatki, opłaty).
Dywidendy – rejestr wpływów dywidendowych i innych przychodów pasywnych.
Notowania – aktualne i historyczne ceny instrumentów oraz kursy walut.
Stan portfela – bieżące pozycje, ich wartość, zyski/straty, udział w portfelu.
Raporty – zyski i straty, stopy zwrotu, strukturę portfela, przegląd roczny.
Wykresy – skoncentrowany arkusz z wykresami udziałów, wartości portfela i dywidend.
Jeden z prostszych sposobów organizacji pracy to zaczynać od wypełniania arkuszy „Transakcje” i „Dywidendy”. Dane z nich „przepływają” przez arkusze obliczeniowe do „Stanu portfela” i „Raportów”, a następnie są wizualizowane w arkuszu „Wykresy”. Dzięki temu można łatwo śledzić drogę każdej liczby.
Rola słowników: instrumenty, kategorie, konta
Porządny słownik instrumentów i innych parametrów to niedoceniany element szablonu. Zamiast wpisywać nazwy akcji czy ETF-ów ręcznie w arkuszu „Transakcje”, lepiej odwoływać się do listy zdefiniowanej w jednym miejscu. Słowniki zmniejszają liczbę literówek, ułatwiają raportowanie i przyspieszają filtrowanie.
Minimalny zestaw słowników obejmuje:
Instrumenty – unikalny identyfikator (np. ticker), pełna nazwa, typ aktywa, waluta notowań.
Kategorie inwestycji – szerokie grupy: akcje, ETF, obligacje, gotówka, inne oraz podkategorie (np. akcje krajowe, akcje zagraniczne).
Konta i rachunki – każdy rachunek maklerski, IKE, IKZE, konto walutowe, rachunek gotówkowy.
Dzięki temu raport „Stan portfela” może grupować dane nie tylko po pojedynczych instrumentach, ale również po kategoriach czy kontach. Analiza staje się wielowymiarowa: można sprawdzić, jak zachowuje się portfel w IKE vs portfel spekulacyjny, jak rozkładają się akcje względem ETF-ów itp.
Oddzielenie danych źródłowych od formuł i wykresów
Jednym z klasycznych źródeł błędów w złożonych skoroszytach jest mieszanie danych ręcznie wprowadzanych z formułami. Bezpieczniejsze podejście to:
arkusze, w których ręcznie wpisuje się dane: Transakcje, Dywidendy, częściowo Notowania,
arkusze, w których nic nie wpisuje się ręcznie – tylko formuły: Stan portfela, Raporty, Wykresy.
Taki podział ułatwia też inspekcję błędów. Jeżeli coś się nie zgadza w bilansie portfela, najpierw sprawdza się, czy transakcje są poprawnie wprowadzone, a potem, czy formuły w arkuszu obliczeniowym prawidłowo zliczają dane. Użytkownik ma wyraźnie wydzielone „strefy”, których lepiej nie dotykać bez potrzeby.
Od transakcji do wykresu udziałów – prosty schemat przepływu danych
Przy projektowaniu szablonu portfolio inwestycyjnego w Excelu pomaga prosty schemat myślowy: skąd dokąd płyną dane. Typowy przepływ wygląda następująco:
Stan portfela – zsumowanie TRANSAKCJI dla każdego instrumentu (ilość bieżąca, koszt nabycia).
Notowania – dopięcie aktualnych cen rynkowych i kursów walut.
Obliczenia wartości – wyliczenie wartości rynkowej pozycji i całego portfela w walucie bazowej.
Raport struktury portfela – udział procentowy pozycji i kategorii w całkowitej wartości.
Wykres struktury portfela – wykres kołowy lub słupkowy na podstawie udziałów procentowych.
Analogiczny przepływ dotyczy dywidend: rejestr w arkuszu „Dywidendy”, podsumowanie w „Raportach” i wizualizacja w „Wykresach”. Kluczowe jest to, aby ta ścieżka była jasna od początku – ułatwia to późniejszą rozbudowę szablonu.
Miejsce na rozwój szablonu i nowe klasy aktywów
Portfel inwestycyjny zmienia się wraz z doświadczeniem inwestora. Dzisiaj może zawierać głównie akcje i ETF-y, jutro dojdą obligacje, złoto, fundusze nieruchomości, a może nawet kryptoaktywa. Szablon powinien być przygotowany na te zmiany.
Kilka prostych zasad projektowych:
zostawienie wolnych kolumn na dodatkowe parametry instrumentów w arkuszu „Słowniki”,
możliwość dopisania nowych typów aktywów w słowniku kategorii (bez przebudowy formuł),
projektowanie raportów w oparciu o kategorie, a nie tylko listę instrumentów – wtedy dodanie nowej klasy aktywów sprowadza się do uzupełnienia słownika,
przemyślane nazwy zakresów i tabel, aby formuły były czytelne i odporne na rozszerzenia.
Szablon portfolio inwestycji w Excelu, który ma żyć latami, nie może być projektem „zamkniętym”. Lepiej zostawić kilka nieużywanych na początku kolumn i arkuszy niż co pół roku przenosić wszystko do nowego pliku.
Arkusz „Ustawienia” i „Słowniki” – fundament, który decyduje o porządku
Co przechowywać w arkuszu „Ustawienia”
Arkusz „Ustawienia” pełni rolę panelu konfiguracyjnego. Nie zawiera transakcji ani wyników, ale parametry, które wpływają na obliczenia w całym skoroszycie. Typowy zestaw zmiennych w tym arkuszu to:
Waluta bazowa – np. PLN; na nią przeliczane są wszystkie wartości portfela.
Parametry ogólne, podatkowe i techniczne
Obok waluty bazowej, w „Ustawieniach” lądują wszystkie parametry, które rzadko się zmieniają, ale mają wpływ na interpretację danych. Praktyczny zestaw obejmuje:
Rok podatkowy i kraj rozliczenia – potrzebne do raportów rocznych i filtrowania transakcji.
Stawki podatkowe – np. podatek od zysków kapitałowych, podatek od dywidend (krajowy i zagraniczny), podatek Belki.
Domyślne prowizje – jeśli biuro maklerskie stosuje stałą lub minimalną prowizję, można ją zapisać jako parametr, który później wykorzystają formuły.
Parametry ryzyka – np. docelowy udział gotówki w portfelu, maksymalna ekspozycja na jednego emitenta, docelowy udział akcji vs obligacji.
Zakresy dat raportów – data początkowa i końcowa analiz, raportów rocznych, wykresów.
Źródła danych rynkowych – nazwy/identyfikatory używane do importu kursów (np. symbole w Yahoo Finance, nazwy serii obligacji z serwisu ministerstwa finansów).
Te parametry nie opisują konkretnych transakcji, ale tworzą ramy, w których portfel jest analizowany. Jeżeli zmieni się stawka podatku lub waluta bazowa, wystarczy korekta w jednym miejscu – resztą zajmują się formuły.
Jak zorganizować arkusz „Słowniki” w praktyce
„Słowniki” mogą przyjąć formę jednego arkusza z kilkoma tabelami lub kilku osobnych zakładek. Z technicznego punktu widzenia istotne jest, aby każda tabela miała:
unikalny klucz – np. ticker instrumentu, kod konta, symbol waluty,
czytelne nagłówki – bez skrótów, które po pół roku przestają być zrozumiałe,
spójne formatowanie – tabele Excela z włączonym automatycznym rozszerzaniem zakresu.
Przykładowy układ dla tabeli instrumentów:
Ticker – unikalny symbol (np. „XYZ”, „ETFUSA”),
Nazwa pełna – nazwa spółki lub ETF-u,
Typ aktywa – akcja, ETF, obligacja, gotówka, surowiec, REIT,
Kategoria – np. „Akcje krajowe”, „Akcje zagraniczne”, „ETF obligacyjny”,
Waluta notowań – PLN, EUR, USD itd.,
Kraj/rynek – GPW, USA, Europa, EM itp.,
Domyślny rachunek (opcjonalnie) – konto, na którym zwykle trzymany jest dany instrument.
Tak zbudowany słownik zasila większość rozwijanych list w arkuszu „Transakcje”, ogranicza pomyłki oraz umożliwia filtrowanie portfela po typie aktywa, rynku czy walucie.
Listy rozwijane i walidacja danych
Przy większej liczbie transakcji błędy wpisywania są nieuniknione. Część z nich można wyeliminować, korzystając z walidacji danych i list rozwijanych. Przykładowo:
kolumna Instrument w „Transakcjach” – lista rozwijana oparta o kolumnę „Ticker” w słowniku instrumentów,
kolumna Typ transakcji – lista obejmująca tylko zdefiniowane typy operacji (kupno, sprzedaż, dywidenda, podatek, prowizja, przelew),
kolumna Konto – lista na podstawie słownika rachunków,
kolumna Waluta – lista z tabeli walut, aby uniknąć literówek.
Walidacja nie rozwiąże wszystkich problemów, ale ujednolica dane. A jednolite dane to prostsze formuły i czytelniejsze raporty. Pytanie kontrolne na tym etapie brzmi: czy każdy rekord transakcji da się jednoznacznie przypisać do instrumentu, konta, waluty i kategorii? Jeżeli nie, brakuje pozycji w którymś słowniku.
Centralne miejsce na kursy walut i mapowanie walut
W portfelu wielowalutowym przydaje się oddzielna sekcja w „Słownikach” lub osobny arkusz na kursy. Minimalny wariant to tabela z kolumnami:
Data,
Waluta (np. USD, EUR),
Kurs do waluty bazowej (np. USD/PLN).
Dzięki temu każdą transakcję da się przeliczyć na walutę bazową po kursie z danego dnia lub z najbliższego dostępnego notowania. Logikę pobierania odpowiedniego kursu można zautomatyzować przy pomocy kombinacji funkcji wyszukiwania (np. WYSZUKAJ.X, INDEKS/PODAJ.POZYCJĘ) lub Power Query.
Źródło: Pexels | Autor: RDNE Stock project
Arkusz „Transakcje” – jak rejestrować każdy ruch w portfelu
Minimalny zestaw kolumn w dzienniku transakcji
Dziennik transakcji to serce szablonu. Zbyt wiele kolumn zniechęca do wprowadzania danych, zbyt mało utrudnia analizę. Prosty, ale kompletny zestaw może wyglądać tak:
Data – dzień rozliczenia transakcji.
Instrument – powiązanie ze słownikiem instrumentów (ticker lub kod).
Waluta prowizji – w przypadku rynków zagranicznych bywa inna niż waluta bazowa.
Podatek – np. potrącony przez brokera przy dywidendzie lub sprzedaży.
Waluta podatku.
Uwagi – miejsce na krótkie komentarze (np. „realizacja części zysku”, „rebalancing”).
Jeżeli portfel obejmuje wiele rynków, przydaje się jeszcze kolumna na kraj rynku lub rynek, aby później filtrować ekspozycję geograficzną.
Konwencje zapisu: ilości dodatnie czy ujemne?
Spójna konwencja ułatwia późniejsze obliczenia. Popularne podejścia są dwa:
Oddzielna kolumna „Kierunek” – np. kupno/sprzedaż, a ilości zawsze dodatnie.
Znaki w ilości – kupno jako ilość dodatnia, sprzedaż jako ilość ujemna, bez dodatkowej kolumny „Kierunek”.
Drugi model upraszcza zliczanie pozycji (wystarczy suma ilości), ale wymaga dyscypliny przy wpisywaniu danych. Niezależnie od wyboru, konwencja powinna być opisana w nagłówku lub w osobnej notatce w skoroszycie. Po pół roku przerwy łatwo zapomnieć, czy sprzedaż oznacza się minusem czy osobną flagą.
Transakcje gotówkowe, podatki i prowizje
Wiele arkuszy śledzi wyłącznie kupno i sprzedaż instrumentów, ignorując ruchy gotówkowe i koszty. To upraszcza widok, ale zniekształca wynik i obraz płynności. Pełniejszy obraz daje podejście, w którym:
wpłaty i wypłaty środków są rejestrowane jako osobne typy transakcji,
prowizje i podatki są przypisane do konkretnych transakcji lub przynajmniej do konkretnego konta w dniu ich pobrania,
saldo gotówki na każdym rachunku da się odtworzyć wyłącznie z dziennika transakcji (bez ręcznych korekt).
Przykład z praktyki: inwestor sprzedaje część udziałów w ETF-ie, broker pobiera prowizję w walucie rynku, a następnie, tego samego dnia, przewalutowuje środki na PLN. W dzienniku pojawiają się wówczas trzy wpisy: sprzedaż ETF-u, prowizja, transakcja FX. Bez takiego rozbicia trudno będzie pózniej jasno stwierdzić, skąd dokładnie wziął się końcowy wynik w złotych.
Automatyczne obliczanie wartości transakcji
Aby uniknąć powtarzalnych obliczeń ręcznych, w arkuszu „Transakcje” można dodać kilka kolumn wyliczeniowych:
Wartość brutto = Ilość × Cena jednostkowa.
Wartość netto = Wartość brutto − Prowizja − Podatek (w walucie transakcji).
Kurs waluty – zaciągany z tabeli kursów na daną datę.
Wartość w walucie bazowej – Wartość netto przeliczona po odpowiednim kursie.
Tak przygotowany dziennik staje się uniwersalnym źródłem zarówno do budowy „Stanu portfela”, jak i do analizy przepływów pieniężnych czy raportów podatkowych.
Obliczanie zysków i strat – od prostego P/L do stopy zwrotu portfela
Poziom 1: P/L na pojedynczej transakcji
Punkt wyjścia to wynik na konkretnej transakcji. Dla pojedynczego zakupu i sprzedaży tego samego pakietu akcjii obliczenie jest proste:
Zysk/strata brutto = (Cena sprzedaży − Cena zakupu) × Ilość.
Jeżeli jednak instrument był kupowany w kilku transzach, obliczenia trzeba oprzeć na koszcie średnim lub na bardziej zaawansowanej metodzie (np. FIFO/LIFO). Tutaj pojawia się pierwsze pytanie kontrolne: czy portfel ma być analizowany w logice średniego kosztu, czy z odwzorowaniem każdej partii (lotu) osobno?
Poziom 2: koszt średni i P/L na pozycji
Dla większości inwestorów wystarczy obliczenie kosztu średniego na danym instrumencie. W arkuszu „Stan portfela” zwykle pojawiają się kolumny:
Ilość bieżąca – suma ilości ze wszystkich transakcji kupna i sprzedaży dla danego instrumentu.
Łączny koszt nabycia – suma wartości netto (uwzględniających prowizje) wszystkich transakcji kupna.
Koszt średni = Łączny koszt nabycia / Ilość bieżąca.
Cena bieżąca – z arkusza „Notowania”.
Wartość bieżąca = Ilość bieżąca × Cena bieżąca.
Niezrealizowany P/L = Wartość bieżąca − Łączny koszt nabycia.
Takie podejście przyjmuje założenie, że sprzedaż redukuje ilość i wartość pozycji w sposób proporcjonalny. Dla raportu na potrzeby osobistej kontroli portfela to zwykle wystarcza, choć do rozliczeń podatkowych często potrzebny jest oddzielny model oparty na logice FIFO.
Poziom 3: P/L zrealizowany, niezrealizowany i łączny
Praktyczna analiza rozdziela zysk już zrealizowany (sprzedane pozycje) od niezrealizowanego (bieżące pozycje). W szablonie można to oddzielić na dwa sposoby:
utrzymywać osobną tabelę z historią zamkniętych pozycji,
lub obliczać P/L zrealizowany w arkuszu „Raporty”, agregując transakcje sprzedaży względem odpowiadających im transakcji kupna.
Podział na P/L zrealizowany i niezrealizowany pomaga odpowiedzieć na pytanie: jaka część wyniku jest już „zamknięta” i nie zależy od bieżących wahań rynku, a jaka jest tylko potencjalna.
Poziom 4: stopa zwrotu portfela z uwzględnieniem przepływów
Sam P/L w złotówkach nie zawsze wystarcza. Portfel jest regularnie zasilany wpłatami i obciążany wypłatami, co utrudnia prostą ocenę efektywności. Do opisu zwrotu portfela lepiej użyć miar typu:
Time-Weighted Return (TWR) – stopa zwrotu ważona w czasie, „oczyszczona” z wpływu wielkości wpłat i wypłat.
W Excelu MWR można przybliżyć funkcją XIRR, traktując:
wpłaty jako przepływy ujemne, wypłaty i wartość portfela na koniec okresu jako dodatnie. TWR wymaga podziału historii na okresy między przepływami kapitału i wyliczenia prostych stóp zwrotu w każdym z nich, a następnie ich złożenia.
Uwzględnianie dywidend w stopie zwrotu
Włączenie dywidend do przepływów pieniężnych
Dywidendy z punktu widzenia inwestora są klasycznym przepływem pieniężnym: wpływają na konto w konkretnym dniu, zwykle w walucie rynku, a część z nich bywa od razu konsumowana na podatek. Technicznie można je rozliczać na dwa sposoby:
traktować dywidendy jako dodatnie przepływy w funkcji XIRR, podobnie jak wypłaty z portfela,
lub ujmować je jako przychody wewnątrz portfela, ale wówczas w przepływach MWR/TWR uwzględnia się jedynie wpłaty i wypłaty „z zewnątrz”.
Pierwsze podejście lepiej pokazuje pełny strumień gotówki, drugie separuje efektywność zarządzania portfelem od decyzji, czy dywidendy są reinwestowane, czy wypłacane na konsumpcję. Co wiemy? Dywidenda zawsze zwiększa majątek inwestora. Czego nie wiemy bez szablonu? Jak duża część łącznego wyniku to dywidendy, a jak duża to zmiana cen.
Praktyczne rozwiązanie: w tabeli przepływów (np. w arkuszu „Raporty”) dodać osobną kategorię „Dywidendy” oraz kolumnę „Typ przepływu” (wpłata, wypłata, dywidenda, odsetki). Umożliwia to:
liczenie MWR zarówno „z dywidendami”, jak i „bez dywidend” (po prostu filtrując rodzaj przepływu),
budowę raportu stopy zwrotu price only (tylko zmiana cen) oraz total return (ceny + dywidendy).
Stopa zwrotu w różnych przekrojach: instrument, konto, waluta
Kiedy podstawowe formuły działają, naturalnym krokiem jest rozbicie stopy zwrotu na mniejsze części: osobny wynik dla konta IKE, osobny dla rachunku zagranicznego, osobny tylko dla ETF-ów akcyjnych. W Excelu można to osiągnąć na dwa główne sposoby:
budując dynamiczne tabele przepływów (przepływ + data + konto + instrument) i łącząc je z funkcją XIRR w połączeniu z funkcjami filtrującymi (np. FILTRUJ, JEŻELI.NR.KWERENDY),
lub generując pomocnicze kolumny typu „Przepływ dla konta X” (0 poza wybranym kontem, właściwy przepływ dla transakcji na koncie X), a następnie uruchamiając XIRR na tak przygotowanej serii.
W przypadku wielu walut pojawia się pytanie o poziom agregacji. Logika jest następująca:
jeżeli MWR ma być liczone w walucie bazowej portfela, każdy przepływ musi być przeliczony na tę walutę po kursie z dnia operacji,
jeżeli chcemy mieć MWR w walucie rynku (np. osobno w USD, osobno w EUR), lepiej podzielić przepływy na osobne strumienie według waluty i dla każdego liczyć MWR osobno.
Prosty przykład: inwestor prowadzi portfel w PLN, ale ma też rachunek w USD. Może policzyć MWR w PLN dla całego portfela, przeliczając każde zdarzenie po kursie NBP/BLOOMBERG z dnia, ale równolegle utrzymywać MWR w dolarach tylko dla części USD, ignorując przeliczenia FX. Dają to dwa różne spojrzenia na ten sam zestaw aktywów.
Arkusz „Dywidendy” i przychody pasywne – jak je śledzić i raportować
Podstawowy układ arkusza „Dywidendy”
Osobny arkusz dla dywidend porządkuje dane, które w dzienniku transakcji szybko giną wśród kupna i sprzedaży. Minimalna tabela może zawierać:
Data wypłaty – dzień, w którym broker faktycznie zaksięgował wpływ.
Instrument – powiązany ze słownikiem (ticker, ISIN, nazwa ETF-u).
Konto – rachunek, na który trafiła dywidenda.
Kwota brutto – dywidenda przed podatkiem, w walucie wypłaty.
Podatek potrącony – kwota podatku u źródła lub podatku krajowego.
Kwota netto – faktycznie otrzymana na konto (brutto − podatek).
Waluta – np. PLN, USD, EUR.
Kurs na dzień wypłaty – do przeliczenia na walutę bazową.
Kwota w walucie bazowej – netto po kursie.
Liczba jednostek w dniu ustalenia prawa – dla analizy dywidendy na akcję/JPU.
Część kolumn można wypełniać formułami, korzystając z powiązań z arkuszami „Ustawienia” (waluta bazowa) i „Słowniki” (domyślna waluta instrumentu). Jeżeli broker udostępnia plik CSV z historią dywidend, wprowadzanie danych da się w dużym stopniu zautomatyzować przez Power Query.
Powiązanie dywidend z transakcjami i stanem portfela
Same kwoty to jedno, druga kwestia to połączenie ich z konkretnymi pozycjami i transakcjami. Dla przejrzystości przydają się dwa dodatkowe wymiary:
Okres odniesienia – rok podatkowy, miesiąc, kwartał (np. kolumna „Rok”, „Miesiąc” wyliczona formułą ROK/TEKST).
Kategoria aktywa – np. akcje, ETF akcyjny, ETF obligacyjny, REIT (pobrane ze słownika instrumentów).
Taki układ umożliwia przynajmniej trzy typy analizy:
dywidendy według roku i instrumentu – suma, średnia dywidenda na akcję, zmiana rok do roku,
dywidendy według kategorii aktywów – np. jaki udział w dochodzie pasywnym generują ETF-y obligacyjne,
dywidendy według kont – łatwiejsza kontrola IKE/IKZE kontra rachunek standardowy.
Po połączeniu arkusza „Dywidendy” z „Stanem portfela” przez wspólny klucz (instrument, konto) da się policzyć bieżącą stopę dywidendy portfela (dividend yield): suma rocznych dywidend z ostatnich 12 miesięcy podzielona przez bieżącą wartość instrumentu lub portfolio.
Miary dochodu pasywnego: yield on cost i bieżący yield
Dywidendy można opisać kilkoma prostymi wskaźnikami. Najczęściej używane to:
Bieżąca stopa dywidendy (dividend yield) = dywidenda za ostatni rok / aktualna cena,
Yield on cost (YOC) = dywidenda za ostatni rok / koszt nabycia.
W szablonie można dla każdego instrumentu wyliczyć obie wartości:
w arkuszu „Dywidendy” agregując wypłaty z ostatnich 12 miesięcy,
w arkuszu „Stan portfela” łącząc bieżącą ilość, koszt nabycia i cenę rynkową.
Yield on cost pokazuje efekt historycznych decyzji zakupowych (czy cena wejścia była atrakcyjna względem dzisiejszych wypłat), natomiast bieżący yield urealnia oczekiwania na przyszłość – ile dywidendowo „pracuje” obecna cena rynkowa. To dwa różne punkty widzenia na tę samą pozycję.
Raportowanie dywidend według miesięcy i lat
Dla wielu inwestorów kluczowe jest nie tyle, ile dywidend wypłacił pojedynczy ETF, ile jak zmienia się dynamika przychodu pasywnego w czasie. Prosty raport można zbudować w oparciu o:
kolumny: Rok, Miesiąc, Kwota netto w walucie bazowej,
tabelę przestawną, w której:
w wierszach znajduje się Rok,
w kolumnach Miesiąc,
w wartościach suma Kwota netto.
Taki widok w kilka sekund odpowiada na pytania: czy rok do roku przychód rośnie, które miesiące są „chudsze”, jak wygląda sezonowość dywidend w portfelu. Na tej samej bazie można zbudować wykres słupkowy lub liniowy, gdzie suma za każdy miesiąc tworzy prostą historię dochodu pasywnego.
Arkusz „Stan portfela” – bieżące pozycje, wartości i przeliczenia walutowe
Źródła danych: transakcje, notowania, ustawienia
„Stan portfela” jest podsumowaniem, do którego spływają informacje z kilku miejsc:
z arkusza „Transakcje” – ilość bieżąca, łączny koszt nabycia, średni kurs zakupu,
z arkusza „Notowania” – cena bieżąca w walucie instrumentu, ewentualnie dodatkowe wskaźniki (P/E, stopa dywidendy rynkowa),
z arkusza „Ustawienia” – waluta bazowa portfela, zasady zaokrągleń, domyślne kursy.
W praktycznym układzie każdemu wierszowi w „Stanie portfela” odpowiada jedna kombinacja instrument + konto. Pozwala to oddzielić tę samą spółkę trzymaną np. na rachunku maklerskim od tej samej spółki na IKE, mimo że ticker jest identyczny.
Minimalny zestaw kolumn w „Stanie portfela”
Żeby arkusz był czytelny, a jednocześnie kompletny, można zacząć od takiego zestawu kolumn:
Ilość bieżąca – suma transakcji ilościowych z „Transakcje”.
Koszt całkowity w walucie instrumentu – suma wartości netto zakupów (po prowizjach).
Koszt średni – koszt całkowity / ilość bieżąca.
Cena bieżąca – z arkusza „Notowania”.
Wartość w walucie instrumentu – ilość × cena bieżąca.
Kurs do waluty bazowej – odpowiedni kurs FX z dnia wyceny.
Wartość w walucie bazowej – wartość w walucie instrumentu × kurs.
P/L niezrealizowany w walucie bazowej – wartość w walucie bazowej − koszt całkowity przeliczony po kursie zakupu lub po historycznych kursach (w zależności od przyjętej metody).
W przypadku pozycji gotówkowych część kolumn będzie pusta (brak ceny bieżącej), ale logika pozostaje ta sama: ilość = saldo, wartość = saldo, kurs = 1 dla waluty bazowej lub kurs FX dla zagranicznych kont.
Przeliczanie walut: kursy historyczne vs bieżące
Najczęstszy dylemat przy przeliczaniu wartości pozycji i P/L to wybór kursu. Możliwe są trzy strategie:
Kurs bieżący do wszystkiego – zarówno koszt, jak i wartość wyceniane są po aktualnym kursie FX.
Plus: prostota, łatwe porównania między instrumentami.
Minus: P/L miesza efekt ruchu instrumentu i kursu waluty.
Kurs historyczny dla kosztu, bieżący dla wartości – koszt nabycia w walucie bazowej liczony po kursie z dnia transakcji, bieżąca wartość po kursie aktualnym.
Plus: P/L odzwierciedla zarówno ruch instrumentu, jak i zysk/stratę na FX.
Minus: większa złożoność, wymaga tabeli kursów po datach.
Oddzielne śledzenie P/L na FX – instrument wyceniany w walucie lokalnej, a zmiany kursu traktowane jako osobne pozycje FX (transakcje walutowe).
Plus: przejrzyste oddzielenie wyniku z rynku i z waluty.
Szablon może wspierać wszystkie trzy podejścia, pod warunkiem, że w arkuszu „Transakcje” znajdują się zarówno daty, jak i waluty każdej operacji, a w pomocniczej tabeli kursów – kursy FX według dat. W praktyce wielu użytkowników zaczyna od najprostszego modelu (kurs bieżący do wszystkiego), a dopiero później rozbudowuje logikę.
Udział procentowy pozycji i wykresy struktury portfela
Główny powód, dla którego portfel ląduje w Excelu, to chęć zobaczenia, jak naprawdę wygląda struktura inwestycji. W arkuszu „Stan portfela” łatwo to przygotować, dodając kilka kolumn:
Udział w portfelu = wartość w walucie bazowej / suma wartości wszystkich pozycji.
Kategoria nadrzędna – np. akcje, obligacje, gotówka, alternatywne (ze słownika).
Region – kraj lub region rynku: Polska, USA, Europa rozwinięta, rynki wschodzące.
Sektor – technologia, finansowy, zdrowie itd. (o ile dostępne).
Na tej bazie można tworzyć wykresy:
kołowy – udział poszczególnych kategorii aktywów w całym portfelu,
Najczęściej zadawane pytania (FAQ)
Jak zrobić własne portfolio inwestycji w Excelu krok po kroku?
Najprostsza droga to podzielić plik na kilka arkuszy zamiast trzymać wszystko w jednej tabeli. Na start wystarczą: Transakcje (wszystkie kupna, sprzedaże, prowizje), Słowniki (lista instrumentów, kont, walut), Stan portfela (bieżące pozycje) oraz Wykresy (udziały procentowe, wartość portfela w czasie).
Najpierw wypełnia się dziennik transakcji, a pozostałe arkusze tylko przeliczają dane i je porządkują. Kluczowe pytanie kontrolne brzmi: czy po spojrzeniu w „Stan portfela” widzisz od razu, co masz, za ile kupione i jaki jest zysk/strata, czy musisz jeszcze coś sobie dopowiadać w głowie?
Jakie arkusze powinno zawierać dobre portfolio inwestycyjne w Excelu?
W praktyce sprawdza się podział na 7–8 arkuszy, które pełnią różne funkcje. Standardowy zestaw wygląda tak:
Słowniki – instrumenty, kategorie aktywów, konta, waluty.
Transakcje – dziennik wszystkich operacji.
Dywidendy – rejestr wpływów dywidendowych i odsetkowych.
Notowania – aktualne i historyczne ceny oraz kursy walut.
Stan portfela – bieżące pozycje, zysk/strata, udziały.
Raporty – zestawienia P/L, stopy zwrotu, analizy okresowe.
Wykresy – wizualizacje struktury portfela i dywidend.
Taki układ porządkuje przepływ danych: od ręcznie wprowadzanych transakcji i dywidend, przez obliczenia, po raporty i wykresy.
Czym różni się „ładna tabelka” od użytecznego szablonu portfolio w Excelu?
Ładna tabelka zazwyczaj pokazuje listę akcji, ich ceny i prosty zysk/stratę. Nie odpowiada jednak na kluczowe pytania: jaki jest udział poszczególnych klas aktywów, jak wygląda ryzyko koncentracji, co się stanie ze strukturą portfela po sprzedaży konkretnej pozycji.
Użyteczny szablon rozdziela dane na moduły (transakcje, słowniki, dywidendy, raporty), umożliwia filtrowanie, sortowanie oraz generowanie wykresów udziałów. Pozwala też na szybkie symulacje: zmiana wartości jednej pozycji powinna od razu pokazać nowy udział w portfelu i wpływ na stopę zwrotu. Pytanie kontrolne: co wiemy po jednym spojrzeniu w arkusz, a czego nadal nie widać?
Jak w Excelu policzyć udział procentowy aktywów i zrobić wykres struktury portfela?
Podstawą jest arkusz „Stan portfela”, w którym każda pozycja ma policzoną aktualną wartość rynkową w walucie bazowej. Na tej podstawie sumuje się łączną wartość portfela, a następnie dla każdej pozycji wylicza udział: wartość pozycji / wartość całego portfela.
Tak przygotowaną tabelę można podpiąć pod wykres kołowy lub paskowy. Praktyczne jest grupowanie według kategorii (np. akcje, ETF, obligacje, gotówka), a nie tylko według pojedynczych instrumentów – wtedy struktura portfela pokazuje, czy faktycznie trzymasz się założonej polityki (np. 60/30/10).
Czy Excel może zastąpić aplikację brokera do śledzenia portfela?
Excel nie zastąpi aplikacji brokera w zakresie natychmiastowych notowań czy zleceń, ale może przejąć rolę głównego rejestru portfela. Łączy dane z różnych rachunków, uwzględnia gotówkę na kontach bankowych, fundusze poza domem maklerskim czy inne aktywa, których broker po prostu nie widzi.
Różnica jest też w elastyczności: w Excelu sam decydujesz, jakie wskaźniki liczyć, jakie raporty i wykresy są potrzebne. Z drugiej strony to inwestor odpowiada za poprawność formuł i logikę pliku. Aplikacja brokera narzuca widok, ale nie wymaga dodatkowej pracy.
Jak w Excelu śledzić dywidendy i przepływy gotówkowe z portfela?
Do tego służy osobny arkusz „Dywidendy”. Każdy wpływ zapisuje się tam z datą, kwotą brutto/netto, walutą, instrumentem oraz kontem, na które trafiła gotówka. Na tej bazie można budować raporty miesięczne i roczne oraz wykres słupkowy pokazujący dynamikę dywidend.
Połączenie tego arkusza ze „Stanem portfela” i „Raportami” pozwala odpowiedzieć na konkretne pytania: ile dywidend wpłynęło w ostatnich 12 miesiącach, które spółki generują najwięcej gotówki oraz jak dywidendy wpływają na łączną stopę zwrotu portfela.
W jaki sposób Excel może pomóc w rebalancingu i ocenie ryzyka portfela?
W arkuszu ustawień można zdefiniować docelowe udziały (np. 60% akcje, 30% obligacje, 10% gotówka). Arkusz „Stan portfela” liczy aktualne udziały, a prosty raport pokazuje różnicę między celem a stanem bieżącym. Na tej podstawie da się policzyć, ile należy dokupić lub sprzedać danej klasy aktywów, żeby wrócić do założonej struktury.
Do oceny ryzyka przydają się dodatkowe kolumny w słownikach (np. sektor, kraj, waluta). Dzięki nim raport może wychwycić nadmierną koncentrację na jednym instrumencie, sektorze czy walucie i wskazać pozycje, które najmocniej ciągną wynik w dół.
Najważniejsze wnioski
Różnica między „ładną tabelką” a narzędziem decyzyjnym polega na tym, czy z arkusza można od razu wyciągnąć konkretne decyzje (dokupić, sprzedać, zmienić strukturę), bez dopowiadania reszty „w głowie”.
Funkcjonalny szablon portfela w Excelu musi łączyć transakcje, aktualne notowania, zyski/straty, dywidendy i wykres struktury portfela, tworząc spójny obraz kapitału, zyskowności i ryzyka.
Arkusz ma wspierać powtarzalne decyzje inwestora: dokupowanie (wielkość pozycji i średnia cena), sprzedaż (wpływ na portfel i gotówkę), rebalancing (powrót do docelowych udziałów) oraz ocenę koncentracji i ryzyka.
Dobre portfolio w Excelu od razu odpowiada na pytania kontrolne: jaka jest łączna wartość w walucie bazowej, struktura klas aktywów, rozkład zysków/strat, przepływ dywidend w czasie i które instrumenty ciągną wynik w górę lub w dół.
Rozwiązania brokerów i serwisów online są wygodne, ale pokazują tylko wycinek rzeczywistości (pojedynczy rachunek, brak pełnej historii transakcji i innych aktywów), podczas gdy Excel scala wszystkie konta, waluty i typy inwestycji w jednym pliku.
Własny szablon w Excelu daje elastyczność (zmiana formuł, nowe wskaźniki, dodatkowe raporty dywidend czy ryzyka), ale przenosi na inwestora odpowiedzialność za poprawność obliczeń i porządek w strukturze pliku.
