Home Products & Services Educational Downloads Risk System Financial Ratios System

CALCULUS
Calcs for Financial Market
Excel Add-in
QUICK REFERENCE

-> Manual and Help of CALCULUS.XLL

QUICK REFERENCE

1.1. Minimum Requirements
 1.2. System Tools
 1.2.1. Help
 1.2.2. Status Indicator
 1.2.3. Idiom
 1.2.4. Brazilian Yield Curve Exponential Interpolation and Extrapolation
 1.2.5. Cubic Spline for Brazilian Yield Curve
 1.2.6. Comparative chart for cubic spline and exponential interpolation
 1.2.7. Duration, Exposure and Convexity for Fixed Income Portfolios
 1.2.8. Hedge by Modified Duration for Fixed Income
 1.2.9. Projected trade volume
 1.2.10. Fare Future Value
 1.2.11. FRC - Foward Rate Contract - and FRA - Forward Rate Agreement
1.1. Minimum Requirements

  • Operation System: Windows XP, Windows 2000, Windows ME or Windows 98
    • This system has no been tested to Windows 95, but may not face troubles.
  • Microsoft Excel with version: Excel 2003, Excel XP or Excel 2000
  • Memory: Minimum of 128 MB
  • Hard Disk space: 10 MB
  • Browser Internet Explorer or Netscape installed 1

1 Allows access to manual and help files available in the Internet.
Top

1.2. System Tools
1.2.1. Help

Access:

  • Menu - Calculus | Help on line
Description: Runs browser Internet Explorer or Netscape in Élin Duxus web page for system manual and help for functions.
Top

1.2.2. Status Indicator

Access:

  • Menu - Calculus s | Status
Description: Allows user to verify systems available updates as well as signature/license status. If signature/license expires, all functionalities will be over and will return #N/A.
  • - Running normal
  • - License or trial is about to expire
  • - System blocked – signature or license need to be revalidated.

Top

1.2.3. Idiom

Access:

  • Menu - Calculus | Idiom
Description: Idiom setting.
Includes options for Portuguese (Brazil) or English (USA).
After change the idiom, it is necessary to restart Microsoft Excel.
Available in version 1.0.1 and superiors.
Top

1.2.4. Brazilian Yield Curve Exponential Interpolation and Extrapolation (base 252)

Access:

  • Menu - Inserir | Função | Calculus
  • - Toolbar Standard | Calculus
Description: Returns interest rate (base ACT/252) from Exponential Interpolation or extrapolation from yield curve informed.
Minimum of 3 points (time position) for interpolation.
For each time position, only one yield must be informed.

Chamada: CC.INTERPOLEX (Workdays, Yield Curve, Years Flat)

Argument

Type

Description

Workdays

integer

Workdays for which interest rate is calculated.

Yield Curve

range

Range (n rows and 2 columns) describing Brazilian yield curve - rates base 252 and workdays.

Years Flat

double

Optional. Number of years after the last rate and time informed where yield curve flats. Default value is 0.


Usage example:

  • Workdays: 120
  • Years Flat: 1,0
  • Yield Curve: A2:B11 (11 points out of sequence)
= CC.INTERPOLEX( 120, A2:B11, 1.0)
Topo

1.2.5. Cubic Spline for Brazilian Yield Curve (base 252)

Access:

  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus
Description: . Returns interest rate (base ACT/252) from natural cubic spline interpolation and from yield curve informed.
For each time position, only one yield must be informed.

Call: CC.CSPLINE (Workdays, Yield Curve)

Argument

Type

Description

Workdays

integer

Workdays for which interest rate is calculated.

Yield Curve

range

Range (n rows and 2 columns) describing Brazilian yield curve - rates base 252 and workdays.


Usage example:

  • Workdays: 120
  • Yield curve: A2:B11 (11 points out of sequence)
= CC.CSPLINE( 120, A2:B11)
Topo

1.2.6. Comparative chart for cubic spline and exponential interpolation (Brazilian yield curve - base 252)

