User Defined Function ... Risk of Ruin

[SBF12345]

Greetings,

I am trying to put together a user defined function called risk of ruin. I would like it to output the probability associated with the occurrence of the 'bankroll' variable falling to <= 0. I am receiving 0 values as output for some reason. My graphing calculator and wikipedia tells me the correct value given the chosen inputs should be about 13%. what is occurring here?

Public Function Risk_of_Ruin(mu As Integer, st_dev As Integer, bankroll As Integer) As Integer
    
    'Risk of Ruin calculates the probability of reaching a less than or equal to zero value for the bankroll value
    'Inputs should be entered as whole dollar values
    
    Dim r As Integer
    
    r = Application.WorksheetFunction.Sqrt(mu ^ 2 + st_dev ^ 2)
    
    Risk_of_Ruin = (((2 / (1 + mu / r)) - 1) ^ (bankroll / r))


    Risk_of_Ruin = output


End Function

 

[StephenCrump]

Three problems immediately apparent:

1. You can't use WorksheetFunction.Sqrt. Use the VBA function Sqr() instead.

2. You don't want to be rounding r (i.e. by declaring as integer)

3. why are you setting Risk_of_Ruin = output (and what is output?) when you have already calculated Risk_of_Ruin in the previous code line?

 

[SBF12345]

I made the changes you identified and the function is producing values as expected.

I am still familiarizing myself with user defined functions and good coding practices. Thanks!