Hozam · 1/6
Így számold ki a portfóliód hozamát Excelben
Sok befektető Excel-táblázatban vezeti a portfólióját. Ez jó szokás, de az a képlet, amellyel szinte mindenki a hozamot számolja, elromlik, amint pénzt teszel be vagy veszel ki. Egy lemásolható példán megmutatjuk, hogyan csináld jól: az egyszerű hozamot, a pénzsúlyozott hozamot az XBMR függvénnyel és az idősúlyozott hozamot (TWR).
6 perc olvasás
Röviden
- Ha nem teszel be és nem veszel ki pénzt, az egyszerű hozam, =(jelenlegi érték - befizetett)/befizetett, helyes, és semmi másra nincs szükséged.
- Befizetések vagy kivétek esetén az egyszerű hozam már nem megbízható, mert ugyanúgy kezeli az egész évben befektetett eurót, mint azt, amelyik a múlt héten érkezett.
- Ha azt akarod tudni, mennyit keresett a pénzed, használd az Excel XBMR függvényét: figyelembe veszi minden mozgás dátumát, és éves rátát ad vissza.
- Ha egy alaphoz vagy indexhez akarod mérni magad, számold ki a TWR-t: a mozgások közötti részidőszakok hozamát, összefűzve.
A szokásos képlet: egyszerű hozam
Az egyszerű hozam azt veti össze, mennyit ér a portfóliód azzal, amennyit beletettél. Ha a B1 cellában a befizetett összeg, a B2-ben a mai érték áll, a képlet =(B2-B1)/B1, százalék formátumban.
Ha 10 000 €-t tettél be, és ma 11 000 €-d van, 10%-ot kerestél. Amíg nem mozgatsz pénzt, ez a szám helyes, és semmi másra nincs szükséged.
Miért rontják el a befizetések
A gond a második befizetéssel kezdődik. A képlet ugyanúgy kezel egy eurót, amely egész évben be volt fektetve, mint egy olyat, amely a múlt héten érkezett, és a szám többé nem jelent semmi egyértelműt.
Nézd meg a táblázatban lévő példát. 2025. január 1-jén beteszel 10 000 €-t. Július 1-jén a portfólió 10 800 €-t ér, és beteszel még 5000 €-t. December 31-én 16 500 €-t ér. 15 000 €-t tettél be, és 16 500 €-d van, így az egyszerű hozam 10%-ot mutat. Csakhogy az 5000 € mindössze fél évig volt befektetve, ezért ez a 10% kevesebb a valóságosnál. Egy kivételnél a hiba az ellenkező irányba is elmehet.
| Sor | A: Dátum | B: Pénzmozgás (€) | C: Érték a mozgás előtt (€) |
|---|---|---|---|
| 2 | 2025.01.01. | -10 000 | 0 |
| 3 | 2025.07.01. | -5 000 | 10 800 |
| 4 | 2025.12.31. | 16 500 | 16 500 |
Pénzsúlyozott hozam Excelben: az XBMR függvény
A pénzsúlyozott hozam (más néven belső megtérülési ráta) figyelembe veszi, mikor érkezett és mikor távozott minden egyes euró. Az Excel az XBMR függvénnyel számolja, amely szabálytalan dátumokon történő mozgásokat is kezel. A sima BMR függvény azt feltételezi, hogy minden időszak egyforma hosszú, egy valódi portfólió viszont nem így működik.
Készíts két oszlopot: az A-ba a dátumok kerülnek, a B-be a mozgások a saját pénztárcád szemszögéből. Amit beteszel, az negatív, amit kiveszel, az pozitív, az utolsó sorba pedig a portfólió jelenlegi értéke kerül pozitív számként, mintha aznap mindent eladnál.
A táblázat adataival írd be: =XBMR(B2:B4;A2:A4). Az eredmény 12,1%, és éves: a függvény mindig évi rátát ad vissza. Ha az Exceled pontosvessző helyett vesszővel választja el az argumentumokat, használj vesszőt.
A TWR: fűzd össze a részidőszakokat
Az idősúlyozott hozam egy másik kérdésre felel: hogyan teljesítettek a befektetéseid, függetlenül attól, mikor tettél be pénzt? A kiszámításához még egy adat kell, a portfólió értéke közvetlenül minden mozgás előtt (C oszlop).
Minden mozgás lezár egy részidőszakot. Első részidőszak: 10 000 €-ról 10 800 €-ra, ez 8%. A második részidőszak 10 800 € plusz az új 5000 €, azaz 15 800 € értékkel indul, és 16 500 €-n zárul, ez 4,43%. A TWR a kettőt összefűzi: =(1+8%)*(1+4,43%)-1, ami 12,8%.
Miért három különböző szám ugyanarra a portfólióra? Az egyszerű hozam (10%) nem törődik az idővel. A pénzsúlyozott hozam (12,1%) azt méri, mit keresett a pénzed, a dátumokkal együtt. A TWR (12,8%) a stratégiádat méri, és ezzel érdemes egy alaphoz vagy indexhez mérned magad. Itt a TWR meghaladja a pénzsúlyozott hozamot, mert épp a gyengébb részidőszak előtt tettél be pénzt.
A munkalapod lépésről lépésre
Rögzíts minden be- és kiáramló pénzt
Minden befizetés vagy kivétel egy sor, a dátumával. Ha a portfólió készpénzét nem vezeted külön, minden vásárlás beérkező pénznek számít, és minden eladás, amelyet nem fektetsz újra, kimenő pénznek.
Jegyezd fel az értéket minden mozgás előtt
Erre az adatra van szüksége a TWR-nek. Megtalálod a brókered aznapi kimutatásában; ha nem írod fel akkor, később nehéz visszakeresni.
Számold ki a pénzsúlyozott hozamot az XBMR függvénnyel
Az utolsó sorba írd be pozitív számként a jelenlegi értéket, majd írd be: =XBMR(mozgások;dátumok). Az eredmény már éves.
Fűzd össze a részidőszakokat a TWR-hez
Számold ki minden részidőszak hozamát, szorozd össze az összes (1 + részidőszak hozama) értéket, és a végén vonj ki belőle 1-et.
Ahol az Excel kevés
Egy kis portfólióval és egyetlen brókerrel a munkalap működik. A nehéz rész a karbantartása: kézzel frissíteni az árakat, rögzíteni minden osztalékot és minden díjat, átszámolni a dollárban lévő tételeket az adott napi árfolyamon, és összefésülni több bróker kimutatásait, mindegyiket a maga formátumában.
A TWR szenvedi meg leginkább, mert a portfólió pontos értékét kéri minden mozgás napján, ez pedig olyan adat, amely szinte senkinek sincs kéznél, ha akkor nem írta fel.
Amit érdemes megjegyezned
Ha nem mozgatsz pénzt, elég az egyszerű hozam. Ha befizetsz vagy kiveszel, használd az XBMR függvényt, hogy megtudd, mit keresett a pénzed, és fűzd össze a részidőszakokat a TWR-hez, ha egy indexhez akarod mérni magad.
Ha nem szeretnéd karbantartani a munkalapot, a MyPortfolióba feltöltheted a brókered kimutatását vagy a saját Excel-fájlodat, sőt CSV-t, PDF-et vagy képernyőképet is: a pénzsúlyozott hozamot és a TWR-t kiszámoljuk helyetted, naprakész árakkal.
Gyakori kérdések
Hogyan számoljam ki a portfólióm hozamát Excelben?
Ha nem mozgattál pénzt, a =(B2-B1)/B1 képlettel, ahol B1-ben a befizetett összeg, B2-ben a jelenlegi érték áll. Ha voltak befizetéseid vagy kivéteid, használd az XBMR függvényt a pénzsúlyozott hozamhoz, a TWR-hez pedig fűzd össze a részidőszakokat.
Hogyan számoljak hozamot, ha pénzt fizetek be?
Írd be minden befizetést a dátumával, negatív számként, az utolsó sorba tedd a portfólió jelenlegi értékét pozitívként, és írd be: =XBMR(mozgások;dátumok). Így minden euró csak annyi ideig számít, amennyit befektetve töltött.
Mi a különbség a BMR és az XBMR között?
A BMR függvény azt feltételezi, hogy minden időszak egyforma hosszú. Az XBMR szabálytalan dátumokon történő mozgásokat is kezel, amilyenek egy valódi portfólióban vannak, ezért ezt érdemes használni.
Éves hozamot ad az XBMR?
Igen. Az XBMR mindig évi rátát ad vissza, akár néhány hónapot, akár több évet fognak át a mozgásaid. A cikk példájában évi 12,1% jön ki.
Ki lehet számolni a TWR-t Excelben?
Igen, de kell hozzá a portfólió értéke közvetlenül minden mozgás előtt. Számold ki az egyes részidőszakok hozamát, szorozd össze az összes (1 + részidőszak hozama) tagot, és a végén vonj le 1-et.