Lease term dates

I need a formula to calculate the end date of a monthly term when the start
date is not the 1st of the month. This is to show the proration for the
first month only, so the end date should be the last day of the initital
month/yr. Then when showing the rates for each period, all subsequent
periods would start on the 1st of the month. For example, start date
8/16/06, initial end date needs to be 8/31/06. The current forumla I am
using is:
=IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
This works for all situations EXCEPT when the start date is other than the
1st and the term is less than 1 mo. (decimal).

Any clues???

I guess I didn't explain the formula well. In my formula
(=IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
G87 = term (in months)
C87 = start date

I'm using Excel XP.

Please help if you can!!!

"OCD Cindy" wrote:

> I need a formula to calculate the end date of a monthly term when the start
> date is not the 1st of the month. This is to show the proration for the
> first month only, so the end date should be the last day of the initital
> month/yr. Then when showing the rates for each period, all subsequent
> periods would start on the 1st of the month. For example, start date
> 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
> using is:
> =IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> This works for all situations EXCEPT when the start date is other than the
> 1st and the term is less than 1 mo. (decimal).
>
> Any clues???

Even with the clarification, your problem still isn't too clear.

If you need to find the last day of the month, given a date within that
month, you can use

=eomonth(startdate,0) (requires the Analysis Toolpak add-in)

or

=date(year(startdate),month(startdate)+1,0)

this DOES NOT require the analysis toolpak
"OCD Cindy" wrote:

> I guess I didn't explain the formula well. In my formula
> (=IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> G87 = term (in months)
> C87 = start date
>
> I'm using Excel XP.
>
> Please help if you can!!!
>
> "OCD Cindy" wrote:
>
> > I need a formula to calculate the end date of a monthly term when the start
> > date is not the 1st of the month. This is to show the proration for the
> > first month only, so the end date should be the last day of the initital
> > month/yr. Then when showing the rates for each period, all subsequent
> > periods would start on the 1st of the month. For example, start date
> > 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
> > using is:
> > =IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> > This works for all situations EXCEPT when the start date is other than the
> > 1st and the term is less than 1 mo. (decimal).
> >
> > Any clues???

Thanks for the reply...sorry to be so unclear -

I am calculating lease rates within the term of a lease. For example my
columns are:
A = Start Date
B = Term (in months or partial mos.)
C = End Date (the column I'm solving for)
D = Rental Rate
So, if there is a rent increase every year in a 10 year lease, I will have
approx. 10 rows. Each following row adds 1 day to the date calculated in
column C to begin the next rental rate increase period. The current formula
is working (though I don't quite understand it) EXCEPT when the initial term
is a partial month and needs to be calculated from a start date other than
the 1st. This happens often when the initial partial month is free and the
rental rate begins on the 1st of the following month. For example, free rent
from 8/15/06 - 8/31/06, and the new rate begins on 9/1/06. I've tried .5
(and several other decimals) as the term to get and end date 0f 8/31/06 for
that first period, but nothing works with the current formula.

Have I confused you even more?

"Duke Carey" wrote:

> Even with the clarification, your problem still isn't too clear.
>
> If you need to find the last day of the month, given a date within that
> month, you can use
>
> =eomonth(startdate,0) (requires the Analysis Toolpak add-in)
>
> or
>
> =date(year(startdate),month(startdate)+1,0)
>
> this DOES NOT require the analysis toolpak
> "OCD Cindy" wrote:
>
> > I guess I didn't explain the formula well. In my formula
> > (=IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> > G87 = term (in months)
> > C87 = start date
> >
> > I'm using Excel XP.
> >
> > Please help if you can!!!
> >
> > "OCD Cindy" wrote:
> >
> > > I need a formula to calculate the end date of a monthly term when the start
> > > date is not the 1st of the month. This is to show the proration for the
> > > first month only, so the end date should be the last day of the initital
> > > month/yr. Then when showing the rates for each period, all subsequent
> > > periods would start on the 1st of the month. For example, start date
> > > 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
> > > using is:
> > > =IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> > > This works for all situations EXCEPT when the start date is other than the
> > > 1st and the term is less than 1 mo. (decimal).
> > >
> > > Any clues???

Use the EOMonth(startdate,#months) function for all your end dates. When you
have a partial month use 0 for the #months argument.

"OCD Cindy" wrote:

> Thanks for the reply...sorry to be so unclear -
>
> I am calculating lease rates within the term of a lease. For example my
> columns are:
> A = Start Date
> B = Term (in months or partial mos.)
> C = End Date (the column I'm solving for)
> D = Rental Rate
> So, if there is a rent increase every year in a 10 year lease, I will have
> approx. 10 rows. Each following row adds 1 day to the date calculated in
> column C to begin the next rental rate increase period. The current formula
> is working (though I don't quite understand it) EXCEPT when the initial term
> is a partial month and needs to be calculated from a start date other than
> the 1st. This happens often when the initial partial month is free and the
> rental rate begins on the 1st of the following month. For example, free rent
> from 8/15/06 - 8/31/06, and the new rate begins on 9/1/06. I've tried .5
> (and several other decimals) as the term to get and end date 0f 8/31/06 for
> that first period, but nothing works with the current formula.
>
> Have I confused you even more?
>
> "Duke Carey" wrote:
>
> > Even with the clarification, your problem still isn't too clear.
> >
> > If you need to find the last day of the month, given a date within that
> > month, you can use
> >
> > =eomonth(startdate,0) (requires the Analysis Toolpak add-in)
> >
> > or
> >
> > =date(year(startdate),month(startdate)+1,0)
> >
> > this DOES NOT require the analysis toolpak
> > "OCD Cindy" wrote:
> >
> > > I guess I didn't explain the formula well. In my formula
> > > (=IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> > > G87 = term (in months)
> > > C87 = start date
> > >
> > > I'm using Excel XP.
> > >
> > > Please help if you can!!!
> > >
> > > "OCD Cindy" wrote:
> > >
> > > > I need a formula to calculate the end date of a monthly term when the start
> > > > date is not the 1st of the month. This is to show the proration for the
> > > > first month only, so the end date should be the last day of the initital
> > > > month/yr. Then when showing the rates for each period, all subsequent
> > > > periods would start on the 1st of the month. For example, start date
> > > > 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
> > > > using is:
> > > > =IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> > > > This works for all situations EXCEPT when the start date is other than the
> > > > 1st and the term is less than 1 mo. (decimal).
> > > >
> > > > Any clues???

Thanks again for the help. That works in the scenario where I want to use a
partial term to arrive at an end date of the end of the first month, but
unfortunately that is not always the case. Sometimes a term can begin on
8/23/06 and increase every year on the anniversary date (instead of the 1st).
Is there something else I could try that would work in both scenarios?

"Duke Carey" wrote:

> Use the EOMonth(startdate,#months) function for all your end dates. When you
> have a partial month use 0 for the #months argument.
>
> "OCD Cindy" wrote:
>
> > Thanks for the reply...sorry to be so unclear -
> >
> > I am calculating lease rates within the term of a lease. For example my
> > columns are:
> > A = Start Date
> > B = Term (in months or partial mos.)
> > C = End Date (the column I'm solving for)
> > D = Rental Rate
> > So, if there is a rent increase every year in a 10 year lease, I will have
> > approx. 10 rows. Each following row adds 1 day to the date calculated in
> > column C to begin the next rental rate increase period. The current formula
> > is working (though I don't quite understand it) EXCEPT when the initial term
> > is a partial month and needs to be calculated from a start date other than
> > the 1st. This happens often when the initial partial month is free and the
> > rental rate begins on the 1st of the following month. For example, free rent
> > from 8/15/06 - 8/31/06, and the new rate begins on 9/1/06. I've tried .5
> > (and several other decimals) as the term to get and end date 0f 8/31/06 for
> > that first period, but nothing works with the current formula.
> >
> > Have I confused you even more?
> >
> > "Duke Carey" wrote:
> >
> > > Even with the clarification, your problem still isn't too clear.
> > >
> > > If you need to find the last day of the month, given a date within that
> > > month, you can use
> > >
> > > =eomonth(startdate,0) (requires the Analysis Toolpak add-in)
> > >
> > > or
> > >
> > > =date(year(startdate),month(startdate)+1,0)
> > >
> > > this DOES NOT require the analysis toolpak
> > > "OCD Cindy" wrote:
> > >
> > > > I guess I didn't explain the formula well. In my formula
> > > > (=IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> > > > G87 = term (in months)
> > > > C87 = start date
> > > >
> > > > I'm using Excel XP.
> > > >
> > > > Please help if you can!!!
> > > >
> > > > "OCD Cindy" wrote:
> > > >
> > > > > I need a formula to calculate the end date of a monthly term when the start
> > > > > date is not the 1st of the month. This is to show the proration for the
> > > > > first month only, so the end date should be the last day of the initital
> > > > > month/yr. Then when showing the rates for each period, all subsequent
> > > > > periods would start on the 1st of the month. For example, start date
> > > > > 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
> > > > > using is:
> > > > > =IF(G87>0,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
> > > > > This works for all situations EXCEPT when the start date is other than the
> > > > > 1st and the term is less than 1 mo. (decimal).
> > > > >
> > > > > Any clues???

Any such formula would have to rely on one item of information that you don't
mention - how do you determine whether to use the anniversary date or the end
of a month? Excel doesn't have a telepath feature, so you'll have to figure
out some way to store an indicator.

If the indicator is "EOM" (for End Of Month), then use the EOMonth()
function, otherwise use the EDATE() function

=IF(indicator cell="EOM",eomonth(startdate,#months),edate(startdate,#months))

"OCD Cindy" wrote:

> Thanks again for the help. That works in the scenario where I want to use a
> partial term to arrive at an end date of the end of the first month, but
> unfortunately that is not always the case. Sometimes a term can begin on
> 8/23/06 and increase every year on the anniversary date (instead of the 1st).
> Is there something else I could try that would work in both scenarios?

The determining factor is the Lease Document itself. The document I'm
creating is an abstract of the lease. What I want to be able to do is input
a lease start date, then the # of months or partial months until the next
incremental increase to arrive at an end date for that rate. I don't quite
understand why the formula I'm using isn't working in just this one
situation. I can input a start date of the 1st of any month and use a
decimal (partial month) as the term and the end date will calculate
correctly. The only scenario where this does not work is when I combine a
partial month term with a start date of other than the first.

I wouldn't nit-pick this, but it is a standardized form with all the
formulas protected to keep people in the field from changing the format.

Do you know of any way to do the following calculation which would cover all
scenarios?:

Start Date (MM/DD/YY) + Term (# mos./partial mos.) = End
Date (MM/DD/YY)

"Duke Carey" wrote:

> Any such formula would have to rely on one item of information that you don't
> mention - how do you determine whether to use the anniversary date or the end
> of a month? Excel doesn't have a telepath feature, so you'll have to figure
> out some way to store an indicator.
>
> If the indicator is "EOM" (for End Of Month), then use the EOMonth()
> function, otherwise use the EDATE() function
>
> =IF(indicator cell="EOM",eomonth(startdate,#months),edate(startdate,#months))
>
> "OCD Cindy" wrote:
>
> > Thanks again for the help. That works in the scenario where I want to use a
> > partial term to arrive at an end date of the end of the first month, but
> > unfortunately that is not always the case. Sometimes a term can begin on
> > 8/23/06 and increase every year on the anniversary date (instead of the 1st).
> > Is there something else I could try that would work in both scenarios?
>

If the ONLY time you use a partial month is when you need a month-end date,
then modify the formula I gave you earlier to

=IF(mod(#months,1)>0,eomonth(startdate,#months),edate(startdate,#months))



"OCD Cindy" wrote:

> The determining factor is the Lease Document itself. The document I'm
> creating is an abstract of the lease. What I want to be able to do is input
> a lease start date, then the # of months or partial months until the next
> incremental increase to arrive at an end date for that rate. I don't quite
> understand why the formula I'm using isn't working in just this one
> situation. I can input a start date of the 1st of any month and use a
> decimal (partial month) as the term and the end date will calculate
> correctly. The only scenario where this does not work is when I combine a
> partial month term with a start date of other than the first.
>
> I wouldn't nit-pick this, but it is a standardized form with all the
> formulas protected to keep people in the field from changing the format.
>
> Do you know of any way to do the following calculation which would cover all
> scenarios?:
>
> Start Date (MM/DD/YY) + Term (# mos./partial mos.) = End
> Date (MM/DD/YY)
>
> "Duke Carey" wrote:
>
> > Any such formula would have to rely on one item of information that you don't
> > mention - how do you determine whether to use the anniversary date or the end
> > of a month? Excel doesn't have a telepath feature, so you'll have to figure
> > out some way to store an indicator.
> >
> > If the indicator is "EOM" (for End Of Month), then use the EOMonth()
> > function, otherwise use the EDATE() function
> >
> > =IF(indicator cell="EOM",eomonth(startdate,#months),edate(startdate,#months))
> >
> > "OCD Cindy" wrote:
> >
> > > Thanks again for the help. That works in the scenario where I want to use a
> > > partial term to arrive at an end date of the end of the first month, but
> > > unfortunately that is not always the case. Sometimes a term can begin on
> > > 8/23/06 and increase every year on the anniversary date (instead of the 1st).
> > > Is there something else I could try that would work in both scenarios?
> >

Unfortunately, sometimes I don't need the month-end date when using a partial
month. It just depends on how the lease is written. I just want it to
calculate correrctly based on the term I use. It works fine if, for example,
the lease start date is 3/1/06 and the first 1/2 mo. is free, I would put a
term of .5 mos. and the end date for that period would be 3/15/06. The
partial month straight calculation only doesn't work if the start date is
other than the first. And some times I might have a start date of 3/15/06
and a term of 60 months ending 3/14/11, so it needs to be able to calculate
either way. Hmm...

"Duke Carey" wrote:

> If the ONLY time you use a partial month is when you need a month-end date,
> then modify the formula I gave you earlier to
>
> =IF(mod(#months,1)>0,eomonth(startdate,#months),edate(startdate,#months))
>
>
>
> "OCD Cindy" wrote:
>
> > The determining factor is the Lease Document itself. The document I'm
> > creating is an abstract of the lease. What I want to be able to do is input
> > a lease start date, then the # of months or partial months until the next
> > incremental increase to arrive at an end date for that rate. I don't quite
> > understand why the formula I'm using isn't working in just this one
> > situation. I can input a start date of the 1st of any month and use a
> > decimal (partial month) as the term and the end date will calculate
> > correctly. The only scenario where this does not work is when I combine a
> > partial month term with a start date of other than the first.
> >
> > I wouldn't nit-pick this, but it is a standardized form with all the
> > formulas protected to keep people in the field from changing the format.
> >
> > Do you know of any way to do the following calculation which would cover all
> > scenarios?:
> >
> > Start Date (MM/DD/YY) + Term (# mos./partial mos.) = End
> > Date (MM/DD/YY)
> >
> > "Duke Carey" wrote:
> >
> > > Any such formula would have to rely on one item of information that you don't
> > > mention - how do you determine whether to use the anniversary date or the end
> > > of a month? Excel doesn't have a telepath feature, so you'll have to figure
> > > out some way to store an indicator.
> > >
> > > If the indicator is "EOM" (for End Of Month), then use the EOMonth()
> > > function, otherwise use the EDATE() function
> > >
> > > =IF(indicator cell="EOM",eomonth(startdate,#months),edate(startdate,#months))
> > >
> > > "OCD Cindy" wrote:
> > >
> > > > Thanks again for the help. That works in the scenario where I want to use a
> > > > partial term to arrive at an end date of the end of the first month, but
> > > > unfortunately that is not always the case. Sometimes a term can begin on
> > > > 8/23/06 and increase every year on the anniversary date (instead of the 1st).
> > > > Is there something else I could try that would work in both scenarios?
> > >