SQL中的XIRR计算
SQL中的XIRR计算
提问于 2014-01-07 20:55:25
我正在努力寻找一种用SQL实现excels XIRR计算的好方法。
我在网上找到了以下函数：
CREATE TYPE dbo.MyXirrTable AS TABLE
(
theValue DECIMAL(19, 9) NOT NULL,
theDate DATETIME NOT NULL
)
GO
CREATE FUNCTION dbo.XIRR
(
@Sample MyXirrTable READONLY,
@Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
DECLARE @LastRate DECIMAL(19, 9),
@RateStep DECIMAL(19, 9) = 0.1,
@Residual DECIMAL(19, 9) = 10,
@LastResidual DECIMAL(19, 9) = 1,
@i TINYINT = 0
IF @Rate IS NULL
SET @Rate = 0.1
SET @LastRate = @Rate
WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.00000001
BEGIN
SELECT @LastResidual = @Residual,
@Residual = 0
SELECT @Residual = @Residual + theValue / POWER(1 + @Rate, theDelta / 365.0E)
FROM (
SELECT theValue,
DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta
FROM @Sample
) AS d
SET @LastRate = @Rate
If @Residual >= 0
SET @Rate += @RateStep
ELSE
SELECT @RateStep /= 2,
@Rate -= @RateStep
SET @i += 1
END
RETURN @LastRate
END
GO(摘自here)
在测试此功能后，取得了很大的成功(结果与excel匹配)。我已经意识到，它似乎不适用于一组将导致负XIRR的事务。
我不完全理解算法的内部原理，并尝试过调试，但没有太多的运气。
下面是一个失败的测试用例：
DECLARE @Test MyXirrTable
INSERT @Test
VALUES (-4471762.56680002, '2008-11-13 00:00:00.000'),
(+2607759.77, '2008-11-14 00:00:00.000'),
(+12263.33, '2008-11-25 00:00:00.000'),
(+1658.89, '2008-11-25 00:00:00.000'),
(+1834423.33, '2008-12-04 00:00:00.000'),
(-0.000245418674579822,'2013-11-14 00:00:00.000')
SELECT dbo.XIRR(@Test, 0.1) 计算值= -0.000000001
期望值= -0.12879
有没有人比我更了解金融算法，有没有解决这个测试用例或者SQL的更好的方法？
回答 1
Stack Overflow用户
发布于 2014-10-30 17:24:27
这与excel值有关：
CREATE TYPE [dbo].[XIRRTable] AS TABLE(
[Value] [decimal](19, 9) NOT NULL,
[Date] [datetime] NOT NULL
)
GO
CREATE FUNCTION [dbo].[CalcXIRR]
(
@Sample XIRRTable READONLY,
@Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
DECLARE @X DECIMAL(19, 9) = 0.0,
@X0 DECIMAL(19, 9) = 0.1,
@f DECIMAL(19, 9) = 0.0,
@fbar DECIMAL(19, 9) = 0.0,
@i TINYINT = 0,
@found TINYINT = 0
IF @Rate IS NULL
SET @Rate = 0.1
SET @X0 = @Rate
WHILE @i < 100
BEGIN
SELECT @f = 0.0,
@fbar = 0.0
SELECT @f = @f + value * POWER(1 + @X0, (-theDelta / 365.0E)),
@fbar = @fbar - theDelta / 365.0E * value * POWER(1 + @X0, (-theDelta / 365.0E - 1))
FROM (
SELECT Value,
DATEDIFF(DAY, MIN(date) OVER (), date) AS theDelta
FROM @Sample
) AS d
SET @X = @X0 - @f / @fbar
If ABS(@X - @X0) < 0.00000001
BEGIN
SET @found = 1
BREAK;
END
SET @X0 = @X
SET @i += 1
END
If @found = 1
RETURN @X
RETURN NULL
END页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20972209
复制相关文章
相似问题
腾讯云开发者
