Stopa zwrotu · 1 z 6
Jak obliczyć stopę zwrotu portfela w Excelu
Wielu inwestorów prowadzi swój portfel w arkuszu Excela. To dobry nawyk, ale wzór, którego prawie wszyscy używają do liczenia stopy zwrotu, zawodzi, gdy tylko wpłacasz lub wypłacasz pieniądze. Pokazujemy, jak zrobić to dobrze, na przykładzie, który możesz skopiować: prosta stopa zwrotu, stopa zwrotu ważona kapitałem z funkcją XIRR i stopa zwrotu ważona czasem (TWR).
6 min czytania
W skrócie
- Jeśli nie wpłacasz ani nie wypłacasz pieniędzy, prosta stopa zwrotu, =(obecna wartość - wpłacone)/wpłacone, jest prawidłowa i nic więcej nie potrzebujesz.
- Przy wpłatach lub wypłatach prosta stopa zwrotu przestaje być wiarygodna, bo traktuje tak samo euro zainwestowane przez cały rok i euro, które weszło w zeszłym tygodniu.
- Aby sprawdzić, ile zarobiły twoje pieniądze, użyj funkcji XIRR w Excelu: uwzględnia datę każdego ruchu i zwraca stopę roczną.
- Aby porównać się z funduszem lub indeksem, oblicz TWR: stopy zwrotu kolejnych podokresów między ruchami, połączone ze sobą.
Zwykły wzór: prosta stopa zwrotu
Prosta stopa zwrotu porównuje, ile jest wart twój portfel, z tym, ile do niego włożyłeś. Jeśli w komórce B1 masz wpłaconą kwotę, a w B2 dzisiejszą wartość, wzór to =(B2-B1)/B1, sformatowany jako procent.
Jeśli wpłaciłeś 10 000 €, a dziś masz 11 000 €, zarobiłeś 10%. Dopóki nie przesuwasz pieniędzy, ta liczba jest prawidłowa i nic więcej nie potrzebujesz.
Dlaczego wpłaty ją psują
Problem zaczyna się przy drugiej wpłacie. Wzór traktuje tak samo euro zainwestowane przez cały rok i euro, które wpłynęło tydzień temu, a wynik przestaje cokolwiek jasno mówić.
Spójrz na przykład w tabeli. 1 stycznia 2025 roku wpłacasz 10 000 €. 1 lipca portfel jest wart 10 800 €, a ty dokładasz kolejne 5000 €. 31 grudnia jest wart 16 500 €. Wpłaciłeś 15 000 €, a masz 16 500 €, więc prosta stopa zwrotu pokazuje 10%. Ale te 5000 € było zainwestowane tylko przez pół roku, więc te 10% to za mało. Przy wypłacie błąd może pójść w drugą stronę.
| Wiersz | A: Data | B: Przepływ (€) | C: Wartość przed ruchem (€) |
|---|---|---|---|
| 2 | 01.01.2025 | -10 000 | 0 |
| 3 | 01.07.2025 | -5 000 | 10 800 |
| 4 | 31.12.2025 | 16 500 | 16 500 |
Stopa zwrotu ważona kapitałem w Excelu: funkcja XIRR
Stopa zwrotu ważona kapitałem (zwana też wewnętrzną stopą zwrotu) uwzględnia, kiedy każde euro weszło i wyszło. Excel oblicza ją funkcją XIRR, która przyjmuje ruchy w nieregularnych datach. Zwykła funkcja IRR zakłada, że wszystkie okresy są równej długości, a prawdziwy portfel tak nie działa.
Przygotuj dwie kolumny: w A daty, w B ruchy widziane z perspektywy twojej kieszeni. To, co wpłacasz, wpisujesz na minusie, to, co wypłacasz, na plusie, a w ostatnim wierszu aktualną wartość portfela na plusie, tak jakbyś tego dnia wszystko sprzedał.
Z danymi z tabeli wpisz =XIRR(B2:B4;A2:A4). Wynik to 12,1% i jest roczny: funkcja zawsze zwraca stopę w skali roku. Jeśli twój Excel oddziela argumenty przecinkami zamiast średnikami, użyj przecinków.
TWR: połącz podokresy
Stopa zwrotu ważona czasem odpowiada na inne pytanie: jak poradziły sobie twoje inwestycje, niezależnie od tego, kiedy dokładałeś pieniądze? Do jej obliczenia potrzebujesz jeszcze jednej danej: wartości portfela tuż przed każdym ruchem (kolumna C).
Każdy ruch zamyka podokres. Pierwszy podokres: z 10 000 € do 10 800 €, czyli 8% zysku. Drugi podokres: zaczyna się od 10 800 € plus nowe 5000 €, czyli 15 800 €, i kończy na 16 500 €, czyli 4,43% zysku. TWR łączy oba: =(1+8%)*(1+4,43%)-1, co daje 12,8%.
Skąd trzy różne liczby dla tego samego portfela? Prosta stopa zwrotu (10%) ignoruje czas. Stopa ważona kapitałem (12,1%) mierzy, ile zarobiły twoje pieniądze, z uwzględnieniem dat. TWR (12,8%) mierzy twoją strategię i to jego używa się do porównań z funduszem lub indeksem. Tutaj TWR jest wyższe od stopy ważonej kapitałem, bo dopłaciłeś tuż przed słabszym podokresem.
Twój arkusz, krok po kroku
Zapisuj każdy wpływ i wypływ pieniędzy
Jeden wiersz na każdą wpłatę lub wypłatę, z datą. Jeśli nie prowadzisz osobno gotówki portfela, każdy zakup liczy się jako pieniądze, które wchodzą, a każda sprzedaż, której nie reinwestujesz, jako pieniądze, które wychodzą.
Notuj wartość przed każdym ruchem
Tej danej potrzebuje TWR. Znajdziesz ją na wyciągu od brokera z danego dnia; jeśli nie zapiszesz jej od razu, później trudno ją odtworzyć.
Oblicz stopę ważoną kapitałem funkcją XIRR
Umieść aktualną wartość na plusie w ostatnim wierszu i wpisz =XIRR(przepływy;daty). Wynik jest już roczny.
Połącz podokresy, aby uzyskać TWR
Oblicz stopę zwrotu każdego podokresu, pomnóż przez siebie wszystkie wartości (1 + stopa zwrotu podokresu) i na końcu odejmij 1.
Gdzie Excel nie wystarcza
Przy małym portfelu i jednym brokerze arkusz działa. Trudniej go utrzymać: ręcznie aktualizować ceny, zapisywać każdą dywidendę i każdą prowizję, przeliczać to, co jest w dolarach, po kursie z danego dnia i łączyć wyciągi od kilku brokerów, każdy w innym formacie.
Najbardziej cierpi na tym TWR, bo wymaga dokładnej wartości portfela w dniu każdego ruchu, a tej liczby prawie nikt nie ma pod ręką, jeśli nie zapisał jej wtedy.
Co warto zapamiętać
Jeśli nie przesuwasz pieniędzy, wystarczy prosta stopa zwrotu. Jeśli wpłacasz lub wypłacasz, użyj XIRR, by sprawdzić, ile zarobiły twoje pieniądze, i połącz podokresy, by uzyskać TWR, jeśli chcesz porównać się z indeksem.
Jeśli wolisz nie prowadzić arkusza, w MyPortfolio możesz zaimportować wyciąg od brokera albo własny plik Excel, a także CSV, PDF czy zrzuty ekranu: obliczymy za ciebie stopę zwrotu ważoną kapitałem i TWR, po aktualnych cenach.
Najczęstsze pytania
Jak obliczyć stopę zwrotu portfela w Excelu?
Jeśli nie przesuwałeś pieniędzy, wzorem =(B2-B1)/B1, z wpłaconą kwotą w B1 i obecną wartością w B2. Jeśli były wpłaty lub wypłaty, użyj XIRR dla stopy zwrotu ważonej kapitałem i połącz podokresy, by dostać TWR.
Jak obliczyć stopę zwrotu, gdy dopłacam pieniądze?
Wpisz każdą wpłatę z datą jako liczbę ujemną, w ostatnim wierszu podaj obecną wartość portfela jako liczbę dodatnią i wpisz =XIRR(ruchy;daty). Dzięki temu każde euro liczy się tylko przez czas, w którym było zainwestowane.
Czym się różni IRR od XIRR?
Funkcja IRR zakłada, że wszystkie okresy są równej długości. XIRR przyjmuje ruchy w nieregularnych datach, takie jak w prawdziwym portfelu, więc to jej należy używać.
Czy XIRR podaje roczną stopę zwrotu?
Tak. XIRR zawsze zwraca stopę w skali roku, niezależnie od tego, czy twoje ruchy obejmują kilka miesięcy, czy kilka lat. W przykładzie z artykułu wychodzi 12,1% rocznie.
Czy da się obliczyć TWR w Excelu?
Tak, ale potrzebujesz wartości portfela tuż przed każdym ruchem. Oblicz stopę zwrotu każdego podokresu, pomnóż wszystkie czynniki (1 + stopa zwrotu podokresu) i na końcu odejmij 1.