Hi - I'm looking for a formula to calculate turnover in a portfolio from one month to the next. The portfolio always has 10 stocks, equally weighted at the start of the month. However by the end of the month the weights will no longer be equal, due to their returns.
Some will now be above 10% of the portfolio weight & some will be less than 10%. At the start of the next month we form a new portfolio of 10 stocks. We are selecting from a universe of 50 stocks, so the stocks in the second month portfolio may or may not have been in the first month portfolio.
We calculate turnover by working out net purchases. So if a stock is new to the portfolio its value is 0.1
If a stock was in the first month portfolio & its weight had fallen to .9 we need to top it up back to .1 so it ads 0.01 to turnover. And so on.
So I need a formula that can recognise if a stock was previously included or not & give me a range of turnover values. I envisage setting it up in 5 columns where columns A & C give the identifying label for the stocks and B & D give the weights. And E gives the turnover values.
A B C D E
93 0.101860317 57 0.1
490 0.09788528 485 0.1
1 0.100369678 3 0.1
57 0.10056843 260 0.1
275 0.098680288 5 0.1
83 0.102357197 498 0.1
301 0.099405732 456 0.1
15 0.099674047 2 0.1
3 0.099574671 4 0.1
456 0.099624359 8 0.1
In this example you will see that 3 stocks carry over from month 1 to the second month. Two (3 & 456)have fallen below 10% so we need to purchase more. The other 7 stocks in month 2 are all new so they contribute 70% to turnover. Stocks that carry over from one month to the next but are sold down to maintain weight don't count as we are using net purchases to calculate turnover.
Doing this by hand I calculate portfolio turnover as 70.08% but I want to automate this so Excel can work it every month & eventually for 50 or 100 stocks.
This message was edited by Nightboy on 2002-09-16 23:01
Some will now be above 10% of the portfolio weight & some will be less than 10%. At the start of the next month we form a new portfolio of 10 stocks. We are selecting from a universe of 50 stocks, so the stocks in the second month portfolio may or may not have been in the first month portfolio.
We calculate turnover by working out net purchases. So if a stock is new to the portfolio its value is 0.1
If a stock was in the first month portfolio & its weight had fallen to .9 we need to top it up back to .1 so it ads 0.01 to turnover. And so on.
So I need a formula that can recognise if a stock was previously included or not & give me a range of turnover values. I envisage setting it up in 5 columns where columns A & C give the identifying label for the stocks and B & D give the weights. And E gives the turnover values.
A B C D E
93 0.101860317 57 0.1
490 0.09788528 485 0.1
1 0.100369678 3 0.1
57 0.10056843 260 0.1
275 0.098680288 5 0.1
83 0.102357197 498 0.1
301 0.099405732 456 0.1
15 0.099674047 2 0.1
3 0.099574671 4 0.1
456 0.099624359 8 0.1
In this example you will see that 3 stocks carry over from month 1 to the second month. Two (3 & 456)have fallen below 10% so we need to purchase more. The other 7 stocks in month 2 are all new so they contribute 70% to turnover. Stocks that carry over from one month to the next but are sold down to maintain weight don't count as we are using net purchases to calculate turnover.
Doing this by hand I calculate portfolio turnover as 70.08% but I want to automate this so Excel can work it every month & eventually for 50 or 100 stocks.
This message was edited by Nightboy on 2002-09-16 23:01