Access:

  • Menu - Calculus | CSpline x Exp
  • - Toolbar Calculus
Description: . Build a comparative chart for cubic spline and exponential interpolation.
Data range must be selected in advance.

Comparative
Comparative chart screen

Top

1.2.7. Duration, Exposure and Convexity for Fixed Income Portfolios

Access:
  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus
Description: Returns Macauley duration, final exposure, convexity and modified duration of a portfolio and a given yield curve.
Long and short positions are accepted. Uses modified duration concepd for hedge or exposure calculations.

Call: CC.DURATION ( Yield Curve, Portfolio)

Argument

Type

Description

Yield Curve

range

Range (n rows and 2 columns) describing yield curve - base 252 yield and workdays.

Portfolio

range

Range (n rows and 2 columns) with payment face value and workdays untill each payment.



Usage example:

  • Yield curve: A2:B11 (11 points out of sequence)
  • Portfolio: range C2:D5
= {CC.DURATION( A2:B11, C2:D5)}
Topo

1.2.8. Hedge by Modified Duration for Fixed Income

Access:
  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus
Description: Returns number of BMF DI1 future contract to hedge a portfolio.

Allows portfolio duration changes by using targets.

Call: CC.HEDGE ( BMF DI1 Yield, Workdays, Face Value, Portfolio Yield, Settlement, Target Duration, Target Yield, Beta)

Argument

Type

Description

BMF DI1 Yield

double

BMF DI1 annual yield (base 252) used for hedging.

Workdays

integer

Workdays till BMF DI1 future settlement - expire date - used for hedging.

Face Value

long integer

Face value of portfolio position to be hedged. Mark positive values for long positions and negative value for short positions.

Portfolio Yield

double

Equivalent annual yield (base 252) for portfolio duration or portfolio settlement.

Settlement

integer

Workdays till portfolio (hedged) settlement.

Target Duration

integer

Optional. Workdays of final duration. Default is 0, meaning total hedge.

Target Yield

double

Optional. Equivalent annual yield (base 252) for target duration. Must be informed if Target Duration is used.

Beta

double

Optional. Relationship between BMF DI1 future contracts and portfolio bonds. Default value is 1.



Usage example:

  • BMF DI1 hedge contract yield 18.5%
  • Settlement 80 (workdays)
  • Face value of long position 100,000,000.00
  • Position yield 19.5% (base 252)
  • Position duration: 100 (workdays)
= CC.HEDGE( 0.185, 80, 100000000, 0.195, 100)
Topo

1.2.9. Projected trade volume

Access:
  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus
Descrição: Returns projected volume for an asset using trade interval.

Call: CC.VOLPROJ ( Time Now, Volume Now, Trade Start, Trade End, Lunch Break, Noon Restart)

Argument

Type

Description

Hora Atual

time

Time now using hh:mm:ss (24H) format that represents present volume.

Volume Atual

long integer

Asset trade volume till now (stocks, futures, forwards etc.).

Início do Pregão

time

Time in hh:mm:ss (24H) format showing first moment of asset trade.

Fim do Pregão

time

Time in hh:mm:ss (24H) format showing first moment of asset trade.

Fim da Manhã

time

Optional. Time in hh:mm:ss (24H) format showing morning break for lunch if volume stops at that time.

Início da Tarde

time

Optional. Time in hh:mm:ss (24H) format showing re-start after lunch time if volume stops at that time.



Usage example:

  • Time now: 12:00:00 hs
  • Volume: 200.000.000
  • Start time: 10:00:00 hs
  • End of trade time: 17:00:00 hs
  • Lunch break: 13:00:00 hs
  • Restart after break: 14:00:00 hs
= CC.VOLPROJ( 12:00:00, 200000000, 10:00:00, 17:00:00, 13:00:00, 14:00:00)


Topo

1.2.10. Fare Future Value

Access:
  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus
Description: Returns fare value for future contract considering time value of money
Uses exponential interpolation for yield curve.

Call: CC.INDJUSTO ( Spot Index, Workdays, Yield Curve)

