Free Microsoft Excel 2013 Quick Reference

Calculate Profit Or Loss

I have a simple spread sheet that tracks stock purchases and sales. I want to calculate the correct profit or loss as I buy and sell the stock. I need to link the sale price of a share to the appropriate purchase price at the time it was purchased. For example:

A B C D
--------------------------
Purchases
Date Shares Price total
4/1 10 $20 $200
4/15 20 $25 $500

Sales
4/20 5 $30 $150
4/21 15 $40 $600

The first sale is 5 shares, so I want to measure profit against the first purchase price: i.e.: 5 shares were bought at $20 each and sold for $30 each = profit of $50.

Next I sell 15 shares @ $40, but the first 5 shares should be calculated as 5 shares bought @ $20 & sold for $40 = $100 profit. The remaining 10 shares were bought at $25 and sold at $40 = $150.

Is there any function that will help me calculate this for numerous purchases and sales?

Thanks
Larry


Post your answer or comment

comments powered by Disqus
Hi ,
I have the following issue, thank you to read my post in advance.

I have the attached file, which is basically a set of stock prices over a certain period. Notice each stock price time gap is 30 min, not daily as usual (but I think it does not matter for what I am looking for). In column C and D I have the moving average period for long period (20 days) and short period (5 days) respectively. Column E is the Profit or Loss

The way it works is each time short period (column D) value is above the long period (Column C) value; I buy the stock at the corresponding price(column B) and I will sell it when the short period will be lower again than Long period at the corresponding price (column B)

See attached the excel file, and let me give you an example

For column C and D from row 21 to 44, Long Period is always higher than Short Period (so I do nothing) BUT on Row 45 Short Period is now above Long Period AND THIS until Row 68.

So what my macro needs to do is to automatically calculate B68-B45 = E45 with
(C68 the corresponding price at which the short period went above long period
C45 the corresponding price just before the short period returns below the long period) and this for each time the short period is above the long.

I put a graph also to have a visual view of what I am saying. each time the red line is above the blue line, the macro need to calculate the second point of cross minus the first, and so on.

I am very new in VBA, and would appreciate and help for you fellow member.

Thank you

Greetings,

I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Where:
Market position is either 1 or -1
Entry price is the price where the position is reversed.

For example:
H3=1 and I3=100.56, then
H4=-1 and I4=100.88, then
H5=1 and I5=100.53

I need to calculate the profit as follows:
I4 - I3 (because market position of 1 expects the price to go up) and then,
I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))

Any help would be greatly appreciated.

Scott

I have my data like so:

ColA ColB ColC ColD ColE ColF
Bid Ask PL Side QTY TradePrice
12.7 12.8 -100 Sell 10 12.7
12.7 12.8 0 Buy 10 12.7
12.7 12.8 100 Sell 10 12.9
12.7 12.8 -200 Buy 10 12.9

ColD thru E has my trade data while ColA and ColB has the realtime market
prices. I am trying to come up with a formula for ColC which will calculate
my Profit or Loss for each trade.

For example on the last line, I bought 10 shares at 12.90. To close out the
position at that moment in time I would need to sell them on the "bid" side
at 12.70, thus creating a $200 loss.

I think the formula needs to look at the trade side. If the trade was a buy,
then the "bid" side of the realtime market is used in the Profit and Loss
Formula. If the trade price is greater than the "bid" price, then there is a
profit. If the trade price is less than the "bid" price, then there is a loss
- as in the example.

Similar but opposite for trade side = "sell". The formula will need to look
at the "ask" side of the real time market. If the trade price is greater than
the "ask" price, then there is a gain. If the trade price is less than the
ask price, there is a loss.

Thank you in advance.

I have my data like so:

ColA ColB ColC ColD ColE ColF
Bid Ask PL Side QTY TradePrice
12.7 12.8 -100 Sell 10 12.7
12.7 12.8 0 Buy 10 12.7
12.7 12.8 100 Sell 10 12.9
12.7 12.8 -200 Buy 10 12.9

