Sijoittajan hyödylliset excel-kaavat

[Sijoittajan hyödylliset excel-kaavat]

Sijoittamiseen liittyy monenlaista laskentaa. Excel on oivallinen apu, jolla erilaiset tuotto- ja riskilaskelmat onnistuvat nopeasti, kunhan keskeiset kaavat tunnetaan. Katsotaan artikkelissa muutamia käteviä sijoittamisen kaavoja aiempien korkokaavojen jatkoksi.

Sijoittajalla olisi hyvä olla excelin perustuntemusta, mutta selitämme kuitenkin kaavat auki. Muitakin vastaavia taulukkolaskentaohjelmia on useita, kuten ilmainen LibreOfficen Calc, joissa on vastaavat komennot kuin excelissä.

Kokosimme artikkeliin yleisimpiä sijoittamiseen liittyviä tärkeimpiä kaavoja. Käytämme läpi artikkelin pääosin esimerkkiä, jossa aikajakso on 10 vuotta, vuosituotto on 5 % ja maksuerä (tai tuottoerä) on 100 € vuosittain.

Jos aikajaksona olisi esimerkiksi kuukausi, niin kaavoissa tuotto tulisi jakaa 12:sta ja 10 vuotta tulisi kertoa 12 maksuerien saamiseksi. Tosin excel-komennolla voi selvittää myös aikajakson.

Arvostuskaavat

NNA

NNA-funktio laskee sijoituksen nettonykyarvon käyttämällä diskonttokorkoa ja tulevien positiivisten tuottoerien sarjaa alkusijoituksen (negatiivinen, koska rahavirta liikkuu ulospäin) jälkeen.

nna.png

Esimerkissä tuottoerien nykyarvoksi tulisi 242 €, mikä on siten ansaittua tuottoa alkuinvestoinnin päälle.

NNA.JAKSOTON

Tämä funktio palauttaa nettonykyarvon rahavirroille kuten yllä, mutta jotka eivät ole säännöllisiä.

nna_jaksoton.png

Esimerkissä nykyarvoksi tulisi 345 €.

NA

NA laskee lainan tai sijoituksen nykyarvon, joka perustuu kiinteään tuottoon. NA-funktiota voi käyttää laskettaessa säännöllisiä vakiomaksueriä kuten tasaerälainassa.

nykyarvo.png

Esimerkissä maksuerien nykyarvoksi tulisi 772 €. Kaava luetaan siten, että 10 vuoden aikana ansaitut vuosittaiset 100 € diskontataan nykyhetkeen yhdeksi summaksi.

TULEVA.ARVO

Tämä funktio laskee sijoituksen tulevan arvon, kun maksuerät ovat kiinteitä. Erien sijasta voi syöttää nykyarvon.

tuleva_arvo.png

Esimerkissä tuleva arvo olisi 1258 €. Tulevan arvon kaava on vastakkainen kaava ylemmälle nykyarvolle. Esimerkiksi ylempänä oleva 772 € nykyarvo vastaisi 1258 € tulevaa arvoa 10 vuoden aikana kun vuosituotto on 5 %.

TULEVA.ARVO.ERIKORKO
Tämä kaava laskee tulevan pääoman arvon vaihtelevalla tuotolla. Funktio on hyödyllinen tuottojen arvon laskennassa, kun ne ovat erisuuruisia.

tuleva_arvo_erikorko.png

Esimerkissä tulevaksi arvoksi tulisi 894 € erilaisilla 3 vuoden tuotoilla. Tämän voi sanoa myös siten, että näillä tuotoilla tulevaisuuden 894 € olisi diskontattuna nykyarvoltaan 772 €.

Aikakaavat

NJAKSO

NJAKSO kertoo, montako erää lainaa joudutaan maksamaan, tai vaihtoehtoisesti palauttaa kausien määrän sijoitukselle, joka perustuu säännöllisiin kiinteisiin maksueriin ja kiinteään korkoon.

