Formula to calculate market rent rates

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
Hello

I have a work book with two tabs.

1. Contractual Rent Steps - This shows the annual cost per square foot.
2. Market Rent Steps - This will use the annual market cost per square foot with a 3% increase when the tenants Contractual annual rent step increases.

What I am trying to do is come up with a formula that will calculate the annual market rate using the information for the tenant on the contractual tab to determine when their rate increased. Then on the market tab use the market rate in cell W6 times the growth percentage in cell C4.

The layout on both tabs is
Column Q is the month 05/01/18.
Column R on the contractual tab is the annual cost per SF, on the Market tab it is the market rate.

For tenant A there start date is 05/01/2018, their increases occur in September each year (this will vary tenant by tenant). On the market tab the rate in column R will be the rate found in cell W6, until the first increase in this case September so the new rate would be the market rate time the growth percentage in cell C4. The next increase would need to take the previous increase and times that by the growth rate and so on for increase increase.

Any help with this formula would be greatly appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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