Schinzilord for wertpapier-forum.de
2014/11/26
Veröffentlicht unter der Lizenz GPL 3.0.

'##########################################################

Function getRate(Curve As Variant, node As Variant)
' This function interpolates and extrapolates a given discount curve 
' and returns the evaluated rate for the given node
Dim iRow As Integer
Dim jRow As Integer
Dim lambda As Variant
Dim Timesteps(LBound(Curve, 1) To UBound(Curve, 1)) As Variant
Dim rate As Variant

' Make 1D Array with Timesteps (Nodes) of Discount Curve:
For iRow = LBound(Curve, 1) To UBound(Curve, 1)
	Timesteps(iRow) = Curve(iRow,1)
'	Msgbox (CF_new(iRow,1) & " and " & CF_new(iRow,2))
Next

	' Now extrapolate downside:
If ( node < MIN(Timesteps) ) Then
	rate = Curve(1,2)
	'MsgBox "MIN(Timesteps)= " & MIN(Timesteps) & " rate is " & rate
	' Or extrapolate upside:	
ElseIf ( node > MAX(Timesteps) ) Then
	rate = Curve(UBound(Curve,1),2)
	'MsgBox "MAX(Timesteps)= " & MAX(Timesteps) & " rate is " & rate	
Else
	' Otherwise Linear Interpolation:
	For iRow = 2 To UBound(Curve, 1)
		lambda = ( node - Curve(iRow-1,1) ) / ( Curve(iRow,1) - Curve(iRow-1,1) )
				if (lambda > 0 ) Then
					if ( lambda <= 1 ) Then
						rate = (1 - lambda ) * Curve(iRow-1,2) + lambda * Curve(iRow,2)
						'MsgBox " lambda: " & lambda & " rate " & rate & " Node low " & Curve(iRow-1,1) & " Node high " & Curve(iRow,1)
					End If
				End If
	Next				
End If

getRate = rate

End Function


'##########################################################


Function pvmp(Curve As Variant, CF As Variant)
' This function returns the present value of Cashflows (CF)
' which are discounted by their corresponding discount rate
' given by the Curve. Therefor the helper function "getRate"
' is incorporated to linearly inter- and extrapolate to match
' the CF timesteps with the nodes from the discount curve
Dim iRow As Integer
Dim jRow As Integer
Dim cf_date As Variant
Dim lambda As Variant
Dim pv As Variant
Dim rate As Variant
Dim node As Variant
Dim DF As Variant


' Present Value
pv = 0
' Loop through all Cashflows
For iRow = LBound(CF,1) To UBound(CF,1)
	' Get Node:
	node = CF(iRow,1)
	' Get Rate
	rate = getRate(Curve,node)
	' Calculate Discount Factor for given Rate:
	DF = ( ( 1 + rate )^(-node/365) )
	' Add up all discounted cashflows to resulting present value
	pv = pv + CF(iRow,2) * DF

next

pvmp = pv

End Function

'##########################################################



'############################# Helper Functions ###########################

Function MAX(Array As Variant)
'If Not isNumeric(Array) Then
'	MsgBox ("Your Array is not numeric")
'	'end
'End If

Dim tmpmax As Variant
Dim i As Integer

tmpmax = 0
For i = LBound(Array) To UBound(Array)
	If (Array(i) > tmpmax) Then
		tmpmax = Array(i)
	End If
Next
MAX = tmpmax

End Function


Function MIN(Array As Variant)
'If Not isNumeric(Array) Then
'	MsgBox ("Your Array is not numeric")
'	'end
'End If

Dim tmpmin As Variant
Dim i As Integer

tmpmin = Array(UBound(Array))

For i = LBound(Array) To UBound(Array)
	If (Array(i) < tmpmin) Then
		tmpmin = Array(i)
		'MsgBox (tmpmin)
	End If
Next
MIN = tmpmin

End Function