Home >  Excel Solutions > 

An Excel Tutorial:

Manage Periodic Payments
With an Excel Cash Calendar

Does your company need to mail checks periodically to
companies that don't send invoices? This Excel worksheet helps
you to remember to pay those companies, and to plan your cash
requirements.

Most companies must write checks periodically to companies that don't send invoices. Often, their accounting systems provide little help in keeping track of these requirements.

This figure shows an Excel spreadsheet that tracks periodic cash requirements. In addition to providing information about each required payment, each of the two sections shows the week in which the payment is due.

(To see a full-size copy of this report, click on it with your mouse pointer. To return to this page, click the Back button on your browser.)

The top section handles checks that are paid on a monthly schedule. The bottom section handles checks paid on a weekly schedule.

These sections provide two benefits. First, they help you to track your weekly cash requirements more accurately. Second, they can serve as a checklist to ensure that payments are made.

Define the Range Names

Each formula in the Monthly Payments Section decides whether it's time to pay the amount specified in column G in the figure shown below. To do so, the formulas must calculate several intermediate results. You will find these easier to use if you define them as the range names that follow.

And you will find these definitions easier to enter if you use R1C1 references rather than A1 references. To use R1C1 references, choose Tools, Options. In the General tab, put a check mark by the Setting of the R1C1 reference style. Then choose OK. After you have entered the three names, remove the check mark from R1C1 reference style.

You also will find the name easier to enter if you name your worksheet Report, as I did. This allows you to enter the names as shown below. After you do so, choose Insert, Name, Define, and then enter the three names that follow.

Day    =MIN(DAY(DATE(YEAR(Report!R6C),MONTH(Report!R6C)+1,1)-1),Report!RC4)

If payment is due on the 31st of the month, it also is due on February 28, on September 30, and so on. This formula defines the name Day to be equal either to the day the payment is due (in cell H6, for example) or to the last day of the month, whichever is less.

Suppose this name is used in cell H10. This formula finds the last day of the month in cell H6 (row 6, same column), finds the day shown in cell D10 (same row, column 4), and then returns the lesser of the two.

Date    =DATE(YEAR(Report!R6C),MONTH(Report!R6C)+(Day<DAY(Report!R6C)),Day)

This formula defines Date to contain the date serial number for the next date that payment is due for the day of the month specified by Day. For example, if today is June 29, the next pay date for an invoice due on the 30th is June 30; for an invoice due on the 28th, it's July 28; and for an invoice due on the 2nd, it's July 2.

Suppose this name is used in cell  H10. This formula finds the date serial number for the year of the date in cell H6 and for the day specified by Day. If Day is less than DAY(H6), this formula adds one month to the month of cell H6; otherwise it adds 0 to the month in cell H6.

Months    =MONTH(Date)-MONTH(Report!RC1)+12*(YEAR(Date)-YEAR(Report!RC1))

Column A contains the beginning date by which to measure the passing of time. This formula defines the range name Months to contain the number of months that have passed since that date. Suppose, for example, that since August 3, 1999, an insurance payment has been due every three months. This range contains the number of months that have passed since that date.

The Monthly Payments Section

Let's back up and take a closer look at the contents of the schedule. The figure repeated below shows a close-up of the first and last few lines of the monthly section.

Column A provides the anniversary date. It tells the formulas in this schedule the beginning date on which to base the calculations for the payment schedule.

Columns B, C, and F are for information only; they don't affect the calculations. Column E tells how often to pay. When 1 appears in this column, the invoice is paid monthly; when a 2 appears, the invoice is paid every other month; and so on.

The date formulas are:

H6:   =CurDate
I6:    =7+H6
J6:    =7+I6

The range name CurDate contains the earliest date for which the schedule is calculated. The cell that contains this date can be anywhere outside the print range. Typically, I put control cells like this in a worksheet that I name Control. These formulas use the Custom format "d-mmm".

The first day formula is:

H5:    =H6

Copy it to the right as needed. Format this row of formulas as: "ddd".

The first formula in the Pay Date column is:

D8:    =DAY(A8)

Copy it down the column as needed. It is formatted as General.

The key formula is:

G8:    =(-INT(-(1+Date-H$6)/7)=1)*(MOD(Months,$E8)=0)*$G8

This is a short, but complex formula. Here's how it works:

A bill is due to be paid when two facts are true.

First, the due date must fall between the current Monday and the following Sunday.

To understand how the INT function makes this determination, suppose that today is the 15th and that the due date is the 21st. The INT formula returns 1. (That is, -INT(-1(1+21-15)/7 = 1.) And if the bill is due on the 15th, the INT formula still returns 1. (That is, -INT(-(1+15-15)/7) = 1.) But if the bill is due on the 14th, the INT formula returns 2. (That is,
-INT(-(1+22-15)/7) = 2.) Therefore, only those dates falling between this Monday and next Sunday return the value 1 for the INT formula shown.

When the complete expression (-INT(etc)=1) is stated as a logical formula, as shown, it returns the value 1 when the relationship is true and the value 0 when it's false.

Second, the payment schedule must let the bill be paid this month.

Suppose the bill should be paid every five months and that 60 months have passed since you first agreed to this arrangement. You know that you must pay again this month because 5 divides evenly into 60. In other words, 60 divided by 5 leaves a remainder of 0. Or, using the mathematical function, MOD(60,5)=0. As before, if the logical formula (MOD(etc)=0) is true, it returns the value 1; otherwise, it returns 0.

The product of the two logical statements equals 1 only if both statements are true (that is, only if both values equal 1); otherwise, the product equals 0. Therefore, multiplying these results by the payment amount will return the amount itself when both tests succeed; otherwise, the formula will return 0.

After you enter this formula, copy it to columns H, I, and J in the top section as needed. Then format the range of numbers using the Custom format "#,##0.00;;".

The Weekly Payments Section

This section, shown in the figure below, is very similar to the first section, which was shown several times above. Here, however, column D contains the day of the week on which the payment must be made. This is for information only; the formulas ignore this information. The formula is:

D34:    =A34

Format the cell as "ddd" and copy the formula down the column as needed.

The formula in columns H through J is similar to that in the first section.

H34:    =(MOD(INT((H$32-$A34)/7),$E34)=0)*$G34

This INT function determines the number of weeks that have passed since the beginning date of the payment plan. The logical formula (MOD(etc)=0) returns 1 if the value in column E divides evenly into the number of weeks that have passed; otherwise, it returns 0. Multiplying by the amount to be paid returns the payment amount when it's time to pay; otherwise, it returns 0.

Completing the Schedule

As shown in the first figure, the last line of the schedule is the grand total of all payments by week. It's merely the sum of the total weekly amounts for each section.

Although the figure schedules only three weeks of payments, you can add as many weeks as you want. To do so, insert additional columns between columns I and J, then copy the range from cell I5 through the bottom of your schedule and paste it to all columns in your schedule that are to the right of column I.