4 ms·

I have my personal weekly budget spreadsheet open on my laptop at all times. I balance my checking account almost daily to know where all the money is going. I

I have my personal weekly budget spreadsheet open on my laptop at all times. I balance my checking account almost daily to know where all the money is going. I did pay for Monarch to see if it would be better and although it’s nice, Excel is still just better. One of my favorite tabs is taking a high interest loan or credit card and increasing the payment amounts (even a little) to reduce the overall impact of the interest rate, something a lot of people don’t understand. You will pry Excel from my cold dead hands.

zwarag 2y ago
While I understand the power of Excel for specific calculations like your loan payment analysis, I struggle with modeling "continuous" data like monthly budgets and yearly reviews. Coming from a database background, I know how to model recurring financial data with tables and relationships, but I'm less clear on Excel best practices for this. How do you structure your spreadsheet to handle: 1. Monthly recurring budgets/allowances 2. Year-over-year analysis 3. Category management 4. Historical tracking Do you maintain separate sheets/tabs for each time period? Use pivot tables?
- kenjackson 2y ago
  Isn’t this exactly the sort of thing spreadsheets are good for? I feel like I must be misunderstanding what you’re doing.
  - pixelatedindex 2y ago
    Maybe if you have millions of rows you might have trouble with excel. Other than that, from a data storage perspective an Excel is just a database that’s human readable.
    - danielmarkbruce 2y ago
      Not really. It generally won't have a schema. You generally can't write queries against it.
      - max51 2y ago
        > You generally can't write queries against it. Yes you can, there are a lot of different ways of doing it depending on what type of data you have and how you structured it.
  - danielmarkbruce 2y ago
    It is nice to have a schema. Once you use schemas, it's hard to think otherwise. It feels gross.
- orev 2y ago
  I suspect that your database background is causing you to think too formally about how people structure Excel data. For the vast majority, they’re not normalizing data, running queries on sheets, etc. I can easily see this as one tab per account, then pulling in specific cells on other tabs for summaries and other analysis.
- goodlinks 2y ago
  There are many ways to skin a cat, but my advice how to try it an excel way (assuming a db backgound) would be.. try dumber things, sounds stupid but you dont need rules and structure, just data :) denormalise a more often to break down the problem, the data and problem are your goal not structure (as much as db). Yes period per tab type of thing is quite common, as at some point you want to close the period and never change it. Lean into the non-standardisation to handle the real world. E.g for most of your budget its one line per item per month but this one are flexes with headcount so that has its own page, and tax is balnced in month x so ill just over type all the formulae there when the real numbers come in. Also if the model is complex try naming fields and showing the formula in a cell next to it to remind you how its calculated (if not ready using it check out "format as table" to do this for tabular data) And yes pivot the crap out of everything. There is also "add to model" which gives you powerbi type modelling in excel which can also be handy and fast. Not extensive list, and for lots of things db is better when you know how to use it.. but those are some of the "i get it" scenarios for me
- danielmarkbruce 2y ago
  Once you go down the relational path, it's really hard to get out of it (and I don't even think you should, I think you are right).
pixelatedindex 2y ago
What does Excel do here that Google Sheets doesn’t? I don’t always carry my laptop with me, so local files are kind of annoying.
- arcbyte 2y ago
  Google Sheets doesnt have Solver in the consumer version for one. In reference to your second statement though, I also dont always carry my laptop but it doesn't matter because my spreadhseets are in onedrive too, so I can close my laptop and open my phone and have the same spreadsheet with zero thought.
  - Andrex 2y ago
    What problem does Solver solve? Reading the overview, I don't really understand how it would be useful. https://support.microsoft.com/en-us/office/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040 https://support.microsoft.com/en-us/office/define-and-solve-... > For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount. I can do this without Solver.
    - max51 2y ago
      The main use of the solver is for something that definitely cannot be done without programming vba macro. You use it to find the optimal value in a cell based on the result in a different cell. You have a cell that excels will solve, a cell with the objective and constraints. For example, let's say you have a spreadsheet to calculate the capacity of a steel column based on its height and cross-section/material. If you wanted to run the calculation backward and find the height that will give you a very specific capacity, you could do it manually by playing with the height until you find it or you can let Excel do it for automatically with the solver.
- kspacewalk2 2y ago
  You can use Excel in the exact same way with Microsoft 365.
