Below is a list of some recurring expenses and corresponding amounts, dates and recurring intervals.

We are going to use this list and create a calendar with each expense at the correct date (and recurring dates).

Array formula in B5:

=SUM(IF(DATE(YEAR(B$4), MONTH(B$4), $A5)=IF(MONTH(DATE(YEAR(Date), MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DAY(Date)))=MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DATE(YEAR(Date), MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DAY(Date)), DATE(YEAR(Date), MONTH(Date+1)+((COLUMN($A$1:$BH$1)-1)*Recurr), 1)-1), Amount, 0)) + CTRL + SHIFT + ENTER.

Copy cell B5 and paste it down to cell B35.

Copy cell range B5:B35 and paste it to the right all the way to M5:M35.

Formula in B4:

=DATE($D$2, COLUMN(A1), 1) + ENTER.

Copy cell B4 and paste it right to cell M4.

Formula in B36:

=SUM(B5:B35) + ENTER.

Copy cell B36 and paste it to the right to cell M36.

Named ranges

Amount ($C$4:$C$11)
Date ($D$4:$D$11)
Recurr ($E$4:$E$11)
What is a named range?

Explaining array formula in cell B5

Step 1 - Calculate date in cell B5

=SUM(IF(DATE(YEAR(B$4), MONTH(B$4), $A5)=IF(MONTH(DATE(YEAR(Date), MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DAY(Date)))=MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DATE(YEAR(Date), MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DAY(Date)), DATE(YEAR(Date), MONTH(Date+1)+((COLUMN($A$1:$BH$1)-1)*Recurr), 1)-1), Amount, 0))

DATE(YEAR(B$4), MONTH(B$4), $A5)

becomes

DATE(YEAR(40179), MONTH(40179), 1) returns 40179.

Step 2 - Find expenses

=SUM(IF(DATE(YEAR(B$4), MONTH(B$4), $A5)=IF(MONTH(DATE(YEAR(Date), MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DAY(Date)))=MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DATE(YEAR(Date), MONTH(Date)+((COLUMN($A$1:$BH$1)-1)*Recurr), DAY(Date)), DATE(YEAR(Date), MONTH(Date+1)+((COLUMN($A$1:$BH$1)-1)*Recurr), 1)-1), Amount, 0))

Download excel sample file for this tutorial.

Schedule recurring expenses in excel.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

COLUMN(reference) returns the column number of a reference

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)