13. januar 2006 - 01:06Der er
21 kommentarer og 1 løsning
SUMPRODUKT - udfordring!
Jeg har brug for hjælp til en SUMPRODUKT formel ifm med automatisk beregning af gennemsnitsanskaffelsespris på aktier. Et simpelt eksempel ser således ud: dato/aktie/antal/pris/salgsdato/gns pris 1.1 / TDC / 1 / 100 / 15.1 / 125 10.1 / TDC / 1 / 150 / 25.1 / 162,5 20.1 / TDC / 1 / 200 / 29.2 / 231,25 5.2 / TDC / 1 / 300 / 29.2 / 231,25
Jeg har været ved at rode med nogle SUMPRODUKT formler men kan ikke rigtig få det til at stemme. Jeg har prøvet som amerikanske fora men de kan vist ikke helt finde ud af det så her skal i vise at i er bedre end dem! Gns skal automatisk tage den enkelte aktie med og tage højde for at der er flere aktier som står ind mellem hinanden. fx linie tre kunne være SAS i stedet for og skal derfor ikke med i TDCs gns. pris.
Håber at I kan klare udfordringen.
ceacer
ps er det muligt at uploade eksempler her på siden så I bedre kan se hvad jeg mener?
synes den er svær denne her så der er 200 point til den som løser spørgsmålet helt!!
den 20.01 købes 1 stk. TDC men jeg ejer i forvejen 1 stk. til en gns. pris på 125. den nye købes til 200. Derfor skal gns. prisen være (200+125)/2 = 162,5. Der skal tages højde for tidspunkterne for køb og salg.
Problemet er at skattereglerne siger således(forudsat tingene sker i denne rækkefølge): køb af 1 stk til 100 => gns på 100 køb af 1 stk til 150 => gns på 125 salg af 1 stk til x => stadig gns på 125 på den ene der er tilbage køb af 1 stk til 200 => gns på 162,5 da prisen på aktie 2 er gns af de to første
Derfor bliver det meget kompliceret!
"Du kan da ikke tage et gennemsnit og lægge til de 200 for den 3 aktie. Ved den 3. aktie, har du betalt total 450 og gennemsnitværdien er 150" - jo for jeg ejer jo ikke den første mere og derfor skal den ikke indgå i beregningerne. Det er derfor prisen på aktie to som skal opgøres som gns af aktie 1 og 2.
hmm.... jeg vil råde dig til at have tabellen sorteret i datoorden, derudover hvis du knaster endnu en kolonne som du kan kalde "transaktionstype", hvor du registrer hhv. "køb" og "salg" - så kan det lade sig gøre med nedenstående.
Formlen er forudsat at du starter i A4 og har følgende kolonner i nævnte rækkefølge: Transaktionstype (A), Dato (B), Aktie (C), Antal (D), pris (E), gns. pris (hvor du nu vil have den)
eneste problem er at den ikke kan "beregne" gns.kursen af første køb!
Mener du at det skal indtastes i dataorden så 1 er fx køb af TDC 2 er salg af TDC? Jeg har nemlig i forvejen lavet regnearket, der kører med datoorden, men det ser således ud:
Aktie (A), Købsdato (B), Mægler (C), Antal (D), Kurs (E), Kurtage (F), Afregnet (G), *Gns pris* (H), Kurs nu (I), Værdi nu (J), Gev. kr. (K), Gev. % (L), Salgsdato (M), Kurs (N), Kurtage (O), Afregnet (P), Gev. kr. (Q), Udbytte (R), Gev. Inkl. udbytte kr. (S), Gev. % (T), Akk. kr. (U)
grunden til at nogle af den går igen er for at kunne udregne urealiseret gevinst. Jeg vil gerne holde fast i denne rækkefølge, men det gør ikke noget hvis beregningerne laves i et andet ark.
Har selv tænkt lidt på om man skulle lave et ark med alle handlerne for hver aktie. Måske noget a la dit eksempel og så i "det rigtige" regneark henføre til den gns pris man finder i det nye ark. Det skal dog også helst tage højde for det første køb.
Jeg ved ikke om jeg må linke til andre sider men her har jeg uploaded et eksempel der kan hjælpe lidt på vej:
ok, jamen det er slet ikke så svært - hvis jeg har lavet det rigtigt. Formlen er lavet så den passer med de kolonner du har nævnt tidligere. Derudover er formlen lavet så den første række, hvor du taster data ind, er række 2! Hvis du starter længere nede end række 2 (f.eks. række 12) erstat da "$2:" med "$12:"! Du kan derfor blot kopiere formlen ind herfra. Temmelig simpelt - derudover er formlen ligeledes lavet, så den bare skal kopieres nedaf, der er ikke noget at du selv skal rette. Håber den virker korrekt:
CRAP! Havde lige skrevet et indlæg som åbenbart blev slettet!! Nå prøver igen. jeg har nu lavet arket så det passer perfekt med indlægget den 13.01.2006 kl. 14:21:02 og starter i række 2. Det virker fint hvis salgsdatoerne fx er 09.01, 15.01, 04.02 og 29.02. Her skal der ikke bruges gns da aktierne er solgt før der er købt nye jf mit første indlæg. Ændrer jeg derimod datoerne til 15.01, 25.01, 29.02 og 29.02 får jeg følgende gns. priser: 100, 125, 162,5 og 196,88. Det stemmer ikke med de rigtige gns. priser jf første indlæg. Hvis du evt selv har lavet arket så prøv at se om du ikke får de samme gns priser eller om jeg gør noget forkert. Det kunne dog tyde på at du er tæt på da gns pris nr. 2 og 3 passer med gns pris 1 og 2 i det første indlæg. Det kan derfor ske at det kun er små justeringer som mangler.
Please don't quite on me now! Jeg har lagt dem ind i eksempel 5 og det virker helt perfekt - næsten. Hvis vi snakker om samme eksempel er der to eksempler med forskellige datoer. Det første virker helt perfekt! Men i det andet er der lidt problemer når der skal tages et gns af et gns mellem de to første aktier og så den tredje aktie. Da Carlsberg i række 12 først sælges efter købet af carlsberg i række 15 skal dens pris både indgå i den i række 11 og den i række 15. Meget indviklet at forklare.
Så kom løsningen! og det var endda helt omkring USA, Danmark, Belgien for til sidst at lande på Cypern. Svaret tager udgangspunkt i eksemplet i tråden ovenover og er egentlig meget simpel: Der skal laves en kolonne der samler værdierne: =SUMPRODUKT((A$2:A$100=A2)*(B$2:B$100<M2)*(G$2:G$100)) en der samler antal stk =SUMPRODUKT((A$2:A$100=A2)*(B$2:B$100<M2)*(D$2:D$100)) og en der finder gns. prisen =+O2/P2
Jeg har forsøgt at ændre i datoerne men kommer stadig til samme resultat. Det kan dog være lidt svært at overskue det så hvis en måske vil tjekke af at de får det samme vil jeg lukke tråden. Eneste problem er hvis jeg køber og sælger samme dag, men det det kan vist ikke løses da det kommer an på hvad der sker først på dagen (altså om det er køb så salg eller salg så køb)
Jeg kan ikke lige se, hvilket ark dit sidste løsningsforslag gælder, men gætter på:
((A$2:A$100=A2) sammeligner navne (så det skulle der være taget højde for!!). (B$2:B$100<M2) købsdato af lignende aktier er før salgsdato af aktier i aktuel række. (G$2:G$100) det område, der summeres (her samlet pris).
Den løsning virker ikke, da den ikke tager højde for tomme felter i kolonne M, og da det ikke undersøges om andre aktier er solgt inden denne aktie er købt. Hvis du prøver formlen på det ark, som jeg har brugt, så virker den ikke. Alt sammen ting, som (jeg mener) min formel tager højde for, og den kan da også sagtens deles op i to (ved divisionstegnet).
Jeg har følgende spørgsmål til dine "Should be" beregninger det nederste ark i eksempel5:
K11: Hvorfor skal række 12 regnes med i gennemsnittet? Række 12 er jo købt dagen efter række 11 er solgt. Hvis beregningen er korrekt: Hvorfor skal de andre "Carlsberg B" ikke indgå? Hvad er kriteriet?
K12: Du skriver de begge har 250 i antal, men den ene værdi (K11) er baseret på gennemsnittet af 300 aktier (række 11 + række 12)
K15: Første del af formlen ((G16/D16)/450*200) Hvor kommer de 450 og 200 fra? D16 er 200, så derfor står der G16/450. Det ser ud som om du beregner prisen på aktier i række 15 og 16 udelukkende baseret på prisen af aktier i række 16. Sidste del af formlen K12/450*250 Hvor kommer de 450 og 250 fra? Her bruger du så den gamle middelværdi og ganger med 250 (som måske er fra række 15 - som ikke har noget med den gamle værdi at gøre), og så divideres med 450 (som - for mig - ubegribeligt svarer til summen den aktuelle aktie og den næste.
Med de der middelværdier af middelværdier, der indgår med en vægtning baseret på antal, der ikke har noget med middelværdierne at gøre, og som nogen gange skal indeholde antallet af den næste række, kan jeg ikke overskue hvordan det skal gøres. Dermed ikke sagt, at det ikke kan lade sig gøre, blot at jeg ikke kan finde ud af det.
Først vil jeg lige svare på dit første indlæg. Der er forskel på første linie og anden linie i det andet løsningsforslag. Første linie var som beskrevet. anden linie ser sådan ud: SUMPRODUKT((A$2:A$100=A3)*(B$2:B$100<M3)*(B$2:B$100>M2)*(G$2:G$100))+O2-(Q2*D2) og SUMPRODUKT((A$2:A$100=A3)*(B$2:B$100<M3)*(B$2:B$100>M2)*(D$2:D$100))+P2-D2 og det er her der kommer problemer hvis der indgår nye aktier. Beklager misforståelsen
Andet indlæg: Vi må hellere lige sikre at det er samme eksempel vi snakker om. Datoerne for Carlsberg i eksempel 2 ser således ud: Køb Salg: 04.01 15.01 10.01 25.01 20.01 29.02 05.02 29.02
K11: Her skal række 12 regnes med da den er købt før række 11 er solgt. Kriteriet er derfor at den skal være købt før den anden er solgt. Se evt indlæg fra den 13.01.2006 kl 02:00.
K12: Ja det er nok her problemet opstår. Gennemsnitsprisen i række 12 skal tage højde for gennemsnitsprisen i række 11 (der består af række 11 og 12) og af den pris jeg giver i række 15 (ikke dens gennemsnitspris!). Det skyldes at jeg ikke har købt carlsberg i række 16 endnu. Det vil sige at alle aktier som købes inden den i række 12 sælges skal indgå og dem som er købt før række 12 er købt og som sælges efter række 12 er købt (det sidste refererer til den i række 11) mht antal: Der købes 50 stk => gns pris på 17144/50 = 342,88 Der købes 250 stk => gns pris på 342,38 for dem begge to der sælges 50 stk => gns pris på den første er nu 342,88 og kan ikke ændres. For de resterende 250 stk er gns prisen stadig 342,38. Når der så købes yderligere 250 stk kan jeg finde gns prisen ved at dividere købsprisen i række 15 med antal købte stk (250) og lægge det sammen med den eksisterende gns. pris (342,38) og så dividere med to, da de vægter lige meget.
K15: Gennemsnittet i denne celle skal bruge gennemsnittet af række 12 pga ovennævnte krav og prisen i række 16. Når der er forskellige antal skal gennemsnittet tage højde for det. Når der er 200 stk i række 16 og 250 stk i række 15 bliver jeg nødt til at tage højde for det. Det gør jeg ved at sige at den ene vægter med 200/450 og den anden med 250/450 modsat før hvor det var 250 ved begge. De 450 = 200+250.
"Med de der middelværdier af middelværdier, der indgår med en vægtning baseret på antal, der ikke har noget med middelværdierne at gøre, og som nogen gange skal indeholde antallet af den næste række, kan jeg ikke overskue hvordan det skal gøres. Dermed ikke sagt, at det ikke kan lade sig gøre, blot at jeg ikke kan finde ud af det."
Den måde jeg udregnede SHOULD BE er bare eksempler. Jeg har ikke lavet dem ens hele vejen igennem som jeg nok burde. Ideen var at man kunne gennemskue metoden vha mine forklaringen og så bruge SHOULD BE til at tjekke resultaterne - ikke til hjælpe med udregningerne. Du har ret i at det er middelværdier af middelværdier, men jeg forstår ikke det der med at der indgår en vægtning baseret på antal der ikke har noget med middelværdier at gøre.
Håber det kunne hjælpe på dine spørgsmål. Ellers er du velkommen til at skrive igen.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
