I made a spreadsheet with automated formulas to pull data from mint and my credit card to track income, taxes, business deductions, expenses budget and forecasting. Super excited at how sophisticated it is. It easily beats Mint.com’s budget functionality. Well, probably because I made it customized for my needs.
Machine Transcript of Video
all right so Teresa form where a 1/2 centralized spreadsheet where I sent you his everything that’s like money related so they core aspect of this pressure is this tap which in which I record in which I record what every time I do work I used to fill form and yeah used to to record the stuff how many hours I spent working for which organization so that’s that’s that now I have is a tab in which eyeballs right how much and how much I expect to earn this year and previous years on a payment by payment be so I have to classified into
so what I got so far and what I expect to get and then this is like in the in between transition stage where I feel that I’m just waiting for the payment and then this one more which I called pending which doesn’t show up here which is maybe and maybe I wont Idle No right now
anyway so this that sold under 10k now I wish you’d have built this to keep track of my deductions I calculate my taxes so so this all the categories that potentially might be tax deductible from what I read so far and I attractive test call deduction shorten it to this right now so this is where last year I was tracking
what I do now is I download my credit card I started using one of my credit cards which is Amazon point it’s a car focus on Amazon Points I’m getting points when you make I want some purchases and I just I’m going to be downloading from now so I start using this to centralize anything that might potentially be deductible and everything is going through that through this credit card or there’s the office rent which I just tracked manually
and then cures for a copy and paste with actual credit card records and then what I do is I do something similar to what means that which is where is it I ripped I have a formula read through this and then shorten it because sometimes our senses especially Amazon does this which is a bunch of details that are related that are that tried to describe the purchase I guess you have to order number or something that just creates too many things I need to do to make it down to the first 11 letters and that gives me this kindness and I copied and pasted a transaction and then I run a duplicate removal based on the 11th digit operation 1111 vision aggravation and then give you the more human readable name
it’s like this this is house now. So far this Amazon and all of this variations are Amazon as well and then I sent him a category this amounts are Jesse or just so I can look at it I have to go back to the original form list
what happens here is we have a vlookup going on here so what this will do is it will it will shorten this to 11 digits 11 11 letters and then we’ll compare that against against this list and then refinance a match it will bring over this the name part and then in the next column it will bring over the category part
now the category has are additional provision which I had it because sometimes I just like make this one time purchase order distinguishable
categorized as Food Wellness
the equipment I-80 Fulton MS Auto Supply in buying that sometimes equipment and I wanted to make sense so whenever I I know that I made this purchasing some equipment I manually override it everything is here directly I already here because just in case I might need to like refresh this whole list which probably will not happen so they are the mythology right now is I download special
this is the export data that the credit card company provides I’m the only bad thing is that a sudden descending or they’re so every time I ask if I just use this asked is I only feel like at on top and that doesn’t work very well with this format so what I do is I should I I don’t know but what I do is I start my. What state
prostate and then yeah I’m just copy and paste I’ve been face from here from here on I just ignore a transaction date and that goes here so we have to vendor and the category I’m just a little more just to help jog my memory when I like we’re doing this either how are you and I and this Joshua’s tanglish dismiss automatic system from the Emmanuel system or write the sinks manually
no amount the way it works is when I write these things manual I just write positive numbers for the credit card company shows me everything is in the negative numbers so I reverse that who is if the category is a utility then I will put one third of it because some options based because I’m working at home I can’t claim a deduction for the amount that I’m using for my for the work aspect of the expense so for rent and utilities you can adopt the square footage of a kind of rough roughly average to 1/3 so that’s what’s here so round cluster for utility and also reverse like x -1 and reverse the number
snow sound like when I claiming my point rewards as cash back and I don’t think that don’t qualify and got out of there by mistake here this this category I think that’s one of the vendors that Lucas Automotive
Hyundai automatic detection but they manually category and the fact that we make it count towards all the other purposes let’s see here is there something like sometimes there’s like there’s some accredits because I returned the equipment so that goes back and it takes away from that amount so they’re sold at going on there so this is a people table here to draw how to draw it out now it will only count when the category is not empty the car needs to be not empty if is empty it will not get counted and that’s how I ignore likes things like this which is a credit card payment seems like that
this to work health insurance is supposed to be deductible but what am I supposed to say when doing my last year’s taxes they said you’re not this is not enough to qualify it doesn’t cross the threshold to qualify for adoption in Utah spend more so I’m just in case
maybe, compiled a medical expenses so I’m keeping track of that Nation
directions to maybe go see ghosts
that
Pluto’s Calvados three categories and that’s this Estes okay now I have this field continue skip track of when was the last time I updated this from the credit card records and the way I do it is
this this this function called are Mac’s so it was it was just scan the entire row entire row and be like you switch the biggest failure
it will be the latest entry to April 16th at the bottom of the road is what gets pulled into this thing and then and I have prepared just in case one which I think is going to be looking at this form again in 2022 to do my final report and then he’s going to get messed up because I’m using is for honor of formula so I needed to stay with him 20 21 it can’t be 20 22 so if if they date is beyond 2021 just accepted value of December 31st otherwise set the last date in the credit card transaction section hello what are used to stage four is I look at this table table
so that’s it
I look at this table table yes look at this table table what is formula last is first there’s the match formula
where in this area in this region is the word grand tour of just picking the grandfather of Soul because you know it’s very busy if I add calories to my like to this list if I had categories then this table rows can I move up and down and The Grand Tour position to keep changing
find the position of the grand total annual say okay it’s one two three four five six seven eight nine 10 11 is on a roll number 11 style from the top so that’s what these returns it returns a value of 11 and then this index formula or so we got a 11 so I want you to look at this region actually no it doesn’t need to be k330 63 through 15 through f330 16 how to use his formula so what he says is okay find this area in this area
give me the value of the Roll number 11 and Justice roll number 11 on column number to so it goes down and then it goes down again okay it’s roll number 11
that’s how I always will this formula was always find the position of the grand total regardless of whether it’s shorter or longer
do you have a lot of requests for me to like classify Monday this category as possible in Manhattan next year so anyway and it doesn’t seem more interesting debate Thompson
how many days have passed since January 1st
bass in Chinese versus how many days are between Bi-State April 16th which is the last day out there the credit card which is what this what these values reflect and
January 1st and you be like I don’t know like a hundred five days I think that’s what it was roughly like and then it’ll be like okay so 105 days what’s the proportion of 105 days to 365 30% or 29% okay no understanding at 29% is this assuming that 29% is the 4600 so tell me how much it will be at the end of the year if this trend continues
Samuel we have spent money over there 29% of the year now we have spent money for 1/3 of the year now if the trend continues how much will you have spent for the entirety of the year
4 * 3 *
I just multiply these 4603
i3 that’s correct
yeah I like that by three and that’s how much I expect to have spent over the year if no other information is provided I try to do this I’ll be more smart or dumb or smart play bachata like there’s some expenses that I know have only happening once there was a repeating so I could try to incorporate that but I try to do it last year and they’re so expensive that you expect the average yourself if you ignore the fact that have expenses are only the beginning of the end of the year to which. Generally averages how so I just decided to not do any of that work and just do this very basic simple arithmetic projection
equal distribution of the expenses over the course of the year
sex is a pretty good sample of one month probably is not a good sample so I thought the one math portion this might be wildly inaccurate at 3 months so that’s the reason is different from 14000 which is here at 16,000 is because it’s 29% or 33% that’s what makes the diff distance so he was protecting and of course ultimate exchange at the continue to talk to the credit card expenses over the course of the year
so we just pick it up completely here
this is going to be the amount used to calculate the taxes no we move over 3 section I’m so now I know that my federal tax and state tax also works is there a different tax tiers for different income brackets so I know that my income is going to fluctuate somewhere between these two figures now sometimes in Mike 1186 last year it was already 6 so I need to take into account this this year this year and this year now at least here I will always have fulfilled so I just have I just added everything
Martin comes about 40 and under 86th at federal tax rate is 22% so Sky could I hear what their status is if my income is
income is above what is the snowiest it says if my income is about 86 and yes X and if it’s under that multiply different see I guess I need to do it this way so it just accounts how much of my income is under 86th and 40000 a year and calculate pi * 8.22
this yes wait actually this might get messed I need to fix the formula weight if my income goes below 86 this formula is going to break because it’s going to give a return my negative number I need to fix it eventually but let’s leave it for now how how can I fix this I think there’s something called absolute value
I need to look like Roundup to Sierra absolute will just give me at 2
okay I’ll
so if
Less Than Zero but if if it’s serious fine by this is less than 0 then its return 0 and otherwise
and otherwise calculator
calculate the bracket between these two ranges it will take her this range i x 24 unless this amount and then Social Security tax which is a fixed-rate 15.3 + 12 x 1,000 x / for its quarterly payments I overpaid my federal taxes in 2020 so there’s that and
I’m going to manually fill this out
do this this this area but eventually I’m going to have this whole area is automatically I haven’t done.
so there’s that and then
SML the actually paid like a week ago and I back then I guess I missed deductions change Taipei I’ll leave it on there so it’s like what these formula that says it goes okay how much have you paid so far and okay that’s how much paid and I have a little column
how many payments are remaining so it counts how many cells in that area have a value in it which is 1 + 4 – 1
and then this formula says I need to pay this amount you paid me so much and how much do you have left so you divide it and you got talent
I copied this amount here and there isn’t it already changes because I think I counted my deductions twice utilities I counted twice so I had that I think so I need to update this so which is just copy and paste
all right now I’m the same thing for the California tax between 57 and 2095 it will always be between these two ranges hopefully I hope you have a lesson 57 and I just calculate the amount they carry over and how much I have left and here I just didn’t bother calculating outside okay I overpay a punk cuz I misunderstood that text so I thought that was very low maybe it’s a quarterly tax so I paid 4 times of it so that’s why I work so much last year so I don’t have to pay tax until January of next year and just $2,000 so that’s how it is and bring it over oh I guess it didn’t have to be this way but sure if two figures just bring over the US from that calculation that I’ve been earlier and just for me just for I don’t know for a sense of what’s what’s going on
this tax rate calculator this amount against I guess it taxable income before or after deductions
because it makes the tax rate very high but I did not get some business expenses and no that’s not what I would call but I make this is what I do is make and then the discriminant so this is a weird favor
and then this comparison against at the gross income and this is just take it as how much I have left over so I’m paying 33% taxes okay so origin I built this to calculate taxes because last year I need to start paying taxes it’s me texting you for that sells my first time I was paying estimated taxes and you’re super confusing and this special super messy but now it’s
alright so something I’d be up for yesterday I finished now it’s okay I can’t stop here this
and how much I’m actually spending and how am I budget is doing versus expenses so I’ve been using mint to track my expenses for a long time now I don’t know five years a couple years
and this is what mint gives me
so this is how much I spend this year
32
so I’m just coming down with us to see if we file so
I need to file that mean generous K and so we have something going on here now I try to keep track of me and uses something else in your system to to what I do and it is a bit less sophisticated than my system because it it does a good job of detecting similar-sounding merchants but the problem they have probably be difficult for me classic cannot establish a rule after classifying ruled the if it comes to Marley’s credit card and then
so in that regard this is time I have is much better and tracking business expenses I love this business for Amazon is chest shopping I love this we going to personal shopping category as sometimes I corrected and I’ll be like I don’t know if I should be doing this is kind of dumb and some point and with this system I’ve been able to continue to categorize as actively I was some kind of your side my anyway
track of everything you put expenses that you put directly on a checking account everything so what I have going on here is
I feel a budget estimate so I had to build this table and I feel this amount
business expenses medical expenses utilities I’m keeping I’m like keeping life track of them here so I miss is 100% tax rate
let’s use this figures / protected by the yearly and divided by 12 so this figure is predictive is the yearly
this for me. That’s just calculus that for utility * 3 because he has been divided by 3
business medical insurance I cooked but I know the exact amount of the health insurance premiums the other figures I looked at how much I’m typically spending which is what means provides tells me how much I’m General spending so I just figured there and then there’s some amounts that lopsided towards the beginning of the year such a shopping shopping is proving to be lost sight of you near the end of the year
the end of the year like like this so I took that into account and so that at set to $65,000 yes
and if I owe taxes which come straight from here just asked is to F and then I’m like okay so if I spend 6555 76 but I’m making 73000 then I’m going to have 8000 leftover so that’s what is calculated by 12 area Stella formula calculus automatic a reminder to not touch and like not manual override please because I sanitize my forgotten I override it without noticing I know only over rare only update this area right now for business expenses
overlap with living expenses so for example rent it’s included here because his office rent but I already have a category for rent here at least doesn’t exclude the office rent so so I create it yet another table table that has like these two are business expenses are not shared with living cost so I took out the rent and I took out the utilities and that’s what this is so so this is all the formula going on to calculate a percentage of that and here’s some percentage you just help me keep a general sense of how much is in spending so I’ve been hearing that your rent and I need should be ideally less than a quarter of what you may so versus Annette the seventy-three thousand
and I’ll tell you when I was employed yeah I guess I calculate versus Annette even Daniel text 15% but I think I should be vs. cross
Samsung
but it was clear that that I’m paying twice there in the last year and it is a big portion of the budget now
I’m done yeah yeah that’s not much else
no this is for a copy and paste the mint amount I mean the amount and one thing I want to do is I want to compare this today a budget here so if I’m going to run like a machine comparison site formula of his comparison I need to Alexander this so that look exactly the same as the labels have here here
fortunately sorts this values by by the amount North by the label which free charts is nice for the chart and all the things you don’t want this to be like all over the place this is for a very good way of looking at that but the thing I love the picture so that’s what I saw you that way I have is concordance table while I’m comparing comparing my labels like replacing it for the meat labels I can’t have a situation where the order of the labels is changing every time for that so that’s that’s so what I do is I sort by label
so this section is what I copy and paste what’s this did this whole thing is a copy and paste and I have pre-made labels Reddit we matched and then I run a pivot table on the point of the table table is there some areas that means classified as a separate Soca
and that was some other things that keeping track of like this $2 fees and charges whatever I’m going to like them other something for entertainment so this personal care which is a $20 laundry machine card recharge I just did and what else there’s no other choice but they might pop up anyway so we have to fill table that’s just consult with a vlookup to my chair so what we have here is a bunch of you look apps like
Agricola Rancier it’s right here and then give me the amount I’m just copies him oh I’m just passed this for all the categories so that’s what that this I know I have something interesting going on on health insurance
all his insurance but is inside Health and Fitness inside House of Fitness means by default classify like visit to the doctor your gym membership and things like that and I hopped in a separate categories on my on my budget so what I did is this know this this will be a projection this this just for Brinks directly from here I have the exact amount I’m Spangled Hatch Insurance right now and that’s the figure that’s here so I just copy stuff about you say they’re the same slope traits this table most hung upside down I just passed his English Max index match the medical expense which is the remaining amount to spend on gym membership membership I need to rethink this but what is that says it just goes okay find me the expense which is $3,000 and they have insurance and dreaming your mouth. So there’s a little tweak to make this work and then what the status is it just calculus
versus my annual budget how much have I spent all of the annual budget and I have to adjust semi annual budget so that it stays within a hundred percent if it goes over 100% it starts to become a problem something interesting with no I also wanted to compare this outside OK Google or bad so here I have a figure that has to compare that maybe I should put this what this is is
it just goes okay so when is it that you updated is figure out which is from mint and that’s April 17th I just manually update this What’s the progress percentage of April 17th this year and that’s figure is 29 % so now I can compare how much I spent how much time has elapsed so if these figures are over 29% I’m over spending a little bit
so any color code it is a little bit at a time color functions so that if I’m 5% over the budget over what I should have spent over the course of a year and Marcus yellow if it’s 25% over the budget I’m just here
it’s all and all these figures that are trending red are they want to start hopefully will be front ID so I spent a lot of money in the beginning of the year and I will hopefully it’s endless over the course of a year and if things work out fine fine and this fresh you can’t tell me how much are the fine at the end of the year that’s something I need to let kind of eyeball or hope for so yeah shopping and shopping will always be over budget fit over spending because some of the shopping is actually actually businesses or like a bunch of that going to hear something which I’m not going to bother and then that means they are going to be on in a business because I am classify everything correctly think because if I spent 2000-2500 which is right here
Tyler
what else what else is under now that’s the only thing that’s on their significant everything is kind of normal within expected range things like rent interesting so I’m at 4% over what they should be when the rent is a fixed amount at Paramount and the answer is I pay rent on April 1st today is April 17th so I’m approximately 2 weeks have passed so there’s four percent difference is the fact that
well actually list I paid April 1st for the entire month of April so this 4% difference means that I’ve already paid for the remaining two weeks of the month
but it’s not my first kid is not April like 28th April 30th yet and that’s why there’s a discrepancy
is very normal everything is fine
last year during during the end of year.
I bought Oculus twice and I returned both of them so and then they return the expenses when in 2020 but then the refunds for for the refunds for returning liquid that the stuff came in 2021 and I haven’t made a whole lot of significant expenses so that’s why I feel there’s more in refunds going on tomorrow so far and I’m on that you spent so that’s why this is my anus
yeah so there’s that little chart showing
this is the budget and expense I don’t know how helpful these charts are its so yeah that’s what is that everything it’s way better than mint budgeting function it’s compared to my system which of course I am always happy to customize for what I do is very lacking like the only allows me to configure things every month or every few months or once and this is not very well I want a system where I can send my annual budget Can Tell Me OK or 50%
so this is concerning try to spend less and still doing that just tells me you are over-budget you’re on their budget blah blah blah and it doesn’t help me plan on a long-term basis
I have no idea what this house miserable because if I wanted to look how much and spending and like try to cut cut call a look at this
I spent too much on food no IR everything seems fine gifts
the budget yeah it’s kind of
so I a psychopath test I love it
what you’re supposed to be better for managing your business
make it work but it’s like I have no idea what this
sure that’s a good idea to send invoices and payments nice expensive
okay is this a very dumb function and it doesn’t let me like the adjustment for you to do that time tracking is very nice pretty but I need to re-enter the spice things by hand or run out like a timer to the east of using this form where I can enter Stephanie Lee Reddit review how much I meant Sometimes some projects into a bill to specific like funding source depending on the rain station so I can do all that so I might cancel freshbooks it’s providing very Little Italy right now
pretty excited about this
Reply