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.
Get Excel *.xlsx file
Finance category
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
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 […]
Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]
This blog article explains in greater detail how to determine stock portfolio performance based on units of NAV (Net Asset […]
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
The image above shows the performance across industry groups for different date ranges, conditional formatting makes the table much easier […]
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]
This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
21 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.
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
Hi Oscar,
Is there a way to summarise the total monthly amount into one cell without having all of the days in the month listed as seperate rows? E.g i would like to only have 1 row which sums the total amount for all the days in the month for each month.
Thank you in advance
Hi Oscar,
I've been looking for this spreadsheet for a long time. It's fantastic, thanks so much.
How do I create payments that are weekly and bi-weekly too? I'd love to be able to put all my expenses into this.
Thanks again