ColD thru E has my trade data while ColA and ColB has the realtime market
prices. I am trying to come up with a formula for ColC which will calculate
my Profit or Loss for each trade.

For example on the last line, I bought 10 shares at 12.90. To close out the
position at that moment in time I would need to sell them on the "bid" side
at 12.70, thus creating a $200 loss.

I think the formula needs to look at the trade side. If the trade was a buy,
then the "bid" side of the realtime market is used in the Profit and Loss
Formula. If the trade price is greater than the "bid" price, then there is a
profit. If the trade price is less than the "bid" price, then there is a loss
- as in the example.

Similar but opposite for trade side = "sell". The formula will need to look
at the "ask" side of the real time market. If the trade price is greater than
the "ask" price, then there is a gain. If the trade price is less than the
ask price, there is a loss.

Thank you in advance.

If in column (A) I have a "B" for buy and a "SS" for sell short. Then in column (B) I have the fill price of a certain stock such as "$98.5" for example. Then in Colomn (C) I have the Closing price for this particular stock such as "$99.5."(since I will hold to the end of the day). In Column (D) I want to have the profit or loss. The problem is that the formula needs to be different depending if the opening transaction is Buy "B" or a "SS" Sell Short. So in other words I need a formula that says if in column (A) there is a "B", then take the closing price column (C) and minus the Fill price in column (B) to give the Profit or loss in Column (D). But at the same time or in the same formula cell if in column (A) there is "SS" then it needs to be reverse and take the fill price Column (B) and minus the Closing Price Column (C) to put the Profit or loss in Column D. Is there a formula I can put in the cells in column D that can do this? Any help is much apreciated.

I'm running the accounts for a village fair, and I'm stuck. I have a list of payments into and out of an account. The list looks a bit like this:
A...................B
Sum....| Profit or loss (P/L)
10........| P
12........| L

I then want to add up all the 'P' items and all the 'L' items separately. I tried VLOOKUP, but it doesn't work.

How do I do it?

Hi All,

Ive been having issues working out a formula. I want it to do the following

Mon Tue Wed Thur Fri
100 200 350 200 300
(N/A) 100 150 150100
Basically i am going to enter a balance daily and need to enter the profit or loss for the day in the field below
I also need to pull through fridays figure into another sheet

Any ideas?

Thanks

Hi i would like to test the profitability of a trading strategy and was hopeing someone may be able to help here.

In simple terms i have a column say D which each day may or may not generate a signal so a cell will have either Buy or Sell or be blank the data is ordered in date order. If a buy signel is shown i wish to take the closing price for that day column c as my entry price. At some point in the future there will be a sell signal (all blanks and additional buy signals to be ignored) at this point the closing price for at this point in column c will be taken as the closing of the trade and this price less the opening buy price calculated to give a profit or loss figure shown in column E. On the sell signal an new order is also generated at the clossing price and will be clossed when the next buy signal is generated. see table below.

A B C D E
Date opening P Clossing P Signal Profit/Loss

3/4/07 860.23 863.55
4/4/07 863.55 880.23 buy
5/4/07 880.23 881.45
6/4/07 881.45 879.30 sell 0.93
7/4/07 879.30 874.56
8/4/07 874.56 870.00
9/4/07 870.00 875.32 buy 3.98
10/4/07 875.32 880.00

It would mean a lot if someone could help thank you

I am trying to calculate the sales growth or loss as a percentage. I have A1 (2007 Sales) B1 (2008 Sales) C1(Percentage Gain/Loss)
I need to show this in a percentage representation either positive or negative. Also, some of the values are $0.00
Can anyone advise best way to achieve this?
Thanks

Hi,
I am trying to total the profit column using this formula without any success:

=IF(OR($J3="",ISERROR(MATCH(-$J3,$J3:$J$1002,0)),),"",$J3*(OFFSET($K3,MATCH
(-$J3,$J3:$J$1002,0)-1,)-$K3))

Where a (1) means the purchase price subtracted from (-1) sales price and
the same (-1) is the sales price and the next (1) is the purchase price and
so on.

