Gerealizeerde winsten bepalen voor meerdere investeerders

Beste Forumleden,

Voor een studie opdracht is het de bedoeling dat we de hoeveelheid gerealizeerde winsten en verliezen bepalen voor ongeveer 1500 investeerders. Het gaat daarbij niet om hoe groot het verlies/winst is maar alleen of het een winst of verlies is.
De data die we hebben gekregen is zichtbaar in de bijlage, deze bevat een example van 1 investor (nr 18) maar het gaat om een totaal van 1500.

Om deze winsten/verliezen te bepalen zoeken we naar een functie in excel die kan bepalen of ten eerste 'accountid' klopt/matcht, daarna 'securitiesid' van verkoop en aankoop te koppelen (of als deze al in de portfolio zat), om daarna te bepalen of het winst of verlies is.
Nu hebben we zelf stapje voor stapje dit geprobeerd met if functies als ook lookup en match maar komen eigenlijk geen stap verder. Is dit mogelijk in excel? Ongetwijfeld, maar weet iemand welke functie(s) hiervoor nodig zijn?

Een bijkomend probleem, welke zichtbaar is in de example is dat sommige investeerders eerst 3x 100 aandelen 'x' kopen tegen verschillende koersen, waarna ze eenmalig 100 aandelen 'x' verkopen. Het zou fantastisch zijn als het mogelijk was om hiervoor de gemiddelde aankoopprijs van de verkoopprijs af te kunnen halen.

Ik hoop zo mijn probleem voldoende te hebben beschreven. Zo niet, laat het als je blieft weten. En hoop echt heel erg dat iemand ons een beetje opweg kan helpen.

Alvast bedankt voor de moeite,

Dijkie91


Bekijk bijlage example.xlsx

Ik had dat al gelezen. Volgens mij heb ik hieraan voldaan door wel degelijk zelf aan de slag te gaan en dit juist te vermelden. Als ook een normale omschrijving gegeven van het probleem.

Niemand die enig idee heeft wat voor functies we hiervoor nodig zouden hebben?

Of is het zo omdat het een studie opdracht is, dat het sowieso niet behandelt wordt?

de vraag is ook niet zo eenvoudig en dan vooral met betrekking tot de orders die in meerdere keren aangekocht waren en daarna gedeeltelijk verkocht worden.
Ik weet niet wat de praktijk normaal doet, maar ik denk dat het logischer zou zijn dat je de verkochtte aandelen via een FIFO (eerst gekochtte aandelen worden eerst verkocht) bepaald ipv een gemiddelde van alle aandelen op dat moment. Als je straks nog aandelen koopt en verkoopt, dan wordt dat straks een hele soep mbt. die gemiddelde prijs.
Of misschien moet er op het ogenblik van de gedeeltelijke verkoop van aandelen, het ganse pakket virtueel verkocht worden en het niet-verkochtte deel aan de gemiddelde prijs terug ingeboekt worden. Dat kan zo met een beetje VBA.
Hoe gaat men normaal te werk ? FIFO of gemiddeld ?

Ik heb even wat dingen opgezocht over fifo of gemiddelde en fifo is ook goed. We kunnen het verantwoorden. Maakt dit het makkelijker? En moet het dan nog steeds via vba of kwestie van verschillende functies?

Bedankt voor je response

Dijkie91,

Voor dit soort berekeningen zijn nogal wat speciale programma's die worden gebruikt.
In dit soort programma's worden over het algemeen Fifo (first in first out) berekeningen gemaakt.
Hiervoor wordt voor ieder aandeel dat je verkoopt gekeken naar de zelfde stukken die je het eerst in
handen hebt. Stel dat je 100 aandelen verkoopt voor € 50 per stuk op 31 jan. en dat je er eerst 70 hebt gekocht
voor € 52 op 04 jan. en 60 hebt gekocht voor € 51 op 10 jan.
Dan is het verlies 100 x 50 -(70 x 52 + 30 x 51) = -170.
In portefeuille zijn er dan nog 30, ingekocht voor 51 dus dat is dan ook de gemiddelde prijs.

Voor jouw vraagstuk zul je dus berekeningen moeten maken waarbij je je moet afvragen per securityid wat de
Waarde is van de overgebleven portefeuille.

Bijgaand een overzicht met de berekening van de winst en verliezen per securityid en een berekening van de
portefeuille waarde en de gemiddelde prijs.

Dit is nog steeds veel handwerk, wil je dit verder berekenen dan zal je naar macro's en VBA moeten kijken.

Veel Succes.

Bekijk bijlage HelpMijBerekenPortefeuille.xlsx

Mijn dank is zeer groot. Het is dus kortweg onmogelijk zonder vba/macro's?

@Elsendoorn2134,

Ik vroeg me af of je in staat bent om zo'n vba te schrijven? En hoeveel tijd dat ongeveer kost? Dit is de helft van wat we nodig hebben, de andere helft beslaat paper gains. Op moment dat investeerder x verkoopt moeten we bepalen hoeveel paper gains and losses hij in z'n portefeuille heeft zitten. Alle stockprices die hiervoor nodig zijn hebben we wel al in een excel bestand staan. Hiervoor zal waarschijnlijk ook een vba nodig zijn?
Het is misschien een wat onbeschofte vraag, maar zou je deze kunnen schrijven? En zo ja wat voor vergoeding zou je hiervoor willen hebben? Dan kan ik even overleggen of we dat bereidt zijn neer te leggen. Als iemand anders zich aanbiedt is het uiteraard ook goed. En als dit niet mag van de moderators, excuus.

Dijkie91,

In het eerste bericht had je het over dat het een studieopdracht was, ik krijg nu het gevoel
dat het gaat om een zakelijke toepassing.
Om je enig idee te geven van een eventuele opzet heb ik het volgend vba programma gemaakt
die vanuit je lijst een overzicht maakt per security code.

Het herschrijft jouw schema in een overzicht per security code.
Wat hier nog gebeuren moet is een berekening van de actuele waarde, de actuele aantallen bestaan
al. Hiervoor moet ik binnen VBA gaan terug tellen van de huidige voorraad naar de aankopen.
tot ik de gehele voorraad heb geteld, en dus de waarde weet.

Als het een studieopdracht is wens ik je veel succes, als het een zakelijke toepassing is wil
ik je wel helpen maar dan moet je me maar even persoonlijk mailen.

Bekijk bijlage HelpMijBerekenPortefeuille.xlsm

Veel Succes.