Weighted average date for stock portfolio

gpalmans

New Member
Joined
Nov 8, 2011
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hi all,

been trying for hour and hours. Probably read half of the Internet by now. Still no luck getting this to work. Would appreciate if anyone has some insights or maybe even a solution.

I keep a spreadsheet to track my investment portofolio and I'm trying to calculate the annualized return (%) for active positions I hold.
Each position can have multiple transactions: buy, dividents,sell, re-buy,... and all the data will be in non-contiguous lines.
I keep a seperate tab with the summary of each position. This is where I'd also like to show the annualized return.

The challenge I'm having, is to calculate the position's offset date to be used in the annualized return calculation.
It seems to work just fine for most of my stock/positions, but for some it comes up with a rediculous date (sometimes centuries into the future).

Hard to explain, and a lot of data/formulas involved. I made a small sample sheet to show what I'm trying to achieve, which can be found here (<-- click)

I tried to just use regular 'average' to calculate the date, but the results are not exaclty correct I believe, and this is supposed to be an exact science ;).

Who can help me out here? You get to be my hero for the day!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

gpalmans

New Member
Joined
Nov 8, 2011
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Not sure if the link to the acutal spreadsheet I put in my original post is allowed, so adding info below:

'Overview' tab:
Stock.xlsx
ABCDEFGHIJ
1Stock NameLast PriceSharesCostDividends CollectedMkt ValueDate 1YoY (%)Date 2YoY (%)
2Google$ 1 395,772$ 963,66$ -$ 2 791,5412/07/201523%4/10/201524%
3Cisco$ 35,69174$ 5 520,14$ 615,85$ 6 210,065/05/2431 30/12/20178%
4Robeco BP Global$ 244,6011,59$ 2 841,23$ -$ 2 834,9122/04/2124 9/06/20180%
5United Health$ 260,8815$ 828,74$ 155,49$ 3 913,2019/11/201326%22/11/201327%
Overview
Cell Formulas
RangeFormula
E2:E5E2=SUMIFS(Transactions!$I$2:$I$55,Transactions!$B$2:$B$55,"Div",Transactions!$C$2:$C$55,Overview!$A2)
F2:F5F2=C2*B2
G2:G5G2=IF(A2="","",ROUND(SUMPRODUCT(--(Transactions!$B$2:$B$55="Buy"),--(Transactions!$C$2:$C$55=Overview!A2),Transactions!$A$2:$A$55,Transactions!$I$2:$I$55)/Overview!D2,0))
H2:H5H2=IF(A2="","",IF(TODAY()-G2>365,(((F2+E2)/D2)^(365.25/(TODAY()-G2)))-1,""))
I2:I5I2=ROUND(AVERAGEIFS(Transactions!$A$2:$A$55,Transactions!$B$2:$B$55,"Buy",Transactions!$C$2:$C$55,Overview!A2),0)
J2:J5J2=IF(A2="","",IF(TODAY()-I2>365,(((F2+E2)/D2)^(365.25/(TODAY()-I2)))-1,""))
C2:C5C2=SUMIFS(Transactions!$D$2:$D$55,Transactions!$B$2:$B$55,"Buy",Transactions!$C$2:$C$55,Overview!A2)-SUMIFS(Transactions!$D$2:$D$55,Transactions!$B$2:$B$55,"Sell",Transactions!$C$2:$C$55,Overview!A2)