Argument

Type

Description

Spot Index

long integer

Spot index for an asset - Stock index for example.

Workdays

integer

Workdays till settlement of the future contract.

Yield Curve

range

Range (n rows and 2 columns) describing yield curve - base 252 and workdays.



Usage example:

  • IBOVESPA spot index: 10,000 points
  • IBOVESPA future expiration: 25 (workdays)
  • Yield curve: A2:B11 (11 points out of sequence)
= CC.INDJUSTO( 10000, 25, A2:B11)


Access 2:

  • Menu - Inset | Function | Calculus
  • - Toolbar Standard | Calculus

Description: Returns interest rate (annual base 252) from future contract considering the spot index.

Call: : CC.INDTAXA ( Future Contract, Workdays, Spot Index)

Argument

Type

Description

Future Contract

long integer

Future contract quotation of the chosen index.

Workdays

integer

Workdays till settlement of the future contract.

Spot Index

long integer

Spot index.



Usage example:

  • IBOVESPA future contract: 10,210 points
  • Workdays till settlement of future contract: 25
  • Spot index IBOVESPA: 10,000 points
= CC.INDTAXA( 10210, 25, 10000)


Access 3:

  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus

Description: Returns the difference between fare future quotation and real (traded) future quotation. Returns negative for undervalued and positive for overvalued.

Call: : CC.INDDIF ( Spot Index, Future Contract, Workdays, Yield Curve)

Argument

Type

Description

Spot Index

long integer

Spot index for an asset - Stock index for example.

Future Contract

long integer

Spot index for an asset - Stock index for example.

Workdays

integer

Workdays till settlement of the future contract.

Yield Curve

range

Range (n rows and 2 columns) describing yield curve - base 252 and workdays.



Usage example:

  • Spot index IBOVESPA: 10,000 points
  • IBOVESPA future contract: 10,210 points
  • Workdays till future settlement: 25
  • Yield curve: A2:B11 (11 points out of sequence)
= CC.INDDIF( 10000, 10210, 25, A2:B11)


Top

1.2.11. FRC - Foward Rate Contract - and FRA - Forward Rate Agreement

Access:
  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus
Description: Returns linear interest rate ACT/360 for BMF FRC contract from yield curve and R$/U$ NDF.

Call: CC.CUPOM_FRA (Short Yield, Short Workdays, Short Settlement, Long Yield, Long Workdays, Long Settlement, Roll Over, Short Dollar)

Argument

Type

Description

Short Yield

double

Annual yield (base 252) for short side of BMF FRC contract.

Short Workdays

integer

Workdays till settlement of short side of BMF FRC contract. Must be greater than 0.

Short Settlement

long integer

Settlement date (day/month/year) of short side of BMF FRC contract.

Long Yield

double

Annual yield (base 252) for long side of BMF FRC contract.

Long Workdays

integer

Workdays till settlement of long side of BMF FRC contract. Must be greater than 0.

Long Settlement

long integer

Settlement date (day/month/year) of long side of BMF FRC contract. Must be greater than Short Settlement.

Roll Over

double

Points of R$/U$ NDF or annual devaluation rate R$/U$ (ACT/360) to be applied for each new monthly NDF.

Short Dollar

double

Optional. R$/U$ NDF quotation for the short side. Mark 0 (default) for points of devaluation and 1 for annual rate of devaluation.



Usage example:

  • Short Yield 18.75% a.a. (base 252) – shortest BMF DI1 contract
  • Short workdays: 18
  • Short settlement: 01/08/02 (dd/mm/yy)
  • Long yield: 19.55% a.a. (base 252)
  • Long workdays: 40
  • Long settlement: 02/09/02 (dd/mm/yy)
  • Roll over in ppoints: 18
  • Shortest R$/U$ NDF quotation: 2,892.00
= CC.CUPOM_FRA (0.1875, 18, 01/08/02, 0.1945, 40, 02/09/02, 18, 2892)


Access 2:

  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus

