¿Cuál es el algoritmo detrás del ACCRINT de Excel?

Question

Esta pregunta fue originalmente publicada en Stackoverflow:

Como parte del proyecto Formula.js, estoy intentando reimplantar la función ACCRINT de Excel (en JavaScript, pero el lenguaje no debería importar). He estado tratando de encontrar una descripción adecuada de cómo se supone que funciona (especialmente con respecto a la primer_interés parámetro), pero no pude encontrar nada.

Curiosamente, Excel, las hojas de cálculo de Google, Apple Numbers, Gnumeric y OpenOffice no están de acuerdo en la forma de implementarlo, aunque las tres versiones principales de Excel (Win, Mac, Web) parecen estar de acuerdo entre sí. Se puede encontrar algo más de contexto en este entrada en el blog .

Se pueden encontrar docenas de casos de pruebas y mi implementación actual (defectuosa) aquí .

¡Cualquier ayuda (código, pseudocódigo o descripción) sería muy apreciada!

ACTUALIZACIÓN: para ser claros, la cuestión es no relacionado con la convención de conteo de días, que implementamos utilizando la pseudocódigo para YEARFRAC que a su vez fue validado por más de 32 millones de pruebas, cubriendo las cinco opciones básicas. La cuestión viene de la first_interest que nadie parece entender realmente. Hasta donde podemos decir, este parámetro es simplemente ignorado por muchas hojas de cálculo alternativas, incluyendo OpenOffice (está comentado en el código fuente). Y este parámetro realmente se comporta de maneras extrañas. Si usas Excel y cambias su valor, verás que cambiará los resultados dados por la función ACCRINT, pero de formas que parecen caóticas. Intenta cambiar el first_interest ...en un siglo completo, y verá que el interés acumulado cambia, pero no por mucho. Realmente no puedo darle sentido a eso. Si alguien puede, soy todo oídos, porque ACCRINT debe ser usado para calcular el interés de billones o trillones de dólares cada año, y encuentro fascinante que nadie fuera de unas pocas personas en Microsoft parece saber cómo se supone que funciona realmente...

Answer 1

Echa un vistazo a las funciones financieras de Excel para .Net, parece que hay un código fuente disponible:

http://archive.msdn.microsoft.com/FinancialFunctions

Answer 2

La fórmula para calcular los intereses devengados utilizada en Excel es 100% correcta (pero la convención de conteo de días no lo es). Lo que no es correcto y no está suficientemente contabilizado es la convención de recuento de días exactos que debe ser usado para el activo específico sobre el que intentas valorar sus intereses acumulados.

Aquí hay un par de puntos que pueden causar sus errores:

• Le recomiendo encarecidamente que lo que haga en cualquier lenguaje, ya sea compilado o escrito, es implementar Enums para su declaración de cambio para la convención de conteo de días para evitar ambigüedades o errores por parte de sus futuros usuarios; No use números enteros o cadenas porque es una gran fuente de errores.

• En su enlace, ¿cómo derivó los valores "requeridos" con los que mide si su cálculo fue correcto o incorrecto?

• ¿Implementó los horarios de los días festivos? ¿Ha tenido en cuenta que esos días festivos son potencialmente diferentes para cada mercado, incluso a veces diferentes para diferentes intercambios en el mismo mercado? No veo ninguna explicación de los días festivos en su código fuente. ¿Es ese el problema? El por qué es importante prestar atención a esto se hace evidente cuando comprueba el siguiente enlace:

• http://en.wikipedia.org/wiki/Date_rolling

Answer 3

La fórmula aplicada por ACCRINT está documentada en office.microsoft.com

Según esta página es

$ACCRINT = par \times \frac{rate}{frequency} \times \sum_{j=1}^{NC} \frac{A_j}{NL_j}$

donde

$A_j$ = número de días devengados para el i-ésimo período de cuasicuotas dentro del período impar.

$NC$ = número de periodos de cuasicuotas que encajan en el periodo impar. Si este número contiene una fracción, se eleva al siguiente número entero.

$NL_i$ = duración normal en días del iésimo período de cuasicuotas dentro del período impar.

Supongo que la mayor parte de la "lógica" en la función es la implementación de las diversas convenciones de conteo de días.

He implementado un montón de convenciones de recuento de días estándar (así como el YEARFRAC no estándar de Excel para acto/acto) en http://finmath.net/topics/daycountingandschedules/ - ver también http://www.christian-fries.de/blog/files/2013-yearfrac.html

Answer 4

El estándar para las funciones de bonos (como ACCRINT) es SIFMA (Securities Information and Financial Markets Association). Han publicado un par de libros (Standard Securities Calculation Methods en 2 volúmenes, recientemente actualizados). Esta es la fuente de las fórmulas que Microsoft documenta en la ayuda para las funciones de bonos en algunas versiones de Excel. El software recomendado por la SIFMA lo publica una empresa llamada TIPS (cuyo presidente desde hace mucho tiempo es el autor de los libros mencionados). Existe una calculadora en línea para calcular casi todas las funciones de los bonos (más de las que admite Microsoft); busque "TIPS calculator".

En la mayoría de las funciones de los bonos, Microsoft utiliza las 6 funciones de los cupones por días (todas empiezan por COUP...). Lamentablemente, la versión utilizada en las funciones de bonos no siempre coincide con la versión independiente, por lo que la creación de la función (mediante la fórmula SIFMA) en una hoja de cálculo de Excel para PRICE o YIELD no siempre coincidirá con el resultado de Excel para esta función. Además, ACCRINT no utiliza la misma definición que la calculadora TIPS (o los libros): TIPS calcula el interés acumulado sólo dentro del periodo de cupón en el que se encuentra la fecha de liquidación, y no es lo mismo que los valores que proporciona Microsoft. Además, incluso cuando se encuentra en el primer periodo de cupón, la respuesta difiere entre Excel y la calculadora en muchos casos. En parte, esto se debe a que Excel no sigue la norma para determinar las fechas de los cupones (véase el Vol. II, p. 7, edición de 2013): requiere la fecha de vencimiento o la última fecha de cupón regular, que no es un argumento para ACCRINT.

Las primeras versiones de las funciones de enlace de Excel (a finales de los 90) tenían muchos errores. En general, Microsoft los ha ido eliminando, de modo que hay muchos menos, PERO ACCRINT y ODDFPRICE siguen teniendo problemas importantes. Estas conclusiones se basan en más de 100 millones de pruebas realizadas sobre el código en un clon de MS utilizando la calculadora TIPS para probar una muestra de las diferencias.