Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Profit and Loss Formula

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.


Post your answer or comment

comments powered by Disqus
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 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.

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

Hi

I want to do an inventory system but I want it to automatically decrease the inventory stock when I increase the stock at Profit and loss side.

Please do take a look at the excel sheet attached.

Thank you :-) and I appreciate any help given.

Hi all members

I attached my stock transactions sheet (Sheet1).
I need macro that converts this data as per sheet2 ,that is my profit and loss statments.
Regards

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?

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.

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 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

Hi, im quite new to excel, iv stared selling things and wanted to keep track of my losses and profits, iv made a spreadsheet and so far its ok, any numbers under 0 so with a minus will come up with a red box and if a profit comes then a green box, i have a total profit and total loss box i want all the minus numbers to be added up and tell me all my loss and then all the profit boxes to be added up in the total profit box. i have taken a screenshot and the vales i have used are just for example, thanks for any help. could you explain well please and if so , somthing to copy and paste, much appreciated.

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,
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

G'day all, as the title suggests, I believe the formulas I have on my excel sheet are causing excel to run exceptionally slow.

I'm not new to excel, but complexed formulas is certainly not my forte and I have no idea on how to speed it up, considering im just in the styling stage of setting up the next financial years profit and loss sheet.

So what i've got if anybody is able to assist is this:
I downloaded a 'profit & loss statement' file and have modified it to meet my business needs, and what i've done is added 2 additional worksheets, one being setup (which includes dates mainly) and the 2nd being a 'ledger' where I enter all the years information in (like purchases, sales etc).
What the program/file is to allow is for the user (me) to enter details of sales, purchases etc into the 'ledger' form, and then when I want to view sales, expenditure etc etc, I go to the profit & loss form, which is categorised by each month.

Now the formula i'm using is the '=sumproduct' formula. Currently, I believe this is what is causing the file to run exceptionally slow, and all I have in there so far is just sample data for several months to ensure the formulas were working correctly.

Now the other thing that also appeared is when I added more formulas (have since deleted them), an error message kept appearing saying out of memory or something.

Also whether this means anything or not, but im using a 1month old computer that is an I7, so its certainly not a slow or old computer.

Any help is greatly appreciated, as I find for a small business like mine, its just too expensive to get MYOB or other accounting software.

attached is the file. (the zip file is the .xls, and the other is the office 2007 version), same file, just saved differently for those that might not have office 2007.

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

I am trying to sum up the results of a few stock trades that I had lately. Please see the attached excel file.

I start off with $10,000 (C2) and you can see the amount that one has at the end of the trading period in C10. It is nothing but adding up profits and losses throughout the trading period from 7/30/2008 to 8/19/2008. E.g. cell D2 displays the total amount (after profit/loss) if one starts trading on 7/30/2012 with $10,000 in pocket. In cell E2 you can see the lowest level of money one has throughout the period.

What I'd like to do is to create some kind of formula/macro that adds similar numbers for rows D3 to E10. i.e. what is the total at the end of the period and lowest amount if one starts trading with $10000 on 7/31. That information should populate automatically in cell D3 & E3. The same way what is the total at the end of the period and lowest amount if one starts trading with $10000 on 08/01. That information should populate automatically in cell D4 & E4.

Don't have any clue in the world how to do that?

Can someone help please?

Appreciate your help in advance. Thanks.

I am not sure the best way to approach this. I was thinking of doing it through functions but VBA may work best.

I have a spreadsheet I have created to calculate rankings. I have figured out how to look up the teams wins and losses. Now I need a way to figure out how to look up all of the oponents wins and losses. The sheet is pulling all data from a score page sheet where the scores of games are entered. I now need to calculate oponents wins and losses that they have currently played.

The second peice is Strength of Schedule and Opponents Strenght of Schedule.
I would like to use current rankings as a strength adder. The last place team will be assigned a 1 counting up to the top ranked team. When a team plays a home game against the lowest ranked team, it will add a 1 (opponents strength adder) to the teams strength of schedule pool. If a team plays an away game it will be the teams I will add 1.5 times the opponents strength adder to the teams schedule strength pool. Then the team with the highest pool with have the hardest strength of schedule. as games are entered I would like the formula to change with the rankings of the teams

Payment is $30 USD

Quick question for everyone pertaining to a spreadhseet I've created to keep track of my dart leagues player stats...

I have 3 columns.... Wins Column (B), Losses Column (C) and Win Pct Column (D)...

I've calculated the Win Pct with this formula: B22/SUM(B22:C22)

I have some players that have not played yet, and since their wins and losses are 0 & 0, the Win Pct column gives a result of #Div/0!....

I print these stats pages out every week and would prefer not to have the error message displayed but rather NA or something similar...

How would I utilize the avg and iserror formula together to get rid of this error? Or is there another way to do this?

Erik

This is probably quite a 'noob' question to most of you, but I have an excel
spreadsheet set up for a month by month profit and loss that uses formulas to
calculate subtotals/totals/balances both accross and down for me.

However when I enter or edit information into the cells that the formulas
relate to, the totals do not update, it isn't until I actually edit and then
accept the formula cell afterwards that the updated value appears.

If I happen to miss 'updating' the formula in one cell then all other
balances remain as were previously calculated. This is quite frustrating and
I'm sure there is just an option or something that I'm missing somewhere.
Please help.

I am trying to build an excel sheet to calculate the profit and loss on hedging heating oil by buying call options. The basic idea is that if the market price exceeds the strike price of a call option, you would exercise your call option to profit on the futures market. However, if the market price does not exceed the strike price of the call option, you would not exercise that option but instead just buy the heating oil at the market price as it would be cheaper (you would just lose the premium you paid for that call option). My question is, is there any way in excel to generate a formula that differentiates between these two. For example, if the price I am hedging is $3.00, and so I buy a $3.00 call, and the futures price rises to $4.00, I set up excel to calculate $4.00 - $3.00 for a $1.00 profit, as we are exercising our call option for a $1.00 profit. However, if the futures price drops to $2.00 (and we have hedged with a $3.00 call) we are obviously not going to exercise that call option as we can get the heating oil for cheaper in the current market. Can I input a formula that will ONLY calculate the difference if the current (futures) price rises above the $3.00 call, but if the market price is below $3.00 have excel just use the current market price? Sorry if this is confusing, I will elaborate if you have any questions. Thanks for any and all help.

Need help. I have to do a profit and loss between two seperate tabs which will have the common denomintor of name only. I need to see money & out for each name. Can anyone guide me on this?

Sorry that I broke a rule before I made this tread. I will not do that again.

I have been working on making formula on streak on wins and losses on my excel for football statistics. The formula I am using is:

"=(INDEX(G10:G26,COUNT(H10:H26))&(INDEX(H10:H26,COUNT(H10:H26))))"

The result came out as L4 (lost 4 straight games), that result is just what I want, but the problem is when I put in the final score on next game, the result came out blank. I couldn't figure the problem.

Thanks

Hello,

This is my first thread.
I search on google/youtube but unable to find solution, so I am asking here.

I want to calculate profit/loss of every month and show it in other box.
Like what would be Jan profit and loss, feb profit and loss, ..............

I am attaching a file.


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