## Schedule recurring expenses in a calendar in excel (Personal Finance)

*Article updated on November 18, 2016*

Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to easily add/delete records without changing the formulas, in other words cell refs to the table are dynamic.

I am going to use this data set and create a calendar with each expense at the correct date (and recurring dates).

The data above is also dynamic, if you change the year in cell D2 formulas in cell range B5:M35 are recalculated.

**Array formula in B5:**

n-th month])), TRANSPOSE(Table1[Amount]), ""))

**How to create an array formula**

- Type the formula in the formula bar
- Press and hold CTRL + SHIFT
- Press Enter once

If you made it right the formula is now surrounded by curly brackets, like this: {=array_formula} in the formula bar.

**Copy formula**

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:**

Copy cell B4 and paste it right to cell M4.

**Formula in B36:**

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

### Explaining array formula in cell B5

*Step 1 - Calculate date in cell B5*

DATE($D$2,MONTH(B$4),$A5)

becomes

DATE(2010,1,1)

and returns 2010-1-1 (1/1/2010)

*Step 2 - Build an array of recurring dates *

EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$1000)-1)*TRANSPOSE(Table1[Recurring

n-th month]))

I can't show all dates here, there are two many. 1000 recurring dates for each date. I have to simplify, I am now using three recurring dates moving forward.

EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$**3**)-1)*TRANSPOSE(Table1[Recurring

n-th month]))

becomes

EDATE({40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205}, ({0;1;2})*{1, 3, 1, 2, 1, 1, 1, 6})

returns

{40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205;40237, 40282, 40225, 40252, 40235, 40235, 40234, 40386;40268, 40373, 40253, 40313, 40263, 40263, 40262, 40570}

**Step 3 - Check if current date is equal to any of the recurring dates and return the corresponding amount**

IF(DATE($D$2, MONTH(B$4), $A5)=EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$1000)-1)*TRANSPOSE(Table1[Recurring

n-th month])), TRANSPOSE(Table1[Amount]), "")

becomes

IF(40179={40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205;40237, 40282, 40225, 40252, 40235, 40235, 40234, 40386;40268, 40373, 40253, 40313, 40263, 40263, 40262, 40570}, TRANSPOSE(Amount), "")

becomes

IF(40179={40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205;40237, 40282, 40225, 40252, 40235, 40235, 40234, 40386;40268, 40373, 40253, 40313, 40263, 40263, 40262, 40570}, {1000, 100, 500, 30, 50, 50, 100, 500}, "")

returns

{"","","","","","","","";"","","","","","","","";"","","","","","","",""}

**Step 4 - Sum values**

SUM(IF(DATE($D$2, MONTH(B$4), $A5)=EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$1000)-1)*TRANSPOSE(Table1[Recurring

n-th month])), TRANSPOSE(Table1[Amount]), ""))

becomes

SUM({"","","","","","","","";"","","","","","","","";"","","","","","","",""})

and returns 0 (zero) in cell B5.

**Download excel sample file for this tutorial. **

schedule-recurring-expenses-in-excel.xlsx

(Excel 2007 Workbook *.xlsx)

**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)

**EDATE(**start_date,months**)**

Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

Automate net asset value (NAV) calculation on your stock portfolio

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]Excel udf: Import historical stock prices from yahoo – added features

This post describes how to import historical stock quotes from yahoo. This custom function is more advanced than the previous […]Calculate your stock portfolio performance with Net Asset Value based on units in excel

In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying […]### 11 Responses to “Schedule recurring expenses in a calendar in excel (Personal Finance)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

hello,

I need your support regarding your above material - Schedule recurring expenses in a calendar.

I tried to use your formulas in LibreOffice 3.4.4(linux) and into any others OpenOffice versions but it doesn't work. It give me next errors - error 508 or #value! .

What do I have to do to convert your excel formulas into libreoffice format,please?!

Thank you in advance for your answers and helping!

Hi,

This is great and incredibly helpful!

Is there a way to modify this to schedule not just monthly, but weekly/bi-weekly/daily expenses as well? (specifically bi-weekly)

I have been looking at this as well as the post on creating a weekly schedule, but can't seem to figure it out.

Many thanks!

There seems to be an error in the Dec 31 calculation. Why is it showing $2330 when the recurring for the 31st should only be $1000? Can't seem to find where it is getting the extra amount from.

Deb,

You are right and I don't know why. I made a new smaller array formula, this one seems to work as intended.

Hi,

There seems to be a problem with values entered for the 1st of the month repeating for the last day of the month.

thanks,

Billy

I didn't understand why COLUMN($A$1:$BH$1)

Why reference to $BH$1?

Only formula COLUMN($A$1:$BH$1) gives result 1.

Nenad Deusic

I made a much smaller array formula and added a complete explanation, see article again.

Thank you for telling me.

Can you help ne with this one

=SUM(IF(DATE($d$2,MONTH(b$4), $a5)=ENDATE(TRANSPOSE Table3"DATE"),(row($45:$1000)-1)*TRANSPOSE(Table,3"recurring n-th month")),TRANSPOSE(Table3"amount")''''))

I have tried changes the prentices and quotation marks around.

Charles,

I am not sure what you are trying to do, I can't see your workbook.

If you are not familiar with excel tables read this post:

Excel Tables

Oscar,

Why do you create an array of 1000 recurring dates for each date? Since your calendar is only a year long, even if there was a bill each day of the year, that would only require 365?

Hi Oscar,

Great article!

I believe this knowledge is helpful especially in Finance (for recurring payment) and Maintenance (recurring preventive maintenance) industry.

I just need to ask one thing. Based on my understanding from your method, I tried to manipulate the template a bit so that the expense(or in my case, tasks) is in the left side, with the schedule at the right side (instead of days at the y axis).

I'm trying to generate forecast of recurring maintenance activity on monthly basis instead of daily basis. However, I failed where the range of the first set of array data does not match range of second set of array data in sequence. For example, using date formula, with Month in x axis, data set is {Jan 2017, Feb 2017, ...Dec 2017}. However, using edate formula (with transpose) the data set starts with whatever date the first occurrence will be, such as {March 2017, ...}

Is there any workaround for this formula so that data set with edate formula will return TRUE or any number as long as it is contained within second data set, regardless of its sequence?