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

**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 *.xlsx file**

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

How to calculate totals of stock transactions based on dates

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

Plot buy and sell points in an Excel Chart based on two moving averages

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]

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

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

The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]

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

### 19 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.

I have been searching for a way to incorporate a monthly calendar that is continuous for recurring bills which gets data from an excel spreadsheet of monthly bills. So I already have a spreadsheet with my monthly bills. I just want to add a calendar that populates with the spreadsheet information, to put it another way. Is there anything available for Excel that does this? Thank you to anyone who might be able to help!

Hi,

I am still learning excel, and hopefully you will still get a notification for this since it is about 3 years old now; but I am having the values from the 1st show up on the 31st again. How do I change that? Thank you

Hannah,

you are right. Thanks for telling me.

I have changed the formula in the article and uploaded a new file.

Hi Oscar,

Love the spreadsheet! Is there a way to generate the calendar for 2020 and beyond?

Go to 'Calendar' tab and select cell 'D2'

Go to the data tab at the top and hit data validation, and change 'allow' from 'list' to 'any value'.

This is epic, only I can't figure out how to make weekly or bi-weekly payments work on this one properly. They multiply to the right monthly sum, but rather spread out over 4 payments they are together as one.

Hi,

I’ve added a lot of lines of expenses to the first tab and at first the amounts were showing x1000 on the calendar for example 1000 will show as 1000000 so I adjusted the formula to divide by 1000 but now it’s showing random amounts on the calendar.

One thing to note is that I’m using vlookups to upload the expanses to the table. And I’ve also adjusted the data validation on both sheets.

Any idea for what I should different?

Thanks in advance