Excel in Action
Manage Periodic Payments with an Excel Cash Calendar
Does your company need to make scheduled payments to companies that don't send invoices? This worksheet will help to plan those 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
automatically . 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 fullsize 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 closeup 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 "dmmm".
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+DateH$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+2115)/7 = 1.) And if the bill is
due on the 15th, the INT formula still returns 1. (That is, INT((1+1515)/7)
= 1.) But if the bill is due on the 14th, the INT formula returns 2.
(That is,
INT((1+2215)/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.
