Dec 24 2023 09:47 AM
I am working on an excel budget workbook. I have no idea if a formula like this is possible! It sounds so complex to me...
I have an "Expenses" sheet where in one column "D" I enter an expense. I then have column "F" which is a drop down list that lets me select the bank account that is being used to pay this expense. Then column "G" where I select, if needed, a debt account that I want to reduce.
For example, I enter in column D, $200. I want to pay those $200 from my "Checking" account that I selected in column F. This will also reduce my "Student Loan" Debt by $200.
Then I have a "Net Worth Tracker" sheet where these drop down accounts are found.
I have the following bank accounts in column C7-C11 :
Checking |
Savings |
Money for Shoes |
Fun money |
Checking 3 |
And The following loan accounts in C34-38:
Student Loan |
Car Loan |
Home Loan |
Shoe Loan |
Loan 4 |
In column D of the net worth sheet Is where I have the cell that I want to change, based on the amount I enter in column D of my "Expenses" Sheet. So for example, Cell D7 of net worth tracker sheet corresponds to the amount in my checking account. I want it to reduce by 200 because of my entry. Cell D34 corresponds to the student loan number that I want changed, and I would like it to reduce by 200.
Other things to keep in mind:
If I ever select the "Checking" option, that amount should reduce, if I select, "Money for Shoes" that amount should reduce. If "Car Loan" is selected, that amount should change... and so on for my various accounts.
I would like an option to set a starting balance for each of these accounts. So for example, I want to set my checking account to start at $600, and the amount will reduce based on my input in the Expenses sheet. And student loan to be -$6000 (negative because I owe it), and because I am paying it off, the negative number should be trending toward positive.
I would like each following row in the expenses sheet that the accounts take into consideration to always be cumulative. For example, in D8 I entered $200 as my expense, paid through my checking account. Then D9 I expensed $100. D10 was $50 and so on. So in total the checking account should reduce by 350. And so on... The rows in the expense page can go for an unlimited time.
I would like to be able to easily add or remove accounts from the formula. Say for example I get a second shoe loan. How would I add that into the formula.
The bank account and debt account do not need to correspond to each other. For example I could set the bank account "savings", but no loan is needed. Say I had a family emergency and needed to use my savings account money. There should be no need to attach a debt/loan account. Or in the reverse. Say I was gifted a one time use gift card. I won't have a bank account for that so I don't need to select a drop down banking account option. But I used it to pay off my loan.
Let me know if you need more information or if I should rephrase something that made no sense. This is far too complex for me and I am not sure where to start looking for the proper formula! Thank you
Dec 25 2023 07:33 AM
This may be easier than you expected. (Of course, with any financial tracking, accurate recordkeeping is important.) In the attached workbook, I created two sets of formulas (actually, four sets, as I describe later) to do those calculations — one set for the deposit (bank) accounts, and one set for the loan accounts.
On the Net Worth Tracker worksheet, each row in one section (deposit accounts or loans) uses a similar formula — so similar that it can be copied to rows below it. Each of those formulas checks cells in column F of its row to retrieve the initial balance, and column C of its row to find the account name (which must be unique).
The primary effort in the calculations is accumulating the "Expense Amt" values, but for only the rows that have a matching deposit account name or loan name. I used Excel's built-in function SUMIFS to accomplish this. (You might find this description more understandable.) SUMIFS is supported in most versions of Excel since version 2010.
So adding a deposit account or loan is as easy as:
Now, the reason for the second two sets of formulas — in column E — of that worksheet is that not all transactions are instantaneous. When you write a check, you probably are mailing it to the loan processor. And after they receive it, it will take time for them to present the check to your bank, for the bank to transfer funds accordingly, and for the loan processor to credit the payment to your loan. So I recommend including data for the Transaction Cleared Date on the Expenses worksheet, which is then used in formulas for Balance (cleared) on the other worksheet. Of course, if you place those data in other columns, your column E formulas will be different.
Side note: The dollar signs inside these formulas are not required, but are helpful if you decide to copy existing formulas to a new column on that worksheet. The dollar sign is used in "mixed addressing" (as here) and "absolute addressing". (This is how Microsoft describes relative addressing (the default) vs. absolute addressing vs. mixed addressing: Switch between relative, absolute, and mixed references )
Data columns for Check Date / Deposit Date data and Description / Note data are optional, but you will likely find them useful.
Dec 25 2023 04:35 PM
working on an excel budget workbook. I have no idea if a formula like this is possible
I guess you need an online form to append new records.
Then you can download the datas to Excel for further analysis. You can use formular,PQ or sql.
By the way,it is easy to summary or analysis if you save both deposit/balance and expenses in one sheet rather than split to several sheets.
Dec 26 2023 10:57 PM
Hi There!
I appreciate your response! I tried to apply your formulas to my sheet but I was not able to get it to work. I also tried to get the formula to work whenever I made changes to the "income" sheet. So that for example, I could get my paycheck from work, and select for that to automatically go into my checking. Or maybe that month I decide to portion a larger amount into my savings.
I am attaching the excel worksheet here and maybe you will be able to see my dilemma! I did not make this sheet, I found it through vast googling and unfortunately I am rather low level in excel skills.
Dec 28 2023 12:51 AM
The workbook that you chose takes up a surprising amount of disk space (1.28 MB) for something that is essentially empty. I see that someone has pre-allocated over 10,000 rows for an Excel table in the Expenses sheet, and similarly for the Income sheet. That seems unnecessary. Still, that does not explain the size.
Oh, I see that this workbook has many hidden worksheets. Did you know of these? You can right-click any worksheet tab and click "Unhide…" to see a dialog listing these (and unhide any that you wish). I unhid the Instructions worksheet.
Based on the web site of the original author ("a Canadian working in the finance industry"), I gather that someone put pieces of the original author's work together (at least two separate workbooks). But that integration is incomplete.
So, on to your problem…
I started by making a copy of the Net Worth Tracker sheet so that you can see any differences. (On the Expenses sheet and the Income sheet, the only changes I made were to their column widths.)
My first edit was to "unhide" the value in cell D7 by changing the font color to Automatic. The before-edits sheet has some odd color setting that hid the value (whether in normal display or with selected-range highlighting).
My second edit was to correct the formula, specifically the fourth parameter. You had that as Expenses!$C:$C. But since that parameter is part of the criterion that selects transactions (rows) after the Initial date, this needs to refer to your column with the (check) dates, which is column B. (Your posted description had not even mentioned dates.) The resulting formula is:
=$C88 - SUMIFS( Expenses!$D:$D, Expenses!$F:$F, $C7, Expenses!$B:$B, ">=" & $D88 )
So now that formula in D7 is corrected, you might expect to see a value other than $500 displayed in that cell, because of the mortgage payment on the Expenses sheet, but the cell displays $500. If that is your expectation, the problem is in your data. I remind you again: Accurate recordkeeping is important. You have an "Initial Balance" for checking of $500 as of 12/15/2023, but the date of the mortgage payment (row 9 on the Expenses sheet) is before that: 12/08/2023.
Which brings us to the topic of dates. You did not describe that you intend to have a separate column of tracked values for each month (or possibly other interval). So the current-value formulas need to be different, both for different date selection and because you will presumably want to include values from the Income sheet (which you also did not mention in the original post).
But I don't really want to provide a set of replacement formulas until you have decided on a consistent set of dates for row 3. (Even the original author has inconsistent values in that row.) What tracking date do you intend: the start of the month (as in cell D3) or the end of the month (as in cells E3 through AA3)? Intermediate values (such as every/every other Friday, or the 15th day of the month plus the last day of the month) are also possible.
Regardless of which tracking dates you want to define, if you are able to use the same Initial Date for all your existing accounts, you could skip the use of cells down around row 88 and instead put the single Initial Date into cell D3, use (the numbered rows of) column D to contain those initial monetary amounts, and put current-value formulas only in (the numbered rows of) columns E and later. I recommend this.
Dec 28 2023 03:08 PM - edited Dec 28 2023 03:18 PM
Thank you for the wonderful post! I was not aware that there were hidden sheets!! Also, I did not have any data yet because I wanted to wait to use the document until I had full integration between the net worth and other sheets.
Using your advice I did a few things in the (with edits) sheet
I did edit in the net worth worksheet, I intend to use the last of the month consistently to track the net worth. So I changed the initial date on column D to be 11/30/2023. Then 12/31/23 for column e, 01/31/24 On column F, and so on...
Dec 30 2023 07:19 PM
@J_doe Yes, including fake data is entirely appropriate.
Your formula in E7 was incorrect, as you included the starting value ($D7) twice. The attached workbook corrects that. And I have hidden the Net Worth Worth Tracker (before edits worksheet, and shortened the name of the newer version. When you are satisfied that you don't need it, unhide and then delete that older version.
Your formulas in (column E of) the Debt section were technically valid, but with an unnecessary negative sign, subtraction (rather than addition), and multiplication by -1. My formulas in that section simply add (positive) payment amounts to (negative) balances.
OK, to compute values for any month after the starting date, there are at least two ways to do the calculations. Let's go with the following:
Each column E formula starts with prior-month values in column D and adjusts for changes from Expenses and Income transactions that occur after the month for the prior column (in D3), but on or before the last day of the month for the current column (in E3). So each SUMIFS now does two date comparisons.
If you have Excel 2021 or later, the LET function can make the formulas easier to follow. I have written formulas using the LET function in column F; for example, the formula in F7:
=LET( prior_bal, E7,
outgo_during_month, SUMIFS(Expenses!$D:$D, Expenses!$F:$F, $C7, Expenses!$B:$B,">" & E$3, Expenses!$B:$B,"<=" & F$3),
income_during_month, SUMIFS(Income!$D:$D, Income!$F:$F, $C7, Income!$B:$B,">" & E$3, Income!$B:$B,"<=" & F$3),
transfer_in_during_month, SUMIFS(Expenses!$D:$D, Expenses!$H:$H, $C7, Expenses!$B:$B,">" & E$3, Expenses!$B:$B,"<=" & F$3),
prior_bal - outgo_during_month + income_during_month + transfer_in_during_month
)
requires less reading to understand than its neighbor in column E:
=D7
- SUMIFS(Expenses!$D:$D, Expenses!$F:$F, $C7, Expenses!$B:$B,">" & D$3, Expenses!$B:$B,"<=" & E$3)
+ SUMIFS(Income!$D:$D, Income!$F:$F, $C7, Income!$B:$B,">" & D$3, Income!$B:$B,"<=" & E$3)
+ SUMIFS(Expenses!$D:$D, Expenses!$H:$H, $C7, Expenses!$B:$B,">" & D$3, Expenses!$B:$B,"<=" & E$3)
If your version of Excel supports LET, then depending on your preference, you can copy either of those two columns (below the date headers) — without manual change — to the right (or to the left, as far as column E).
Note that for these formulas to work, I needed to use absolute references (i.e., prefixing the column letters and row numbers, if present, with $) for references to columns in other sheets, and conversely use relative references (without the $) for references to the cells containing the prior-month's balance.
BTW, I prefer the term "Deposit Accounts" to the original author's usage of the term "Bank Accounts". A bank account could be a deposit account or a loan account or even an investment account. And the deposit account might be with a credit union rather than with a bank. And note that other types of deposit account could include the proverbial Cookie Jar or Shoe Box or Stash in the Mattress. (No, I'm not recommending those, at least not for any substantial amounts.)