Calculating by hand would look like this:
101.125 - 100.531 = +.594 and,
101.125 - 94.750 = +6.375 and,
98.250 - 94.750 = +3.500

MARKET ENTRY PROFIT
POSITION PRICE
1 100.531
1
1
1
1
1
1
-1 101.125
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
1 94.750
1
1
-1 98.250

Any help would be greatly appreciated,

Regards,

Scott

Hi,
I am trying to total the profit column using this formula without any success:

=IF(OR($J3="",ISERROR(MATCH(-$J3,$J3:$J$1002,0)),),"",$J3*(OFFSET($K3,MATCH
(-$J3,$J3:$J$1002,0)-1,)-$K3))

Where a (1) means the purchase price subtracted from (-1) sales price and
the same (-1) is the sales price and the next (1) is the purchase price and
so on.

Calculating by hand would look like this:
101.125 - 100.531 = +.594 and,
101.125 - 94.750 = +6.375 and,
98.250 - 94.750 = +3.500

MARKET ENTRY PROFIT
POSITION PRICE
1 100.531
1
1
1
1
1
1
-1 101.125
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
1 94.750
1
1
-1 98.250

Any help would be greatly appreciated,

Regards,

Scott

Hi,
I am trying to total the profit column using this formula without any success:

=IF(OR($J3="",ISERROR(MATCH(-$J3,$J3:$J$1002,0)),),"",$J3*(OFFSET($K3,MATCH
(-$J3,$J3:$J$1002,0)-1,)-$K3))

Where a (1) means the purchase price subtracted from (-1) sales price and
the same (-1) is the sales price and the next (1) is the purchase price and
so on.

Calculating by hand would look like this:
101.125 - 100.531 = +.594 and,
101.125 - 94.750 = +6.375 and,
98.250 - 94.750 = +3.500

MARKET ENTRY PROFIT
POSITION PRICE
1 100.531
1
1
1
1
1
1
-1 101.125
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
1 94.750
1
1
-1 98.250

Any help would be greatly appreciated,

Regards,

Scott

I am using this formula to calculate gain or loss
How can i get a + or - sign to show in my answer.

=IF(B3<B2,B2-B3,IF(B3>B2,B3-B2,""))

example: + $100.00
or - $100.00

Any help is appreciated
Thanks
Joel

I am working on a stock trading sheet that is checking on several
paramaters like volume, change in price today vs change in price
yesterday, and price moving above or below a trading channel. This is
a back testing process working from historical data downloaded from the
yahoo stock site.

What would the best process be to quary all of the paramaters above and
when each is true issue a "Buy" signal and start accumilating "Profits"
untill the sheets hits a new criteria for "Sell".

I want to calculate the profit or loss based upon the change in stock
price between two sets of multiple criteria. Addationally I want the
sheet to issue one of three positions based upon the most current state
of the markers checked: Buy, Sell or Hold.

Not looking for specific answer just a point me to the correct excel
function.

Thanks, Michael

--
MJSlattery
------------------------------------------------------------------------
MJSlattery's Profile: http://www.excelforum.com/member.php...o&userid=16141
View this thread: http://www.excelforum.com/showthread...hreadid=378306

This is really a basic math question but I thought Excel was the Math Wizard
(more so than me) and could help, but this has opened up a bit of can of
worms in our office and would like some other input.
If we had a net loss the previous month of $-31,000 and this month we show a
net gain of $51,000.. what is the difference and is it a gain or loss? I say
that you take the negative $31000 and add it to the $51000 by that very
simple formula in Excel and you end up with a net gain between the 2 months
of $20,000... however, the others in my office are saying that you really end
up with $82,000... which means that you would have to take that negative
-31000 and subtract the 51000 in excel.. and then it's a negative (loss) of
the $82000... that doesn't seem right when you did end up this month with a
gain..
thanks for the help..

Hi
i am working out the total income of a contract minus the total costs to
show the contracts profit or loss.
how do i do this as a percentage]

