Excel Tips and Tricks - Calculate the next period for your payment

MS Excel users often need to calculate a series of payments that occur at regular intervals based on specific dates.

This spreadsheet can help Excel users calculate series of periodic payments based on dates, such as mortgage periods, pay periods, and more, for various frequencies including weekly, bi-weekly, semi-monthly, monthly, bi-monthly, quarterly, semi-annually, and annually.

Download free DATE formulas - Periodic payments frequencies Excel template with samples of calculations by clicking the download button at the end of this article.

The EDATE formula is very useful for calculating payments that occur at annual or semi-annual, quarter, bi-monthly and monthly intervals.

The syntax is very simple: EDATE(start_date, months) and it has the following arguments:

Start_date is required. A date that represents the start date.

Months are required. The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

Payment frequency can be calculated as follows:

Paid annually =             EDATE(Start_date,12)

Paid semi-annually =  EDATE(Start_date,6)

Paid quarterly =            EDATE(Start_date,3)

Paid bi-monthly =         EDATE(Start_date,2)

Paid monthly =              EDATE(Start_date,1)

The formula for regular addition is particularly useful for calculating payments that take place at bi-weekly and weekly intervals.

Bi-Weekly and weekly payments can be calculated as follows:

Paid bi-weekly =                (Start_date+14)

Paid weekly =                      (Start_date+7)

Semi-Monthly payments formula is slightly more complicated.

The DATE formula is very useful for calculating payments that occur at semi-monthly intervals.

The syntax is very simple: DATE(Year, Month, Day)

The DATE function includes the following arguments:

1. Year – This is a required argument. The value of the year argument can include one to four digits.
2. Month – This is a required argument. It can be a positive or negative integer representing the month of the year from 1 to 12 (January to December).

• Day – This is a required argument. It can be a positive or negative integer representing the day of a month from 1 to 31.

A two-step approach is required to update the frequency of payments for semi-monthly intervals.

First - To calculate the first 15th day in the month, enter the following formula:

Paid Semi-Monthly - DATE(YEAR(Start_date),MONTH(Start_date),15*(DAY(Start_date)<>15)),2)

Second - to calculate the last day of the month, enter the formula in the next cell, then copy it down:

Paid Semi-Monthly - DATE(YEAR(Start_date),MONTH(Start_date)+1,15*(DAY(Start_date)<>15)),2)

Please download our free Excel template for the DATE formulas - Periodic payments frequencies that automatically computes your payment frequency.