find month end value for portfolio from trade list

[uptickdk]

I have a list of stock trades (see attached sample WS) and am trying to write a formula(s) to calculate the monthly values based on the investments in the portfolio at month's end. Since the shares purchased amount is calculated based on the sale proceeds of the stock sold on same date we just need to know what positions are in the portfolio and their price at month's end. The portfolio only has 2 positions at all times and they can be 2 stocks, 1 stock and 1 Cash position, or 2 Cash positions. Cash is treated as a stock position and it's price is always $1.

I can use the following to find the month end price for a stock based on the purchase date but this doesn't work when it's owned into another month.
=VLOOKUP(WORKDAY(DATE(YEAR(B18),MONTH(B18)+1,1),-1),Prices!$A$1:$S$1911,MATCH(A18,Prices!$A$1:$S$1,0),0)

So how can I:
1. find both securities are in the portfolio at the end of each month and their share amount? (Remember some positions are owned for multiple months but share quantity doesn't change.)
2. multiply the shares bought for each security owned at month's end by the month end price. (All prices are stored in another worksheet called Prices with Column A holding the date and Row 1 the stock symbol. (Prices!$A$1:$S$1911)
3. add the month end values of both securities together and put in a cell.

Sample Output: (actual results I calculated manually)
Jan 2007 20000
Feb-2007 20000
Mar 2007 20000
Apr 2007 20416.21
May 2007 21341.15

Thanks,
Doug

 

[Hui]

Doug

Firstly, Welcome to the Chandoo.org forums

In M2: =SUMIFS($E$18:$E$32,$B$18:$B$32,"<="&L2)-SUMIFS($I$18:$I$32,$F$18:$F$32,"<="&L2)
Copy down

However I don't get the values that you get

I get

Month Returns
Dec 2006 $ 20,000.00
Jan 2007 $ 20,000.00
Feb 2007 $ 20,000.00
Mar 2007 $ 19,991.30
Apr 2007 $ 19,987.46
May 2007 $ 19,904.62
Jun 2007 $ 19,579.67
Jul 2007 $ 19,579.67
Aug 2007 $ 19,588.83
Sep 2007 $ 19,571.20

Using march as an example
Total Buys is 29,991.30 Total sells = 10,000
Total = 19,991.30 where your sample said 20,000?

See attached file:

 

[uptickdk]

Hui-

Thank you for your response. 1. you are correct I made an error on my manual calculations and have updated them on the attached spreadsheet. I also showed the math that I need a formula(s) to calculate. I forgot to mention that there is a $10 transaction cost for all buys and sells except for Cash.
2. Your math was incorrect in the sense that I need to know what was owned on the month's end, then look up the price at month's end and multiply it by the number of shares of that stock owned at month's end. The period would be < last day of previous month and >= last day of current month.
3. I also copied part of my Prices worksheet into columns R-Y so that you values can be looked up.

Hope this clarifies things a little better.
Regards,
Doug

 

[NARAYANK991]

Hi Doug ,

I am sure Hui will answer your questions ; I would only suggest two points :

1. If you can use Excel Tables , please do so ; it makes formulae much simpler.

2. When creating a workbook , please segregate input data , calculations and outputs on different worksheets ; it makes it so much simpler to follow and maintain.

Narayan

 

[Hui]

Doug

I would do the following

In
E18: =C18*D18
I18: =G18*H18
Copy both down

M2: =SUMIFS($E$18:$E$32,$B$18:$B$32,"<="&L2)-SUMIFS($I$18:$I$32,$F$18:$F$32,"<="&L2)-(COUNTIFS($B$18:$B$32,"<="&$L2,$A$18:$A$32,"<>Cash")+COUNTIFS($F$18:$F$32,"<="&$L2,$A$18:$A$32,"<>Cash"))*10
Copy down

In March I still get a different value to you
I get 19,991.30
You get 19,981.30

In march there are 3 cash transactions = $20,000 and one non-cash RSP $10001.30 less one transaction cost of $10 = $19991.30

See attached:

Alternatively you can build the $10 fee into columns E & I and that will simplify the formula in M2

 

[uptickdk]

Hui-

Thank you for your suggestion. I worked on it this weekend and have it working. I added 2 more columns, each listing the stock held at month's end in column M and N. Then in O I calculated the month end portfolio value using
M3=INDEX($A$18:$A$250,SMALL(IF($B$18:$B$250<=$L3,IF($F$18:$F$250>$L3,ROW($A$18:$A$250)-ROW($A$18)+1)),1))
N3==INDEX($A$18:$A$250,SMALL(IF($B$18:$B$250<=$L3,IF($F$18:$F$250>$L3,ROW($A$18:$A$250)-ROW($A$18)+1)),2))
O3=(INDEX($D$18:$D$250,SMALL(IF($B$18:$B$250<=$L3,IF($F$18:$F$250>$L3,ROW($A$18:$A$250)-ROW($A$18)+1)),1))*VLOOKUP(WORKDAY(DATE(YEAR(L3),MONTH(L3)+1,1),-1),$V$1:$AC$280,MATCH($M3,$V$1:$AC$1,0),0))+(INDEX($D$18:$D$250,SMALL(IF($B$18:$B$250<=$L3,IF($F$18:$F$250>$L3,ROW($A$18:$A$250)-ROW($A$18)+1)),2))*VLOOKUP(WORKDAY(DATE(YEAR(L3),MONTH(L3)+1,1),-1),$V$1:$AC$2000,MATCH($N3,$V$1:$AC$1,0),0))

It's ugly but gets the job done.

Question: If I wanted to expand the number of securities to 3, or 4, or X how could I make this dynamic?

Doug