VBA Function Help Requied - Real Estate Problem

andreashak

New Member
Joined
Mar 15, 2010
Messages
30
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top