lostlogin 2y ago
> I balance my checking account Assuming that this relates to checks/cheques, are they going away in the US?
- ghaff 2y ago
  Many of us still write them semi-regularly although many are "written" by the bank. I admit that I keep my eye on my banking account online but otherwise don't pay a lot of attention day to day. Far larger amounts of cash are flowing through my credit cards than any individual direct payments.
- gosub100 2y ago
  It's very uncommon to use them for any common expenses like groceries or gas. I use them for a quarterly HOA payment, yearly CPA tax preparation, petty government fines and fees, and donations to charity. Generally things that you mail a payment for and you want a record of the payment. Most of these I could do electronically but I don't really save any time doing so because of the friction of setting up the payment.
  - baq 2y ago
    > Generally things that you mail a payment for I’m sorry but this sentence makes absolutely zero sense to my europoor brain. What do you mean when you say mail a payment and why would you need to do that. I literally lack the imagination.
    - dmd 2y ago
      The European mind cannot comprehend how primitive American consumer banking is.
    - thibautg 2y ago
      I don't understand either, but I would be genuinely interested in what "mailing a payment" means in practice. Is it like sending a check by mail or some kind of paper bank transfer?
codethief 2y ago
> One of my favorite tabs is taking a high interest loan or credit card and increasing the payment amounts (even a little) to reduce the overall impact of the interest rate, something a lot of people don’t understand. Looks like I belong to those people that don't understand: What "payment amounts" are you increasing?
- jayd16 2y ago
  Presumably they're talking about paying down more of the principle sooner, lowering the interest amounts.
  - ChicagoDave 2y ago
    Take any loan/credit and stretch it out until it’s paid off under the current parameters. You can calculate total interest paid. Now add an extra column to pay extra (against the principal). Now calculate total interest paid. Save this separately. Now the fun part. Go back to the original and adjust the interest rate until the total interest paid is the same as the adjusted version. You just found a way to reduce your interest “rate”.
    - abenga 2y ago
      There is the "debt is bad" viewpoint, where paying your loan earlier is seen as a good thing, which is a valid way to look at it, depending on your values. However, what you described can also be done this way: invest the extra payment amount elsewhere and get interest/capital gains on it, which gives you the lower effective interest rate and the extra liquidity as well. Or in other words, the figures on the spreadsheet need to reflect the opportunity cost of the extra payment, or the lower total interest paid is a misleading mirage.
      - ChicagoDave 2y ago
        There are definitely levels of sophistication in managing interest liabilities. If you have a mortgage at 3%, paying the principal early is wasting your extra monty. As you said, you can invest the extra and make 7% (hypothetical) and clearly that’s advantageous. My example is for people struggling with credit cards and high interest loans that don’t have investment properties.

zwarag 2y ago

While I understand the power of Excel for specific calculations like your loan payment analysis, I struggle with modeling "continuous" data like monthly budgets and yearly reviews. Coming from a database background, I know how to model recurring financial data with tables and relationships, but I'm less clear on Excel best practices for this. How do you structure your spreadsheet to handle: 1. Monthly recurring budgets/allowances 2. Year-over-year analysis 3. Category management 4. Historical tracking Do you maintain separate sheets/tabs for each time period? Use pivot tables?

kenjackson 2y ago
Isn’t this exactly the sort of thing spreadsheets are good for? I feel like I must be misunderstanding what you’re doing.
- pixelatedindex 2y ago
  Maybe if you have millions of rows you might have trouble with excel. Other than that, from a data storage perspective an Excel is just a database that’s human readable.
  - danielmarkbruce 2y ago
    Not really. It generally won't have a schema. You generally can't write queries against it.
    - max51 2y ago
      > You generally can't write queries against it. Yes you can, there are a lot of different ways of doing it depending on what type of data you have and how you structured it.
- danielmarkbruce 2y ago
  It is nice to have a schema. Once you use schemas, it's hard to think otherwise. It feels gross.
orev 2y ago
I suspect that your database background is causing you to think too formally about how people structure Excel data. For the vast majority, they’re not normalizing data, running queries on sheets, etc. I can easily see this as one tab per account, then pulling in specific cells on other tabs for summaries and other analysis.
goodlinks 2y ago
There are many ways to skin a cat, but my advice how to try it an excel way (assuming a db backgound) would be.. try dumber things, sounds stupid but you dont need rules and structure, just data :) denormalise a more often to break down the problem, the data and problem are your goal not structure (as much as db). Yes period per tab type of thing is quite common, as at some point you want to close the period and never change it. Lean into the non-standardisation to handle the real world. E.g for most of your budget its one line per item per month but this one are flexes with headcount so that has its own page, and tax is balnced in month x so ill just over type all the formulae there when the real numbers come in. Also if the model is complex try naming fields and showing the formula in a cell next to it to remind you how its calculated (if not ready using it check out "format as table" to do this for tabular data) And yes pivot the crap out of everything. There is also "add to model" which gives you powerbi type modelling in excel which can also be handy and fast. Not extensive list, and for lots of things db is better when you know how to use it.. but those are some of the "i get it" scenarios for me
danielmarkbruce 2y ago
Once you go down the relational path, it's really hard to get out of it (and I don't even think you should, I think you are right).

