MySQL Forums
Forum List  »  Newbie

XIRR Function in mySQL
Posted by: Sud Ku
Date: August 21, 2014 10:38AM

Hello, I am new to MYSQL. Want to compute XIRR (Internal Rate of Return function of Excel sheet). I got the code in SQL. But new to SQL as well as MYSQL so don't know how to convert below code into MYSQL. Thanks in advance.

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

Options: ReplyQuote


Subject
Written By
Posted
XIRR Function in mySQL
August 21, 2014 10:38AM
August 21, 2014 11:57AM
August 21, 2014 10:50PM
August 22, 2014 09:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.