'Transactions' tab:
Stock.xlsx
ABCDEFGHIJKL
1DateTypeStock NameUnitsPriceFeesPrevious UnitsCum. UnitsValuePrevious CostCost of TransactionCum. Cost
222/11/2013BuyUnited Health15$ 54,26$ 14,78015$ 828,68$ --$ 828,68
317/12/2013DivUnited Health15$ 0,20$ 1,091515$ 1,91$ 828,68-$ 828,68
425/03/2014DivUnited Health15$ 0,20$ 1,091515$ 1,91$ 828,68-$ 828,68
525/06/2014DivUnited Health15$ 0,28$ 1,521515$ 2,68$ 828,68-$ 828,68
623/09/2014DivUnited Health15$ 0,29$ 1,581515$ 2,77$ 828,68-$ 828,68
716/12/2014DivUnited Health15$ 0,30$ 1,631515$ 2,87$ 828,68-$ 828,68
824/03/2015DivUnited Health15$ 0,35$ 1,901515$ 3,35$ 828,68-$ 828,68
924/06/2015DivUnited Health15$ 0,44$ 2,391515$ 4,21$ 828,68-$ 828,68
1022/09/2015DivUnited Health15$ 0,45$ 2,451515$ 4,30$ 828,68-$ 828,68
114/10/2015BuyGoogle2$ 472,84$ 16,0802$ 961,76$ --$ 961,76
1215/12/2015DivUnited Health15$ 0,47$ 2,561515$ 4,49$ 828,68-$ 828,68
1322/03/2016DivUnited Health15$ 0,45$ 2,451515$ 4,30$ 828,68-$ 828,68
147/04/2016BuyRobeco BP Global12$ 215,75$ 9,95012$ 2 510,49$ --$ 2 510,49
1528/06/2016DivUnited Health15$ 0,55$ 2,991515$ 5,26$ 828,68-$ 828,68
1620/09/2016DivUnited Health15$ 0,56$ 3,051515$ 5,36$ 828,68-$ 828,68
1713/12/2016DivUnited Health15$ 0,59$ 3,211515$ 5,64$ 828,68-$ 828,68
1830/12/2016BuyCisco175$ 23,53$ -0175$ 4 117,75$ --$ 4 117,75
1925/01/2017DivCisco175$ 0,24$ 6,30175175$ 35,70$ 4 117,65-$ 4 117,65
2027/02/2017SellCisco100$ 32,26$ 45,0017575$ 3 181,00$ 4 117,65$ 2 352,94$ 1 764,71
2121/03/2017DivUnited Health15$ 0,58$ 3,151515$ 5,55$ 828,68-$ 828,68
2226/04/2017DivCisco75$ 0,27$ 3,047575$ 17,21$ 1 764,71-$ 1 764,71
2315/05/2017SellCisco75$ 30,06$ 45,00750$ 2 209,50$ 1 764,71$ 1 764,71$ -
2427/06/2017DivUnited Health15$ 0,56$ 3,051515$ 5,36$ 828,68-$ 828,68
2530/06/2017BuyCisco205$ 21,53$ -0205$ 4 413,65$ --$ 4 413,65
2626/07/2017DivCisco205$ 0,25$ 7,69205205$ 43,56$ 4 414,50-$ 4 414,50
2719/09/2017DivUnited Health15$ 0,62$ 3,371515$ 5,93$ 828,68-$ 828,68
2825/10/2017DivCisco205$ 0,25$ 7,69205205$ 43,56$ 4 414,50-$ 4 414,50
2912/12/2017DivUnited Health15$ 0,63$ 3,431515$ 6,02$ 828,68-$ 828,68
3029/12/2017BuyCisco257$ 20,38$ -205462$ 5 237,66$ 4 414,50-$ 9 652,16
3124/01/2018DivCisco462$ 0,23$ 15,94462462$ 90,32$ 9 652,93-$ 9 652,93
327/02/2018SellCisco462$ 32,71$ 40,004620$ 15 072,02$ 9 652,93$ 9 652,93$ -
3320/03/2018DivUnited Health15$ 0,61$ 3,321515$ 5,83$ 828,68-$ 828,68
3426/06/2018DivUnited Health15$ 0,77$ 4,191515$ 7,36$ 828,68-$ 828,68
3529/06/2018BuyCisco265$ 20,95$ -0265$ 5 551,75$ --$ 5 551,75
3620/07/2018SellCisco265$ 35,45$ 42,002650$ 9 352,25$ 5 551,73$ 5 551,73$ -
3725/07/2018DivCisco265$ 0,28$ 11,1300$ 63,07$ --$ -
3818/09/2018DivUnited Health15$ 0,77$ 4,191515$ 7,36$ 828,68-$ 828,68
3913/12/2018DivUnited Health15$ 0,79$ 4,301515$ 7,55$ 828,68-$ 828,68
4031/12/2018BuyCisco174$ 31,72$ -0174$ 5 519,28$ --$ 5 519,28
4123/01/2019DivCisco174$ 0,29$ 7,57174174$ 42,89$ 5 520,14-$ 5 520,14
4219/03/2019DivUnited Health15$ 0,79$ 4,301515$ 7,55$ 828,68-$ 828,68
4324/04/2019DivCisco174$ 0,31$ 8,09174174$ 45,85$ 5 520,14-$ 5 520,14
4425/06/2019DivUnited Health15$ 0,95$ 5,171515$ 9,08$ 828,68-$ 828,68
4524/07/2019DivCisco174$ 0,31$ 8,09174174$ 45,85$ 5 520,14-$ 5 520,14
4624/09/2019DivUnited Health15$ 0,98$ 5,331515$ 9,37$ 828,68-$ 828,68
4723/10/2019DivCisco174$ 0,31$ 8,09174174$ 45,85$ 5 520,14-$ 5 520,14
4817/12/2019DivUnited Health15$ 0,97$ 5,271515$ 9,28$ 828,68-$ 828,68
4922/01/2020DivCisco174$ 0,32$ 8,35174174$ 47,33$ 5 520,14-$ 5 520,14
5024/03/2020DivUnited Health15$ 1,00$ 5,441515$ 9,56$ 828,68-$ 828,68
5122/04/2020DivCisco174$ 0,33$ 8,61174174$ 48,81$ 5 520,14-$ 5 520,14
5230/06/2020DivUnited Health15$ 1,11$ 6,041515$ 10,61$ 828,68-$ 828,68
5322/07/2020DivCisco174$ 0,31$ 8,09174174$ 45,85$ 5 520,14-$ 5 520,14
548/08/2020SellRobeco BP Global12$ 245,20$ 37,50120$ 2 804,37$ 2 510,49$ 2 510,49$ -
5510/08/2020BuyRobeco BP Global12$ 247,10$ -012$ 2 863,89$ --$ 2 863,89
Transactions
Cell Formulas
RangeFormula
K2:K55K2=IF(C2="","",IF(B2="Sell",IF(G2=0,0,D2/G2*J2),"-"))
L2:L55L2=IF(C2="","",IF(B2="Buy",I2+J2,IF(B2="Div",J2,IF(B2="Sell",IF(J2<=0,0,J2-K2)))))
F52,F50,F48,F46,F44,F42,F38:F39,F33:F34,F29,F27,F24,F21,F15:F17,F12:F13,F3:F10F3=(D3*E3)*0.3625
F11F11=(D11*E11)*0.017
F19,F53,F51,F49,F47,F45,F43,F41,F37,F31,F28,F26,F22F19=D19*E19*0.15
I2:I55I2=IF(C2="","",IF(B2="Buy",D2*E2+F2,IF(B2="Sell",D2*E2-F2,D2*E2-F2)))
 

Watch MrExcel Video

Forum statistics

Threads
1,112,996
Messages
5,543,188
Members
410,584
Latest member
Bluefox68
Top