modified amortization schedule for open ended loan with EO..

staplers · External Since: Jun 12, 2009 Posts: 4

I can fill cells of a column with all 26 recurring biweekly Mondays (for
instance) in the year. I can fill cells of a column with all 12 EOMONTHs in
the year. But I cannot do both at once. How can I create a column that
lists selected biweekly days/dates including EOMONTH for all 12 months? 10
months would have two biweekly days/dates plus one EOMONTH (3 dates) and two
months would have three biweekly days/dates plus one EOMONTH (4 dates). This
is for an Open Ended loan I am trying to track with an amortization plan
where I make 26 payments per year, every other Monday, and a Loan Protection
Plan charge is added to my loan the last day of every month (No, I don't like
being forced to pay interest on the extra loan amount, but I cannot pre-pay
it monthly.) I want a “global” formula using defined ranges, not one that
sequences from the previous/last cell to test and fill the next/succeeding
cell. However, anything is welcome.
--
staplers

Shane Devenshire · External Since: Jan 07, 2009 Posts: 252

Hi,

I don't think you can do this with a simple fill command, you could do it
using VBA.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"staplers" wrote:

> I can fill cells of a column with all 26 recurring biweekly Mondays (for
> instance) in the year. I can fill cells of a column with all 12 EOMONTHs in
> the year. But I cannot do both at once. How can I create a column that
> lists selected biweekly days/dates including EOMONTH for all 12 months? 10
> months would have two biweekly days/dates plus one EOMONTH (3 dates) and two
> months would have three biweekly days/dates plus one EOMONTH (4 dates). This
> is for an Open Ended loan I am trying to track with an amortization plan
> where I make 26 payments per year, every other Monday, and a Loan Protection
> Plan charge is added to my loan the last day of every month (No, I don't like
> being forced to pay interest on the extra loan amount, but I cannot pre-pay
> it monthly.) I want a “global” formula using defined ranges, not one that
> sequences from the previous/last cell to test and fill the next/succeeding
> cell. However, anything is welcome.
> --
> staplers

Fred Smith · External Since: Jan 17, 2009 Posts: 72

As you said "anything is welcome", here's a formula to do what you want.
Assumes starting date is in a2:
=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))

Regards,
Fred

"staplers" <staplers.TakeThisOut@discussions.microsoft.com> wrote in message
news:94841A89-EA55-4ECF-8F78-1944E392DF92@microsoft.com...
>I can fill cells of a column with all 26 recurring biweekly Mondays (for
> instance) in the year. I can fill cells of a column with all 12 EOMONTHs
> in
> the year. But I cannot do both at once. How can I create a column that
> lists selected biweekly days/dates including EOMONTH for all 12 months?
> 10
> months would have two biweekly days/dates plus one EOMONTH (3 dates) and
> two
> months would have three biweekly days/dates plus one EOMONTH (4 dates).
> This
> is for an Open Ended loan I am trying to track with an amortization plan
> where I make 26 payments per year, every other Monday, and a Loan
> Protection
> Plan charge is added to my loan the last day of every month (No, I don't
> like
> being forced to pay interest on the extra loan amount, but I cannot
> pre-pay
> it monthly.) I want a “global” formula using defined ranges, not one that
> sequences from the previous/last cell to test and fill the next/succeeding
> cell. However, anything is welcome.
> --
> staplers

staplers · External Since: Jun 12, 2009 Posts: 4

This doesn't work as I need. [re:
"=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))"]

For my loan:
D10 = Loan_Start = 3/20/09
A19 = Payment #1 = 3/27/09
A19 – A29 = Payments 1 – 11, irregular payments trying to set a schedule
A30 = first regular, scheduled biweekly payment = Monday, June 8 = Payment 12
Everything following should be every other Monday + every EOMONTH

Your formula gives 3 dates per month where 2 Mondays exist in the month,
but they are every 14th and every 28th + every EOMONTH. I need them to be
every Monday + every EOMONTH. Your formula never gives 3 Mondays + EOMONTH,
but two months should have these 4 dates.

I can ignore payments 1 -11 since they are not on a schedule, but I need to
begin my schedule by specifying the date for payment 12, Monday, June 8, and
then follow a biweekly schedule + EOMONTH from then on.

Is this possible to do?

--
staplers

"Fred Smith" wrote:

> As you said "anything is welcome", here's a formula to do what you want.
> Assumes starting date is in a2:
> =IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
>
> Regards,
> Fred
>
> "staplers" <staplers RemoveThis @discussions.microsoft.com> wrote in message
> news:94841A89-EA55-4ECF-8F78-1944E392DF92@microsoft.com...
> >I can fill cells of a column with all 26 recurring biweekly Mondays (for
> > instance) in the year. I can fill cells of a column with all 12 EOMONTHs
> > in
> > the year. But I cannot do both at once. How can I create a column that
> > lists selected biweekly days/dates including EOMONTH for all 12 months?
> > 10
> > months would have two biweekly days/dates plus one EOMONTH (3 dates) and
> > two
> > months would have three biweekly days/dates plus one EOMONTH (4 dates).
> > This
> > is for an Open Ended loan I am trying to track with an amortization plan
> > where I make 26 payments per year, every other Monday, and a Loan
> > Protection
> > Plan charge is added to my loan the last day of every month (No, I don't
> > like
> > being forced to pay interest on the extra loan amount, but I cannot
> > pre-pay
> > it monthly.) I want a “global” formula using defined ranges, not one that
> > sequences from the previous/last cell to test and fill the next/succeeding
> > cell. However, anything is welcome.
> > --
> > staplers
>
>

staplers · External Since: Jun 12, 2009 Posts: 4

O.K. I figured out your formula and what I did wrong when I made it fit my
cell numbers. After I corrected my mistake, it almost works perfectly -
unless the last Monday in the month is the 17 th and the EOMONTH is the 31st
.. When this happens, the formula calculates one date on the 17, then the
EOMONTH for the 31, and then 17 + 14 = 31 again. It would do the same thing
for a 16th and 30th combination.

Through March 2013, this happens for August 2009 and January 2011.

Your formula almost did the trick, with this exception. I have tried to use
the double date, since it means I am making a payment on the date as well as
a LPP payment on that date, but a single date would work better.

Can you figure out how to eliminate either the last Monday or the EOMONTH in
the case where they coexist?

Thanks for your help.

--
staplers

"staplers" wrote:

> This doesn't work as I need. [re:
> "=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))"]
>
> For my loan:
> D10 = Loan_Start = 3/20/09
> A19 = Payment #1 = 3/27/09
> A19 – A29 = Payments 1 – 11, irregular payments trying to set a schedule
> A30 = first regular, scheduled biweekly payment = Monday, June 8 = Payment 12
> Everything following should be every other Monday + every EOMONTH
>
> Your formula gives 3 dates per month where 2 Mondays exist in the month,
> but they are every 14th and every 28th + every EOMONTH. I need them to be
> every Monday + every EOMONTH. Your formula never gives 3 Mondays + EOMONTH,
> but two months should have these 4 dates.
>
> I can ignore payments 1 -11 since they are not on a schedule, but I need to
> begin my schedule by specifying the date for payment 12, Monday, June 8, and
> then follow a biweekly schedule + EOMONTH from then on.
>
> Is this possible to do?
>
> --
> staplers
>
>
> "Fred Smith" wrote:
>
> > As you said "anything is welcome", here's a formula to do what you want.
> > Assumes starting date is in a2:
> > =IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
> >
> > Regards,
> > Fred
> >
> > "staplers" <staplers.DeleteThis@discussions.microsoft.com> wrote in message
> > news:94841A89-EA55-4ECF-8F78-1944E392DF92@microsoft.com...
> > >I can fill cells of a column with all 26 recurring biweekly Mondays (for
> > > instance) in the year. I can fill cells of a column with all 12 EOMONTHs
> > > in
> > > the year. But I cannot do both at once. How can I create a column that
> > > lists selected biweekly days/dates including EOMONTH for all 12 months?
> > > 10
> > > months would have two biweekly days/dates plus one EOMONTH (3 dates) and
> > > two
> > > months would have three biweekly days/dates plus one EOMONTH (4 dates).
> > > This
> > > is for an Open Ended loan I am trying to track with an amortization plan
> > > where I make 26 payments per year, every other Monday, and a Loan
> > > Protection
> > > Plan charge is added to my loan the last day of every month (No, I don't
> > > like
> > > being forced to pay interest on the extra loan amount, but I cannot
> > > pre-pay
> > > it monthly.) I want a “global” formula using defined ranges, not one that
> > > sequences from the previous/last cell to test and fill the next/succeeding
> > > cell. However, anything is welcome.
> > > --
> > > staplers
> >
> >

