I am trying to create a Macro/formula that calculates the probability that the value of a property is greater than a "Goal" value (which is an input). I want the value to be calculated a certain number of times, hence the Do While Loop, just like a Monte Carlo Simulation. I am very new to working in VBA so please excuse any basic errors. Here is the code I tried to write:
Function Probability_Profit(NOI, CapRate_Avg, CapRate_STDV, PurchasePrice, Goal, Trials)
n = 0
g = 0
CapRate = Application.NormInv(Rnd(), CapRate_Avg, CapRate_STDV)
Do While n < Trials
Value = NOI / CapRate - PurchasePrice
If Value > Goal Then
g = g + 1
Else
g = g
End If
n = n + 1
Loop
Probability_Profit = (g / Trials) * 100
End Function
Function Probability_Profit(NOI, CapRate_Avg, CapRate_STDV, PurchasePrice, Goal, Trials)
n = 0
g = 0
CapRate = Application.NormInv(Rnd(), CapRate_Avg, CapRate_STDV)
Do While n < Trials
Value = NOI / CapRate - PurchasePrice
If Value > Goal Then
g = g + 1
Else
g = g
End If
n = n + 1
Loop
Probability_Profit = (g / Trials) * 100
End Function