ie: total income £19,213.68
total costs £10,274.11
Profit / (loss) £8,939.57

any help would be appreciated
Lisa

Hi guys

As you probably know by now I have set up several sheets to try and help me with the virtual game I play and I am trying to set another one up now.

I am wanting to calculate how much a horse has made profit or loss for each win it gets. So I want to calculate how much it made up to its first win, then from its first to its second win etc. I have attached a file so you can see more clearly what I am hoping to set up.

Thanks
Gemma

I have downloaded my Paypal history to excel. As I have transferred money to my checking account every now and then, the column that states my balance is not accurate. Even after deleting the withdrawn money rows it still reflects what Paypal shows.

I am trying to have Excel show my Paypal overall profit and loss, so I believe I probably have to enter some formulas in Excel, but don't know how or where. I have a few purchases mixed in, payments received, there's a fees column for processing the credit cards, and there are also some refunds mixed in, not sure if this complicates things or not. I would also like to be able to see this month by month, perhaps I just customize the date range from Paypal before transferring.

HI everyone,

I am making a share portfoilo spreadsheet,and want an easy viewing profit or loss coloured cell. if i am making a profit i want green and if making a loss make red

so (amount invested £10,000 * current price £2) -( amount invested £100* purchase price £1)

Profit = £10,000

I'm trying to create a spreadsheet that will automatically calculate profit/loss, cumulative total, and AUM for a series of securities transactions when information such as quantity, purchase price, and sales price are input. I don't want to just insert the formula in the first line of the column and drag down because this is disrupting my ability to print (ie, the computer will want to print hundreds of pages).

Is there a way to set up a macro or button or something that will calculate my desired categories when needed without having to drag the formula down throughout the spreadsheet? Also, the macro would only be needed once the security is sold, therefore at times only columns A through C will be populated. Lastly, is it possible for the macro to work if some securities have completed their sale while others have not? For example, the security represented in row 5 is still open, while in row 12 has been sold and therefore should be included in profit/loss, cumulative total, and AUM columns.

Thanks a bunch!

Currently my spreadsheet has the following column headings:

A1: Security
B1: Date Purchased
C1: Quantity
D1: Purchase Price
E1: Date Sold
F1: Sale Price
G1: Profit or Loss
H1: Cumulative Total Profit/Loss
I1: AUM
I2: (my starting figure for AUM)
Ideally my transaction data would begin in row 3 then

I'm trying to show profit and loss over a 12 week period with stocks. A line
graph is the best way but bar graphs are useful as well. However, I m not
able to find either in excel. Does anyone out there have any suggestions?

I need help creating a formula to total the current gain or loss based on two numbers
For example:
If cell D4 is greater than F4,the difference between the two cells has to show a negative answer in cell E4
If cell F4 is greater than cell D4, the difference between the two cells has to show a positive answer in cell E4

High Guys,

I need some help writing a few formulas in the attached spreadsheet. The Data_Input sheet where all the information will come from I have set-up Name ranges for each category.

I need to write the following formulas in the calculation sheet.

1 Fixed costs per week

2 Variable costs per drink in normal hours

3 Variable Costs per Drink in Overtime

4 The breakeven point in the number of 375ml drinks per week Inoring overtime

5 If the selling price per drinks changes, while all other data remains the same, what is the minimum selling price per drink below which overtime will be required to breakeven. I will need an If statement to test whether overtime is required.

6 A formula that calculates the profit or loss in normal hours

7 Graph_Data sheet will need formulas to allow me to graph it

8 I need to calculate the number of drinks that must be sold to achieve that target profit of $45,000.

9 A formular that will warn me that a target profit I keyed in will require more that 15 hours overtime.

Thank You for Your Help in advance

Runner77

XL2000: Syntax Errors When Calculating Functions or Opening a Workbook

When you recalculate values in a Microsoft Excel workbook that contains a custom function, the Microsoft Visual Basic Editor may be open and you may receive the following error ...


No luck finding an answer? You could always try Google.