Fred Smith · External Since: Jan 17, 2009 Posts: 72

I haven't been able to come up with a solution to the problem. What I would
do is either live with it, or convert the dates to values (copy>paste
special... values), and then delete the duplicate rows.

Regards,
Fred.

"staplers" <staplers RemoveThis @discussions.microsoft.com> wrote in message
news:5253BC46-2BC5-483B-A92A-B1EB1A820905@microsoft.com...
> O.K. I figured out your formula and what I did wrong when I made it fit
> my
> cell numbers. After I corrected my mistake, it almost works perfectly -
> unless the last Monday in the month is the 17 th and the EOMONTH is the
> 31st
> . When this happens, the formula calculates one date on the 17, then the
> EOMONTH for the 31, and then 17 + 14 = 31 again. It would do the same
> thing
> for a 16th and 30th combination.
>
> Through March 2013, this happens for August 2009 and January 2011.
>
> Your formula almost did the trick, with this exception. I have tried to
> use
> the double date, since it means I am making a payment on the date as well
> as
> a LPP payment on that date, but a single date would work better.
>
> Can you figure out how to eliminate either the last Monday or the EOMONTH
> in
> the case where they coexist?
>
> Thanks for your help.
>
> --
> staplers
>
>
> "staplers" wrote:
>
>> This doesn't work as I need. [re:
>> "=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))"]
>>
>> For my loan:
>> D10 = Loan_Start = 3/20/09
>> A19 = Payment #1 = 3/27/09
>> A19 – A29 = Payments 1 – 11, irregular payments trying to set a schedule
>> A30 = first regular, scheduled biweekly payment = Monday, June 8 =
>> Payment 12
>> Everything following should be every other Monday + every EOMONTH
>>
>> Your formula gives 3 dates per month where 2 Mondays exist in the month,
>> but they are every 14th and every 28th + every EOMONTH. I need them to
>> be
>> every Monday + every EOMONTH. Your formula never gives 3 Mondays +
>> EOMONTH,
>> but two months should have these 4 dates.
>>
>> I can ignore payments 1 -11 since they are not on a schedule, but I need
>> to
>> begin my schedule by specifying the date for payment 12, Monday, June 8,
>> and
>> then follow a biweekly schedule + EOMONTH from then on.
>>
>> Is this possible to do?
>>
>> --
>> staplers
>>
>>
>> "Fred Smith" wrote:
>>
>> > As you said "anything is welcome", here's a formula to do what you
>> > want.
>> > Assumes starting date is in a2:
>> > =IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
>> >
>> > Regards,
>> > Fred
>> >
>> > "staplers" <staplers RemoveThis @discussions.microsoft.com> wrote in message
>> > news:94841A89-EA55-4ECF-8F78-1944E392DF92@microsoft.com...
>> > >I can fill cells of a column with all 26 recurring biweekly Mondays
>> > >(for
>> > > instance) in the year. I can fill cells of a column with all 12
>> > > EOMONTHs
>> > > in
>> > > the year. But I cannot do both at once. How can I create a column
>> > > that
>> > > lists selected biweekly days/dates including EOMONTH for all 12
>> > > months?
>> > > 10
>> > > months would have two biweekly days/dates plus one EOMONTH (3 dates)
>> > > and
>> > > two
>> > > months would have three biweekly days/dates plus one EOMONTH (4
>> > > dates).
>> > > This
>> > > is for an Open Ended loan I am trying to track with an amortization
>> > > plan
>> > > where I make 26 payments per year, every other Monday, and a Loan
>> > > Protection
>> > > Plan charge is added to my loan the last day of every month (No, I
>> > > don't
>> > > like
>> > > being forced to pay interest on the extra loan amount, but I cannot
>> > > pre-pay
>> > > it monthly.) I want a “global” formula using defined ranges, not one
>> > > that
>> > > sequences from the previous/last cell to test and fill the
>> > > next/succeeding
>> > > cell. However, anything is welcome.
>> > > --
>> > > staplers
>> >
>> >

staplers · External Since: Jun 12, 2009 Posts: 4

