VBA Function Help Requied - Real Estate Problem

[andreashak]

I am trying to define some calculations for various rental review profiles in a real estate cashflow. The problems being that there are to many variables and as such I want to create functions in VBA to cater for them.

Example 1: - Fixed% review with a cap, collar and ratchet (little redundant but hey)

Calc in excel at the moment:
Explanation
If the review date trigger is =1 then check the ratchet, if Y review the rental amount by fixed checking that its within the min max references and not less than 0% increase (ie not negative), where ratchet is N review the rental by fixed amount within min / max (it can be negative).

=IF(Z$76=1,
IF($R79="Y",MAX(IF(AND($K79>$N79,ISNUMBER($N79)),Y79*(1+$N79),IF(AND($K79<$M79, ISNUMBER($M79)),Y79*(1+$M79),Y79*(1+$K79))),Y79),
IF(AND($K79>$N79,ISNUMBER($N79)),Y79*(1+$N79),IF(AND($K79<$M79, ISNUMBER($M79)),Y79*(1+$M79),MAX(0,Y79*(1+$K79))))),Y79)

Z76 is another formula to match the review date with the current date and is used as a trigger
R79 is the ratchet and is a value of "Y" or "N"
K79 is a fixed percentage +/-
N79 max percentage +/-
M79 min percentage +/-
Y79 previous months rental amount

Basically this is one of a number of formulas that I would like to use and really require user defined function in VBA. Unfortunately my VBA is crap to worse.

I think the first part of this formula would be to set the value of the fixed percent based on the min max and final variable of ratchet (ie not less than 0)

Once the value of that is set the formula should be simple I think

Public Function Fixedpctrev (Base as Currency, FixedPct as Double) As Double

Fixedpctrev = (Base * (1+FixedPct))

This could be totally wrong by the way. Any help here would be much appreciated.

To add to the complexity this review could be based around the fixed percent & min max percent or alternately it coule be based around another set of variables fixed dollar, min dollar & max dollar. I was hoping to make the dollars & percentage inputs optional and have the calc work dependant on which set had value of more than 0.
(ok even I dont think the last paragraph makes much sense sorry.)

I am lost. Thanks in advance for any help.

I think with one example I should be able to make all of the other review types I am thinking of work.

Cheers

Hak

 

[andreashak]

Public Function FixedReview(Base As Currency, Optional ByVal FixedPct As Variant, Optional MinPct As Double, _
Optional MaxPct As Double, Optional Ratchet As String) As Variant

'Fixed review must increase the base by a fixed percent or dollar which should not be greater
'than the respective min/max arguments.
'If the ratchet is Y the resulting review may not be less than 0

If FixedPct > 0 Then

If FixedPct >= MaxPct Then

FixedPct = MaxPct

ElseIf FixedPct <= MinPct Then

FixedPct = MinPct
Else
End If


If Ratchet = "Y" Then
FixedReview = Application.Worksheet.Max((Base * (1 + FixedPct)), Base)

Else

FixedReview = (Base * (1 + FixedPct))

End If
End If

End Function

Ok this seems to work sporadicaly. If you leave max input blank it doesnt seem to work.