Excel, from a blank sheet to a payment schedule.

We're going to learn how to create a balance sheet using Microsoft Excel. Open a brand new worksheet looking like this and click in cell A1 and type assets. We're going to list our current assets first starting with cash, then accounts receivables, marketable securities, inventory, and finally other assets. Then we want to subtotal our current assets, skip a cell, and now we want to list our fixed assets such as property, plant, and equipment. Then we want to subtotal our fixed assets and then total all of our assets.

In order to make it look presentable and eye appealing, we just want to indent the subcategories for each of these assets. That way when we auto-expand cell A1 or column A, it will expand and look presentable. We'll leave column B for the data. Column C we'll bring in a little bit just to create a left and right side to a balance sheet. Then in cell D1, we're going to type liabilities.

We're going to list our current liabilities such as accounts payable and notes payable. Then we're going to subtotal our current liabilities and then skip a cell and then type long-term debt and then total non-current liabilities and then total liabilities under that. Following liabilities on the same side of the balance sheet, we're going to put in equity. Included in equity is common stock, paid in capital, and treasury stock. Then we want to total equity.

Then finally, total liabilities. Then we want to make the same formatting adjustments as we did with the assets in order to show categories and then highlight column D and double click column D to create that. Now remember folks, total assets must always equal total liability and equity. That's how you create a balance sheet in Microsoft.

Captions and transcript are machine-generated and reviewed.

Type these in as you watch. The template adds accumulated depreciation, other current liabilities and retained earnings to the sheet from lesson 1.

In our next installment, we're going to learn how to input the data and formulas into Microsoft Excel. Open your previous balance sheet template and notice I've made a couple changes to the template. I've added accumulated depreciation, other current liabilities, and retained earnings. Now, on the left side, I have my assets, liabilities, and equities as the data that has been given. So, easy enough, we're going to just input the figures that I see on the left. 100,000, 192,000, 1.262 million, 420,000, and 150,000.

And now we're going to total these with Alt + equals, which will sum that range of figures. We'll give us a subtotal of current assets. We don't have any property. We don't have a plant. We have $2 million in equipment, as stated, and we have $200,000 of accumulated depreciation.

So, make sure you put the negative sign in front of the 200,000, and then we're going to auto-sum those two. But a little modification is if we ever buy a plant or property in the future and we add that, we can just input that information in there, and it will automatically add those figures for you. And then we're going to add both fixed assets and current assets together to get our total assets. So, equals B7 plus B13. And that way, you don't have to worry about making any changes.

It will automatically update itself. Now, we're going to go to the right side of the balance sheet. And notice here we have an accounts payable of $154,000. We have zero notes payable, and then we have $50,000 of other current liabilities. We want to sum these together to get our total current liabilities of $204,000.

We don't have a mortgage, which would be long-term debt. So, therefore, our total non-current liabilities would be zero as well. And then our total liabilities would be total current plus total non-current, which is equal to E5 plus E8. And that's all I did was hit equal sign, the cell I clicked on E5, the addition sign, and then I clicked on E8. And that gives us a total of $204,000 in liabilities.

Now, we want to enter our equity. Our equity consists of common stock of $100 ,000, paid in capital of $3.4 million, treasury stock, which is zero, and $220,000 in retained earnings. We want to sum the equity, which gives us $3.72 million. And now we want to sum liabilities and equity, which should equal total assets. And again, that's equal to cell E9 plus cell E16.

Therefore, we balance, and our balance sheet is equal. And therefore, and that's how you input and add formulas to your balance sheet. Hope this is a great help, and look for more videos coming in the future. Thanks, and have a great day.

Captions and transcript are machine-generated and reviewed.

Today, we're going to learn how to find and calculate a payment in Microsoft Excel. First, click in cell A1 after you've opened up a new workbook and type loan amount. In the cell below, you want to type i for interest, nper for the number of payments or the number of periods, then you want to type payment and then future value or FV. For experienced Excel users, you can also type present value instead of loan amount, either one, same difference. Then for our present value, we're going to enter the amount of money we'd like to borrow.

For this example, I'm going to enter $200,000, so minus $200,000. My interest rate on an annualized basis will be 5%, .05. Say this is a house note, so I typically am going to have 30-year payment or 30 years of payments. I don't know my payment, that's what we're trying to find. And at the end of the loan, we expect the value of the loan to be zero because we've paid it off.

