O HotXLS avalia nativamente as funções financeiras de títulos e tesouraria do Excel no Delphi e no C++Builder: PRICE, YIELD, DURATION, MDURATION, as seis primitivas de data de cupom, o trio de letras do tesouro, a família de desconto e as funções de depreciação francesas. Elas são calculadas dentro do próprio motor de fórmulas da biblioteca, de modo que uma pasta de trabalho que precifica uma carteira de títulos recalcula em um servidor sem Excel e sem o suplemento Analysis ToolPak em lugar nenhum à vista
Essas funções costumavam viver no Analysis ToolPak, razão pela qual ainda carregam uma reputação de serem incômodas. Desde o Excel 2007 elas fazem parte do conjunto de funções padrão e são gravadas sem adornos no formato de arquivo, sem o prefixo _xlfn. que funções genuinamente mais novas carregam. Uma pasta de trabalho que as usa é uma pasta de trabalho comum; a única questão é se o que quer que a recalcule conhece as funções
O que o motor de datas de cupom precisa acertar
Todo cálculo de título repousa sobre as mesmas primitivas: quantos dias desde o último cupom, quantos dias no período de cupom atual, quantos dias até o próximo cupom, quando esses cupons caem e quantos restam. Essas são COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD e COUPNUM, e toda função de preço e de rendimento as chama
O que as torna não triviais é o argumento de base. Cinco convenções de contagem de dias estão em jogo — 30/360 EUA, real/real, real/360, real/365 e 30/360 europeia — e elas discordam sobre questões como o que acontece quando uma data de liquidação cai no dia 31 de um mês, ou como uma data de cupom em fevereiro se comporta em um ano bissexto. Acertar uma convenção e aproximar as demais produz resultados que parecem plausíveis e estão errados por alguns pontos-base, o que em uma posição grande é dinheiro real
O HotXLS implementa as cinco em um motor aninhado autocontido de datas de cupom, compartilhado por toda função que precisa dele, de modo que PRICE e DURATION não podem discordar entre si sobre onde ficam as datas de cupom
Precificação e rendimento em uma pasta de trabalho
Nada no código de chamada é especial: escreva as fórmulas, recalcule, leia os valores. Esse é o ponto — a pasta de trabalho é o modelo, e seu aplicativo Delphi é a calculadora:
uses
lxHandleX;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Bond');
Sheet.Cells[1, 1].Value := EncodeDate(2026, 3, 15); // liquidação
Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15); // vencimento
Sheet.Cells[3, 1].Value := 0.0425; // taxa de cupom
Sheet.Cells[4, 1].Value := 0.0391; // rendimento exigido
Sheet.Cells[5, 1].Value := 100; // resgate
Sheet.Cells[6, 1].Value := 2; // frequência
Sheet.Cells[7, 1].Value := 1; // base: real/real
Sheet.Cells[9, 1].Formula := '=PRICE(A1,A2,A3,A4,A5,A6,A7)';
Sheet.Cells[10, 1].Formula := '=DURATION(A1,A2,A3,A4,A6,A7)';
Sheet.Cells[11, 1].Formula := '=MDURATION(A1,A2,A3,A4,A6,A7)';
Sheet.Cells[12, 1].Formula := '=COUPNUM(A1,A2,A6,A7)';
Book.Recalculate;
Writeln(Format('price %.6f Macaulay %.6f modified %.6f coupons %d',
[Double(Sheet.Cells[9, 1].Value), Double(Sheet.Cells[10, 1].Value),
Double(Sheet.Cells[11, 1].Value),
Integer(Sheet.Cells[12, 1].Value)]));
finally
Book.Free;
end;
end;As datas entram como valores de data reais, não como strings. Uma data em texto funciona no Excel porque o Excel a converte, e a conversão depende do idioma regional, exatamente o tipo de dependência que um recálculo do lado do servidor não deveria ter. As regras do sistema de datas que ficam por baixo são abordadas em números de série de data e o sistema 1904
Rendimento não tem forma fechada, e isso tem consequências
PRICE é um cálculo direto: desconte os fluxos de caixa, some-os. YIELD é seu inverso e não tem solução de forma fechada para um título com mais de um cupom restante, então precisa ser resolvido numericamente. O HotXLS usa um resolvedor por bisseção, que converge de forma confiável, embora não rapidamente
Duas implicações práticas decorrem disso. Yield é mensuravelmente mais caro do que price, de modo que uma revalorização de carteira que calcula rendimentos para dez mil posições custa perceptivelmente mais do que uma que calcula preços, e vale a pena saber qual dos dois seu modelo realmente precisa. E um rendimento que não pode ser delimitado — porque as entradas descrevem um título que nenhum rendimento consegue precificar no valor dado — retorna um erro em vez de um número errado, o que é o comportamento correto e o que se deve testar
Letras do tesouro são sua própria família
As letras são instrumentos de desconto sem cupons, então usam TBILLPRICE, TBILLYIELD e TBILLEQ em vez das funções de título. TBILLEQ converte uma taxa de desconto em um rendimento equivalente a título, e tem uma peculiaridade documentada que vale a pena conhecer: para vencimentos de mais de 182 dias, a conversão é quadrática em vez de linear, porque o título abrange mais de um período de meio ano
Alimentar uma letra em PRICE em vez disso, com cupom zero e uma frequência, não produz o mesmo número. A família de instrumentos de desconto — DISC, INTRATE, RECEIVED, PRICEDISC e YIELDDISC — existe precisamente porque esses instrumentos são cotados e liquidados de forma diferente
O restante do conjunto, em um só lugar
Além de títulos e letras, o mesmo lote cobre as funções que os cercam em modelos reais. PRICEMAT e YIELDMAT tratam de títulos que pagam juros no vencimento. ACCRINTM calcula os juros acumulados para eles. EFFECT e NOMINAL convertem entre taxas anuais efetiva e nominal, a conversão mais comumente feita errada à mão. CUMIPMT e CUMPRINC dão juros e principal cumulativos ao longo de um intervalo de períodos. FVSCHEDULE aplica uma série de taxas diferentes em sequência, aceitando um intervalo ou um escalar
DOLLARDE e DOLLARFR convertem entre cotações em dólar decimal e fracionária, o que importa para qualquer instrumento ainda cotado em trinta e dois avos. E AMORDEGRC e AMORLINC implementam os métodos de depreciação franceses, exigidos pelas regras contábeis francesas, não conveniências opcionais
// Uma conversão de taxa que as pessoas rotineiramente erram à mão
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)'; // nominal para efetiva
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // e de volta
// Juros cumulativos nos dois primeiros anos de um empréstimo de 25 anos
Sheet.Cells[4, 3].Formula :=
'=CUMIPMT(0.0389/12,300,340000,1,24,0)';
// Uma projeção de taxa variável: 3 por cento, depois 3,5, depois 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';Validando uma implementação que você não escreveu
Funções financeiras são a única área em que "retorna um número" não é evidência de nada. O número é plausível por construção, e um erro em uma convenção de contagem de dias desloca os resultados por uma quantidade pequena demais para notar e grande demais para aceitar
O teste que realmente funciona é diferencial. Monte uma planilha de instrumentos representativos — um título por base de contagem de dias, um que liquida no fim de um mês, um que abrange um fevereiro de ano bissexto, uma letra abaixo de 182 dias e uma acima — calcule os valores no Excel uma vez, armazene-os como resultados esperados, e verifique-os contra eles após toda atualização da biblioteca. Posições que atravessam uma data de cupom e liquidações no dia 31 são onde as implementações divergem, então inclua ambas deliberadamente
Para as próprias pastas de trabalho, as duas companheiras deste recurso são o motor de cálculo que decide o que recalcular, descrito em recálculo incremental e o grafo de dependências, e a família estatística abordada em funções de distribuição estatística. Juntas, são o que permite que um modelo financeiro rode sem supervisão em um servidor, que foi todo o motivo para implementá-las nativamente
Finanças de títulos, estatística, funções de engenharia e o motor de recálculo vêm em uma única biblioteca para Delphi e C++Builder; a lista completa de recursos está na página do componente de planilha Delphi HotXLS