Portfoio modelling formula help
 

Hi,

Im doing some portfolio modelling in Excel and need some advice on some of
my formulas. I have a list of stock market trades and I what to model
different profit scenarios based on setting a limit on the number of trades
in the portfolio, and then calculating a running total of the accepted trades
based on this limit. Ill first explain what I have, and secondly what I
would like to do.


1) What I have

My Worksheet is arranged as follows in the following columns.

A €“ Security (Stock name)
B €“ Cost ($ amount to buy)
C €“ Sell ($ amount sold)
D €“ Profit ($ profit i.e. Sell €“ Cost)
E €“ Entry Date (Date purchased)
F €“ Exit Date (Date Sold)
G €“ Duration (Trade Duration in days)

Data is sorted ascending based on the Exit Date which defines when the trade
is complete
Hope this gives a picture of my data



2) What I would like to do

a) The portfolio can only take 5 trades at a time based on combinations of
overlapping Entry date and Exit Dates. Once 5 trades are accepted at any
given date, all others are rejected until one or more are closed. I think I
some kind of counter that compares the Exit date of the current row to the
overlapping dates of the preceding€¦.Any thoughts

b) I wish to sum the profit (D) for accepted trades in a) in col H. This
will give a running total / balance.



Appreciate any thoughts on this or other ways to approach. I have a few
ideas, just cant seem to get it to work.

Regards,

Bruce