Now, in order to solve for a monthly payment, we need to first divide the interest by 12. So in cell C2, type equals cell B2 divided by 12. That changes our annualized 5% rate into a monthly rate. And obviously, we're paying over 30 years and 12 months a year, so we'd have to do the same thing but multiply by 12. So in cell C3, we're going to type equals cell B3 times 12.

Now, here in both situations, we can have an annualized payment and we can have a monthly payment. So first, in cell B4, we're going to solve for the monthly, or excuse me, for the annual payment. So there's one of two ways, and I'm going to show you both ways using different methods of solving for a payment. The first way is we're going to come up to this function box and click Insert Function. Then it's going to bring this function box up.

It's going to ask you, type a brief description of what you want to do and then click Go. We want to find payment. So I'm going to type payment in the box and hit Go. It comes up with several different selection functions. The first one says FV, which is the future value, which we already know is zero.

The next one is the X net present value. We don't need that one. We don't need the following. But we come down here to payment, PMT. That is the one we're looking for.

It tells you rate, nper, present value, future value, and type. We're going to hit OK, and now it brings up a second dialog box. Slide that over just a little bit. Well, on the first side, we're going to do our annualized rate. So the rate for our annual will be 5%.

The number of payments, considering the bank says we can pay once a year, will say 30 payments. So that's 30 years. The present value is our current loan amount. And at this point, our current loan amount is $200,000. So that's B1.

And then our future value, after we've finished completely paying the loan, will be zero. Now what I suggest is to put the cell into the function box. So you don't have to go back every time to change the actual function when you want to change a value in your variables here on the left. Once you're finished, hit OK. We've ignored type.

That is irrelevant at this point. So our annualized payment is $13,010.29. Now we want to do the same thing for our monthly payment. Yeah, you could say just take the annualized payment and divide by 12. It'll give you a nice estimate.

But doing it on a monthly basis and re-entering the formula will give you a more accurate figure for a monthly payment. The second way to find that is to type equals PMT open parentheses. And notice how it'll bring up rate, nper, PV, future value and type again. And now I just want to click in each cell that I'm looking for. So the rate here was going to be the monthly rate, as well as the monthly number of periods.

The present value is still the same, minus $200,000. And the future value as well is still zero. And then close parentheses. Again, ignore the type. And hit enter.

Now it shows us $1,073.64 every month for 360 months will be our payments. That is how you can solve for payments in Microsoft Excel. Thank you and look for my next tutorial on amortization.

Captions and transcript are machine-generated and reviewed.

Welcome back. Previously, we learned how to create a payment using Excel. Now we're going to learn how to make a payment table from the information we have done already. The first thing in cell A7 is we want to type payment number, then total payment, followed by the interest per payment, the principal per payment, and then the balance after the payment has been made. We're going to put the original loan amount balance in cell E8, and then we're going to start our payment table in cell A9 through E9.

So payment 1, 2, 3, 4, 5. We're going to do 360 payments. So entering 360 payments here is a little bit different. We're going to use a trick called the fill feature. Now you want to highlight 1 through 5, and then make sure the cursor turns into the black cross, as you see here.

Once the cursor has turned into the black cross, you can now fill it down to 360 payments. A little too far. And 360. There we go. It automatically filled 360 payments for you.

Now back to creating the table. Our total payment is going to be the monthly payment of $1,073.64. And what we're going to do is we're going to absolute that cell, because that payment is going to stay the same as we continue our table. Our interest, however, is going to be based upon our balance. So our balance times the monthly interest, which will remain the same.

And again, we're going to absolute that, and that's done by hitting F4, the F4 key at the top above the number 4. And with the newer model computers, you might have to press Function + F4, or on an Apple, it would be a little bit different. So we're going to calculate the interest, and then the principal is the total payment minus the interest. And then the current balance would now be the balance, the previous balance, minus the current principal payment. Now we're going to do that for the second row as well.

We want to ensure that our formulas are correct. So we're going to fill it one row. And if it comes out, which it has, we're now going to double click that fill button so it fills our whole payment schedule. And that's how you create a payment schedule or an amortization schedule in MS Excel. And you can go ahead and check it all the way down on your own.

And the last payment would create a zero balance. That is how to find a payment and create an amortization schedule in MS Excel.

Captions and transcript are machine-generated and reviewed.