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

### Category: Finance

Calculate your stock portfolio performance in excel

Track your stock investments in excel and use a web query to import current stock prices from yahoo. Setup excel […]Comments(31) Filed in category: Excel, Stock portfolio

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

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]Comments(27) Filed in category: Excel, Stock portfolio

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 […]Comments(18) Filed in category: Excel, stock chart

Comments(14) Filed in category: Excel, Finance

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 […]Comments(13) Filed in category: Excel, Stock portfolio

Tracking a stock portfolio in excel (auto update)

Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]Comments(9) Filed in category: Excel, Stock portfolio

Comments(7) Filed in category: Excel, Stock market trend

Excel udf: Import historical stock prices from yahoo

Here is how to quickly import historical stock quotes from yahoo. First you need to know the company ticker. Go […]Comments(4) Filed in category: Excel, stock chart

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 […]Comments(4) Filed in category: Excel, Finance, Pivot table

### 9 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>

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