Formula Assistance - Date Issues in Calculating Annual Lease Expense for Lease with non-annual lease escalations

[jpancerman]

Hello All,

I have been struggling to get a workable solution to this problem for a while. Here's a synopsis of the task and what I have done so far.

Task: Calculate the Annual Lease Cost by Year for 20 Years.

The following inputs are provide by anticipate user:

• Payment Schedule - Monthly, Quarterly, Semiannual, Annual
• Start Date
• Lease End/Option End Date (only relevant if <20 years from Start Date)
• Start Date and amount associated with each rent escalation (i.e. may not be an annual amount and may change)
• Escalation % as amount associated with escalation is not alwasy known

Inputs for Example:

• Monthly
• 8/20/2009
• 10/31/2050
• Original and Escalation Monthly Rent
  • 8/20/2009 12,000
  • 8/20/2009 12,500
  • 11/1/2012
  • 11/1/2015
  • 11/1/2018
  • 11/1/2021
  • 11/1/2024
  • 11/1/2027
• Escalation % for Escalations without known Monthly Rent 15%

So for example the assumed monthly Rent for 11/1/2012 - 10/31/2015 is 12,500 * 1.15 or 14,375.

Any or all of the inputs may change (e.g. 11/1/2012 to 11/17/2012, etc.). For right now I want to get the Monthly payment schedule correct.

Also, for years where the applicable rent rate changes I use number of days to determine how much of the rent payment will be applied at the current rent and the escalation rent.

Desired Output:

Year Ended Annual Lease Amount
8/19/2010 144,000
8/19/2011 150,000
8/19/2012 150,000
8/19/2013 168,000 *lease year uses 2 monthly rent rates
... ...
... ...
... ...
8/19/2029 346,959

Would be very appreciative of any advice the forum members can provide to help me with this challenge. If there is anythign I can do to facilitate this request let me know, as I am newer to the forum.