Description: Retorna o número de contratos a serem registrados na BM&F na ponta curta de um contrato de FRC considerando um cliente apenas.

Call: CC.CUPOM_FRACURTA ( FRC Yield, Contracts, Long Settlement, Short Settlement)

Argument

Type

Description

FRC Yield

double

Annual yield (ACT/360) of traded BMF FRC contract.

Contracts

long integer

Number of BMF FRC contracts traded (long side).

Long Settlement

long integer

Settlement date (day/month/year) of long side of BMF FRC contract. Must be greater than Short Settlement.

Short Settlement

long integer

Settlement date (day/month/year) of short side of BMF FRC contract.



Usage example:

  • FRC yield: 11.20% (ACT/360)
  • Number of contracts: 100
  • Long settlement: 01/03/02 (dd/mm/yy)
  • Short settlement: 01/03/01 (dd/mm/yy)
= CC.CUPOM_FRACURTA ( 0.112, 100, 01/03/02, 01/03/01)


Access 3:

  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus

Description: Retorna a rolagem em pontos para o dólar futuro implícita na cotação do FRA de cupom cambial (cupom limpo).

Returns the roll over points for R$/U$ NDF accounted in BMF FRC contract traded.

Call: CC.CUPOM_ROLIMP ( FRC Yield, Long Settlement, Long Workdays, Short Yield, Workdays Short, Short Dollar, Short Settlement, Long Yield)

Argument

Type

Description

FRC Yield

double

Annual yield (ACT/360) of BMF FRC contract traded.

Long Settlementa

long integer

Settlement date (day/month/year) of long side of BMF FRC contract. Must be greater than Short Settlement.

Long Workdays

long integer

Workdays till BMF FRC contract settlement. Must be greater than Workdays Short.

Short Yield

double

Annual yield (base 252) for short side of BMF FRC contract. It is the same as BMF DI1 for short side of BMF FRC contract.

Workdays Short

long integer

Workdays till settlement of long side of BMF FRC contract. Must be greater than 0.

Short Dollar

double

Settlement date (day/month/year) of long side of BMF FRC contract. Must be greater than Short Settlement.

Short Settlement

long integer

Points of R$/U$ NDF or annual devaluation rate R$/U$ (ACT/360) to be applied for each new monthly NDF.

Long Yield

double

Optional. R$/U$ NDF quotation for the short side. Mark 0 (default) for points of devaluation and 1 for annual rate of devaluation.

Usage example:

  • FRC yield: 11.20% (ACT/360)
  • Long settlement: 02/09/02 (dd/mm/yy)
  • Workdays long: 40
  • Short yield: 18.75% a.a. (base 252) – shortest BMF DI1 quotation
  • workdays short: 18
  • Shortest R$/U$ NDF quotation: 2,892.00
  • Long settlement: 01/08/02 (dd/mm/yy)
  • Long yield: 19.45% a.a. (base 252)
= CC.CUPOM_ROLIMP (0.112, 02/09/02, 40, 0.1875, 18, 2892, 01/08/02, 0.1945)


Access 4:
  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus

Description: Returns annual devaluation rate (ACT/360) for R$/U$ NDF accounted in BMF FRC contract.

Call: CC.CUPOM_DEVIMP ( FRC Yield, Long Settlement, Workdays Long, Short Yield, Workdays Short, Short Settlement, Long Yield)

Argument

Type

Description

FRC Yield

double

Annual yield (ACT/360) of BMF FRC contract traded.

Long Settlement

long integer

Settlement date (day/month/year) of long side of BMF FRC contract. Must be greater than Short Settlement.

Workdays Long

long integer

Workdays till BMF FRC contract settlement. Must be greater than Workdays Short.

Short Yield

double

Annual yield (base 252) for short side of BMF FRC contract. It is the same as BMF DI1 for short side of BMF FRC contract.

Workdays Short

long integer

Workdays till settlement of short side of BMF FRC contract. Must be greater than 0.

Short Settlement

long integer

Settlement date (day/month/year) of short side of BMF FRC contract.

