Recurring anniversary date

**mcarr5** · 05-04-2009, 08:03 PM

Hi,

Our Company has long term leases that increase according to the Consumer Price Index every three years.

I can easily calculate the trigger date down Col. A. What "If" formula could I use in Col. B to recognize the triannal date and rate (or forecast) the new rent?

I've thought of using an array and Match but I really don't know how to write the formula.

Help!

Mike

**daddylonglegs** · 05-04-2009, 08:14 PM

I'm not clear how that would work, Mike

Can you give an example? What date would you have in A2, what result would expect in B2, how would that result be calculated if you calculated it manually?

**JBeaucaire** · 05-04-2009, 08:16 PM

You need to mockup a sample sheet that shows what you're talking about. I thought at first you wanted dates...but then you say you know how to do that. Then at the last second you mention forecasting rate increases...

Create a sample sheet showing 5 or 6 examples of what you're talking about, and explain any calculations you did mentally if the price adjustments and such are not obvious.

Once we see the before and after, we can offer some easy formulas to automate the process.

**mcarr5** · 05-04-2009, 11:09 PM

Originally Posted by daddylonglegs

I'm not clear how that would work, Mike

Can you give an example? What date would you have in A2, what result would expect in B2, how would that result be calculated if you calculated it manually?

Today is 5/5/2009
Current Rent $ $375.00
Annual CPI Date 10/1/2009
Inflation Rate 6%
Last CPI Date 9/1/2008
Next CPI Date 10/1/2009
Maturity Date 10/1/2015
I can Handle the Date Changes
I need a formula that is triggered by the
annual CPI that multiplies the last rent
by 1.06 to determine the new rent.
My next step is to take it all the way
to maturity as part of budgeting
Col A Col B Col C
Item
Quarterly Rent 10/1/2008 $375.00
Quarterly Rent 1/1/2009 $375.00
Quarterly Rent 4/1/2009 $375.00
Quarterly Rent 7/1/2009 $375.00
Quarterly Rent 10/1/2009 $397.50 Rent Increases
Quarterly Rent 1/1/2010 $397.50
Quarterly Rent 4/1/2010 $397.50
Quarterly Rent 7/1/2010 $397.50
Quarterly Rent 10/1/2011 $421.35 Rent Increases
Quarterly Rent etc.

**mcarr5** · 05-04-2009, 11:11 PM

Originally Posted by JBeaucaire

You need to mockup a sample sheet that shows what you're talking about. I thought at first you wanted dates...but then you say you know how to do that. Then at the last second you mention forecasting rate increases...

Create a sample sheet showing 5 or 6 examples of what you're talking about, and explain any calculations you did mentally if the price adjustments and such are not obvious.

Once we see the before and after, we can offer some easy formulas to automate the process.

Today is 5/5/2009
Current Rent $ $375.00
Annual CPI Date 10/1/2009
Inflation Rate 6%
Last CPI Date 9/1/2008
Next CPI Date 10/1/2009
Maturity Date 10/1/2015
I can Handle the Date Changes
I need a formula that is triggered by the
annual CPI that multiplies the last rent
by 1.06 to determine the new rent.
My next step is to take it all the way
to maturity as part of budgeting
Col A Col B Col C
Item
Quarterly Rent 10/1/2008 $375.00
Quarterly Rent 1/1/2009 $375.00
Quarterly Rent 4/1/2009 $375.00
Quarterly Rent 7/1/2009 $375.00
Quarterly Rent 10/1/2009 $397.50 Rent Increases
Quarterly Rent 1/1/2010 $397.50
Quarterly Rent 4/1/2010 $397.50
Quarterly Rent 7/1/2010 $397.50
Quarterly Rent 10/1/2011 $421.35 Rent Increases
Quarterly Rent etc.

**JBeaucaire** · 05-05-2009, 12:09 AM

Please Login or Register  to view this content.

This is what I meant by uploading a sheet...

(click GO ADVANCED and use the paperclip icon)

The formula in C6 can be copied down as far as you'd like.