VBA Answers


VBA - Q1

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

VBA - Q2

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

VBA - Q3

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

VBA - Q4

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


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext