Financial function

Hello,

I seek a solution for the cacule of YIELD TO MATURITY of the bonds. In Excel av YIELD() but SQL server ?

Do you have an idea?

Thank you

I do not know the exact logic of the function but you should be able to code the same in a UDF or procedure in SQL Server. In SQL Server 2005, you can implement the same using C#/VB.NET function/procedure. Alternatively, you can invoke the Excel function via OLE automation or pass-through query provided you have to data in a Excel sheet. This approach however is not that pretty and probably overkill.

Anyway, what is the reason for trying to implement the function in SQL Server? You can get the data from SQL Server into Excel easily and perform the computation there. You can then persist results back to the database if you want to. And you can automate this whole process using say DTS or SSIS package for example.

|||SET NOCOUNT ON
CREATE TABLE Cash_Flows
(
cashflow DECIMAL(8,2),
valuta DATETIME
)
INSERT INTO Cash_Flows VALUES (-95.92,'20060115')
INSERT INTO Cash_Flows VALUES (5,'20070115')
INSERT INTO Cash_Flows VALUES (5,'20080115')
INSERT INTO Cash_Flows VALUES (5,'20090115')
INSERT INTO Cash_Flows VALUES (105,'20100115')

GO

CREATE FUNCTION yield_to_maturity(@.issue_date SMALLDATETIME)
RETURNS DECIMAL(15,14)
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 )/
360.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
) /360.0))
FROM Cash_Flows)
END
RETURN @.ytm
END
GO

SELECT dbo.yield_to_maturity('20060115')
DROP FUNCTION yield_to_maturity
DROP TABLE Cash_Flows
GO
SET NOCOUNT OFF

The fundamental principle of every asset valuation is that the fair value of an asset equals the present value of its cash flows. So, every asset valuation consists more or less of these three steps.
1. Estimate the expected cash flows
2. Determine one or more appropriate discount rates, that you will use to discount the cash flows
3. Calculate Present Value of 1. using 2.

While this is a nice exercise, the above covers only the most basic cases. You can only calculate plain vanilla bonds without embedded derivatives with that formula. Also, it doesn't take into account common day conventions like ISMA 251 or money market conventions. The formula rather uses a simple 30/360 day convention, which nowadays isn't common anymore in Europe. You would need to extend that on your own.
Calculating the YTM is an iterative process while not really is what SQL Server excels at. Like UC said, you would rather do this in a spreadsheet application with a specialised Add-In or some other front-end language|||Isn't the sigature automatically inserted anymore?
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs