Home budget spreadsheet with specific inputs

[CHIPDIP268]

Hi everyone! I've referenced this forum many times and it has always been helpful! But this time I have been unsuccessful. I am trying to find a budget worksheet that has an automatic bank ledger and allows for custom $$$ inputs. For example, I have a second checking account strictly for paying certain bills. The amount that goes in is fixed every Friday. I want to have a cell that I can input the weekly deposit amount, and cells for me to input vehicle payment, HELOC payment, mortgage payment and IRA contribution on certain days of each month. The caveat is I would like to be able to make these cells all adjustable so I can maximize my payments. Basically deposit every Friday, mortgage debit on the first of every month, vehicle on the 15th, HELOC on the 24th, etc. A daily bank ledger would be awesome so that I don't accidently deplete my account if I increase payments at some time. I've attached a generic start to what I was going to do but it is going to be time consuming since there are uneven days in each month and the deposit spacings are different. Hope this makes sense...... Thanks in advance!!!

[Glenn Kennedy]

Hi.

It make life much easier if everything kicks off on Jan 1st...

=SORT(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,WORKDAY.INTL($B3-1,1,"1111011")+7*(ROW($1:$54)-1),EDATE(DATE(YEAR($B$3),MONTH($B$3),$I$5),ROW($1:12)-1),EDATE(DATE(YEAR($B$3),MONTH($B$3),$I$6),ROW($1:12)-1),EDATE(DATE(YEAR($B$3),MONTH($B$3),$I$7),ROW($1:12)-1))&"</m></x>","//m"),,1)

This utilises the small lookup table and returns a column of dates, in order, corresponding to your credits and debits. If you don't mind keeping the golden shaded columns, it can be shortened to:

=SORT(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,M4#,$N$4#,$O$4#,$P$4#)&"</m></x>","//m"),,1)

... otherwise you can delete the golden cells...

Then in B2, copied across to C2, and then down:

=IFERROR(IF(COUNTIF($A$5:$A5,$A5)=1,VLOOKUP(DAY($A5),$I$5:$K$7,COLUMNS($B5:B5)+1,FALSE),NA()),J$4)

this returns the type of payment and the amount.

Finally (and easiest), this, in D2, retuns the running balance:

=D4+C5

It would also be easy to hide income and expenditure that occur in the future, should you so wish...

[Glenn Kennedy]

I should have added... that it will also work fine if you adjust the starting date to the 1st of ANY month... so 1st Feb 21.... 1st Jan 2022... will all be fine. But it will not work properly if mid-month dates are chosen as the starting point.

[CHIPDIP268]

Thank you very much! That's awesome

[CHIPDIP268]

I just tried to adjust the start date and it is going to "#NAME? " for both the start date and short version columns. Adjusting the dollar amounts also do the same... Am I changing the wrong thing? Sorry I am not very experienced with excel.

[Glenn Kennedy]

RU on the subscription verdion of O 365?

Is the SORT function available to you?

[CHIPDIP268]

Yes, I am on the sub 365. I have sort and filter at the right end of the tool bar

[Glenn Kennedy]

Are you playing on my sheet... or have you copied stuff onto yours?

[Glenn Kennedy]

Re open my sheet. Change the dollar amounts. Still OK? Now change the start date. And??

It might be to do with your odd mm/dd/yy date format that only you guys use...

[CHIPDIP268]

I directly opened your sheet. I change the dollar amount and it does the #NAME? thing.

[AliGW]

I have sort and filter at the right end of the tool bar

Glenn was talking about two new functions - SORT and FILTER - not the sorting and filtering tools on the ribbon. Which release channel are you on?

[Glenn Kennedy]

Im off for some zzzz now. If you type = SORT into a cell... does it find the SORT function?

Are you on a Mac?

[CHIPDIP268]

My apologies! I am at home and forgot that my home computer has 2019 Home and Student. I remoted into my work computer which has 365 and it works. Again, my apologies!

[CHIPDIP268]

I appreciate all the help!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Glenn Kennedy]

I should never check emails at night. I ruminate on these issues and wake up thinking about them. At least tomorrow, I can roll over and forget....

[CHIPDIP268]

Thank you all for the help.