This seems to work:

Where:

A1 = 1/5/2009 = the first Monday of January, 2009
A2 = 1/19/2009 = the second Monday of January, 2009
A3 = 1/31/2009 = EOMONTH for January, 2009
A4 = 2/2/2009 = first Monday for February, 2009

Cell A3
=IF(A2=EOMONTH(A2,0),IF(A1+14<>EOMONTH(A2,0),A1+14,A2+14),IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
Cell A3 = 1/31/2009
Cell A4 = 2/2/2009
Cell A5 = 2/16/2009
etc.
etc.

This fills all cells of a column with dates corresponding to the first
Monday of January, 2009 and every-other Monday thereafter, including the
correct EOMONTH for each month with no duplicates for the end of the month.
I have not figured out how to "kick it off", so I have to manually fill in
the first two dates. From then on, the formula works. I tried to shorten
the formula using an AND function but ran out of patience.

Of course, the equation will work with any day of the week and any week of
the month to produce biweekly dates, you just have to (with this formula)
kick it off with the appropriate first two manual dates.

Banks don't work on Holidays, and this formula will not shift a date for a
Holiday. It produces May 25, a Holiday, instead of May 26, when the bank is
open. Some of the EOMONTHs are on weekends as well. I will have to wait and
see what day the bank automatically debits my loan for the end of the month
LPP. If the computer doesn't work on weekends, I will have to try to move
EOMONTH payments back to the last workday.

Any ideas how I might do that, if required?

--
staplers

"Fred Smith" wrote:

> I haven't been able to come up with a solution to the problem. What I would
> do is either live with it, or convert the dates to values (copy>paste
> special... values), and then delete the duplicate rows.
>
> Regards,
> Fred.
>
> "staplers" <staplers.DeleteThis@discussions.microsoft.com> wrote in message
> news:5253BC46-2BC5-483B-A92A-B1EB1A820905@microsoft.com...
> > O.K. I figured out your formula and what I did wrong when I made it fit
> > my
> > cell numbers. After I corrected my mistake, it almost works perfectly -
> > unless the last Monday in the month is the 17 th and the EOMONTH is the
> > 31st
> > . When this happens, the formula calculates one date on the 17, then the
> > EOMONTH for the 31, and then 17 + 14 = 31 again. It would do the same
> > thing
> > for a 16th and 30th combination.
> >
> > Through March 2013, this happens for August 2009 and January 2011.
> >
> > Your formula almost did the trick, with this exception. I have tried to
> > use
> > the double date, since it means I am making a payment on the date as well
> > as
> > a LPP payment on that date, but a single date would work better.
> >
> > Can you figure out how to eliminate either the last Monday or the EOMONTH
> > in
> > the case where they coexist?
> >
> > Thanks for your help.
> >
> > --
> > staplers
> >
> >
> > "staplers" wrote:
> >
> >> This doesn't work as I need. [re:
> >> "=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))"]
> >>
> >> For my loan:
> >> D10 = Loan_Start = 3/20/09
> >> A19 = Payment #1 = 3/27/09
> >> A19 – A29 = Payments 1 – 11, irregular payments trying to set a schedule
> >> A30 = first regular, scheduled biweekly payment = Monday, June 8 =
> >> Payment 12
> >> Everything following should be every other Monday + every EOMONTH
> >>
> >> Your formula gives 3 dates per month where 2 Mondays exist in the month,
> >> but they are every 14th and every 28th + every EOMONTH. I need them to
> >> be
> >> every Monday + every EOMONTH. Your formula never gives 3 Mondays +
> >> EOMONTH,
> >> but two months should have these 4 dates.
> >>
> >> I can ignore payments 1 -11 since they are not on a schedule, but I need
> >> to
> >> begin my schedule by specifying the date for payment 12, Monday, June 8,
> >> and
> >> then follow a biweekly schedule + EOMONTH from then on.
> >>
> >> Is this possible to do?
> >>
> >> --
> >> staplers
> >>
> >>
> >> "Fred Smith" wrote:
> >>
> >> > As you said "anything is welcome", here's a formula to do what you
> >> > want.
> >> > Assumes starting date is in a2:
> >> > =IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
> >> >
> >> > Regards,
> >> > Fred
> >> >
> >> > "staplers" <staplers.DeleteThis@discussions.microsoft.com> wrote in message
> >> > news:94841A89-EA55-4ECF-8F78-1944E392DF92@microsoft.com...
> >> > >I can fill cells of a column with all 26 recurring biweekly Mondays
> >> > >(for
> >> > > instance) in the year. I can fill cells of a column with all 12
> >> > > EOMONTHs
> >> > > in
> >> > > the year. But I cannot do both at once. How can I create a column
> >> > > that
> >> > > lists selected biweekly days/dates including EOMONTH for all 12
> >> > > months?
> >> > > 10
> >> > > months would have two biweekly days/dates plus one EOMONTH (3 dates)
> >> > > and
> >> > > two
> >> > > months would have three biweekly days/dates plus one EOMONTH (4
> >> > > dates).
> >> > > This
> >> > > is for an Open Ended loan I am trying to track with an amortization
> >> > > plan
> >> > > where I make 26 payments per year, every other Monday, and a Loan
> >> > > Protection
> >> > > Plan charge is added to my loan the last day of every month (No, I
> >> > > don't
> >> > > like
> >> > > being forced to pay interest on the extra loan amount, but I cannot
> >> > > pre-pay
> >> > > it monthly.) I want a “global” formula using defined ranges, not one
> >> > > that
> >> > > sequences from the previous/last cell to test and fill the
> >> > > next/succeeding
> >> > > cell. However, anything is welcome.
> >> > > --
> >> > > staplers
> >> >
> >> >
>
>

