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
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