 Personal finance practice problem using OneNote. Comprehensive problem, home financing, financial statements, and property insurance estimate. Prepare to get financially fit by practicing personal finance. You're not required to, but if you have access to OneNote, would like to follow along. We're in the icon on the left-hand side, practice problem tab in the 8210 comp probe tab. Also, take a look at the immersive reader tools. Most of our practice problems are in the text area too with the same name, same number, but with transcripts. Transcripts that can be translated into multiple languages, either listened to or read in them. Information on the left, calculations on the right. We're going to start off with our loan payment calculations, creating our amortization table, then breaking it out into a year-by-year calculation instead of a month-by-month breakout of the amortization table data, which is useful for putting things together such as the financial statements, which is the next thing we'll look at. That includes the balance sheet and the income statement. We'll do two formats of an income statement discussing why you might use different formats of an income statement, tools you might use to put them together. We'll look at the difference and reconcile those two income statements, and then we'll take a look at the estimated property insurance needed calculation on down below. Scroll in all the way back up to the top. Now note that we do do this in Excel too, so if you want to work through this in Excel worksheet, really good practice problems to train in Excel and get these concepts down. Information on the left, we've got the assets, the liabilities. These will be used to help us put together our personal financial statements. Note that you can use tools such as accounting software, like QuickBooks, or I think there's a wave accounting, like free accounting software that you can connect to your bank account, for example. But you still need to kind of understand the accounting concepts a bit in order to make those accounting software's work. For the balance sheet, you can also use other tools such as there's a personal capital kind of tool that just pulls in the ending balances, for example, instead of using the transactions to create the ending balance as accounting software does, which is good for balance sheets. Calculation is not so good for the income statement where you really need to do some kind of pulling that data into a time horizon. We'll talk more about that shortly when we get to the income statement and the balance sheet. Then we've got the income data. We're going to assume that we have W2 incomes for the personal income. Again, there's kind of a problem with that in that when it hits our bank, if we're using our bank as the source of when we're going to calculate the income, it's going to go into our bank net of withholdings, which could include things like the social security, Medicare, federal income tax, and possibly other withholdings such as benefits 401K and so on. We'll talk more about that later. We've got the expenses. Now, the expenses, of course, are what we are expending. We can't get that using software like a personal capital, which pulls the ending balances. We'd have to possibly actually look at the income statement or the bank statement details and pull our expenses, put them into categories and or use software accounting software to help us do that like a QuickBooks or like a wave accounting. A lot of different options. I'm not promoting any one of them. Just you have a lot of things you can use there. Then we've got the home purchase. So we've got the home information, home price, rate, years, down payments. We got the current value of the home and the estimated value of appliances, furniture, clothing, household items, jewelry. All right, given all that data that we've gathered together, first, we're just going to look at our loan payment calculation. And this will help us kind of understand some components of our balance sheet and income statement, financial statements as well. So I'm going to say the home price is the 306,200. We're going to say the down payment was 20%. So we've got the down payment then being at the 61,240, which is of course going to be the 306,200 times the 0.2. That's going to be the 62,61,240. That means that we've got the loan of the 306,200 price of the home minus the 61,240 we put down on it. That gives us the 244,960 the amount that we're going to finance, the loan that we're going to be getting. That means that when we have the home on the books, the difference between the value of the home, which currently at the point of purchase, we're going to say is the 306 minus the 61,240. I'm sorry, 306,2 minus the loan, 244,960 is the 61,240, which in essence is the equity in the home, the net value of the home, which we might reflect on the balance sheet assets, liabilities, assets minus liabilities being the net value. We'll talk more about that later. Okay, so that's going to give us the payment calculation. Now you could use that payment calculation in Excel. In order to do this payment calculation, you can use online calculators to help to calculate that payment calculation as well. We won't go through it here, but just note we do work this in Excel if you want to take a look at that. We could then build our amortization table, which does sound daunting, but it's I think useful to do in Excel. Remember, you do have online tools to help you with this as well, so we could use an online calculator. I'm not promoting this one, but here's one you could use and I could put the loan amount, not the home price, but the loan amount, 30 year, 5% every month, run it, and we get our payment calculation and we could create an amortization table, noting that that amortization is broken out basically by month. We however might want to do it in an Excel worksheet so we can kind of combine it into our other calculations and break it out by year. So I won't go through the loan calculations in depth here. We do build these in Excel, which makes it a lot easier to do. It's easier than you would think to build these amortization tables. So then, but noting on the amortization table that the breakout between the interest and the loan reduction changes each period even though the payment is the same. That gives a level of complexity when we start to think about our financial statements and how we're going to record these on, say our balance sheet and an income statement, how we're going to record each payment in basically our accounting system. It's also useful oftentimes to break this out on a year by year basis. So for example, the payments for the entire year is going to be something like that, 13, 15. This is rounded times 12. We'll be paying about 15, 7, 80. I think it's rounded to zero in any case. But the interest gets a little bit more tricky because if I was going to add the full year, which would be 12 months or the full year here of interest, you could see that they change each month. So that's part of the problem. So if we were to break this out in a table, we can use Excel formats or a pivot table to sum up the interest, which you could see changes each year. And it's kind of nicer oftentimes when we're doing budgeting, for example, to see that on a year by year breakout, which is something we can't see on the online tool as easily. The same with the decrease in the loan principle. You can see that that changes each time frame. So it's nice to see the decrease in the loan principle on a year by year breakout. And then it's also nice to see the loan balance at the end of each year, meaning that's going to be the smallest number at the end of each period. So here we have the end of year one. There's the 241.346, which will be left due principle balance owed after one year. Here it is after two years. So we can do that calculations in Excel. I won't go over them here. But if you want to work that in Excel, we've got that. And then once we have that, I'm going to scroll all the way down because I'm going past the 30 year amortization table here past the 30 year to the balance sheet. So then we're going to construct the balance sheet. Now remember the balance sheet stuff you might use like accounting to do that, an accounting system, meaning you actually enter the data into the accounting software like a QuickBooks, which creates the balance sheet, the endpoint from the ground up, meaning I'm not going to look at the financial statements, ending balance to record the checking account, but rather actually enter the data from the bank, the increases and decreases or whatever, debits and credits that will then create the ending balance and then reconcile my books to the bank. So that would be what accounting software does. Although you can use other software just pulls the information in from the ending balance, which is great for the balance sheet, but isn't as helpful for the income statement. So it kind of depends on your needs and how precise you want to be with your financial statement creation. You could use both. I like to use both. So I have an ending balance kind of calculation that's coming from the financial statements that might help with investment planning and stuff that can be updated a little bit more quickly and then also have the calculations on the QuickBooks or something like that so that you can create the actual income statement, the timing data. So remember that the balance sheet shows where we stand as of a point in time. So let's think about the balance sheet information here. So we're going to say, all right, the balance sheet, I can break the assets out into categories. We've got the current assets. Now it's often useful to do that. That might be more of a business term you might think, but same kind of concept. These are more liquid type of assets that we have that we might be able to use to pay off our current debts that are becoming due. Clearly the checking account. Now the checking account, if you were using something like QuickBooks, you would have to reconcile entering the actual transactions to get to the ending balance. If you use something like a personal capital, kind of software, then it'll just give you the ending balance based on the financial institution that you're linking to. So either way, you can link to the bank, but they do different links. One links the activity so you can calculate the ending balance. The other links just to the ending balance, which is great here, but not so good for the income statement or reconciling and giving a double check of your figures. Savings account, same thing. You can get them in a similar fashion with those two ways. Emergency fund, same kind of thing. You could typically get that from the financial institution both ways, connecting to the bank if you wanted to do it that way. Total current assets then are going to be the 58,000. Notice how we did this. We basically said current assets. We indented, put a colon, given the fact that that's a subcategory. Put the subcategory calculations in an inner column and then broke the outer calculation, the sum of it into the outer column. That's a common format. Practicing that format in Excel is a good practice, good tool to do. So then we do this in Excel if you want to work through that. We got other assets. I'm going to put the IRA down here in the other assets. It might also be, in your case, a 401k plan or something like that, which is often also, of course, in or with a financial institution, which you can get from basically personal capital kind of software, which pulls in the ending balance. And if you're using something like a QuickBooks, note that it's a little different than the checking account because something like an IRA, which is supported by stocks and bonds, possibly, is going to change in value due to the changing values of the stocks and bonds. And you have to determine how are you going to account for that on your side of things because you're not going to be doing it by just increases and decreases to the checking account and reconciling it. You'd have to actually look at the statements, for example, possibly periodically and see whether or not there's been an increase and decrease. And then you've got to figure out how you want to record basically the unrealized gains, the gains that you have not yet sold the stock for, but you've got kind of unrealized gains that would be involved. And that's one reason that you might use both software, like a QuickBooks, to calculate the periodic data as you're entering the data and then other software that pulls in the ending balance to give you kind of up-to-date ending balance on things like the stocks and bonds as it jumps around, right? Okay, in any case, that's going to be that one. I put it down here because we can't really use it to pay off the current liabilities as easily because it's under the umbrella of an IRA as opposed to other savings. If they were outside of the umbrella of an IRA, you might call them current because I can take them out or sell the stocks if they weren't under that umbrella easily. And then we've got the property plants and equipment, which you might call fixed assets or something like that on the personal side of things, same kind of idea. We've got the home here. Now, the home is something that you can't really just go to a financial institution to figure it out. What you can do is take at least the cost of the home and put that on the books possibly, which might be relevant to do given the fact that it's going to be a huge asset for most people. And again, you could change the value of the home periodically by using tools online to adjust the appraisal of the home. But again, if you haven't sold it, you're going to deal with this unrealized gain or loss that you're going to have to deal with on the other side of the calculation from a bookkeeping standpoint. And then we've got the car, same kind of concept. You may or may not put the car on your books because it's significant, but it's not as significant as the home. And again, the car will depreciate over time. So it's a question of, do you need it on there for the purposes of what you are doing kind of concept? Then we're going to have the second car, same thing. You might say there's a whole bunch of other assets like your TV and all this other stuff that you've got. And again, you might put that on there or try to estimate household goods to get an idea, but you're not going to probably want to depreciate all of your personal belongings on your balance sheet unless you're using it or using it for some purpose, like to get a loan and trying to look as good as possible to get a loan or something like that. But for your personal use, it might not be worth the time to put all of your personal belongings basically on the financial statement. You want to have a list of those. You want to have items of them possibly for homeowners insurance, if nothing else, right? So that you can value them in the event that you have a fire or something like that. But we're going to keep it here. We're going to sum those up. So now we've got the 3062, the 14, the 21. That's going to be the 341, 2. Notice again, all this stuff up top we can possibly get from financial software oftentimes like a personal capital pulling in the ending balance. And if you used like a QuickBooks then you'd have to enter the adjustments, the actual transactions here, and then the periodic adjustments here that are taking place with unrealized gains and losses determining what you're going to do there. And then these down here, we have to value periodically if we want to put them on a real-time value taking some kind of appraisal periodically of the home and the car. If it was a business, we'd have to depreciate it, right? But anyways, that's that. So then we're going to say the total assets then are going to be the 58,000, the 40,000. And so we're going to, let's just add them up. We're going to say that's the 58,000. I'm only adding up the outer column now, 40,000, 341, 2. That's going to give us the 439, 2 here. On the liability side of things, we're going to say that we have once again current liabilities. Those are things that are due within a year typically. So oftentimes we've got like a credit card balance that is going to be due. So we're going to put that there. That also we can get oftentimes from the financial institution. If you're using something like personal capital, it can pull in that ending balance. If you're using something like QuickBooks, it will give you the transactions as you buy things and pay them off in order to calculate the ending balance. The car loan also with a financial institution. So typically if you're using like a personal capital, it can pull in that ending balance. If you're using something like accounting software, then when you make the payments, you'll have to break it out between interest and principle. That gives you the total current liabilities. And then we've got the long-term liabilities, which I'm going to say is the home loan, which we're pulling in from the data on the left-hand side. And that's going to be the home loan, which also you can pull from a financial institution if you're using something like a personal capital. And when you pay off your loan payments, you can break out the interest and principle according to the amortization tables that we calculated up top in order to calculate it if you're using something like a QuickBooks. Total liabilities then, the 93, the 241, 346. And so then we've got the net assets. Now, remember if the net assets you can think of as kind of like equity for a corporation, we can say it's the 341, 200 minus the 241, I'm sorry, minus the 250, 646. One more time. Hold on a second. We've got the total assets are down here. That's not the total. That's 439, 200 minus the 253, 646. That gives us the 1-88554. Now, if you did this using accounting software, then you would let the system, all your transactions would be in balance, for example. And you would be using a calculation of assets, assets equal liabilities plus equity kind of calculation and all transactions will kind of be in balance. And that'll be kind of your double check. If you're just building your balance sheet, possibly using something like a personal capital, then it's just going to use this kind of subtraction method to get to the net assets, meaning if these are your assets minus your liabilities, these are the net assets that you have if you were to liquidate, right? If you're just going to give everything away or something like that, right? You'd sell all your assets. You'd get the cash. You pay off all the liabilities and that would be kind of like your net worth. Okay, and so then we could add that to the assets and that would give us our total assets and liabilities, which is kind of how we are typically seeing it from a business perspective or from a company perspective or from an accounting perspective, assets equal liabilities plus equities. So notice the term isn't quite right here. It should be liabilities and I'm sorry, it says total net assets and liabilities or it would be total liabilities and equity would be kind of like the bottom line. That's how we're kind of used to seeing it from just an accounting perspective and you can see it as the left side being what we own, right? And the life, the right side being who we owe it to, either a third party liabilities or ourselves. It's actually something that we own after the assets. We have claimed to it and a third party does not. So then on the income statement, so now this is going to be our timing statement. This is the one that we can't really construct with something like a personal capital by just looking at the ending balance and we have to use either something like a QuickBooks to construct it or we have to basically look, actually just look at our bank statements possibly and be pulling the information to construct the income statement. Now, the first part of the income statement, we'll do this a couple of different ways. The first part's kind of the most confusing oftentimes because if we're building this just from our bank statement, then if I look at my income side of things and if I have a W-2 job, then I'm going to see it hit at the net amount, the 48,000 in this case and the 65,000. But I actually earned in this case the 60,000 and the 79,000, the gross amount and the difference between those two were taken out directly by the employer partially because they're required to for with mandatory withholdings, taxes, Social Security, Medicare, federal income tax and partially because possibly we told them to for benefits such as 401k plan health and so on. But that kind of messes up our accounting because if we're on a cash basis system, then we don't see it hit our checking account that way. They pulled it out before it hit our checking account. So the proper way to do this is you'd like to say, okay, I'd like to put my income on the gross income which in this case would be the 60 and the 79,000 which you could get from the paycheck stub because they will give you the paycheck stub. So every paycheck stub you might pull it from there. You might be trying to construct it at the end of the year or something possibly from your W-2 which means box one might not be the best box because it might be reduced for things like the 401k. So box, I think it's five would be the one for the Medicare which is probably the closest one if you're trying to have the highest income level because that doesn't have as much of the deductions which have tax implications for it. Obviously the W-2 is for tax implications. The actual paycheck stub will show you the gross pay that you earned in theory because it gets kind of complicated in like reality like who's actually paying the taxes and so on when you start to put them. But in any case, you've got that amount there and that's one way that you can kind of construct this. So that means that when you put this into something like QuickBooks, you whenever you see the bank account go up by your income level what you really should be doing is putting the income in there at your gross income and then putting the other items related to taxes as expenses as well as the other items that are related to benefits like a 401k and the 401k which can get a little bit kind of confusing. We might go into that in future presentations but that's going to give us the total income of the 139,000. We've got the normal expenses then. So we're going to say property taxes is a normal expense like utilities. These you might be getting by just going through your income statement or you might use bank, I'm sorry, your bank statement or you might use bank feeds and something like QuickBooks assigning these as you pay them on a cash basis to two categories like property taxes as an expense, utilities, food, gas and maintenance, and interest on a car and credit card and so on, interest on the home loan. Now these two are getting kind of tricky. These two down here because if you're trying to do the bookkeeping on these then when you record them you're not going to just record the interest on something like the home loan. It's going to have the actual payment you pay on the home loan. So it's going to go down by the interest and principal. That's when you'd have to use our amortization table. I won't scroll up top but I'll go here. Your amortization table to break out the interest and principal depending on what kind of bookkeeping process you're using. We might dive into that in more detail in the future but that's where the kind of a tricky thing is with your bookkeeping. So the total normal expenses is going to be, we're adding up to 600 down through the 12166 to give us the 1491 and then we're going to say this is net income before taxes and withholdings, meaning if my gross income was 139,000 minus the 14091, that's going to give us the 124909 and then we've got the taxes and withholdings. So because I put this amount up top on the gross income the amount that's been taken out I'm going to assume to be all taxes. It may not be. It might be a 401k plan in your case or something like that. It is included in there as well but that's what I'm going to account for down here and I'm going to do that. You would do that basically by saying I'm just going to say all right my gross amount that I got paid according to my pay stubs I'm going to say is the 60,000 plus the 79,000 minus the net amount which is 48,000 the amount that actually hit the checking account minus 65,000. If we assume that difference to 26,000 is taxes then we've got the taxes and withholdings at the 26,000 and that's going to give us our net income which is going to then be the 124,909 minus 26,000 for the 98,909 on the bottom line. Okay so you could try to do this income statement on like a more of a cash basis. So if you try to do it on like a cash basis from things that hit your bank account you might say I'm not going to be able to enter it gross so you might be using like a QuickBooks and you're saying every time something hits my account on the bank account I'm just going to record it as income from my W2 income that means you're going to record up top net and that means this is already net of the withholdings that have been taken out. So you have to kind of realize that if you're entering your income statement in that fashion and if you need to adjust it if you need to look good for a bank or something like that you might want to put the gross amount of course up top if you're trying to get a loan you want the highest income line item typically up top but in any case or whatever they ask for but you hire better. So total income is going to be the 113 then because really this is kind of net of the taxes and withholdings and then the expenses would be cash flow basis property taxes here same utilities and then we got the food we've got the gas and maintenance credit card payment. Now notice here I'm not just looking at the interest I'm looking at the full payment here because I'm thinking about this on a cash flow basis at this point and that you know you it could be useful depending on what you're trying to calculate for to think of your your cash flow versus your your like a cruel basis kind of income statement and if you're using QuickBooks or something like that you can actually construct kind of more of an accrual basis income statement and a statement of cash flow but the statement of cash flow is often kind of confusing to people but in any case I'm going to put the full payment down here in a normal bookkeeping system what you would want to do is just record the interest on an accrual system and then the the principle being a decrease to the loan balance now there's multiple different ways you can kind of do that we might get into the bookkeeping later the same is going to be true for the for the car loan payment and the same is true for the home loan payment I have the full amount here on a cash flow basis even though part of it is principal that I'm going to be paying that should be paying down the loan which is a which is a liability on the on the balance sheet so those are the little bit more confusing so that means I'm going to get the 18195 here if I add all that up and that's going to give me my net cash flow of we said that the net income is the 11300-18185 that's going to give us the 94815 now if I try to take the difference between these two and reconcile them and say well what's the difference between those two we've got the 98909-94815 and I've got a difference then of the 4094 if we reconcile that that difference is due to the fact that the home loan a balanced reduction meaning we've got we've got these two numbers up here I deducted more for the home loan so if I take this this 15780-12166 we get that that 3614 and that we can look at the amortization table I won't go up and look at it right now but you can look at the amortization table to determine the breakout between interest and principle and that's that bookkeeping thing that we got we're going to have to basically deal with on an accrual kind of basis so that this one's on a cash flow we just record the whole thing as a cash flow expense and this one we did an accrual kind of calculation we got the credit card payments of the 310 because we had the full payment on this side which includes interest and principle same with the car loan and then we've got the interest on on the credit card that's the 75 where we just recorded the interest portion here and that's gives us the difference of the 4094 so just to give us an idea of a couple ways the income statements can be a little confusing it can be you know confusing to get your bookkeeping stuff down and there's no way to get around some accrual concepts when you're when you're talking about loans like a mortgage or something like that typically okay last thing let's take a look at the estimated property insurance needed so if our property insurance how much do we need would be a question we might ask so we've got the property insurance you might put a calculation something like this we might say with the current replacement cost of the home notice if I look at the home you're gonna you're gonna probably looking at the replacement cost of it we purchased the home for 306 200 but it might have gone up in value so we might want the home you know to be covered at the current price here which we might have to do by looking at appraisal tools online to be valuing the home so we've got that at the 125,000 we've got the personal property we said was the estimated value of appliances furniture clothing and so on which we estimated at 21,000 21,000 down here notice again that 21,000 we didn't put on the incomes on the balance sheet as an asset you could you can estimate it on the balance sheet and but it might change over time right you might buy new stuff and whatnot and it might be tedious to try to put that on the balance sheet because it those kind of things depreciate over time so you might have to revalue you might put just like a static number on the balance sheet to kind of represent what you feel the value is but it changes over time so it depends on how you want to do that you do want to have a list of these items and be able possibly to give an approximate value of them and have maybe images of pictures of them and whatnot in a list of them in the event of course from an insurance standpoint that there was a fire or something so that you can list those items and possibly get recouped for them so then we have the additional coverage for items with items on standard personal coverage like jewelry firearms and that kind of silverware photographic electronics so over and above computer equipment we're going to say 2000 here because we have 2000 for the for the jewelry so it might have a whole separate kind of calculation we would have to add for the additional coverage items such as jewelry possibly total personal property then if we add this up 21,000 plus the 2000 23,000 and then we might have type of coverage for personal property so we're going to have replacement value is what we would like to have and notice again we've got this kind of how are they going to recoup the property we would like to have it recouped it would be nice if we can have it recouped enough get enough payment in order to purchase a new property right we would like to replacement cost so that's what we're going to be covering here we'll put that personal liability so we got the additional personal liability coverage personal personal injury claims this is something that we might add to it if we so choose over and above what the standard policy is we could have specialized coverages flood or earthquake coverage excluded from the home so these are kinds of coverages like we talked about in our presentations that oftentimes might not be in the standard calculation so if you're subject to earthquake or something like that or flood then you might have to buy like special insurance which might be in conjunction with the current homeowners insurance or some or separate policy for those particular items because they kind of could throw off you could we talked about the actuarial calculations and why that might be the case the way the insurance kind of calculation works so that's going to give us our total property insurance needed at the 348,000 so again this was just kind of an overview we do work through this problem in Excel so if you want to just kind of practice putting these tables together working through the problems in Excel then it's a good thing to do