Financial function

Financial function

• PierreEric

  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
  
  
  

• bbyrd

  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
  
  
  

• jborza

  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.

  
  
  
• Financial function