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
Schedule-recurring-expenses-in-excel-2
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 […]
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