VBA Code

Future Value

Write a function that returns the future value of an investment that is compounded n times a year.

Public Function FutureValue( _ 
         ByVal PresentValue As Double, _ 
         ByVal AnnualInterestRate As Double, _ 
         ByVal NumberOfYears as Integer, _ 
         ByVal NumberOfCompounds As Integer) 

   FutureValue = PresentValue * (1 + AnnualInterestRate / NumberOfCompounds) ^ (NumberOfCompounds * NumberOfYears) 
End Function 

Effective Interest Rate

Write a function that returns the annual effective interest rate given a nominal interest rate.

Public Function EffectiveInterestRate( _ 
         ByVal NominalInterestRate As Double, _ 
         ByVal NumberOfCompounds As Integer) 

   EffectiveInterestRate = ( (1 + NominalInterestRate) ^ NumberOfCompounds ) - 1 
End Function 

Effective Interest Rate

Write a function that returns the annual effective interest rate given a continuously componded nominal interest rate.

Public Function EffectiveInterestRateContinuous( _ 
         ByVal NominalInterestRate As Double) 

   EffectiveInterestRateContinuous = VBA.Exp(NominalInterestRate) - 1 
End Function 

Net Present Value

Write a function that is equivalent to the Excel NPV worksheet function.
The first argument should be an array of cash flows.
The second argument should be the discount rate.

Public Function NetPresentValue( _ 
         ByVal arCashFlows As Variant, _ 
         ByVal DiscountRate As Double) 

Dim I As Integer 
   For I = 1 to Application.Count(arCashFlows) 
      NetPresentValue = NetPresentValue + arCashFlows(i) / (1 + DiscountRate) ^ (I - 1) 
   Next i 
End Function 

??

Strategy to hedge interest rate risk
buy corporate bonds
sell equivalent amount of treasury bonds

Public Function BuySellHoldStrategy( _ 
         ByVal rgePrices As Range, _ 
         ByVal rgeClosingPrice As Range, _ 
         ByVal iInterval As Integer) As String 

Dim lrowno As Long 
Dim inumber As Integer 
Dim dbtotal As Double 
   
   Application.Volatile (True) 
   ReDim arresult(rgePrices.Rows.Count - 1) 
   For lrowno = 1 To (iInterval - 1) 
      arresult(lrowno - 1) = 0 
   Next lrowno 
   For lrowno = 1 To (rgePrices.Rows.Count - iInterval + 1) 
      dbtotal = 0 
      For inumber = 1 To iInterval 
         dbtotal = dbtotal + rgePrices(lrowno, 1).Offset(inumber - 1, 0).Value 
      Next inumber 

      If (dbtotal / iInterval) > rgeClosingPrice.Value Then 
         BuySellHoldStrategy = "Buy 50" 
         Exit Function 
      End If 
   Next lrowno 
   
   If Application.WorksheetFunction.StDev(rgePrices) + 2 > rgeClosingPrice.Value Then 
      BuySellHoldStrategy = "Sell 40" 
      Exit Function 
   End If 
   
   BuySellHoldStrategy = "Hold" 
End Function