--- id: financeiros/indexadores-oficiais industry: Financial services usage: specific effort: low grain: Business day x index (daily curves); month x index (monthly series); day x currency (FX) --- # Official benchmark rates CDI, Selic, IPCA, IGP-M, Focus expectations and PTAX read straight from the Central Bank of Brazil, with business days and actual and forecast values flagged, ready to adjust amounts, accrue interest and convert currency without spreadsheets. ## Executive summary **For:** Lending companies, funding platforms, asset managers and investment offices that calculate interest, returns or adjustments tied to an index. **What it solves** - Rates come from the official source every day, with no manual file that stops being updated. - The accumulated rate between two dates is always calculated the same way, with business days and holidays. - Forecast and actual live in the same series, flagged, and the actual replaces the forecast when it is published. **Why it matters:** Floating-rate interest, dividends, debt and inflation adjustment depend on an index on an exact date. When each spreadsheet uses its own rate, day-count convention and reference date, the same contract gives different values in finance, in sales and in the investor report. A single rate dimension is what makes it possible to project interest and check what was charged. **Business questions it answers** - What is the accumulated CDI between the contribution date and today? - What Selic rate does the Focus survey forecast for December? - How much does this contract yield at 100% of CDI and at CDI plus 2% a year until maturity? - Which selling PTAX rate was used to convert this month's dollar commissions? - What is the 12-month accumulated IPCA to adjust this contract? - Did the FX series stop updating on any business day? **What you need** - REST sources for the Central Bank's SGS, Focus and PTAX set up in Nekt - A calendar table with national holidays and a business-day flag - For each contract or product: index, percentage or spread, day-count convention and reference date **What you get** - Daily curves per business day with daily rate, annual rate, status and source - Monthly inflation and expectations series, with actual and forecast - Closing FX per day, ready to join - An AI agent that answers about rates with the right convention ## Detailed ### The problem The CDI rate comes per day, the Focus Selic comes per year and by reference year, IPCA comes as the month's change and PTAX has several bulletins a day. Whoever adds daily rates, mixes a 252 basis with 365 or takes the FX rate from the wrong hour ends up with interest that does not match the statement. And when the source is a manually downloaded file, it stops being updated on some random day and the calculations keep running on the last value. ### Business rules 1. **Official source via API, never a manual file.** Read the series straight from the Central Bank's SGS and Olinda, loaded daily. An FX file kept in a folder stopped being fed and the calculations ran on the last value for months. Long series may come partitioned by period (by decade, for example); unify them in one table without duplicating the boundary. (Avoid: Copying the rate from the website into a shared spreadsheet.) 2. **One row per business day, with its own calendar.** Daily curves have one row per business day, from the calendar table with fixed and movable national holidays (Carnival, Good Friday and Corpus Christi computed from Easter). A business day with no publication inside the actual period repeats the last published value, flagged as filled. Weekends and holidays have no row. (Avoid: Generating one row per calendar day, or leaving a gap on a business day with no publication.) 3. **Daily and annual rate on a 252 basis.** Daily CDI and Selic from SGS come as a percentage per day. Annual rate = (1 + daily rate)^252 - 1; daily rate = (1 + annual rate)^(1/252) - 1. Whenever an annual rate is adjusted (for example, with a spread), recalculate the daily rate after the adjustment, never before. (Avoid: Dividing the annual rate by 252 or by 365.) 4. **Accumulated is a product, never a sum.** The factor between two dates is the product of (1 + daily rate) on each business day in the interval, start date included and end date excluded, or the contract's convention. A percentage of CDI applies the percentage to the daily rate before the product; CDI plus spread compounds the annual spread converted to daily. Monthly inflation accumulates as the product of (1 + the month's change). (Avoid: Adding daily or monthly rates, or using the period average.) 5. **Day-count convention declared per product.** Floating rates on CDI and Selic use business days over 252. Some systems calculate fixed-rate instruments in calendar days over 365. The same bond gives different values under the two conventions. Store the convention with the product and use the same one in every report. (Avoid: Applying calendar days to a business-day contract, or the other way around.) 6. **Actual takes priority over forecast.** The consolidated series combines actual and forecast with status and source on every row. The Selic forecast comes from the latest Focus median per reference year, turned into a monthly path in steps of 0.50 or 0.25 percentage points in Copom (monetary policy committee) meeting months; after the last Focus year, the last level is held constant. Forecast CDI = forecast Selic minus 0.10 percentage points a year. When the actual comes out, it replaces the forecast for that date. (Avoid: Keeping forecast and actual in loose tables, or leaving the forecast after the actual is published.) 7. **The reference date is a contract rule.** Each contract states which index value to use: the previous month's Selic, the observation on the 20th (or the closest one) for a monthly cycle, the IPCA for the accrual month or the 12-month accumulated value. Inflation adjustment and interest may use different indexes on the same item, each with its own accrual period. Store the rule in the contract and join on the reference date, not on the calculation date. (Avoid: Using the index for the day the calculation ran.) 8. **Spread, percentage and cap after the index.** A floating rate is index plus spread or a percentage of the index, and some contracts have a ceiling: apply the lower of the calculated rate and the cap. Do not confuse CDI plus spread with a percentage of CDI. In brokerage reports, the field that tells the two apart is often correct only for CDI and repeats the index for the others. (Avoid: Treating 110% of CDI and CDI + 1.10% as the same rate.) 9. **FX: closing selling PTAX, one value per day.** From PTAX, use the selling rate of the closing bulletin, the last of the day. Deduplicate by day before any join, so rows are not multiplied. Convert each amount at the rate for the date of the fact and keep the original amount, currency, rate and rate date. When the source already carries the rate it used (dollar commissions at the closing rate), use that one. (Avoid: Joining on every bulletin of the day, or converting the whole month at today's rate.) ### Series and where to find them Central Bank of Brazil, with the series code and the unit it arrives in. - **SGS 12, % per day, per business day** → Daily CDI (`sgs 12`) - **SGS 4391, % in the month** → CDI accumulated in the month (`sgs 4391`) - **SGS 11, % per day, annualize with 252** → Daily Selic (`sgs 11`) - **SGS 4390, % in the month** → Selic accumulated in the month (`sgs 4390`) - **SGS 433, % change in the month** → IPCA (consumer inflation) (`sgs 433`) - **SGS 189, % change in the month** → IGP-M (general price index) (`sgs 189`) - **Olinda Expectations, annual median per reference year** → Focus survey (`ExpectativasMercadoAnuais`) - **Olinda PTAX, cotacaoVenda from the last bulletin of the day** → Dollar PTAX (`CotacaoDolarPeriodo`) ### Definitions | Metric | Definition | |---|---| | Daily rate | daily rate published on SGS as a decimal, or (1 + annual rate)^(1/252) - 1 when the source is annual | | Annual rate | (1 + daily rate)^252 - 1, per business day | | Accumulated factor | product of (1 + daily rate) over the business days between the start and end dates, by the contract's convention | | Percentage of CDI | accumulated factor with (1 + percentage x daily CDI) on each business day | | CDI plus spread | accumulated factor with (1 + daily CDI) x (1 + daily spread) on each business day, with the annual spread converted on a 252 basis | | Month's Selic (monthly cycle) | annualized Selic at the observation on the 20th or the closest one, actual when available, forecast when not | | 12-month accumulated inflation | product of (1 + monthly change) over the 12 accrual months before the reference, minus 1 | | Day's PTAX | selling rate of the last closing bulletin published that day | ### Pitfalls - **Stalled FX file.** The rate came from a file in a shared folder that stopped being updated. The average dollar price kept being calculated at a stale rate until someone noticed. How to avoid: Switch to the official API and alert on business days without a rate. - **Rows multiplied in the join.** The FX table had more than one rate per day, and joining it to the orders duplicated positions. How to avoid: One rate per day before the join. - **Floating rate without an index.** A fixed-income view calculated redemption value only for fixed-rate bonds and left floating-rate ones empty, because Selic was not in the base. How to avoid: Join floating-rate bonds to the index's daily curve. - **A query that works and a transformation that fails.** A query validated in the editor failed when it became a transformation, because the execution engine was different and handled date and array functions another way. How to avoid: Validate on the same engine the transformation will run on. - **Horizon too short.** Debt contracts mature up to ten years out, beyond the last year covered by the Focus survey. Without a rule for that stretch, the interest engine has no rate at the end of the contract. How to avoid: Build the curve to the latest maturity, holding the last Focus level constant. ### Acceptance test - [ ] The daily curve has exactly one row per calendar business day in the period, and none on weekends or holidays. - [ ] The CDI accumulated over a closed month from the daily curve matches the SGS 4391 series for the same month (difference below 0.01 percentage point). - [ ] For every date with a published actual, the consolidated series carries the actual status. - [ ] The FX table has at most one rate per day, and every business day in the last 30 days has a rate or an alert. - [ ] Running the load again does not change any actual value already stored. ### Context document ```text Official benchmark rates - Source: Central Bank of Brazil API. Daily CDI SGS 12, monthly CDI SGS 4391, daily Selic SGS 11, monthly Selic SGS 4390, IPCA SGS 433, IGP-M SGS 189, Focus in Olinda Expectations, PTAX in Olinda. - Daily curves: one row per business day from the calendar table. A business day with no publication repeats the last value, flagged. - Annual rate = (1 + daily rate)^252 - 1. Daily rate = (1 + annual rate)^(1/252) - 1. - Accumulated between dates = product of (1 + daily rate) over the business days. Never add rates or take an average. - Percentage of CDI: apply the percentage to the daily rate. CDI plus spread: compound the spread converted to daily. Cap: the lower of the calculated rate and the ceiling. - Day-count convention per product: >. - Consolidated series: actual takes priority; forecast comes from Focus in steps in Copom months. Forecast CDI = forecast Selic - 0.10 p.p. - Reference date: the contract's (e.g. previous month's Selic, observation on the 20th). Never the calculation date. - FX: selling PTAX from the last bulletin of the day, one rate per day, by the date of the fact. Keep original amount, rate and rate date. - Always say whether the number is actual or forecast and the date of the latest publication. Client exceptions: - : product uses from the of the previous month, capped at % a year. - : dollar commissions already arrive with the broker's closing rate; do not convert again. ``` ### Variations - **Inflation adjustment of credits:** Items adjusted by an inflation index up to a date and with Selic interest afterwards, each part with its own accrual period and accumulated value. - **Dollar commissions and positions:** International market revenue converted at the closing rate for the date, with the dollar amount preserved. - **Contract adjustments:** Rent, tuition or installments adjusted by 12-month accumulated IPCA or IGP-M on the contract anniversary.