Portfólió követése Excelben: felépítés, képletek és XIRR (magyarul)

Mire jó a táblázat, és mire nem?

Ha a befektetéseid egy-két számlán vannak, és kevés tranzakciót kötsz, egy jól felépített Excel-táblázat elegendő a követéshez. A kulcs a szerkezet: minden eseményt egyszer rögzítesz, a többit képletek számolják. A cikk általános módszertan, nem befektetési tanács, és minden példa kitalált adat.

A portfólió-követés fogalmairól (bekerülési érték, osztalék, TBSZ, hozamtípusok) a Befektetési portfólió követése 2026-ban cikkünk szól. Itt az Excel-kivitelezésre koncentrálunk. Ha Google Táblázatot használnál (automatikus árfolyam-lekérés, megosztás, telefonos használat), ahhoz külön cikk készült: Portfólió követése Google Táblázatokban.

A táblázat felépítése: három lap

1. lap: Tranzakciók

Oszlopok (a sor 2-től kezdődik, az 1. sor a fejléc):

A forintos pénzáram képlete az I2 cellában (magyar nyelvű beállításnál a listaelválasztó pontosvessző, angol beállításnál vessző). A képletek angol függvénynevekkel szerepelnek; magyar nyelvű Excelben a magyar nevet kell használni, ezeket a táblázat mutatja:

=IF(B2="Vétel"; -(D2*E2*G2)-H2;
  IF(B2="Eladás"; D2*E2*G2-H2;
  IF(B2="Osztalék"; E2*G2-H2; 0)))

Az előjel-szabály az egész táblázat alapja: ami kimegy a zsebedből (vétel, díj), az negatív, ami bejön (eladás, osztalék), az pozitív. Az XIRR ezt az előjelezést várja: legalább egy pozitív és egy negatív érték kell hozzá, és ha az első érték költség vagy kifizetés, annak negatívnak kell lennie (Microsoft súgó).

Kitalált példa-adatok

A PÉLDA-A nem létező eszköz, az árak és a dátumok szemléltetésre kitaláltak, nem történelmi adatok.

2. lap: Állomány

Az A oszlopban az azonosítók (itt: PÉLDA-A), a többi oszlop képlet. A Tranzakciók lap oszlopait teljes oszloppal hivatkozzuk (C:C, D:D).

A példában a vételek összköltsége 22 200 Ft (10 100 + 12 100), a vásárolt darabszám 20, így az átlagár díjjal 1 110 Ft (22 200 / 20), díj nélkül 1 100 Ft (saját számítás). Ez az átlagár-módszer: az eladás a darabszámot csökkenti, az átlagárat nem változtatja. Ha a számlavezetőd vagy az adóbevallásod más sorrendet (például FIFO) alkalmaz, a követésed eltérhet attól; adóügyben a NAV tájékoztatója irányadó.

Az aktuális ár

A legegyszerűbb módszer, ha az aktuális árat havonta egyszer kézzel beírod a számlakivonatról vagy a kereskedési felületről. Az automatikus árfolyam-lekérés lehetőségeiről és elérhetőségéről a Microsoft súgója ad tájékoztatást, ebben a cikkben nem építünk rá. Automatikus árfolyamnál ellenőrizd, hogy a lekért ár és a forintos érték ugyanabban a devizában van-e. A Google Táblázatok beépített árfolyam-függvényéről a Google Táblázatos cikkünk szól.

3. lap: Hozam és az XIRR

A Hozam lapra másold át a pénzáramokat időrendben, két oszlopba (A: dátum, B: pénzáram). A legutolsó sor legyen a portfólió mai értéke pozitívan, mai dátummal.

Az utolsó sor a példában 20 darab, darabonként 1 500 Ft (kitalált aktuális ár), vagyis 30 000 Ft.

Az éves hozam:

=XIRR(B2:B5; A2:A5)

Az eredmény a példában 13,5% évesítve (saját számítás, kitalált adatokkal). Az XIRR a dátumokhoz kötött, nem feltétlenül szabályos időközű pénzáramok belső megtérülési rátáját számítja (Microsoft súgó).

Miért nem elég az egyszerű hozam?

A példában a befektetett összeg 22 200 Ft, a kapott érték és osztalék 30 150 Ft, a nyereség 7 950 Ft. Ez egyszerű hozamként 35,8% (saját számítás). Ugyanez évesítve, az idő figyelembevételével 13,5%, mert a pénz az első vételtől számítva 984 napig, vagyis közel 2,7 évig volt bent. A két szám más kérdésre válaszol: az egyik azt mutatja, mennyivel lett több a pénzed, a másik azt, mekkora éves ütemnek felel meg ez.

Az XIRR a te pénzmozgásaidhoz kötött hozam. Ha a portfóliódat egy mutatóhoz akarod hasonlítani, az idősúlyozott hozam a megfelelő mérőszám; erről a portfólió-követési cikkünk szól.

Excel-eszközök, amelyek rendben tartják a táblázatot

