Calculate current rent

**[email protected]** · 07-13-2009, 04:56 PM

Sorry if I made it unclear. The amendments only relevance is to explain what changed in the lease. As these are multi-year leases, sometimes things change and tenants require more space or other services. These changes cause increases or legal document changes, and are made as part of amendments. They are not very relevant to this problem.

The only thing I really need is to have Excel calculate the present rent automatically. Meaning, that a refresh would allow it to use today's date and figure out if the rent escalated since the lease started. I don't really have ideas on how to present this, and that is why it is explained so vaguely.

**shg** · 07-13-2009, 05:02 PM

In U2 and copy down, =(1 + H2) ^ DATEDIF(F2,TODAY(), "Y") * G2

**[email protected]** · 07-13-2009, 07:37 PM

Hey,

thanks for the idea. I am trying to understand what you are doing. In the first example, the rent is calculated correctly. Unfortunately, this won't work for everything. I can't use the original rent for the calculations, because sometimes the amendments change the rent. So I would need all calculations to be based off of the "Present rent" and any escalation would go from this number. I noticed your formula uses the Original rent to base itself off of.

So in the first example, this is ok because there are no amendments so the escalation is a basic compound interest. However, in the second example, I would need excel to calculate the Date function from the present rent and force it to escalate every 365 days. Does this make sense?

**shg** · 07-13-2009, 07:47 PM

Then you need a column to contain the most recent rent baseline and as-of date, and then adapt the same formula.

**deadlyduck** · 07-13-2009, 08:10 PM

Does this formula in U2 copied down work:

=(1 + H2) ^ IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),DATEDIF(F2,TODAY(), "Y"),1) * IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),G2,I2)

It requires that there are always 3 columns prior to the cell where the calculation is performed for Terms/renewal/Expiration. It also assumes that if ANY amendment has been entered in a row that you use the present rent for the purpose of escalating to the new rent.

[EDIT- I MODIFIED THE ABOVE FORMULA SINCE I FIRST POSTED IT. THE ABOVE IS THE EDITED EFFORT.]

**[email protected]** · 07-15-2009, 01:31 PM

Ok so this a big step. This formula is a great start so thank you for your time. I REALLY appreciate it.

However, just using it on some other parts of the Excel, why does it give me a date as an answer instead of a number? Can you possibly explain a litlte how this formula works. I cannot seem to understand it.

I can send you a larger chunk of the data I am working with if you would like to make it easier.

**deadlyduck** · 07-15-2009, 02:36 PM

Here's the explanation of what the formula after (1+H2) is doing:

IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),DATEDIF(F2,TODAY(), "Y"),1)

This formula is used to determine whether you need to escalate from date of the original creation of the lease or just 1 year- note that I'm working on the basis that there are ANNUAL rent escalations so if you're not computing from the original date, you're simply increasing the most recent rate up by the annual escalation amount eg add 4% or whatever to the current rent amount.
The formula checks to see if there is any text in the amendment or details cells ("Amendment 1" "Details" etc). It does this by using the LOOKUP function- this is checking in a range which starts in cell J2 and goes to a cell 4 to the left [the OFFSET(U2,0,-4) part] from where the actual formula is entered eg if the formula is in cell U2 it looks in cells J2 to Q2 for some text. That's why it's important that there are always 3 columns prior to the cell where the calculation is performed to hold data for "Terms/renewal/Expiration".

If it doesn't find any text, it then knows to calculate the length of time from the original date of the lease to today [the DATEDIF(F2,TODAY() part]. Otherwise, it assumes that the time period is 1 year - that's what the ",1)" part of the formula is doing.

Here's what the next part is doing (the part after the foregoing formula):

* IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),G2,I2)

This is kind of similar to the previous part. This formula is figuring out whether to use the original rent amount or the most recent rent amount. It performs a test to see of there is any text in the cells for "Amendment" or "Details". If it finds nothing, it knows to use the original rent amount [G2 value] , otherwise it uses the most recent rent amount [I2 value].

If the formula is returning a date in a particular cell, make sure that the Formatting of that cell is set to either 'General' or 'Currency' rather than 'Date'. Excel stores dates as numbers so if you enter say 2500 in a cell formatted as 'Date' it'll be displayed as Nov 5 1910, which is 2500 days since Jan 1 1900.