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

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 […]

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 […]

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]

Sum security holdings monthly and yearly in a pivot table

Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]

Plot buy and sell points in an excel chart using two moving averages

Below is an animated gif demonstrating a stock chart with monthly prices. Two moving averages #1, #2, buy and sell […]

Compare your stock portfolio with S&P500 in Excel

By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]

Add buy and sell points to a stock chart

It is easy to create a stock chart in excel. In this post I am going to describe how to […]

Follow stock market trends – Moving Average

In my previous post I described how to build a dynamic stock chart that lets you easily adjust the date […]

### 12 Responses to “Schedule recurring expenses in a calendar in excel (Personal Finance)”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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?

Thank You!

I Want to Know how to set end date for individual expenses.