O HotXLS avalia nativamente as funções financeiras de obrigações e tesouro do Excel em Delphi e C++Builder: PRICE, YIELD, DURATION, MDURATION, as seis primitivas de data de cupão, o trio de bilhetes do tesouro, a família de desconto e as funções de depreciação francesas. Calculam dentro do próprio motor de fórmulas da biblioteca, pelo que um livro que avalia uma carteira de obrigações recalcula num servidor sem Excel e sem o suplemento Analysis ToolPak à vista
Estas funções costumavam viver no Analysis ToolPak, razão pela qual ainda carregam uma reputação de serem incómodas. Desde o Excel 2007 fazem parte do conjunto de funções padrão e são escritas sem prefixo no formato de ficheiro, sem o prefixo _xlfn. que as funções genuinamente mais recentes carregam. Um livro que as utiliza é um livro comum; a única questão é saber se o que quer que o recalcule conhece as funções
O que o motor de datas de cupão tem de acertar
Todos os cálculos de obrigações assentam nas mesmas primitivas: quantos dias desde o último cupão, quantos dias no período de cupão atual, quantos dias até ao próximo cupão, quando é que esses cupões caem, e quantos restam. São elas COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD e COUPNUM, e todas as funções de preço e de rendimento as chamam
O que as torna não triviais é o argumento da base. Estão em jogo cinco convenções de contagem de dias — 30/360 EUA, real/real, real/360, real/365 e 30/360 europeia — e discordam em questões como o que acontece quando uma data de liquidação cai no dia 31 de um mês, ou como se comporta uma data de cupão de fevereiro num ano bissexto. Acertar numa convenção e aproximar as restantes produz resultados que parecem plausíveis e estão errados por alguns pontos base, o que numa posição grande é dinheiro real
O HotXLS implementa as cinco num motor de datas de cupão aninhado e autónomo, partilhado por todas as funções que dele necessitam, pelo que PRICE e DURATION nunca podem discordar entre si sobre onde estão as datas de cupão
Preço e rendimento num livro
Não há nada de especial no código que chama: escreva as fórmulas, recalcule, leia os valores. É esse o objetivo — o livro é o modelo, e a sua aplicação 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); // maturidade
Sheet.Cells[3, 1].Value := 0.0425; // taxa de cupão
Sheet.Cells[4, 1].Value := 0.0391; // rendimento exigido
Sheet.Cells[5, 1].Value := 100; // reembolso
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, e não como strings. Uma data em texto funciona no Excel porque o Excel a converte, e essa conversão depende do idioma/região, que é 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 estão por baixo estão descritas em números de série de data e o sistema de 1904
O rendimento não tem forma fechada, e isso tem consequências
PRICE é um cálculo direto: descontar os fluxos de caixa, somá-los. YIELD é o seu inverso e não tem solução em forma fechada para uma obrigação com mais de um cupão por vencer, pelo que tem de ser resolvido numericamente. O HotXLS usa um solucionador por bisseção, que converge de forma fiável, em vez de rápida
Seguem-se duas implicações práticas. O rendimento é mensuravelmente mais caro do que o preço, pelo que uma reavaliação de carteira que calcula rendimentos para dez mil posições custa visivelmente mais do que uma que calcula preços, e vale a pena saber qual dos dois o seu modelo realmente precisa. E um rendimento que não pode ser delimitado — porque os dados de entrada descrevem uma obrigação que nenhum rendimento consegue avaliar ao valor dado — devolve um erro, em vez de um número errado, o que é o comportamento correto e aquele que se deve testar
Os bilhetes do tesouro são a sua própria família
Os bilhetes são instrumentos de desconto sem cupões, pelo que usam TBILLPRICE, TBILLYIELD e TBILLEQ, em vez das funções de obrigações. TBILLEQ converte uma taxa de desconto num rendimento equivalente a obrigação, e tem uma peculiaridade documentada que vale a pena conhecer: para maturidades superiores a 182 dias, a conversão é quadrática, e não linear, porque o título abrange mais do que um período de meio ano
Introduzir um bilhete em PRICE, em vez disso, com um cupão 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 estes instrumentos são cotados e liquidados de forma diferente
O resto do conjunto, num só lugar
Para além de obrigações e bilhetes, o mesmo lote cobre as funções que as rodeiam em modelos reais. PRICEMAT e YIELDMAT tratam de títulos que pagam juros na maturidade. ACCRINTM calcula os juros acumulados para esses. EFFECT e NOMINAL convertem entre taxas anuais efetivas e nominais, que é a conversão mais frequentemente feita mal à mão. CUMIPMT e CUMPRINC dão os juros e o capital acumulados 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ólares decimais e fracionárias, 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, que são exigidos pelas regras contabilísticas francesas, e não conveniências opcionais
// Uma conversão de taxas que as pessoas costumam fazer mal à 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 acumulados nos primeiros dois 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})';Validar uma implementação que não escreveu
As funções financeiras são a única área em que "devolve um número" não é prova de nada. O número é plausível por construção, e um erro numa convenção de contagem de dias desloca os resultados por uma quantidade demasiado pequena para se notar e demasiado grande para se aceitar
O teste que realmente funciona é um teste diferencial. Construa uma folha de cálculo de instrumentos representativos — uma obrigação por base de contagem de dias, uma que liquide no fim de um mês, uma que abranja um fevereiro de ano bissexto, um bilhete com menos de 182 dias e um com mais — calcule os valores no Excel uma vez, guarde-os como resultados esperados, e valide-os contra eles a cada atualização da biblioteca. As posições que atravessam uma data de cupão e as liquidações no dia 31 são onde as implementações divergem, por isso inclua ambas deliberadamente
Quanto aos próprios livros, os dois complementos desta funcionalidade 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 corra sem supervisão num servidor, que foi toda a razão para as implementar nativamente
As finanças de obrigações, a estatística, as funções de engenharia e o motor de recálculo fazem parte da mesma biblioteca para Delphi e C++Builder; a lista completa de funcionalidades está disponível na página do componente de folha de cálculo HotXLS para Delphi