njakso.png

Esimerkissä kausien määräksi tulee 10, eli aiemmin laskettu nykyarvo 772 €  saadaan 5 % tuotolla, ja 100 € erällä 10 vuoden aikajaksolla kun laskentaväli on 1 vuosi. Kaava vaatii maksuerän syöttämisen negatiivisena.

Huomaa että tämä laskee aiemman NYKYARVO-funktion tarvitseman kausien määrän ja KORKO-funktio selvittää vastaavasti tarvittavan tuoton. Näitä 3 peruskaavaa voidaan käyttää sen mukaan mikä laskennan osa halutaan selvittää, tai muiden kaavojen osana kun funktioita tarvitaan useampia.

STP

Palauttaa sijoituksen tasapoiston yhdeltä jaksolta. Kaava voi olla hyödyllinen tarkemmissa investointilaskelmissa, kun esimerkiksi jäännösarvo halutaan laskea säännönmukaisesti.

stp.png

Esimerkissä vuosittaiseksi tasapoistoksi tulisi 1800 €.

Tuottokaavat

Julkaisimme hyödyllisimpiä tuotto- ja korkokaavoja edellisessä artikkelissa hyödylliset korkolaskut, josta näitä voi tarkemmin katsoa. Korkokaavat ovat myös sijoittajan olennaisia kaavoja, kuten IPMT, PPMT, KORKO.EFEKT, KORKO ja SISÄINEN.KORKO.

Artikkelissa sijoituksen tuoton laskeminen on myös tuoton laskentaa havainnollisina matemaattisina peruskaavoina, jotka voidaan laskea kätevästi myös excel-kaavoina.

Näiden lisäksi olennaisia kaavoja ovat myös joukkovelkakirjojen  tai tavallisten velkakirjojen laskentaan sopivat kaavat, kuten TUOTTO tai TUOTTO.DISK.

TUOTTO

Tuotto joukkovelkakirjalle, jonka kuponkikorko maksetaan säännöllisin väliajoin. Tätä voi käyttää joukkovelkakirjan tuoton laskemiseen.

tuotto.png

Tilityspvm. on velkakirjan liikkeellelaskun jälkeinen myyntipäivä ja erääntymispvm viimeinen voimassaolopäivä. Korkojakso tarkoittaa monestiko kuponki maksetaan vuodessa. Hinta ja lunastushinta ovat arvopaperin nimellishintoja 100 rahayksikköä kohtaan. Esimerkiksi jos arvopaperi lasketaan liikkeelle 90 euron hintaan, myydään se diskontolla nimellishintaan nähden.

Esimerkissä joukkovelkakirjan tuotoksi tulee 7,4 %.

TUOTTO.DISK

Palauttaa diskontatun arvopaperin kuten joukkovelkakirjan tai velkakirjan vuosituoton.

tuotto_disk.png

Esimerkissä vuosituotoksi tulee 2,2 %.

Muita hyödyllisiä velkakirjojen hintoihin ja herkkyyksiin liittyviä kaavoja ovat duraation excel-kaavat, kuten KESTO ja KESTO.MUUNN. Näiden laskennasta on myös valmiit kaavat investointilaskurissa, kuten myös monien muiden tässä artikkelissa mainituista kaavoista.

Riskikaavat

KESKIHAJONTA

KESKIHAJONTA-funktiolla voi laskea sijoituksen volatiliteetin, mikä on yleisin riskin mittari. Funktiosta on kaksi eri versiota, KESKIHAJONTA.S ja KESKIHAJONTA.P. Käytännössä näiden ero on lähes merkityksetön, jos dataa on riittävästi (ts. ainakin "satoja").

S-versio laskee keskihajonnan populaatiota pienemmän otoksen perusteella, mikä sopii paremmin esimerkiksi sijoituksen volatiliteetin laskentaan. P-versio laskee keskihajonnan koko populaation perusteella.

keskihajonta.png

