Formula Uses More Levels Of Nesting Than Are Allowed In Current File Format

Hello,

I have been working on a dynamic commercial real estate leasing model formula where I created a table where I can select a term (say 5 years from 1/1/12-12/31/16), add a rental rate ($20/sf) and a free rent number (say 3 months), as well as year over year escalations. As of now I have the model flows flawlessly in creating the time period to constrict the term as well as strings out the rental rate over the term and subtracts the free rent.

Where I am running into trouble is with the escalation formula. I have created the below formula which triggers a 3% escalation every year after the initial yearBasically, the only problem is that it only allows me to go to year 6 (I need it to exted to 10)DCF Model__Fully Functional Test.xls. Every past that gives me the following box in the title of this post: "Formula Uses More Levels Of Nesting Than Are Allowed In Current File Format". Below is my formula which starts in the "Rent" tab at E8 and attached is the model:


=IF(AND(Q7>=Inputs!$D$6,Q7<Inputs!$D$12),0,IF(AND(Q7>=Inputs!$D$12,Q7<(Inputs!$D$12+(Inputs!$D$13*1))),1,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*1)),Q7<(Inputs!$D$12+(Inputs!$D$13*2))),2,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*2)),Q7<(Inputs!$D$12+(Inputs!$D$13*3))),3,IF(AND(Inputs!$D$12+(Inputs!$D$13*3),Q7<(Inputs!$D$12+(Inputs!$D$13*4))),4,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*4)),Q7<(Inputs!$D$12+(Inputs!$D$13*5))),5,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*5)),Q7<(Inputs!$D$12+(Inputs!$D$13*6))),6,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*5)),Q7<(Inputs!$D$12+(Inputs!$D$13*6))),7,0)))))))

Hi

Easiest way would be to build a vlookup table to bring back your required output.

rylo