The attached spreadsheet is showing both a win and a loss for trades which were opened as a Credit and as a Debit.
I am trying to change the formula in a cell based on whether another cell is reading the word “Credit” or “Debit”. In addition, I would like to look at another cell and if that cell is empty, and if so, then don’t apply the formula at all.
I have the three formulas, which all work independently, but when I put them all on the same line, in the same cell, I get an error message that reads . . . “There is a problem with this formula. Not trying to type of formula? When the 1st character is equal or - Excel thinks it’s a formula”
I need to know if I have a Profit or Loss in Row R of the attached spreadsheet.
• If I sell an Option trade to open a contract, there is the word “Credit” in column D.
• If I buy an Option trade to open a contract, there is the word “Debit” in column D.
To determine profit or loss, this involves six fields
1. D = Opening a trade with either a Debit (Buy to Open), or Credit (Sell to Open).
2. F = Amount of money paid (Debit) or money received (Credit) to Open a trade, shown as the price of an individual share.
a. (Note: Price paid is for an individual share, so price x 100 = cost of ONE contract.)
3. K = Quantity of contracts included in the trade. (Note: each contract is 100 shares).
4. L = Closing cost per share shown as cost of individual share.
a. Note: If the trade starts as a Debit (contracts are Bought to Open), then profitability is determined by comparing column F to column L (Open:Close) and the close price must be MORE than open price to show a profit, otherwise it is a loss.
b. Note: If the trade starts as a Credit (contracts are Sold to Open), then profitability is determined by comparing column F to column L (Open:Close) and the close price must be LESS then open price to show a profit, otherwise it is a loss.
5. P = Commissions and fees paid to broker to transact the trade. This cell will always be deducted from any profitability or added to any loss.
=IF(ISBLANK(X3),"", If cell X3 is blank
=IF(F3="Credit",100*(H3-U3)*(T3)-(Y3)) If cell F3 reads “Credit”
=IF(F6="Debit",100*(U6-H6)*(T6)-(Y6)) If cell F3 reads “Debit”
Bookmarks