{"id":"gen-1785884727-PnjtQaKjcIEsSnoduAQm","choices":[{"finish_reason":"stop","index":0,"logprobs":null,"message":{"content":"# Fórmulas de Excel para Controle de Ações e Opções (B3)\n\n## 1. Estrutura sugerida da planilha\n\n**Aba \"Operações\"** — uma linha por operação:\n\n| Coluna | Campo | Exemplo |\n|--------|-------|---------|\n| A | Data | 15/01/2025 |\n| B | Ticker | PETR4 / PETRA50 |\n| C | Tipo (C/V) | C |\n| D | Quantidade | 100 |\n| E | Preço unitário | 38,50 |\n| F | Custos (corretagem + emolumentos) | 5,00 |\n| G | Valor total | `=D2*E2` |\n| H | Categoria | Swing Ação / Swing Opção / Day Trade |\n\n> **Dica:** ordene por ticker e data para as fórmulas de preço médio funcionarem.\n\n---\n\n## 2. Preço médio (essencial para IR)\n\n**Saldo de quantidade (coluna I):**\n```\n=SE(B2<>B1; SE(C2=\"C\";D2;-D2); I1+SE(C2=\"C\";D2;-D2))\n```\n\n**Preço médio (coluna J)** — vendas não alteram o PM, apenas compras:\n```\n=SE(C2=\"V\"; J1; SE(B2<>B1; (G2+F2)/D2; (I1*J1+G2+F2)/(I1+D2)))\n```\n\n---\n\n## 3. Resultado por operação\n\n**Lucro/prejuízo na venda (coluna K):**\n```\n=SE(C2=\"V\"; (E2-J2)*D2-F2; \"\")\n```\n\n**Rentabilidade da operação:**\n```\n=SE(C2=\"V\"; (E2-J2)/J2; \"\")\n```\n\n---\n\n## 4. Consolidação mensal (aba \"Resumo\")\n\n**Total de vendas de ações no mês** (para verificar isenção de R$ 20 mil):\n```\n=SOMASES(Operações!G:G; Operações!A:A;\">=\"&DATA(2025;1;1);\nOperações!A:A;\"<=\"&FIMMÊS(DATA(2025;1;1);0);\nOperações!C:C;\"V\"; Operações!H:H;\"Swing Ação\")\n```\n\n**Lucro do mês por categoria:**\n```\n=SOMASES(Operações!K:K; Operações!A:A;\">=\"&DATA(2025;1;1);\nOperações!A:A;\"<=\"&FIMMÊS(DATA(2025;1;1);0);\nOperações!H:H;\"Swing Opção\")\n```\n\n---\n\n## 5. Cálculo do Imposto de Renda\n\n**Ações — swing trade** (isenção se vendas ≤ R$ 20.000/mês):\n```\n=SE(vendas_mês<=20000; 0; MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,15)\n```\n\n**Opções — swing trade** (⚠️ sem isenção, sempre 15%):\n```\n=MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,15 - IRRF_retido\n```\n\n**Day trade** (20%, sem isenção):\n```\n=MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,20 - IRRF_retido\n```\n\n**IRRF \"dedo-duro\" retido na fonte:**\n- Swing: `=vendas_mês*0,00005` (0,005%)\n- Day trade: `=lucro_mês*0,01` (1%)\n\n> Prejuízos podem ser compensados em meses futuros, mas **apenas dentro da mesma categoria** (day trade só abate day trade; opções podem abater de ações swing e vice-versa).\n\n---\n\n## 6. Fórmulas específicas para opções\n\n**Dias até o vencimento:**\n```\n=data_vencimento-HOJE()\n```\n\n**Classificação (moneyness):**\n```\n=SE(tipo=\"Call\"; SE(preço_ativo>strike;\"ITM\";SE(preço_ativo=strike;\"ATM\";\"OTM\"));\nSE(preço_ativo0\")/CONT.SES(Operações!K:K;\"<>\")\n```\n\n**Fator de lucro:**\n```\n=SOMASE(Operações!K:K;\">0\")/ABS(SOMASE(Operações!K:K;\"<0\"))\n```\n\n**Buscar PM atual de um ticker (com PROCX):**\n```\n=PROCX(\"PETR4\"; Carteira!A:A; Carteira!B:B; \"Sem posição\")\n```\n\n---\n\n## Observações importantes\n\n- As alíquotas e regras (15%/20%, isenção de R$ 20 mil, IRRF) refletem a legislação vigente até meu conhecimento — **confirme com um contador**, pois regras fiscais mudam.\n- Use **Tabela Dinâmica** sobre a aba Operações para consolidar por mês/ticker sem fórmulas.\n- Formatação condicional em verde/vermelho na coluna de resultado facilita a leitura.\n\nQuer que eu detalhe alguma parte — por exemplo, uma planilha completa de controle de IR mensal com compensação de prejuízos, ou fórmulas de Black-Scholes para precificação de opções?","refusal":null,"role":"assistant","annotations":null,"audio":null,"function_call":null,"tool_calls":null,"reasoning":"The user is asking in Portuguese about Excel formulas for financial accounting related to options and stocks on B3 (the Brazilian stock exchange - Brasil, Bolsa, Balcão). This is a legitimate financial/accounting question. Let me think about what would be most useful.\n\nThe person likely wants:\n1. Formulas for tracking stock portfolios\n2. Options pricing/tracking formulas\n3. Tax calculations (Brazil has specific tax rules for stocks and options)\n4. Profit/loss calculations\n5. Average price calculations (preço médio - important for Brazilian tax purposes)\n6. IR (Imposto de Renda) calculations - in Brazil, stocks have 15% tax on gains (swing trade) and 20% for day trade, with exemption for sales under R$20,000/month for stocks (but NOT for options)\n7. DARF calculations\n\nKey Brazilian market specifics:\n- Ações: 15% IR on gains for swing trade, exemption if total sales < R$20,000/month\n- Day trade: 20% IR, no exemption\n- Opções: 15% IR (no exemption for options!), 20% day trade\n- Preço médio calculation is crucial\n- Custos operacionais (corretagem, emolumentos, taxa de liquidação)\n- IRRF (imposto retido na fonte) - 0.005% for swing trade (\"dedo-duro\"), 1% for day trade\n\nUseful Excel formulas:\n1. Preço médio ponderado\n2. Lucro/Prejuízo por operação\n3. Cálculo de IR\n4. SUMIF/SUMIFS for consolidating by ticker\n5. IF formulas for tax exemption logic\n6. Options: tracking strikes, vencimentos, prêmios\n7. Greeks calculations (maybe too advanced, but Black-Scholes is possible in Excel)\n8. Rentabilidade\n9. Position sizing\n\nLet me structure a comprehensive answer in Portuguese (since they asked in Portuguese):\n\nStructure:\n1. Controle de operações (buy/sell tracking)\n2. Preço médio\n3. Resultado (P&L)\n4. Cálculo de IR (with Brazilian rules)\n5. Opções específicas\n6. Fórmulas auxiliares (PROCV/VLOOKUP, SUMIFS, etc.)\n7. Maybe a suggested spreadsheet layout\n\nLet me write the formulas:\n\n**Preço Médio (weighted average):**\nWhen buying: PM = (Qtd_antiga × PM_antigo + Qtd_compra × Preço_compra + Custos) / (Qtd_antiga + Qtd_compra)\n\nIn Excel, if you have a transactions table:\n=SUMIFS(valor_total; ticker_range; ticker; tipo_range; \"C\") / SUMIFS(qtd...)\n\nActually for preço médio with sales (sales don't change PM in Brazilian method):\nPM = Total spent on purchases / Total quantity purchased... no wait, that's not right either when you sell partially.\n\nThe correct Brazilian method: when you sell, you keep the same PM, reduce quantity. When you buy, recalculate weighted average.\n\nA practical Excel approach with a running calculation:\n- Column for quantity balance: =previous + IF(type=\"C\", qty, -qty)\n- Column for PM: =IF(type=\"C\", (prev_qty*prev_PM + qty*price + costs)/(prev_qty+qty), prev_PM)\n\n**Resultado da venda:**\n=(Preço_venda - PM) × Qtd - Custos_venda\n\n**IR Swing trade ações:**\n=IF(total_sales_month <= 20000, 0, MAX(0, lucro) * 0.15) - IRRF\n\nWait, the exemption applies if total sales in the month ≤ R$20,000 for stocks. Options always taxed at 15%.\n\n**IR Day trade:**\n=MAX(0, lucro_daytrade) * 0.20 - IRRF_daytrade (1% withheld)\n\nCompensation of losses: you can offset losses. Formula with MAX(0, lucro - prejuízo_acumulado).\n\n**For options:**\n- Prêmio recebido/pago\n- Resultado = prêmio recebido - prêmio pago (for expired positions)\n- If exercised: the premium adjusts the PM of the underlying\n\n**Useful functions:**\n- SUMIFS (SOMASE/SOMASES in Portuguese Excel)\n- VLOOKUP/XLOOKUP (PROCV/PROCX)\n- IF (SE)\n- MAX (MÁXIMO)\n- EOMONTH (FIMMÊS) for monthly grouping\n- IFERROR (SEERRO)\n\nSince the user writes in Portuguese, I should use Portuguese function names (SE, SOMASES, PROCV, etc.) but mention English equivalents. Actually, in Brazilian Excel, functions are in Portuguese: SOMASE, SOMASES, SE, PROCV, PROCX, MÁXIMO, SEERRO, FIMMÊS, ARRED, etc.\n\nImportant: separator in Brazilian Excel is semicolon (;) not comma.\n\nLet me draft a good structure:\n\n1. **Estrutura básica da planilha** - suggested columns\n2. **Preço médio**\n3. **Resultado por operação**\n4. **Consolidação mensal** (SOMASES)\n5. **Cálculo de IR** (ações swing, day trade, opções)\n6. **Opções específicas** (prêmio, exercício, resultado)\n7. **Indicadores** (rentabilidade, win rate)\n8. **Dicas extras** (tabela dinâmica, formatação condicional)\n\nFormat considerations: This deserves a fairly detailed response with headers, code blocks for formulas, and tables where useful. The user asked a broad question, so a comprehensive but organized answer is appropriate. I should use markdown with headers and code blocks for formulas. Keep formulas accurate and practical. Should be in Portuguese.\n\nLength: Medium-long, but not exhaustive. Focus on the most useful formulas. I'll include disclaimers briefly about tax rules changing (the R$20k exemption, rates as of my knowledge).\n\nLet me write specific formulas assuming a layout:\n\nAba \"Operações\":\n- A: Data\n- B: Ticker (ex: PETR4, PETRA50)\n- C: Tipo (C/V)\n- D: Quantidade\n- E: Preço unitário\n- F: Custos (corretagem + emolumentos)\n- G: Valor total: =D2*E2\n- H: Mercado (Ação/Opção/Day trade)\n\n**Preço médio (na aba da carteira ou com coluna auxiliar):**\n\nColuna I (Saldo quantidade):\n=SE(B2<>B1; SE(C2=\"C\";D2;-D2); I1 + SE(C2=\"C\";D2;-D2))\n\nColuna J (Preço médio):\n=SE(C2=\"V\"; J1; SE(B2<>B1; (G2+F2)/D2; (I1*J1 + G2 + F2)/(I1+D2)))\n\nHmm, need to be careful with the logic. Let me simplify:\n\nJ2: =SE(C2=\"C\"; SE(B2=B1; (I1*J1+G2+F2)/(I1+D2); (G2+F2)/D2); J1)\n\nWhere I1 is previous quantity balance for that ticker. This assumes data sorted by ticker and date. That's a caveat to mention.\n\nAlternative simpler approach: separate sheet per ticker, or use a running balance approach.\n\n**Resultado da venda (coluna K):**\n=SE(C2=\"V\"; (E2-J2)*D2 - F2; \"\")\n\nNote: J2 here should be the PM before the sale. Since for sales J2=J1, it works.\n\n**Consolidação mensal (aba Resumo):**\n\nVendas de ações no mês (swing):\n=SOMASES(Operações!G:G; Operações!A:A; \">=\"&DATA(2025;1;1); Operações!A:A; \"<=\"&FIMMÊS(DATA(2025;1;1);0); Operações!C:C; \"V\"; Operações!H:H; \"Ação\")\n\nHmm, but day trade classification is by same-day buy/sell, harder. Let me include a \"Categoria\" column: Swing Ação, Swing Opção, Day Trade.\n\n**IR Ações (swing):**\n=SE(vendas_mês<=20000; 0; MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,15)\n\n**IR Opções (swing):**\n=MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,15 - IRRF\n\n**IR Day trade:**\n=MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,20 - IRRF_DT\n\nIRRF swing: 0,005% sobre vendas = valor_vendas*0,00005\nIRRF day trade: 1% sobre lucro = lucro*0,01\n\n**Opções - prêmio e resultado:**\n\nPara lançador (vendedor) de opção que expira: lucro = prêmio recebido\nPara titular que expira: prejuízo = prêmio pago\nRecompra: resultado = prêmio_venda - prêmio_compra - custos\n\nExercício:\n- Call exercida (titular): PM da ação = strike + prêmio pago + custos\n- Put exercida (lançador): PM da ação = strike - prêmio recebido\n\n**Fórmulas auxiliares:**\n- Dias para vencimento: =data_venc - HOJE()\n- Moneyness: =SE(E2=\"Call\"; SE(preço_ativo>strike;\"ITM\";...))\n- Rentabilidade: =lucro/capital_investido\n\nAlso mention: PROCV/PROCX to fetch PM in the sale rows, formatação condicional, tabela dinâmica for monthly consolidation.\n\nMaybe a small example table. Keep it practical.\n\nI should also note rates: 15% swing, 20% DT, exemption R$20k/month only for ações (spot market), opções não têm isenção. IRRF dedo-duro 0,005% swing, 1% DT. These are current rules as far as I know (2024/2025). Should add a caveat to verify current rules with a contador.\n\nFormat: Portuguese, headers, code blocks for formulas, maybe one or two small tables. Not too long — I'll aim for comprehensive but scannable. I'll avoid excessive nesting.","reasoning_details":[{"type":"reasoning.text","text":"The user is asking in Portuguese about Excel formulas for financial accounting related to options and stocks on B3 (the Brazilian stock exchange - Brasil, Bolsa, Balcão). This is a legitimate financial/accounting question. Let me think about what would be most useful.\n\nThe person likely wants:\n1. Formulas for tracking stock portfolios\n2. Options pricing/tracking formulas\n3. Tax calculations (Brazil has specific tax rules for stocks and options)\n4. Profit/loss calculations\n5. Average price calculations (preço médio - important for Brazilian tax purposes)\n6. IR (Imposto de Renda) calculations - in Brazil, stocks have 15% tax on gains (swing trade) and 20% for day trade, with exemption for sales under R$20,000/month for stocks (but NOT for options)\n7. DARF calculations\n\nKey Brazilian market specifics:\n- Ações: 15% IR on gains for swing trade, exemption if total sales < R$20,000/month\n- Day trade: 20% IR, no exemption\n- Opções: 15% IR (no exemption for options!), 20% day trade\n- Preço médio calculation is crucial\n- Custos operacionais (corretagem, emolumentos, taxa de liquidação)\n- IRRF (imposto retido na fonte) - 0.005% for swing trade (\"dedo-duro\"), 1% for day trade\n\nUseful Excel formulas:\n1. Preço médio ponderado\n2. Lucro/Prejuízo por operação\n3. Cálculo de IR\n4. SUMIF/SUMIFS for consolidating by ticker\n5. IF formulas for tax exemption logic\n6. Options: tracking strikes, vencimentos, prêmios\n7. Greeks calculations (maybe too advanced, but Black-Scholes is possible in Excel)\n8. Rentabilidade\n9. Position sizing\n\nLet me structure a comprehensive answer in Portuguese (since they asked in Portuguese):\n\nStructure:\n1. Controle de operações (buy/sell tracking)\n2. Preço médio\n3. Resultado (P&L)\n4. Cálculo de IR (with Brazilian rules)\n5. Opções específicas\n6. Fórmulas auxiliares (PROCV/VLOOKUP, SUMIFS, etc.)\n7. Maybe a suggested spreadsheet layout\n\nLet me write the formulas:\n\n**Preço Médio (weighted average):**\nWhen buying: PM = (Qtd_antiga × PM_antigo + Qtd_compra × Preço_compra + Custos) / (Qtd_antiga + Qtd_compra)\n\nIn Excel, if you have a transactions table:\n=SUMIFS(valor_total; ticker_range; ticker; tipo_range; \"C\") / SUMIFS(qtd...)\n\nActually for preço médio with sales (sales don't change PM in Brazilian method):\nPM = Total spent on purchases / Total quantity purchased... no wait, that's not right either when you sell partially.\n\nThe correct Brazilian method: when you sell, you keep the same PM, reduce quantity. When you buy, recalculate weighted average.\n\nA practical Excel approach with a running calculation:\n- Column for quantity balance: =previous + IF(type=\"C\", qty, -qty)\n- Column for PM: =IF(type=\"C\", (prev_qty*prev_PM + qty*price + costs)/(prev_qty+qty), prev_PM)\n\n**Resultado da venda:**\n=(Preço_venda - PM) × Qtd - Custos_venda\n\n**IR Swing trade ações:**\n=IF(total_sales_month <= 20000, 0, MAX(0, lucro) * 0.15) - IRRF\n\nWait, the exemption applies if total sales in the month ≤ R$20,000 for stocks. Options always taxed at 15%.\n\n**IR Day trade:**\n=MAX(0, lucro_daytrade) * 0.20 - IRRF_daytrade (1% withheld)\n\nCompensation of losses: you can offset losses. Formula with MAX(0, lucro - prejuízo_acumulado).\n\n**For options:**\n- Prêmio recebido/pago\n- Resultado = prêmio recebido - prêmio pago (for expired positions)\n- If exercised: the premium adjusts the PM of the underlying\n\n**Useful functions:**\n- SUMIFS (SOMASE/SOMASES in Portuguese Excel)\n- VLOOKUP/XLOOKUP (PROCV/PROCX)\n- IF (SE)\n- MAX (MÁXIMO)\n- EOMONTH (FIMMÊS) for monthly grouping\n- IFERROR (SEERRO)\n\nSince the user writes in Portuguese, I should use Portuguese function names (SE, SOMASES, PROCV, etc.) but mention English equivalents. Actually, in Brazilian Excel, functions are in Portuguese: SOMASE, SOMASES, SE, PROCV, PROCX, MÁXIMO, SEERRO, FIMMÊS, ARRED, etc.\n\nImportant: separator in Brazilian Excel is semicolon (;) not comma.\n\nLet me draft a good structure:\n\n1. **Estrutura básica da planilha** - suggested columns\n2. **Preço médio**\n3. **Resultado por operação**\n4. **Consolidação mensal** (SOMASES)\n5. **Cálculo de IR** (ações swing, day trade, opções)\n6. **Opções específicas** (prêmio, exercício, resultado)\n7. **Indicadores** (rentabilidade, win rate)\n8. **Dicas extras** (tabela dinâmica, formatação condicional)\n\nFormat considerations: This deserves a fairly detailed response with headers, code blocks for formulas, and tables where useful. The user asked a broad question, so a comprehensive but organized answer is appropriate. I should use markdown with headers and code blocks for formulas. Keep formulas accurate and practical. Should be in Portuguese.\n\nLength: Medium-long, but not exhaustive. Focus on the most useful formulas. I'll include disclaimers briefly about tax rules changing (the R$20k exemption, rates as of my knowledge).\n\nLet me write specific formulas assuming a layout:\n\nAba \"Operações\":\n- A: Data\n- B: Ticker (ex: PETR4, PETRA50)\n- C: Tipo (C/V)\n- D: Quantidade\n- E: Preço unitário\n- F: Custos (corretagem + emolumentos)\n- G: Valor total: =D2*E2\n- H: Mercado (Ação/Opção/Day trade)\n\n**Preço médio (na aba da carteira ou com coluna auxiliar):**\n\nColuna I (Saldo quantidade):\n=SE(B2<>B1; SE(C2=\"C\";D2;-D2); I1 + SE(C2=\"C\";D2;-D2))\n\nColuna J (Preço médio):\n=SE(C2=\"V\"; J1; SE(B2<>B1; (G2+F2)/D2; (I1*J1 + G2 + F2)/(I1+D2)))\n\nHmm, need to be careful with the logic. Let me simplify:\n\nJ2: =SE(C2=\"C\"; SE(B2=B1; (I1*J1+G2+F2)/(I1+D2); (G2+F2)/D2); J1)\n\nWhere I1 is previous quantity balance for that ticker. This assumes data sorted by ticker and date. That's a caveat to mention.\n\nAlternative simpler approach: separate sheet per ticker, or use a running balance approach.\n\n**Resultado da venda (coluna K):**\n=SE(C2=\"V\"; (E2-J2)*D2 - F2; \"\")\n\nNote: J2 here should be the PM before the sale. Since for sales J2=J1, it works.\n\n**Consolidação mensal (aba Resumo):**\n\nVendas de ações no mês (swing):\n=SOMASES(Operações!G:G; Operações!A:A; \">=\"&DATA(2025;1;1); Operações!A:A; \"<=\"&FIMMÊS(DATA(2025;1;1);0); Operações!C:C; \"V\"; Operações!H:H; \"Ação\")\n\nHmm, but day trade classification is by same-day buy/sell, harder. Let me include a \"Categoria\" column: Swing Ação, Swing Opção, Day Trade.\n\n**IR Ações (swing):**\n=SE(vendas_mês<=20000; 0; MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,15)\n\n**IR Opções (swing):**\n=MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,15 - IRRF\n\n**IR Day trade:**\n=MÁXIMO(0; lucro_mês - prejuízo_acumulado)*0,20 - IRRF_DT\n\nIRRF swing: 0,005% sobre vendas = valor_vendas*0,00005\nIRRF day trade: 1% sobre lucro = lucro*0,01\n\n**Opções - prêmio e resultado:**\n\nPara lançador (vendedor) de opção que expira: lucro = prêmio recebido\nPara titular que expira: prejuízo = prêmio pago\nRecompra: resultado = prêmio_venda - prêmio_compra - custos\n\nExercício:\n- Call exercida (titular): PM da ação = strike + prêmio pago + custos\n- Put exercida (lançador): PM da ação = strike - prêmio recebido\n\n**Fórmulas auxiliares:**\n- Dias para vencimento: =data_venc - HOJE()\n- Moneyness: =SE(E2=\"Call\"; SE(preço_ativo>strike;\"ITM\";...))\n- Rentabilidade: =lucro/capital_investido\n\nAlso mention: PROCV/PROCX to fetch PM in the sale rows, formatação condicional, tabela dinâmica for monthly consolidation.\n\nMaybe a small example table. Keep it practical.\n\nI should also note rates: 15% swing, 20% DT, exemption R$20k/month only for ações (spot market), opções não têm isenção. IRRF dedo-duro 0,005% swing, 1% DT. These are current rules as far as I know (2024/2025). Should add a caveat to verify current rules with a contador.\n\nFormat: Portuguese, headers, code blocks for formulas, maybe one or two small tables. Not too long — I'll aim for comprehensive but scannable. I'll avoid excessive nesting.","format":"unknown","index":0}]},"native_finish_reason":"stop"}],"created":1785884727,"model":"moonshotai/kimi-k3","object":"chat.completion","service_tier":null,"system_fingerprint":null,"usage":{"completion_tokens":3794,"prompt_tokens":109,"total_tokens":3903,"completion_tokens_details":{"accepted_prediction_tokens":null,"audio_tokens":0,"reasoning_tokens":2339,"rejected_prediction_tokens":null,"image_tokens":0},"prompt_tokens_details":{"audio_tokens":0,"cached_tokens":64,"cache_write_tokens":0,"video_tokens":0},"cost":0.0570642,"is_byok":false,"cost_details":{"upstream_inference_cost":0.0570642,"upstream_inference_prompt_cost":0.0001542,"upstream_inference_completions_cost":0.05691}},"provider":"Together"}

Fórmulas de excel para contabilidade financeira no mercado d...