pixelatedindex 2y ago

What does Excel do here that Google Sheets doesn’t? I don’t always carry my laptop with me, so local files are kind of annoying.

arcbyte 2y ago
Google Sheets doesnt have Solver in the consumer version for one. In reference to your second statement though, I also dont always carry my laptop but it doesn't matter because my spreadhseets are in onedrive too, so I can close my laptop and open my phone and have the same spreadsheet with zero thought.
- Andrex 2y ago
  What problem does Solver solve? Reading the overview, I don't really understand how it would be useful. https://support.microsoft.com/en-us/office/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040 https://support.microsoft.com/en-us/office/define-and-solve-... > For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount. I can do this without Solver.
  - max51 2y ago
    The main use of the solver is for something that definitely cannot be done without programming vba macro. You use it to find the optimal value in a cell based on the result in a different cell. You have a cell that excels will solve, a cell with the objective and constraints. For example, let's say you have a spreadsheet to calculate the capacity of a steel column based on its height and cross-section/material. If you wanted to run the calculation backward and find the height that will give you a very specific capacity, you could do it manually by playing with the height until you find it or you can let Excel do it for automatically with the solver.
kspacewalk2 2y ago
You can use Excel in the exact same way with Microsoft 365.

lostlogin 2y ago

> I balance my checking account Assuming that this relates to checks/cheques, are they going away in the US?

ghaff 2y ago
Many of us still write them semi-regularly although many are "written" by the bank. I admit that I keep my eye on my banking account online but otherwise don't pay a lot of attention day to day. Far larger amounts of cash are flowing through my credit cards than any individual direct payments.
gosub100 2y ago
It's very uncommon to use them for any common expenses like groceries or gas. I use them for a quarterly HOA payment, yearly CPA tax preparation, petty government fines and fees, and donations to charity. Generally things that you mail a payment for and you want a record of the payment. Most of these I could do electronically but I don't really save any time doing so because of the friction of setting up the payment.
- baq 2y ago
  > Generally things that you mail a payment for I’m sorry but this sentence makes absolutely zero sense to my europoor brain. What do you mean when you say mail a payment and why would you need to do that. I literally lack the imagination.
  - dmd 2y ago
    The European mind cannot comprehend how primitive American consumer banking is.
  - thibautg 2y ago
    I don't understand either, but I would be genuinely interested in what "mailing a payment" means in practice. Is it like sending a check by mail or some kind of paper bank transfer?

codethief 2y ago

> One of my favorite tabs is taking a high interest loan or credit card and increasing the payment amounts (even a little) to reduce the overall impact of the interest rate, something a lot of people don’t understand. Looks like I belong to those people that don't understand: What "payment amounts" are you increasing?

jayd16 2y ago
Presumably they're talking about paying down more of the principle sooner, lowering the interest amounts.
- ChicagoDave 2y ago
  Take any loan/credit and stretch it out until it’s paid off under the current parameters. You can calculate total interest paid. Now add an extra column to pay extra (against the principal). Now calculate total interest paid. Save this separately. Now the fun part. Go back to the original and adjust the interest rate until the total interest paid is the same as the adjusted version. You just found a way to reduce your interest “rate”.
  - abenga 2y ago
    There is the "debt is bad" viewpoint, where paying your loan earlier is seen as a good thing, which is a valid way to look at it, depending on your values. However, what you described can also be done this way: invest the extra payment amount elsewhere and get interest/capital gains on it, which gives you the lower effective interest rate and the extra liquidity as well. Or in other words, the figures on the spreadsheet need to reflect the opportunity cost of the extra payment, or the lower total interest paid is a misleading mirage.
    - ChicagoDave 2y ago
      There are definitely levels of sophistication in managing interest liabilities. If you have a mortgage at 3%, paying the principal early is wasting your extra monty. As you said, you can invest the extra and make 7% (hypothetical) and clearly that’s advantageous. My example is for people struggling with credit cards and high interest loans that don’t have investment properties.