Adjusting dates for different time periods

Loan Amortization in Google Sheets

Brent Allen

Instructor

Review - EOMONTH() and monthly payments

The EOMONTH() function

=EOMONTH(date, number of months)

Monthly Payment

=EOMONTH(loan date, periods since start of loan) or

=EOMONTH(prior payment, 1)

Bi-weekly payments - just add the days!

No function exists to add days to a prior date.
From a date, add days to get to the next date.
For a bi-weekly calculation, add 14 days to get to the next date.

Adding 14 Days to a date

Semi-monthly - 2 different cases!

Consistent payments on monthly or bi-weekly with single formula, not on semi-monthly.
Payments occur on either:
- The 15th of the month
- The last day of the month.
No single formula for semi-monthly payments!

Semi-monthly payment schedule

Review - IF() statements

Checks if one value in a cell or formula is equal to another value.

=IF(logical_expression, value_if_true, value_if_false)

If formula example

Returns the true value if the expressions are equal.
Returns the false value if the expressions are not equal.

The semi-monthly IF() statement pattern

Prior payment is the last day of the month:
- Current payment is the 15th.
Prior payment is not on the last day of the month
- Current payment must be on the last day of the month.

Formula for cell B6
=IF(EOMONTH(B5,0) = B5, B5+15, EOMONTH(B5,0))

Example of semi-monthly formula pattern

Putting all the formulas together

=IFS(monthly, biweekly, semi-monthly)

Monthly Payments
=IF(number_of_payments = 12, EOMONTH(prior_installment_date,1))

Bi-Weekly Payments
=IF(number_of_payments = 26, prior_installment_date + 14)

Semi-monthly Payments

=IF(number of payments = 24,
        IF(EOMONTH(prior_installment_date,0) = prior_installment_date, 
            prior_installment_date+15, EOMONTH(prior_installment_date,0)
           )
    )

And now... working with dates!

Loan Amortization in Google Sheets