September 16, 2005 at 10:08 am
Hi Gang,
I need to create a stored procedure that queries a SQL database, calculates an XIRR (consistant with the Excel function), and passes the results to Crystal for reporting.
Does anyone have any experience calculating XIRR's from a stored procedure?
For example,
- Is there some way I can call the Excel XIRR function?
- If I have to write the XIRR function myself, does anyone have some code they can share?
- If I have to write the XIRR function myself, should I write it as a T-SQL Function or as a C/VB Extended Stored Procedure?
Thanks in advance,
DM.
September 16, 2005 at 10:20 am
Do not have code for xirr, but if you can pull it off in t-sql then a sql function would be easiest, and work fine. extended stored procedures must be written in c++
May 10, 2011 at 3:14 am
Try this and enjoy..
/*
DROP TABLE Cash_Flows
CREATE TABLE Cash_Flows ( cashflow DECIMAL(10,2), valuta DATETIME )
INSERT INTO Cash_Flows VALUES (-10000,'20080101')
INSERT INTO Cash_Flows VALUES (2750,'20080301')
INSERT INTO Cash_Flows VALUES (4250, '20081030')
INSERT INTO Cash_Flows VALUES (3250,'20090215')
INSERT INTO Cash_Flows VALUES (2750,'20090401')
SELECT dbo.XIRR_SURESH('20110510')
*/
create FUNCTION XIRR_SURESH(@issue_date SMALLDATETIME)RETURNS DECIMAL(15,2)
AS
BEGIN
DECLARE @ytm_tmp FLOAT
DECLARE @ytm FLOAT
DECLARE @pv_tmp FLOAT
DECLARE @pv FLOAT
SET @ytm_tmp = 0
SET @ytm = 0.1
SELECT @pv_tmp = SUM(cashflow) FROM Cash_Flows
SET @pv = (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta)* 30 + DAY(valuta)-DAY(@issue_date) - CASE WHEN(@issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END)/ 365.0 )) FROM Cash_Flows)
WHILE ABS(@pv) >= 0.000001
BEGIN
DECLARE @t FLOAT
SET @t = @ytm_tmp
SET @ytm_tmp = @ytm
SET @ytm = @ytm + (@t-@ytm)*@pv/(@pv-@pv_tmp)
SET @pv_tmp = @pv
SET @pv = (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta) * 30 + DAY(valuta)-DAY(@issue_date) - CASE WHEN(@issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END) /365.0)) FROM Cash_Flows)
END
RETURN @ytm * 100
END
August 6, 2021 at 10:16 am
DELIMITER $$
USE bodb$$
DROP FUNCTION IF EXISTS Fn_XIRR$$
CREATE DEFINER=rxqueryuser@% FUNCTION Fn_XIRR(issue_date DATE) RETURNS DECIMAL(38,9)
DETERMINISTIC
BEGIN
DECLARE ytm_tmp DECIMAL (20,4);
DECLARE ytm DECIMAL (20,4);
DECLARE pv_tmp DECIMAL (20,4);
DECLARE pv DECIMAL (20,4);
DECLARE t DECIMAL (20,4);
SET ytm_tmp = 0;
SET ytm = 0.1;
SELECT SUM(cashflow) INTO pv_tmp FROM Cash_Flows;
SET pv = (SELECT SUM(cashflow/POWER(1.0+ytm,(TIMESTAMPDIFF(MONTH,issue_date, valuta)* 30 + DAY(valuta)-DAY(issue_date) -
CASE WHEN(issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END)/ 365.0 )) FROM Cash_Flows);
WHILE ABS(pv) >= 0.000001 DO
SET t = ytm_tmp;
SET ytm_tmp = ytm;
SET ytm = ytm + (t-ytm)*pv/(pv-pv_tmp);
SET pv_tmp = pv;
SET pv = (SELECT SUM(cashflow/POWER(1.0+ytm,(TIMESTAMPDIFF(MONTH,issue_date, valuta) * 30 +
DAY(valuta)-DAY(issue_date) - CASE WHEN(issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END) /365.0)) FROM Cash_Flows);
END WHILE;
RETURN ytm * 100;
END$$
DELIMITER ;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply