itsmekarak
New Member
- Joined
- Sep 29, 2014
- Messages
- 30
Hello,
We just signed on an equipment note for 5 years with 8 payments each year followed by a 4 month sabbatical. I made a change to the amortization schedule excel template we always use which only allowed for 12 consecutive months of payments. I think I have all but the date functions figured out.
If you look at the following spreadsheet, you will see that cell B36 references the date in cell D16. The following cell, B37, then references the cell above it with an "IF" function stating if it's a certain date then add 30 days, however, if it's a date that falls in the sabbatical period (referenced in D9), then add 121 days (4 months) to the sabbatical date referenced in D9.
The formula only works partially, as I have to change the less than symbol to the greater than symbol in the first cell in which the sabbatical period would fall (see B35 vs. B36 vs. B37). Even though the spreadsheet may work for this particular note, it's my goal is to find a template we can use for any date variation. The minute the date in the 1st payment due cell (D8), the 1st Sabbatical Month (D9) or # of Sabbatical Months (D10) changes, then it won't work. Why? Because of the change from less than to greater than the first sabbatical month requires.
Can anyone help me to improve or replace the formulas in the Payment Date column to allow for date changes?
Thank you in advance for your help! - Kara
We just signed on an equipment note for 5 years with 8 payments each year followed by a 4 month sabbatical. I made a change to the amortization schedule excel template we always use which only allowed for 12 consecutive months of payments. I think I have all but the date functions figured out.
If you look at the following spreadsheet, you will see that cell B36 references the date in cell D16. The following cell, B37, then references the cell above it with an "IF" function stating if it's a certain date then add 30 days, however, if it's a date that falls in the sabbatical period (referenced in D9), then add 121 days (4 months) to the sabbatical date referenced in D9.
The formula only works partially, as I have to change the less than symbol to the greater than symbol in the first cell in which the sabbatical period would fall (see B35 vs. B36 vs. B37). Even though the spreadsheet may work for this particular note, it's my goal is to find a template we can use for any date variation. The minute the date in the 1st payment due cell (D8), the 1st Sabbatical Month (D9) or # of Sabbatical Months (D10) changes, then it won't work. Why? Because of the change from less than to greater than the first sabbatical month requires.
Can anyone help me to improve or replace the formulas in the Payment Date column to allow for date changes?
Thank you in advance for your help! - Kara
