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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy