Function VaRCalculate(confidence, horizon, method, rf)
'Variable declarations
Dim nrow As Integer, i                          As Integer
Dim currentCell, nextCell
Dim logreturn() As Double, vol As Double, simreturn(1 To 10000) As Double
 
    'Count the last populated cell from the cell G26
    nrow = Range(Range("G26"), Range("G26").End(xlDown)).Rows.Count
    'Intitialize an array with 1 dimension equal to the number of row found just above
    'in a double type to fill the array with the cells content
    ReDim logreturn(1 To nrow - 1) As Double
    'Define a variable for the cell G26
    Set currentCell = Range("G26")
    'Start to count from 1 to the number of row found -1
    For i = 1 To (nrow - 1)
        'Define a variable of each next cell
        Set nextCell = currentCell.Offset(1, 0)
        'The array is filled according to the index i with the the natural logarithm of the next value
        logreturn(i) = Application.Ln(nextCell.Value) - Application.Ln(currentCell.Value)
        'The currentCell is now the nextCell
        Set currentCell = nextCell
    Next i
    'The variable vol is computed with the function StdDev() to calculate the Standard Deviation
    vol = StdDev(nrow - 1, logreturn)
    'If the parameter "method" passed from this function VaRCalculate() is equal to 1
    If method = 1 Then
        'VaRCalculate is equal to the computed "vol" multiplied with the inverse of the normal cumulative distribution
        VaRCalculate = vol * Application.NormInv(confidence, 0, 1) * Sqr(horizon)
        'Other wize
    'If the parameter "method" passed from this function VaRCalculate() is equal to 2
    ElseIf method = 2 Then
        'Start a loop from 1 to 10000
        For i = 1 To 10000
            'Computes each item oft the array simreturn() to return a double specifying e (base of natural logarithms) raised to a power.
            simreturn(i) = Exp((rf - 0.5 * vol * vol * 250) / 250 + vol * Sqr(250) * Sqr(1 / 250) * Application.NormInv(Rnd(), 0, 1)) - 1
        Next i
        'Computes VaRCalculate with the negative square root of "horizon" multiplied by the k-th percentile of values contained into the "simreturn".
        VaRCalculate = -Sqr(horizon) * Application.Percentile(simreturn, 1 - confidence)
    'If the parameter "method" passed from this function VaRCalculate() is equal to 3
    ElseIf method = 3 Then
        'VaRCalculate is equal the negative square root of "horizon" multiplied by  the k-th percentile of values contained into the "logreturn".
        VaRCalculate = -Sqr(horizon) * Application.Percentile(logreturn, 1 - confidence)
    End If
    'Finally, the function returns the computed value of the current cell multiplied by the result of the function
    VaRCalculate = currentCell.Value * VaRCalculate
End Function
 
'***********************************************************************
'* Mean *
'***********************************************************************
Function Mean(k As Long, Arr() As Double)
Dim Sum                                         As Double
Dim i                                           As Integer
    'Certainly to compute an average of the array "Arr"
    Sum = 0
    'Count until i = k
    For i = 1 To k
        'Sum is equal to itself + each item of the array "Arr"
        Sum = Sum + Arr(i)
    Next i
    'Then the function returns the "Sum" divided by "k"
    Mean = Sum / k
 
End Function
 
'************************************************************************
'* Standard Deviation *
'************************************************************************
Function StdDev(k As Long, Arr() As Double)
Dim i                                           As Integer
Dim avg As Double, SumSq                        As Double
 
    'Call the function Mean() to compute avg
    avg = Mean(k, Arr)
    'Count until i = k
    For i = 1 To k
        'Sum is equal to itself + each item of the array "Arr" power 2
        SumSq = SumSq + (Arr(i) - avg) ^ 2
    Next i
    'Then the function returns the  square root of "SumSq" divided by "k - 1"
    StdDev = Sqr(SumSq / (k - 1))
End Function