Adjusting dates for different time periods

1. Adjusting dates for different time periods

Earlier in the course, we added dates into our amortization schedule using the EOMONTH() function, which allowed us to include the actual date when payments come due. This function works great for monthly payments, but not all loans have monthly payments. What about bi-weekly payments? Or semi-monthly payments? We will be using this section to learn how to adjust our schedule for different installment periods.

2. Review - EOMONTH() and monthly payments

As a quick recap, we'll go over the EOMONTH() function. In the EOMONTH() function, you start with a date, then provide the number of periods forward or backward as the second argument. For monthly installments, you can either use the loan date and offset by the number of periods or use the prior payment date and offset by one.

3. Bi-weekly payments - just add the days!

For cases where the next payment comes a certain number of days after the prior payment, there is no function to calculate the next day. It is simple, just add the number of days to the prior payment, or to the loan start date, and you will return your next payment date! For bi-weekly, like cell B3, add 14 days to the prior payment date - there is no fancy formula required!

4. Semi-monthly - 2 different cases!

In the case of monthly or bi-weekly payments, there was a consistent pattern for payments which can be handled by a single formula. Semi-monthly payments; however, require 2 different types of payment. The first payment of the month is always made on the 15th day of the month. The second payment is like a monthly payment and is made on the last day of the month. Unfortunately, there is no single formula which we can use to create a semi-monthly payment schedule. For this, we'll have to use an IF formula.

5. Review - IF() statements

The way that we check whether the installment is on the 15th or on the last day of the month is with an IF() formula. This formula will check a condition; which might be a value in a cell or a calculation, and see if it is true. If it is, it will return the true value; if not, it will return the false value. We'll now explore how to use the IF() statement to determine whether the payment is on the 15th or the last day of the month.

6. The semi-monthly IF() statement pattern

Now, we'll break down the IF formula for calculating semi-monthly payments. First, we check if the prior period is the last day of the month. We use an EOMONTH() formula with the zero month offset argument to provide the last day of the month. If that is equal to the actual date of the payment, then we evaluate true. In the case of a true evaluation; that is, the previous payment was the last day of the month, the 15th of the month is just 15 days after the last payment. In the case of a false evaluation where the last payment was the 15th of the month, we use the EOMONTH() function with a zero month offset to provide the last day of the month.

7. Putting all the formulas together

Finally, we take all 3 payment patterns and combine them into one long IFS() formula. Instead of checking for the words "Monthly" or "Bi-weekly", it is much easier to check against the number of annual installments which you would get from a SWITCH() formula. The number of payments should equal 12 installments for monthly, 26 for bi-weekly or 24 for semi-monthly.

8. And now... working with dates!

Now, it's time for you to make your schedule work with multiple installment periods!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.