Long Yield

double

Annual yield (base 252) for long side of BMF FRC contract.



Usage example:

  • FRC yield: 11.20% (ACT/360)
  • Long settlement: 02/09/02 (dd/mm/yy)
  • Workdays long: 40
  • Short yield: 18.75% a.a. (base 252) – shortest BMF DI1 quotation
  • workdays short: 18
  • Long settlement: 01/08/02 (dd/mm/yy)
  • Long yield: 19.45% a.a. (base 252)
= CC.CUPOM_DESVIMP ( 0.112, 02/09/02, 40, 0.1875, 18, 01/08/02, 0.1945)


Access 5:

  • Menu - Insert | Function | Calculus
  • - Toolbar Standard | Calculus

Description: Returns annual yield (ACT/360) of dirty BMF FRC contract according yield curve, R$/U$ roll over and PTAX.

Call: CC.CUPOM_SUJO ( PTAX Yesterday, Dollar 2, Dollar 2 Settlement, Today, Long Yield, Workdays Long, Long Settlement, Roll Over, Type)

Argument

Type

Description

PTAX Yesterday

double

Commercial dollar - Ask - for the day before.

Dollar 2

double

Commercial dollar for present day (PTAX - R$/U$ - Ask) or R$/U$ NDF (R$/1000 U$).

Dollar 2 Settlement

long integer

Settlement date (day/month/year) of Dollar 2. For PTAX, mark present day. For NDF's, mark contract settlement.

Today

long integer

Present day (day/month/year).

Long Yield

double

Annual yield (base 252) for long side of BMF FRC contract.

Workdays Long

integer

Workdays till settlement of long side of BMR FRC contract. Must be greater than 0.

Long Settlement

long integer

Settlement date (day/month/year) of long side of BMF FRC contract. Must be greater than Dollar 2 Settlement.

Roll Over

double

Points of R$/U$ NDF or annual devaluation rate R$/U$ (ACT/360) to be applied for each new monthly NDF.

Type

boolean

Optional. Mark 0 (default) for roll over in points or 1 for rate of devaluation ACT/360.



Usage example:

  • PTAX yesterday: 2.870 R$/US$
  • PTAX today: 2.892 R$/US$
  • Today: 01/08/02 (dd/mm/yy)
  • Long yield: 21.00% a.a. (base 252)
  • Workdays long: 44
  • Long settlement: 01/11/02 (dd/mm/yy)
  • Roll over in points: 20

= CC.CUPOM_SUJO (2.87, 2.892, 01/08/02, 01/08/02, 0.21, 44, 01/11/02, 20)
Top

Home | Company information | Tools | CED | Forum | News | Links | Clients | Cases | Templates | Careers | Contact
©All rights reserved - Élin Duxus - Brazil - 2002 - 2008


Excel course, formulas, macros, sheets - Finance Mathematics, HP 12C - Options pricing and course, options Greeks analysis - VaR (Value at Risk) course - Equity, stocks, ADR and international arbitrage - Certified Professionals - Excel add-in for finance - Customized Excel add-in - System and application development, technology - C/C++, VBA, macros, models - Communication in Excel, RTD - Financial modeling - Financial consulting, corporate finance - Cash Flow Analysis - Brazilian PPP (Public-Private Partnership) - Excel Add-in Downloads - Templates - Duxus Risk System Élin Duxus Sitemap
Excel course, formulas, macros, sheets - Finance Mathematics, HP 12C - Options pricing and course, options Greeks analysis - VaR (Value at Risk) course - Equity, stocks, ADR and international arbitrage - Certified Professionals - Excel add-in for finance - Customized Excel add-in - System and application development, technology - C/C++, VBA, macros, models - ARCH, GARCH, TGARCH, ICARG - Communication in Excel, RTD - Financial modeling - Financial consulting, corporate finance - Cash Flow Analysis - Excel Add-in Downloads - Templates - Duxus Risk System - Value at Risk - VaR - ALM - Asset Liability Simulation