Legördülő lista a Típus oszlopra. A B oszlopba (Vétel, Eladás, Osztalék) adatérvényesítéssel legördülő listát tehetsz, így nem tudsz elgépelni egy típust, és a pénzáram-képlet mindig felismeri. Az Adatok lap Adatérvényesítés parancsánál a Lista típust kell választani, a forrás lehet egy külön lapon felsorolt három elem (Microsoft súgó). Ha a forrást Excel-táblázatként tárolod, a listák automatikusan frissülnek az elemek hozzáadásakor és törlésekor (Microsoft súgó).

Kimutatás az összesítéshez. Ha a tranzakcióid sok sort tesznek ki, a Beszúrás lap Kimutatás parancsával azonosítónként és típusonként összesíthetsz kézi képlet nélkül. A Microsoft súgója szerint a kimutatáshoz tiszta, táblázatos adatot érdemes használni, Excel-táblázatként formázva, és minden oszlopnak egyedi, nem üres fejléc kell. Ha új adatot veszel fel a kimutatás forrásába, a kimutatást frissítened kell (Microsoft súgó). A kimutatás nem helyettesíti az Állomány lap átlagár-képleteit, de gyorsan megmutatja például az összes vételi díjat azonosítónként.

Excel-táblázat a Tranzakciók lapra. Ha a Tranzakciók lap adatait Excel-táblázatként formázod, a hozzá kapcsolódó legördülő lista a bővülést automatikusan követi (Microsoft súgó). A kimutatást új sorok után frissítened kell.

Devizás tételek forintosítása

Ha a tranzakciód külföldi devizában történt, a G oszlopba a tranzakció napján érvényes forint/deviza árfolyamot írd. Hivatalos árfolyamnak használhatod az MNB közzétett árfolyamát: az MNB az árfolyamokat munkanapokon (hétfőtől péntekig) állapítja meg, és a következő közzétételig érvényesek. Hétvégi vagy ünnepnapi tranzakciónál ezért a legutóbbi közzétett árfolyamot használhatod. Ez a követés egyszerűsítése, adóügyi célra a NAV tájékoztatója az irányadó.

A forintos hozamot két tényező adja: az eszköz saját devizás hozama és a deviza forinttal szembeni változása.

forinthozam = (1 + devizás hozam) × (1 + árfolyamváltozás) − 1

Kitalált példa: ha egy eszköz devizában 10%-ot hoz, a deviza forintban kifejezett árfolyama pedig 5%-ot esik, a forinthozam 1,10 × 0,95 − 1 = 4,5% (saját számítás). A képlet szorzás, ezért a két hatást külön érdemes látni, nem összeadni.

Havi rutin: egyeztetés

Havonta egyszer:

1. Vesd össze az Állomány lap darabszámát a számlakivonatoddal. Eltérésnél keresd meg a hiányzó vagy dupla sort.
2. Írd be az aktuális árat és frissítsd a Hozam lap utolsó sorát (mai dátum, mai érték).
3. Nézd meg az XIRR-t és az egyszerű hozamot egymás mellett, és jegyezd fel, hogyan változott.

Gyakori hibák

• A saját számláid közti átvezetés nem hozam. Ne vedd fel vételként és eladásként, különben a pénzáramok torzulnak.
• Szövegként tárolt dátum. Az XIRR #VALUE! hibát ad, ha a dátum nem érvényes dátum (Microsoft súgó). Ellenőrizd, hogy a cella dátumformátumú.
• Elfelejtett mai érték. Az utolsó sor nélkül az XIRR csak befizetéseket és osztalékot lát.
• Dupla osztalék. Ha az osztalék egy másik rendszerben már a tőkéhez van írva, ne rögzítsd még egyszer.
• Előjelhiba. Ha minden pénzáram pozitív vagy minden negatív, az XIRR #NUM! hibát ad (Microsoft súgó).

Ha nem akarsz táblázatot karbantartani

Az ÁllásKisokos Portfólióm eszköze a leírása szerint egy helyen kezeli a részvényeket, az egyéb értékpapírokat, a lakossági állampapírokat, a kriptoeszközöket és a készpénzt. Hogy az egyes eszköztípusoknál milyen árfolyamadat érhető el, azt az eszközben láthatod. Bejelentkezés után használható. Ha a költéseidet is követni szeretnéd, a Költségkövető a bevételeket és kiadásokat kezeli, és a megtakarítási rátádat is kiszámolja havonta. A két adat együtt a FIRE-hez is jó kiindulópont.

Összefoglalás

• Három lap: Tranzakciók, Állomány, Hozam; egy tételt egyszer rögzítesz.
• Az előjel-szabály: ami kimegy, negatív, ami bejön, pozitív.
• Az XIRR-hez a mai értéket is pozitív pénzáramként add hozzá, mai dátummal.
• Az egyszerű hozam és az XIRR más kérdésre válaszol, a kettőt együtt érdemes nézni.
• A példák kitalált adatok, az eredmények saját számítások, nem előrejelzés és nem befektetési tanács.

Források és feltételezések

Az alábbi táblázat minden állítást forráshoz vagy saját számításhoz köt.

Feltevések: a példa-adatok kitaláltak; az átlagár-módszer az eladásnál az átlagárat nem módosítja; a hozamok adó nélkül értendők; az aktuális ár és árfolyam kézzel beírt érték. Az adózásra és az árfolyam hivatalos használatára vonatkozóan a NAV tájékoztatója az irányadó.