# Portfolio turnover

#### Nightboy

##### New Member
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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Nightboy,

Assuming the "A" is in cell A1
put the following formula in cell E2
=IF((D2-IF(ISERROR(VLOOKUP(C2,A:B,2,FALSE)),0,(VLOOKUP(C2,A:B,2,FALSE))))<0,0,(D2-IF(ISERROR(VLOOKUP(C2,A:B,2,FALSE)),0,(VLOOKUP(C2,A:B,2,FALSE)))))

and copy down for each cell in column E.

Cheers

Put this in E2 and copy down:
=IF(ISNA(MATCH(C2,A:A,0)),0.1,MAX(0,0.1-INDEX(A:B,MATCH(C2,A:A,0),2)))

Guys, I thank you.
I tried both formulas & they both work fine.
I guess I will go with the one from Scott R as it does the same job with a simpler entry.
I'm impressed!

Replies
2
Views
369
Replies
0
Views
166
Replies
1
Views
171
Replies
1
Views
2K
Replies
7
Views
391

1,217,347
Messages
6,136,045
Members
449,981
Latest member
kjd513

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back