Need to calculate a product based on a date (MAY NEED VBA)

[reanalyst]

This one is a little intricate...

Capture.PNG

The boxes numbered 1-5 correspond with years. So if we're in 2017, 1 is 2017, 2 is 2018, 3 is 2019.... The number underneath it is the new rent number.

In the box that says Current Monthly Rent, I need the cell to 1) determine if =YEAR() fits between the range of "Lease Begins" and "Lease Ends", 2) determine if it is the first period (2017 =1), second period (2018=2), ...., and then 3) based on what the date is, multiply the relative number under RSF by the relative number under the period.

In example..

If today is 1/1/2018, then we are in the second period of lease for "Store", and I need the Current Monthly Rent to return ($22.00*1,800).


Please instruct how this could be done, if it needs VBA, please advise what to do as I am not a programmer.

Thanks!