Esimerkissä volatiliteetiksi tulisi pyöreästi 4 %. Koska esimerkissä dataväli on kuukausi, on tämä kuukausivolatiliteetti. Vuosivolatiliteetti on usemmin käytetty mittari riskin ilmaisemisessa, joten kuukausivolatiliteetti olisi skaalattava vuosivolatiliteetiksi kaavalla 4 % * NELIÖJUURI(12). Suluissa datavälien määrä vuodessa.

VAR.P VAR.S

VAR.P tai VAR.S-funktioilla lasketaan varianssi, samantapaisesti kuin keskihajonta-kaavoissa. Se on riskin mittari kuten volatiliteetti, mutta yleensä sitä käytetään välivaiheena tai kun keskihajonnan negatiivisuus aiheuttaa tietyissä riskilaskennoissa vaikeuksia.

KULMAKERROIN

Excelin KULMAKERROIN-funktio on kätevä beta-kertoimen laskennassa. Beta on sijoituksen systemaattisen riskin mittari, joka on tärkeä monissa sijoittamisen konsepteissa kuten capm-mallissa. Määritelmällisesti beta on arvopaperin ja vertailuindeksin tuottojen suhteen kulmakerroin.

kulmakerroin.png

Esimerkissä betaksi tulisi 0,96, mikä kertoo, että osake on hieman vähemmän altis markkinatapahtumien vaikutuksille kuin verrokki-indeksi.

KORRELAATIO

Tämä funktio laskee korrelaatiokertoimen. Korrelaatio kertoo kahden arvojoukon keskinäisen yhteyden, positiivisen tai negatiivisen välillä [-1,1].

Korrelaatio on olennainen monissa sijoittamisen konsepteissa, kuten portfolion riskin laskennassa.

korrelaatio.png

Esimerkissä korrelaatiokertoimeksi tulisi 0,54, eli osakkeen hinta ja verrokki-indeksi korreloivat keskenään positiivisesti.

Hakukaavat

Hakukomennot ovat erittäin hyödyllisiä tietojen hakemiseen suurista datamääristä tai tietotauluista muutoinkin kuin sijoittamiseen liittyvissä laskuissa. Kuitenkin esimerkiksi sijoittamisessa näistä kaavoista on suuri hyöty omien valmiiden mallien rakentamisessa.

PHAKU (haku sarakkeesta) ja VHAKU (haku rivistä) ovat monelle tuttuja, mutta INDEKSI VASTINE kattaa molemmat ja on joustavampi.

VHAKU vaatii pysyvän sarakeviittauksen (PHAKU sama mutta riviviittaus), kun INDEKSI VASTINE toimii myös muuttuvalla viittauksella. Jos sarakkeita lisätään tai poistetaan, VHAKU ei enää toimi ilman viittauksen muuttamista. INDEKSI VASTINE on myös nopeampi, mutta tällä ei ole väliä, jos hakutaulukot eivät ole kovin suuria.

INDEKSI VASTINE hakee tiedon hakuarvon perusteella tietystä sarakkeesta (tai rivistä) ja se koostuu kahdesta excel-komennosta, INDEKSI ja VASTINE:

indeksivastine.png

Esimerkissä kaava hakee kiivien määrän maaliskuussa. Maaliskuussa kiiviä vastaa arvo 5.

Komennosta on myös laajempi INDEKSI VASTINE VASTINE, joka hakee tiedon matriisista kahden hakuarvon perusteella.

indeksivastinevastine.png

Näin kaava hakee greippien määrän helmikuussa, joka on 2 kpl. Pelkällä INDEKSI VASTINEELLA pärjää yleensä riittävän pitkälle, eikä kahta hakuehtoa tarvita. Tämän tuloksen saisi myös kahdella erillisellä INDEKSI VASTINE -kaavalla, mikä voi olla joustavampi sijoituslaskelmissa kun halutaan tietää yhden hakuehdon mukainen tulos.