Portfolio turnover

Nightboy

New Member
Joined
Sep 14, 2002
Messages
16
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 videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

robmac

New Member
Joined
Sep 16, 2002
Messages
4
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.

The sum of column E will be your answer.

Cheers
 

Scott R

Active Member
Joined
Feb 20, 2002
Messages
430
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)))
 

Nightboy

New Member
Joined
Sep 14, 2002
Messages
16
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top