Fred Smith · External Since: Jan 17, 2009 Posts: 72

I agree with you about running out of patience. It depends what you want to
achieve. If you have to know the exact amortization schedule that the bank
will apply, you will need to check with them to see what the rules are. I
expect, however, you'll find this is a very frustrating process.

If you only need something that's good enough, you have it. To track the
loan, I would just adjust the dates manually as each one goes through your
bank.

Regards,
Fred.

"staplers" <staplers.TakeThisOut@discussions.microsoft.com> wrote in message
news:1BA0D4A4-E749-49C9-9E19-6F791F034799@microsoft.com...
> This seems to work:
>
> Where:
>
> A1 = 1/5/2009 = the first Monday of January, 2009
> A2 = 1/19/2009 = the second Monday of January, 2009
> A3 = 1/31/2009 = EOMONTH for January, 2009
> A4 = 2/2/2009 = first Monday for February, 2009
>
> Cell A3
> =IF(A2=EOMONTH(A2,0),IF(A1+14<>EOMONTH(A2,0),A1+14,A2+14),IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
> Cell A3 = 1/31/2009
> Cell A4 = 2/2/2009
> Cell A5 = 2/16/2009
> etc.
> etc.
>
> This fills all cells of a column with dates corresponding to the first
> Monday of January, 2009 and every-other Monday thereafter, including the
> correct EOMONTH for each month with no duplicates for the end of the
> month.
> I have not figured out how to "kick it off", so I have to manually fill in
> the first two dates. From then on, the formula works. I tried to shorten
> the formula using an AND function but ran out of patience.
>
> Of course, the equation will work with any day of the week and any week of
> the month to produce biweekly dates, you just have to (with this formula)
> kick it off with the appropriate first two manual dates.
>
> Banks don't work on Holidays, and this formula will not shift a date for a
> Holiday. It produces May 25, a Holiday, instead of May 26, when the bank
> is
> open. Some of the EOMONTHs are on weekends as well. I will have to wait
> and
> see what day the bank automatically debits my loan for the end of the
> month
> LPP. If the computer doesn't work on weekends, I will have to try to move
> EOMONTH payments back to the last workday.
>
> Any ideas how I might do that, if required?
>
> --
> staplers
>
>
> "Fred Smith" wrote:
>
>> I haven't been able to come up with a solution to the problem. What I
>> would
>> do is either live with it, or convert the dates to values (copy>paste
>> special... values), and then delete the duplicate rows.
>>
>> Regards,
>> Fred.
>>
>> "staplers" <staplers.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:5253BC46-2BC5-483B-A92A-B1EB1A820905@microsoft.com...
>> > O.K. I figured out your formula and what I did wrong when I made it
>> > fit
>> > my
>> > cell numbers. After I corrected my mistake, it almost works
>> > perfectly -
>> > unless the last Monday in the month is the 17 th and the EOMONTH is the
>> > 31st
>> > . When this happens, the formula calculates one date on the 17, then
>> > the
>> > EOMONTH for the 31, and then 17 + 14 = 31 again. It would do the same
>> > thing
>> > for a 16th and 30th combination.
>> >
>> > Through March 2013, this happens for August 2009 and January 2011.
>> >
>> > Your formula almost did the trick, with this exception. I have tried
>> > to
>> > use
>> > the double date, since it means I am making a payment on the date as
>> > well
>> > as
>> > a LPP payment on that date, but a single date would work better.
>> >
>> > Can you figure out how to eliminate either the last Monday or the
>> > EOMONTH
>> > in
>> > the case where they coexist?
>> >
>> > Thanks for your help.
>> >
>> > --
>> > staplers
>> >
>> >
>> > "staplers" wrote:
>> >
>> >> This doesn't work as I need. [re:
>> >> "=IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))"]
>> >>
>> >> For my loan:
>> >> D10 = Loan_Start = 3/20/09
>> >> A19 = Payment #1 = 3/27/09
>> >> A19 – A29 = Payments 1 – 11, irregular payments trying to set a
>> >> schedule
>> >> A30 = first regular, scheduled biweekly payment = Monday, June 8 =
>> >> Payment 12
>> >> Everything following should be every other Monday + every EOMONTH
>> >>
>> >> Your formula gives 3 dates per month where 2 Mondays exist in the
>> >> month,
>> >> but they are every 14th and every 28th + every EOMONTH. I need them
>> >> to
>> >> be
>> >> every Monday + every EOMONTH. Your formula never gives 3 Mondays +
>> >> EOMONTH,
>> >> but two months should have these 4 dates.
>> >>
>> >> I can ignore payments 1 -11 since they are not on a schedule, but I
>> >> need
>> >> to
>> >> begin my schedule by specifying the date for payment 12, Monday, June
>> >> 8,
>> >> and
>> >> then follow a biweekly schedule + EOMONTH from then on.
>> >>
>> >> Is this possible to do?
>> >>
>> >> --
>> >> staplers
>> >>
>> >>
>> >> "Fred Smith" wrote:
>> >>
>> >> > As you said "anything is welcome", here's a formula to do what you
>> >> > want.
>> >> > Assumes starting date is in a2:
>> >> > =IF(A2=EOMONTH(A2,0),A1+14,IF(MONTH(A2)=MONTH(A2+14),A2+14,EOMONTH(A2,0)))
>> >> >
>> >> > Regards,
>> >> > Fred
>> >> >
>> >> > "staplers" <staplers.TakeThisOut@discussions.microsoft.com> wrote in message
>> >> > news:94841A89-EA55-4ECF-8F78-1944E392DF92@microsoft.com...
>> >> > >I can fill cells of a column with all 26 recurring biweekly Mondays
>> >> > >(for
>> >> > > instance) in the year. I can fill cells of a column with all 12
>> >> > > EOMONTHs
>> >> > > in
>> >> > > the year. But I cannot do both at once. How can I create a
>> >> > > column
>> >> > > that
>> >> > > lists selected biweekly days/dates including EOMONTH for all 12
>> >> > > months?
>> >> > > 10
>> >> > > months would have two biweekly days/dates plus one EOMONTH (3
>> >> > > dates)
>> >> > > and
>> >> > > two
>> >> > > months would have three biweekly days/dates plus one EOMONTH (4
>> >> > > dates).
>> >> > > This
>> >> > > is for an Open Ended loan I am trying to track with an
>> >> > > amortization
>> >> > > plan
>> >> > > where I make 26 payments per year, every other Monday, and a Loan
>> >> > > Protection
>> >> > > Plan charge is added to my loan the last day of every month (No, I
>> >> > > don't
>> >> > > like
>> >> > > being forced to pay interest on the extra loan amount, but I
>> >> > > cannot
>> >> > > pre-pay
>> >> > > it monthly.) I want a “global” formula using defined ranges, not
>> >> > > one
>> >> > > that
>> >> > > sequences from the previous/last cell to test and fill the
>> >> > > next/succeeding
>> >> > > cell. However, anything is welcome.
>> >> > > --
>> >> > > staplers
>> >> >
>> >> >
>>
>>