modified amortization schedule for open ended loan with EOM LPP ch
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 |
modified amortization schedule for open ended loan with EOM LPP ch
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 |
modified amortization schedule for open ended loan with EOM LPP ch
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+1 4),A2+14,EOMONTH(A2,0))) Regards, Fred "staplers" wrote in message ... 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 |
modified amortization schedule for open ended loan with EOM LP
This doesn't work as I need. [
"=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+1 4),A2+14,EOMONTH(A2,0))) Regards, Fred "staplers" wrote in message ... 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 |
modified amortization schedule for open ended loan with EOM LP
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. [ "=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+1 4),A2+14,EOMONTH(A2,0))) Regards, Fred "staplers" wrote in message ... 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 |
modified amortization schedule for open ended loan with EOM LP
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 (copypaste special... values), and then delete the duplicate rows. Regards, Fred. "staplers" wrote in message ... 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. [ "=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+1 4),A2+14,EOMONTH(A2,0))) Regards, Fred "staplers" wrote in message ... 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 |
modified amortization schedule for open ended loan with EOM LP
This seems to work:
Whe 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 (copypaste special... values), and then delete the duplicate rows. Regards, Fred. "staplers" wrote in message ... 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. [ "=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+1 4),A2+14,EOMONTH(A2,0))) Regards, Fred "staplers" wrote in message ... 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 |
modified amortization schedule for open ended loan with EOM LP
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" wrote in message ... This seems to work: Whe 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 (copypaste special... values), and then delete the duplicate rows. Regards, Fred. "staplers" wrote in message ... 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. [ "=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+1 4),A2+14,EOMONTH(A2,0))) Regards, Fred "staplers" wrote in message ... 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 |
All times are GMT +1. The time now is 09:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com