# Excel 2007: Financial Analysis

## with Curt Frye

Numbers and financial data drives today's business world and Excel 2007: Financial Analysis can help decode this information. The proper understanding of these numbers, and the formulas behind them, can be the gateway to corporate and personal success. Microsoft MVP (Most Valuable Professional) Curt Frye teaches basic fluency in corporate finance, enabling users to see the meaning behind essential financial calculations. Curt explains how to review formulas to ensure they have the proper inputs, and shows how to interpret formula output. He also covers how to calculate leverage ratios and amortization and depreciation schedules, as well as forecast future growth. Exercise files accompany this course.
Topics include:
• Building a financial worksheet with Pivot Tables Reviewing financial statements through common-sized balance sheets Calculating percentage change over time in financial statements Determining profitability ratios and return on investments Studying liquidity and activity ratios through an average collection period Computing the future value of an investment
author
Curt Frye
subject
Business, Data Analysis, Finance
software
Excel 2007
level
Intermediate
duration
2h 18m
released
Aug 25, 2009

Ready to join? subscribe

Keep up with news, tips, and latest courses.

submit Course details submit clicked more info

• ### FAQs

Search the closed captioning text for this course by entering the keyword you’d like to search, or browse the closed captioning text by selecting the chapter name below and choosing the video title you’d like to review.

Introduction
Welcome
 00:00 (Music playing.) 00:03 Hi! I'm Curt Frye and I'd like to welcome you to Excel 2007: Financial Analysis. 00:08 In this course, I'll identify the most essential calculations and formulas that 00:12 you will need to perform common financial analysis tasks in Excel. 00:16 We'll begin by looking at the best way to set up your worksheets and to 00:19 troubleshoot the common mistakes. 00:20 I'll show you how to use special tools in Excel such as Pivot Tables and Excel 00:24 tables to organize and analyze data. 00:27 We'll get you comfortable reading financial statements, then look at techniques 00:30 for comparing data from the financial statements of different companies. 00:34 I'll show you how to implement several ratio and interest rate calculations, 00:37 as well as look at several techniques for predicting future performance and 00:41 analyzing bond investments. 00:43 Financial analysis in Excel requires a basic knowledge of implementing formulas 00:46 and formatting data in an Excel worksheet. 00:48 If you're not familiar with these areas, you might want to check out Excel 2007 00:52 Essential Training before watching this course. 00:55 As a Microsoft Most Valuable Professional, my goal in this course is to help you 00:59 unlock Excel's ability to support your work as a financial analyst. 01:03 Now, let's get started with Excel 2007: Financial Analysis. Collapse this transcript
Using the exercise files
 00:00 If you are a premium member of the Lynda.com Online Training Library, 00:04 or if you're watching this tutorial on a DVD-ROM, you have access to the exercise 00:09 files used throughout this title. 00:11 You can find the files in the Exercise Files folder, are divided by chapter. 00:15 If you double-click to open the folder, you'll find all the files used for 00:19 a particular chapter. 00:20 If you are a monthly subscriber or annual subscriber to Lynda.com, you don't 00:24 have access to the exercise files, but you can follow along from scratch or use your own assets. 00:29 Let's get started. Collapse this transcript
Disclaimer
 00:00 This course, Excel 2007: Financial Analysis, is designed to be a guide to implementing financial 00:06 techniques and equations typically used in the United States. 00:09 Some of these techniques might be different in other parts of the world. 00:12 Nothing in this course should be considered investment or financial advice. 00:16 This course should not be considered a substitute to consultation with a 00:19 licensed financial advisor. 00:21 If you would like financial planning or investment advice, please consult your certified financial planner. Collapse this transcript
1. Designing a Financial Worksheet
Separating inputs and formulas
 00:01 Microsoft Excel is such a versatile tool that it's easy to create 00:04 complex worksheets. 00:05 Sometimes, those worksheets can be so complex that is difficult to find the 00:08 data you're looking for. 00:10 In this lesson, I'll demonstrate some strategies to make your worksheets easier 00:13 to understand and maintain. 00:15 One of the first techniques you should use is limiting each worksheet to the 00:19 data you absolutely need for the calculations on that worksheet. 00:22 If the data isn't part of the calculation, it shouldn't be on the worksheet. 00:26 Also, you'll find that it's very easy to create crowded worksheets that make it 00:30 hard to find specific items. 00:31 For example, on this worksheet, I have information for two loans, a construction loan 00:35 where a company is building a manufacturing facility, and also the 00:40 automation loan, which is to build the equipment that's going to part of the 00:44 construction facility. 00:45 The information is all thrown together. It's scrambled. 00:48 It's almost impossible to tell which data goes with which loan. 00:51 Here's one example, just find a method that works for you and stay 00:55 consistent with it. 00:56 What I did was divide my worksheet into two areas, one for the Construction Loan, 01:00 one for the Automation Loan, and within each of those areas, I have 01:04 the loan terms, the Principal, the Rate, and the Term. 01:08 The term is expressed in years. 01:10 So, I have those separate from the Monthly Payment and you'll notice that 01:14 I followed the same structure here with the Automation Loan. 01:18 If you like, you can also use color to distinguish between formula inputs 01:23 and formula outputs. 01:25 So, in this case we have the Principal, Rate, and Term. 01:29 If I wanted to highlight those, I could do it in yellow so they stand out, 01:34 I'll do the same over here. 01:38 The Monthly Payment is a formula output. 01:42 So, I'll select those cells and I'll just make them a neutral gray color. 01:47 So it's easy to read it as an answer for the formula, and it's also easy to 01:51 distinguish it from the formula's inputs. 01:54 If you follow these design practices, you'll create worksheets that are 01:57 much easier for you and your co-workers to understand and work with for years to come. Collapse this transcript
Avoiding common mistakes
 00:01 It's amazing how many errors can creep into even the simplest worksheets. 00:04 Most often the errors come from actions that seem very logical when you take them, 00:08 but result in formulas that give you the wrong answers. 00:10 Something you should avoid is using numbers as column headers. 00:14 Instead, qualify the values with text to specify the type of data you're working with. 00:18 For example, use FY 2009 for Fiscal Year 2009, and CY 2009 for Calendar Year 00:26 2009, and I'll show you why that could be a problem. 00:30 In this worksheet, we have a Sales Summary by Region, North America, 00:33 South America and so on, and we have the sales numbers here. 00:37 But let's see what happens if I create an AutoSum formula in cell B10. 00:41 So, the cell's clicked, I go up to the Home tab, click the AutoSum button. 00:45 Let's take a look at the formula Excel is trying to create. 00:50 You'll notice that it includes the year. 00:53 That's because 2007 is a number, which means Excel cannot distinguish it from 00:57 the other values in the column and it tries to include it in the formula. 01:01 However, if we change the value in that cell to CY 2007, I'll delete the 01:10 formula and create a new AutoSum, because CY starts with text, Excel does not 01:16 recognize it as a number, and it correctly creates the AutoSum formula with 01:20 the cells B5 through B9. 01:22 Press Enter and there you have the correct result. 01:25 Another error to avoid is hard-coding values in formulas. 01:30 Move to this worksheet and you see that I have a loan, Principal of \$10 million, 01:34 Interest Rate of 6% and a Term of 12 years. 01:37 Here's the formula that calculates the Monthly Payment. 01:41 I'll click that so you can see the formula in the Formula bar. 01:44 Now, you'll notice that I have the interest rate of 6% hard-coded into the formula. 01:50 Instead of using a cell reference, we have the cell here B4 with the interest rate. 01:55 So, if I press Escape to get out of formula editing mode and change the value 02:00 in B4 to 5%, the payment doesn't change, even though the interest rate changed. 02:06 It should have gone down, because you're not paying as much interest in each payment. 02:10 So what I should do, I'll change this back to 6%, is edit the formula so that 02:16 it refers to the cell that contains the interest rate. 02:18 So, I'll change the reference there to cell B4 and both of the other arguments, 02:25 the number of periods, and the loan principal already used Cell References, 02:29 so I don't need to change them. 02:31 I'll press Enter, there we have the value. 02:34 Now when I change the interest rate to 5%, the payment will change. 02:39 One final common mistake that Excel users make is creating formulas that cannot 02:44 be copied accurately. 02:46 They change Cell References when you move them. 02:48 In this worksheet, I have calculations for monthly payments for two separate loans. 02:53 One for a Factory Update and one for Storage Construction. 02:58 This is an example of bad design. 03:00 I have the principal and the term for each loan listed separately and instead 03:04 of having the rate in a separate place entirely, I should have it here with the 03:08 principal and the term for the two loans. 03:10 It's just an example of good design and you'll see why in a second. 03:15 So, here I have the formula for the Payment for the Factory Update Loan and 03:19 everything looks good. 03:20 What I'll do now is copy that formula so that it works for the 03:27 Storage Construction Loan. 03:28 Now, there is no obvious error, but if you were to run these calculations 03:33 you would find that there is actually an interest rate of 0% being applied to this loan. 03:39 You can determine that by going up to the formula bar, where we have Payment and then D3. 03:46 Now, cell D3 is the cell from which Excel is attempting to draw the interest rate 03:51 for this loan, but you'll note that it's empty. 03:54 The interest rate that you want is over in cell B3. 03:58 The mistake happened because when you copy a formula from one cell to another, 04:03 if you don't do anything special to these Cell References, Excel figures, oh! 04:07 you want me to shift all the Cell References as far up-and-down or 04:11 left-and-right in the worksheet as you shifted the formula when you copied and pasted it. 04:17 So, if you compare the two formulas, here we have D3, D8, and D7, and here, 04:22 we have B3, B8, and B7. 04:25 The change occurred when we copied the formula from here to here. 04:29 So, how do you keep cell B3 constant in this formula? 04:34 That's again where the interest rate is. 04:36 I'll delete this to show you what happens when you copy it. 04:41 What you need to do is click on the formula argument with the cell that you want 04:46 to remain constant and press F4. 04:49 Pressing F4 adds dollar signs in front of the column and the row. 04:55 What the \$ means is that the reference which was a relative reference and 04:58 capable of changing has now become an absolute reference. 05:02 That means it will not change. 05:04 So in this case, both the row and the column will stay the same. 05:07 If you press F4 again, now the column can change but the row can't. 05:11 If you press it again, the column will not change but the row can. 05:15 If you press it again, you're back to the relative reference at the start, where 05:18 both the column and the row can change. 05:20 So, pressing F4 to edit the Cell Reference, so it will not change. 05:25 Press Enter to keep the formula and now when we copy it, you get what turns out 05:31 to be the right result. 05:32 By following these strategies you'll avoid the most common spreadsheet errors 05:35 and be well on your way to creating usable and accurate worksheets. Collapse this transcript
Tracing formula precedents and dependents
 00:00 Excel includes a number of very useful tools you can use to ensure your formulas 00:04 incorporate the proper values. 00:06 One of those tools give you the ability to determine which cells are used in 00:09 which worksheet formulas. 00:11 Cells can play two roles in a formula, as a precedent or as a dependent. 00:16 A cell that is precedent supplies its value to the formula in another cell. 00:20 A dependent is a cell that contains a formula and has other cells 00:24 providing values to it. 00:25 Displaying a cell's precedents indicates which other cells provide the formula's inputs. 00:30 Now you can see the two cells here, B3 and B4 in this simple example, but say 00:35 you had a more complex formula, 00:37 you can still use this technique to determine where the values are coming from. 00:40 So, to display precedents, you go to the Formulas tab and then in the Formula 00:46 Auditing group, you click Trace Precedents. 00:50 You'll see that we have cells B3 and B4 as advertised that are highlighted with a dot. 00:55 There's a line with an arrow and it goes to the cell that contains the formula 00:59 that uses those two values. 01:00 So that is how you trace dependents. 01:02 When you're done doing the tracing, you can go back up to the Formula Auditing group, 01:06 click Remove Arrows and the arrows disappear. 01:10 So let's see how it works for dependents. 01:12 On this worksheet, we have Bonds, Notes and Loan Interest and we have a Total. 01:16 This is a company's monthly debt service for the year 2009. 01:20 If you want to see which cells use the value from cell B3 as part of its formula, 01:25 you can click cell B3, again, on the Formulas tab and in the Formula Auditing group, 01:30 click Trace Dependents. 01:33 You'll see that once again you have the blue circle. The line draws an arrow to 01:37 the cell that uses the value as part of it's formula. 01:39 To remove the arrow, you go up to the ribbon and click Remove Arrows. 01:44 Now tracing precedents and dependents also works for values that are on other worksheets. 01:48 So, for example, here in cell B5, I have a loan interest and that is on the 01:53 Interest worksheet, which is the one we looked at previously. 01:57 If you want to see which cell contains that value, you would select it, 02:04 click Trace Precedents. 02:06 Tracer arrows can also show you when values have been drawn from other worksheets. 02:10 So, for example, on this worksheet, we have cell B5, which draws its value from 02:15 the Interest worksheet. 02:17 So, if you click cell B5 and then go up to Trace Precedents to see where it 02:21 draws its value from, Excel shows you this indicator, which tells you that the 02:25 value is drawn from another worksheet. 02:27 It can also be from another workbook entirely, but in this case it's just from 02:30 another worksheet in the same workbook. 02:32 So, to display that value, you click the line, the Go To dialog box appears and 02:37 you can select the location to which you want to go, click OK, and it takes you 02:43 to the Interest worksheet, cell B6, which is where the value came from. 02:47 Identifying a cell's precedents and dependents will help you discover and 02:49 correct many formula inaccuracies. Collapse this transcript
Evaluating Excel formulas step by step
 00:00 Sometimes you'll look over a formula, verify that it uses the proper inputs, but 00:05 still find that it returns an incorrect result. 00:07 If that's the case, you can move to the formula, with one calculation at a time 00:10 to identify the problem. 00:12 Some of the tactics you can use are setting up the formula with inputs that you know 00:16 will generate a specific result. 00:18 If you don't get the result you expect, then you know there is a problem with the formula. 00:22 Then you can step through the formula with one calculation at a time, I'll show 00:25 you how in a moment, attempt to identify at which point the formula generates an 00:29 incorrect result while you are stepping through it and once you've identified 00:32 the problem, edit the formula and step through it again to ensure that you're 00:36 getting the right result for the right reasons. 00:39 As an example of a formula that you might evaluate, take a look at the formula 00:43 in cell B7, which is Monthly Payment. 00:45 Now we have a \$245,000 loan with a 6.9% interest rate being paid off over three years. 00:51 There is no way that you would have a Monthly Payment of \$0. 00:54 It just doesn't happen. 00:55 So, how do you find the error in the formula? 00:58 To do that, you can go to the Evaluate Formula dialog box. 01:01 So, I click the cell that contains the formula I want to evaluate and then on 01:06 the Formulas tab, in the Formula Auditing group, click Evaluate Formula. 01:10 The Evaluate Formula dialog box displays the formula and allows you to go 01:14 through and determine the value step by step. 01:18 So, we'll start out by clicking Evaluate and it pulled the value from B4, 01:22 which is 6.9% or 0.069. 01:25 Next step is to divide that by 12, with the result you see here and when I click 01:31 Evaluate again, it will display the number of years, which is in cell B5. 01:37 Click Evaluate again. 01:38 You get 36, which is the number of payments. 01:41 You made 12 payments per year, over three years, that's 36. 01:44 Then you have the Loan Principal and here's where we see the error. 01:48 Instead of a Loan Principal here in cell B3, I have \$245,000, it's 0. 01:54 So, I have already found the error but just to show you how it works, when you 01:58 evaluate the rest of the formula, I'll click Evaluate again and you get a value of \$0. 02:03 So, I'll click Close and edit this formula 02:07 so that the Principal is pulled from cell B3 as opposed to cell B2. Press Return 02:13 and we get the right result. 02:14 I won't go through all the steps of the formula again, but you could use the 02:18 Evaluate Formula dialog box to determine that you were getting the proper result 02:22 for the right reasons. 02:24 Evaluating Excel formulas is a powerful technique 02:26 you can use to verify that a formula produces the correct result. 02:30 You can also use formula evaluation to understand how a formula's logic works. 02:34 If you encounter a formula that uses a function you haven't used before, take 02:38 the time to look at the function in the Excel Help files and step through the 02:41 formula so you'll have a better idea of why the formula is in the workbook and 02:45 how it derives its result. Collapse this transcript
2. Analyzing Cash Flows Using Excel Tables and Pivot Tables
Tracking income and expenses using an Excel table
Creating a Pivot Table from table data
Pivoting a Pivot Table
Filtering a Pivot Table
Adding Pivot Table columns to enhance data analysis
 00:00 Pivot Tables enable you to analyze your data efficiently, but some questions 00:04 can only be answered by performing calculations on the data used to create your pivot table. 00:09 To perform that type of analysis, you can create calculated fields that 00:12 summarize pivot table data using a formula. 00:16 Without calculated fields, you would have to copy the data from your pivot table, 00:19 paste it onto another worksheet and create the formulas there. That's a pain. 00:23 It's extra work that you absolutely do not have to go through. 00:26 Now how to create a calculated field in an Excel 2007 pivot table isn't the most 00:32 intuitively obvious thing. 00:33 So I'll just show you where it is on the user interface so you can do it later. 00:38 To create a calculated field, first you select any cell in the pivot table 00:42 and then on the Pivot Table Tools Options tab of the ribbon, you go to the 00:46 Tools group and click Formulas and that's where you find the Calculated Field menu item. 00:53 So click that and you get the Insert Calculated Field dialog box. 00:58 And what you do here is create a name for your field. 01:03 The goal for this field is to calculate a company's revenue as a proportion of 01:09 the cost of goods sold. 01:12 So it will be dividing revenue by COGS. 01:15 So for that one, I'll just call it Times COGS, press Tab and the formula will be equal. 01:26 And then again, I want to divide revenue by cost of goods sold. 01:29 So I'll click Revenue, insert that field into the formula, forward slash to 01:35 indicate division and then I insert the Cost of Goods Sold field. 01:42 So there we have our calculated field and everything looks correct. 01:47 So I'll click OK. 01:49 The Times COGS field appeared in the Pivot Table Field List and it also appears 01:54 down here in the pivot table values area. 01:58 So at present, our pivot table has two data fields. 02:02 We have the sum of revenue and we then we also have the sum of times cost of goods sold. 02:08 So I'll remove the Revenue field from the data area for the moment and 02:15 you can see here, in January 2008, we had a ratio of 17:1 and the data field is 02:23 currently configured the way it was before to display numbers as a currency 02:28 or accounting field. 02:29 So I'm going to change the data type just to make it easier to read. 02:33 To do that, you right-click a cell that contains the data that you want to 02:36 change and click Number Format and from the list of available values, 02:42 in this case I'll click Number and I'll put two decimal places on it. 02:46 Click OK and there we have it. 02:48 We have a ratio of 16.75, which again is different from 17. 02:52 The rounding got taken care of when we added the decimal points. 02:57 Calculated fields extends the types of analysis you can perform in Excel, which 03:00 makes pivot tables even more useful for your financial analyses. Collapse this transcript
Tracking cash flow using a Pivot Chart
 00:01 Pivot Tables are great tools for summarizing large data collections. 00:04 But sharing hundreds of individual values isn't always the best way to 00:07 discover trends in your data. 00:09 Creating a Pivot Chart helps you visualize your data while allowing you to 00:13 change the data's arrangement within the chart. 00:15 Now there are two ways to create a Pivot Chart. 00:17 You can either create a Pivot Table and a Pivot Chart at the same time from the 00:21 same data source, or you can create a Pivot Chart from an existing Pivot Table. 00:26 You can't create a Pivot Chart by itself without an underlying Pivot Table. 00:31 The program just doesn't setup to work that way. 00:33 You need to have the Pivot Table with its attendent data objects before the Pivot 00:38 Chart to work properly. 00:40 If you do want to create a Pivot Table and a Pivot Chart at the same time, you 00:45 can do so by clicking any cell in your data source, usually an Excel table. 00:50 And then on the Insert menu, go over to Pivot Table, and you'll notice that the 00:54 Pivot Table button is separated into two. 00:57 There is an upper-half and a lower-half. 00:59 If you click just the upper-half, you will create a Pivot Table, but if you 01:03 want to create a Pivot Chart, you click the bottom-half, and then click Pivot 01:08 Chart, and then you can jump in to the procedure as I'll show you in just a few seconds. 01:13 But since I already have a Pivot Table on this worksheet, I'll go here, click 01:17 any cell in the existing Pivot Table, and then again on the Insert menu, you can 01:22 click the type of chart you want to create. 01:25 So in this case, I'll click Line to create a line chart. 01:30 Select the type of chart that I want and Excel creates the Pivot Chart. 01:34 Now by default, Excel creates the Pivot Chart on the same worksheet as the Pivot Table. 01:39 So I'll just rearrange things here, there we are. 01:43 So there is a Pivot Chart and it shows the revenue in thousands of dollars for 01:48 FirmA in blue, and FirmB in red, over the course of these two years and you can 01:53 see where the months are delineated along the bottom. 01:57 So there is the chart. 01:58 So when you have a Pivot Chart, you can do all of the things that you can with 02:02 the Pivot Table, you can pivot it and you can filter it. 02:06 So if you want to filter a Pivot Chart, then you can select the field from 02:10 within the Pivot Chart Filter pane, click the Down Arrow, next to the field by 02:14 which you want to filter, and in this case, we'll go by year, and since we only 02:17 want to see 2008, we'll clear the 2009 check-box, click OK, and Excel resizes 02:24 the chart, so that the data you've chosen to display fills the available area. 02:29 Once again, the Pivot Chart Filter pane indicates that we have an active filter 02:34 by placing a funnel icon, as part of the down arrow. 02:39 So to clear the filter, just click Clear Filter From "Year", and the Pivot Chart 02:43 returns to its original value. 02:45 Pivot Charts help you summarize your data visually. 02:48 Combining the ability to change the Pivot Charts arrangement on the fly with the 02:51 detailed-oriented analysis in a Pivot Table, you've a powerful tool in your financial analysis arsenal. Collapse this transcript
3. Analyzing Financial Statements
Reading a corporate financial statement
 00:01 Companies create financial statements to provide information to potential 00:04 investors and creditors, help current investors evaluate the health and 00:08 performance of the company, and summarize the company's resources, so the 00:12 government can tax them appropriately. 00:13 I've selected the annual report from 2008 for the Washington Post Company, 00:20 just to give you an idea of what the annual report for large company might look like. 00:25 I have started out with the income statement, which summarizes the company's 00:29 income as a function of all income and that includes sales revenue, investment 00:34 income, and takes out operating expenses and taxes. 00:38 And like most companies, the Washington Post Company displays multiple years of data. 00:45 So for example, we have data from 2008, 2007, and 2006, and they use that as a 00:51 comparison so that investors and regulators will have an idea of what the trends 00:56 within the company look like. 00:58 It also breaks down the operating revenues and other costs and expenses by category. 01:03 So the way the Washington Post breaks down their operating revenues is by 01:07 Education. They own the Kaplan educational publishing service, so they list that separately. 01:13 Also, they gain revenue from advertising in the Washington Post and elsewhere. 01:18 Circulation and Subscriber Income and then other income, whatever that might be. 01:24 The next element, Operating Costs and Expenses, just shows how the Washington Post 01:29 spends their money in running the company. 01:31 You've got general operating expenses, sales general, administrative, 01:35 depreciation, which we'll cover in Chapter 10 of this course, and so on. 01:41 And you also have Income from Operations, and that includes interest income, 01:45 so that would be from any investments that the Washington Post Company happens to have. 01:50 And then you also see Income Before Income Taxes and the Cumulative Effect of 01:56 Change in Accounting Principle. 01:57 What that means is that the government changed a rule or regulation and 02:01 the Washington Post needed to account for things differently then they had done in the past. 02:05 That made for a change in their accounting results. 02:08 So they are just accounting for it here in their annual report. 02:12 So that is the highlight of the income statement, and if you continue on, 02:20 you'll see a balance sheet. 02:22 And a balance sheet summarizes a company's assets and liabilities. 02:27 Current Assets are cash or items that could be converted to cash within a year, 02:32 and Current Liabilities are liabilities that must be paid within a year. 02:36 So for example, for the Current Assets, the Washington Post Company has Cash and 02:40 cash equivalents, those are the things such as bonds or notes that could be sold 02:43 immediately or at least within a year. 02:46 So that they could get cash out of them. 02:48 Accounts receivable are also considered current assets. Those are generally due 02:52 within 30 to 90 days. 02:54 Deferred income taxes are current assets because it's the money that 02:57 company still has on hand. 02:58 Inventory can be converted to cash through sales, so it's also included and 03:03 other current assets are just anything else that the company could convert to cash. 03:08 When you get away from the Current Assets, you get into the assets that cannot 03:12 be converted to cash readily. 03:14 Buildings, takes a long time to sell them. 03:16 You can't turn them into cash. 03:17 Same thing for machinery, equipment and fixtures and land and so on. 03:22 So those are the highlights of the balance sheet. 03:26 Next we have the Stockholders' Equity statement. 03:29 The Stockholders' Equity statement summarizes the company's value to its 03:33 shareholders by detailing the value of the stock sold, investment income, and it's balance. 03:38 It also shows current liabilities which impact the value of the company to 03:44 shareholders, because the more money the company owes to outside sources, 03:47 the less it has to distribute to shareholders as either a dividend or another sort of payment. 03:54 So you have accounts payable and accrued liabilities, income taxes that are due, 03:59 and any deferred revenue. 04:00 This is revenue that is not on the books this year, but it might be next year. 04:05 Then you also have short-term borrowings and those need to be repaid again 04:09 within a year for them to be considered a current liability. 04:13 So you also have pension funds, other commitments and contingencies and this is 04:17 where you get into the Common Shareholders' Equity. 04:21 The Common Shareholders' Equity refers to shares of common stock. 04:25 You generally have two types of stock for a company. 04:28 One is preferred stock, which gets paid first, before the common stock and often 04:33 at a higher rate of dividend or payment per share than the common stock. 04:39 So that's usually counted separately. 04:41 When you get to the general shareholders, people like you and me who purchase 04:44 shares from a stockbroker or through an online service, then that's where this 04:49 information comes in. 04:50 That's the Common Shareholders' Equity. 04:52 So you have the number of shares of common stock, whether it's divided into 04:56 classes and different types of income or retained earnings, simply money left 05:02 over from previous years that wasn't invested or spent by the company. 05:06 And all of those items represent aspects of Shareholder Equity. 05:11 Finally, we have the company's Cash Flow Statement, and the Cash Flow Statement 05:16 simply summarizes how the company earns and spends its cash. 05:18 Companies generate and spend cash through operations which includes sales, 05:23 financing through stock and other issues and through investments. 05:26 So this just breaks it down. 05:28 You have the Net Income at the top, and the details as you go along. 05:32 So you can have as much or as little detail as you like. 05:36 The government requires a significantly detailed level of reporting from public companies. 05:40 That is, those that are traded on the stock exchange. 05:42 So that's where you get a lot of these details. 05:46 Privately held companies do not need to file this sort of form with the government. 05:50 You can find the Corporate Financial statements on the Security and Exchange 05:54 Commission's website. Just look for the EDGAR database and search for the 10-K 05:58 form of the company you want to investigate. Collapse this transcript
Introducing common-sizing strategies for analyzing financial statements
 00:00 Financial statements provide vital information to potential investors. 00:04 But most industries have competitors of various sizes and sales levels. 00:08 The computer software industry, for example, has competitors including 00:11 multi-billion dollar corporations, all the way down to smaller shops that 00:15 produce specially products for limited markets. 00:17 Comparing sales figures, income and other financial aspects of these diverse 00:21 companies directly isn't always helpful. 00:23 But you can perform meaningful analysis on the financial statements of two 00:26 separate companies using a mathematical technique known as Common-Sizing. 00:29 Common sizing is the practice of treating financial performance in terms of 00:34 another factor such as company size or market share. 00:38 You can common-size a company sales figures, for example, by dividing components 00:42 of those sales figures by the cost of good sold or buying that sales. 00:46 As an example, I have a worksheet with information from two companies, just 00:51 hypothetical companies of different sizes but within the same industry. 00:55 As you can see, Company A with Operating Revenues of over 4 billion is 00:59 significantly larger than Company B which is still impressive but below a 01:04 billion at 685 million dollars of operating revenue per year. 01:08 So the question is, how do you compare the two companies performance? 01:11 And once again, the idea is the common-size. 01:14 So in this example, we'll take these operating cost and expenses such as general 01:19 operations, general administrative, depreciation and so on, and express them as 01:27 a percentage of operating revenues. 01:29 So the way that you create that formula, type an equal sign and then divide 01:34 the value, in this case B8, the operating cost by the value in B6, which are 01:42 the operating revenues. 01:43 Now I haven't pressed Enter yet, and that's because I don't want the reference 01:47 to change when I copy the formula to other cells in this column. 01:51 So I'll press F4, which makes the reference into an absolute reference, and then press Enter. 01:56 And there you have it. 01:57 This is about 45% of this value, so it looks like the calculation is correct. 02:02 And I'll copy the formula down to the remaining cells in the column and you see the values. 02:10 Now we can do the same thing for Company B. 02:13 So we have cell D8 that are the operating expenses for Company B, divided by the 02:20 operating revenues, which are in D6. 02:23 Again pressing F4 to make D6 into an absolute reference, so it won't change when 02:28 we copy the formula and press Enter. 02:30 And we can see the Company B's operating expenses are a little bit more but 02:35 very comparable to those of Company A . So to calculate the percentage of 02:41 revenue for the other expenses, we just copy the formula down the column and there you have it. 02:47 We are getting a value error in cell E12, because there is no value or a 02:54 zero value in cell D12. 02:56 That's simply just a function of the data in the worksheet and it's not an 03:00 error in the formula. 03:01 So it's nothing to worry about. 03:02 You can just treat it as a null value. 03:04 As we can see, Company B is significantly more efficient in terms of its 03:10 selling the general and administrative cost, those are only 27% of their operating revenue. 03:16 And the two companies are pretty similar, within a percentage point on 03:21 amortization of intangible assets. 03:24 But it looks like Company B has significantly more depreciation of property 03:30 plans and equipment, and that could be due to a couple of factors. 03:33 The equipment could be newer and the sum total of the depreciation of all of 03:38 that equipment is adding up and allows for a significant expense this year. 03:43 You do need to be careful when you perform a common-sizing operation to ensure 03:48 that your analysis make sense within the context of an industry. 03:51 For the software industry, for example, you have relatively low production cost, 03:55 so the cost of good sold might not be the best to use in your analysis. Collapse this transcript
Creating common-sized income statements
 00:01 A company's income statement summarizes the company's operating expenses, total 00:05 income and deducts the provision for income taxes. 00:08 You can compare two companies results by dividing the component parts of 00:11 Operating expenses, Cost of revenue, Research and development, Sales and 00:15 marketing, and General and administrative, by the Total operating income from the year. 00:19 In the worksheet that you see in front of you, I pulled some results from the 00:23 Microsoft 2008 Corporate Annual Report and specifically, I have the Operating 00:29 expenses and what I would like to do is compare them to Operating Income. 00:34 So I want to point out that unlike the analysis that I did in the previous 00:38 lesson, these factors here, these items do not add up to the total on Operating income. 00:44 Basically operating income is the net profit or loss that a company makes 00:50 based on its operations. 00:52 It's a function of its total revenue, so don't be thrown off by the fact that 00:57 these number don't add up. 00:58 So to calculate the percentage of Operating Income, again we're using it as a ratio. 01:03 Type =B6, which contains the value for the Cost of revenue divided by B11, 01:09 the Operating Income. 01:11 And again, when I copy the formula further down the column, I want the 01:15 cell reverence B11 to remain constant, so I press F4 to make it an 01:20 absolute reference. 01:22 Press Enter and we see that Cost of revenue is 51.57% of Operating Income. 01:28 And I'll just copy that down. 01:30 You'll see that in this case, Total operating expenses were higher than 01:35 the Operating Income. 01:37 But that's not unusual. 01:38 Say a company has an Operating revenue of \$100 million in a year and it has 01:43 total operating expenses of 80 million. 01:46 They still have made \$20 million on their operations. 01:49 So they're in the black. 01:50 The only question is whether they can reduce their Operating expenses or apply 01:55 the spending more effectively so that Operating Income goes up. 01:59 If you wanted to compare two companies, one like Microsoft over here which has 02:03 an extremely high operating revenue and cost associated with that revenue, you 02:08 would type in the formula = and the value is in F6 divided by the value in F11 02:15 and again, we don't want this all referenced to F11 to change when we copy the 02:19 formula, so you press F4, to turn it into an absolute reference and press Enter 02:25 and there your have your result. 02:27 And I'll copy the formula down here and now you're able to compare the two 02:33 companies performance in terms of Operating Income, based on their expenses for 02:37 Cost of revenue, Research and development, Sales and marketing, and so on. 02:41 Common-sizing a company's income statement enables you to determine what portion 02:45 of the company's expenses go to which components of its cost. 02:48 If one company has a much larger percentage of general and administrative cost 02:52 for example, you might want to hold off investing in that company, until it 02:55 finds efficiencies in that area, freeing up more resources for product development and growth. Collapse this transcript
Creating common-sized balance sheets
 00:00 Just as you can compare the income statements of two differently sized 00:03 companies, you can common size entries on their balance sheets to allow for 00:07 meaningful analysis between those companies. 00:10 One useful balance sheet element to use as a basis for common sizing is the 00:13 current liabilities account. 00:15 The Current Liabilities Account includes account payable, income taxes, 00:18 short-term unearned revenue, securities lending payable and the even popular, other. 00:25 Accounts payable is a measure of how much money is owed to creditors for 00:28 products and services and securities lending payable shows how much money is 00:32 owed to creditors who purchase bonds, and other debt instruments issued by the company. 00:37 In this case, I'm going to use the Total current liabilities as my Common-sizing 00:41 factor and I'll be comparing the companies' assets. 00:45 So first we have the Total cash, cash equivalents, and short-term investments. 00:48 We have Total current assets which includes the cash, but also includes 00:53 other items that can be converted to cash within a year and then Total 00:57 assets which includes things such as buildings and other assets that can't 01:02 be converted readily into cash. 01:03 So, to create the formula, I'll divide the value in B9 by the value of the Total 01:12 current liabilities in B7 and again, when I copy the formula, I don't want the 01:17 cell reference to B7 to change. 01:19 I want it to remain absolute, so I'll press F4 and that makes the cell reference 01:25 an absolute reference. 01:26 Press Enter and I'll express these as percentage with two decimal points and 01:37 I'll copy the formula down, so that we've the Total current assets and Total 01:43 assets also expressed as a percentage of the Total current liabilities. 01:47 Here we have Company B, again, it's significantly smaller. 01:52 But when we perform the analysis the common sizing, we take F9, divide that by 01:59 the Total current liabilities for the company, which are in F7. 02:02 Again, pressing F4, say the cell reference doesn't change when we copy the 02:06 formula to another cell. 02:08 Press Enter and again, I'll format these as percentages adding two decimal 02:13 points and copy the formula down. 02:18 So in this case, these two companies have a lot of cash on hand and also lot of 02:22 assets in relation to their total current liabilities. 02:26 The higher the ratio between a company's assets and its liabilities the 02:29 better shape it's in, because the better off it is in terms of what it owes its creditors. 02:34 Common-sizing a balance sheet enables you to determine how well a company 02:38 is managing its debt. 02:40 Both the members of its supply chain and the creditors who bought bonds and notes. 02:44 That said, you should always check a company's guidance and explanations in its 02:48 quarterly or annual report, so you have a context for the raw numbers. Collapse this transcript
Calculating percentage changes in financial statements
 00:01 When you analyze a company's performance, it's often useful to see how much 00:04 the company's revenue or expenses have increased or decreased since the last 00:08 month quarter or year. 00:11 Some company's annual reports include calculations indicating these changes, but 00:14 if they don't, you can create formulas to calculate that difference quickly and 00:17 easily, especially if you store your data using an Excel table. 00:21 So the first step is to input your data into an Excel table. 00:25 I have one here in my Percentage Change worksheet. 00:28 I have the values for operating cost, selling, general and administrative, 00:33 basically operating costs and expenses for fiscal year 2008 and fiscal year 2007. 00:39 Once again, my goal is to calculate the percentage change from 2007 to 2008. 00:45 To do that, you create a formula, where you subtract the previous year's value 00:49 from the current year value and then divide it by the previous year to 00:52 calculate the change. 00:53 So when you put that into operation, you type in =, the present year or the 00:59 most recent year 2008 is in cell B6, subtract the previous year, C6 and close the parenthesis. 01:10 The reason that I have these two values in a parenthesis is because when I put 01:15 in division and divide by C6, Excel would divide C6 by C6, because division is a 01:24 higher priority operation than subtraction. 01:27 So if there were no parenthesis, it will do the division first, dividing C6 by 01:31 C6, which is 1, it has to be, and subtract the value of 1 from the value in B6. 01:36 And you would get the wrong answer for your formula. 01:39 So, by using the parenthesis, I'm able to calculate their percentage change correctly. 01:43 So, my formula is in order and I press Enter and there you have the percent change. 01:49 That is correct in this case, so I don't make any changes. 01:52 But if you did want to make some changes on how Excel handles formulas that you 01:57 enter into tables, you can use the AutoCorrect Options. 02:00 Click here and then you can either undo the current operation or you can have 02:04 Excel not automatically create calculated columns whenever you enter a formula 02:10 into a cell at the top of a column. 02:13 It's useful to compare a year to a previous year, but you should also compare 02:16 previous quarters or months to the previous quarter. 02:19 If you want, you can also perform To A Year Ago Analysis where you compare this 02:24 January to the previous January. 02:26 That's particularly useful for companies who have sales that are highly seasonal. 02:31 Once you've calculated a company's percentage change for income and expenses, 02:35 you can compare its values to those of other companies based on the values in 02:38 their annual reports. 02:40 Revenue growth provides one obvious data point for you to compare, but even 02:44 negative growth might not be the worst outcome imaginable. 02:47 During a recession, the company whose sales decrease the least will often be in 02:51 the best position when the economy recovers. Collapse this transcript
4. Calculating Profitability Ratios
Calculating earnings per share
 00:01 Investors can make money by purchasing stock in companies that pay dividends 00:04 on a per share basis. 00:05 Companies that pay dividends regularly, such as every quarter, decide how 00:09 much money they want to distribute to shareholders and make that payment 00:12 after the quarter ends. 00:13 To calculate Earnings per Share, you divide the income to be paid to 00:17 shareholders of common stock by the number of outstanding shares. 00:20 As you can see here in our worksheet, we have 45 million shares. This is 00:25 45,000, but because all values are in thousands, we have 45000x1000, which is 00:31 45 million outstanding shares. 00:33 We also have the net income available for shares of common stock, which is 00:37 18000x1000 or 18 million. 00:40 So then to find the Earnings per Share, you create a formula that divides the 00:45 Income Available for Common Shares, which is in B6, by the number of 00:49 Outstanding Shares, which is in B5, press Enter and you have an earning of 40 cents per share. 00:55 There are some factors you should take into account when you're evaluating a 00:59 company based on their Earnings per Share. First off is that companies can 01:02 decide against paying dividends to shareholders and instead retain the 01:05 earnings to spend on items such as research and development, new office 01:09 buildings or equipment upgrades. 01:10 Earnings per Share can also be skewed positive by generating unusually high 01:14 income such as selling a product line to another company or negatively by 01:18 one time charges such as writing off substantial debt. 01:21 Companies can also buy back stock to reduce the number of outstanding shares, 01:24 making the per share price look better. 01:27 Be sure to check a company's public filings to see if they've bought back 01:30 any shares recently. 01:31 You should also be sure to determine whether a company has issued any preferred stock, 01:35 which has paid its dividend before consideration, if any, is given to the 01:39 holders of common stock. 01:41 Companies often give guidance regarding how much of a dividend they expect to 01:44 pay in future quarters. Comparing the company's Earnings per Share with the 01:47 amount of money they retain for capital improvement and business development, 01:50 provides a solid basis for evaluating the attractiveness of a company stock. Collapse this transcript
Calculating return on equity and return on assets
 00:00 In this lesson, I'll discuss how to calculate return on equity and return on 00:05 assets, two important ratios when you're considering the financial health of a company. 00:09 A publicly traded company has one goal: to maximize a shareholder value. 00:14 The bottom line measure of a firm shareholder value is its equity, which is 00:17 Total Assets minus Total Liabilities. 00:20 Comparing the relationship between a company's earnings to its equity provides a 00:23 clear picture of the company's ability to generate returns for its stockholders. 00:27 You divide the company's net income, which is its income after taxes by its 00:32 shareholder's equity. 00:33 So here in the worksheet, I have two entries, one is the Net Income After Taxes 00:37 and the other is the Shareholder's Equity. 00:40 So to divide the two, type equal to start the formula and we have Net Income 00:45 After Taxes in cell B5, divide that by Shareholder's Equity in B6 and press 00:50 Enter and there you have your ratio of 0.11 for the return on equity. 00:56 You can compare this result to other companies in which you're considering 00:59 investing, so you can determine which company has provided a better result 01:02 for its shareholders. 01:03 And just as a general basis for comparison, most firms in the S&P 500 generate 01:09 ROE ratios of between 10% and 15%. 01:13 You should also take into consideration a firm's Return on Assets. 01:17 Companies need to make sure that their infrastructure, which includes their 01:20 office buildings, computers and manufacturing systems, are of high enough 01:24 quality to support the company's activities. 01:25 It's important that companies not spend too much on their assets, as doing so 01:29 cuts into profitability, but they should spend enough to ensure their 01:32 infrastructure supports the operations. 01:34 One measure you can use to determine how efficiently a company spends its money 01:37 on assets is by calculating the Return on Assets ratio. 01:42 To calculate the Return on Assets ratio, you divide a company's earnings before 01:46 interest and taxes by the company's total assets. 01:48 So here we have those two values, the Earnings Before Income and Taxes and the Total Assets. 01:54 So I'll divide earnings, B5/B6 and you have a ratio of 0.04. 02:01 Comparing a company's Return on Assets ratio to those of other firms in the 02:04 sector help you determine how efficiently the company employs its assets. 02:09 Reductions in a company's Return on Assets ratio could indicate either 02:12 economic opalescence, such as when a company's manufacturing system falls 02:16 behind the technology curve or it could mean that the company just invested a 02:20 significant amount of money in the new system and has yet to reap the benefits 02:23 of the new technology. 02:25 As always, be sure to read a company's public filings to help you interpret the 02:28 numbers included in those documents. 02:30 You should compare a company's Return on Equity and Return on Assets ratios to 02:34 determine how the company pays for its operations. 02:37 When a company's Return on Equity is higher than its Return on Assets, it's 02:40 usually an indication that the company finances its operations through sales of 02:44 stock instead of taking on debt. 02:46 If Return on Assets is higher than Return on Equity, then the opposite is true. Collapse this transcript
Calculating gross profit margin and net profit margin
 00:01 One of the most basic measures of profitability is how much money a company 00:04 makes on each item it sells. 00:06 You can analyze a company's profit by subtracting the cost of goods sold from 00:09 total revenue and then dividing that result by total sales revenue. 00:13 The result is the gross profit ratio. 00:15 A company's gross revenue ignores all expenses except the cost of goods sold. 00:20 In industry lingo, gross revenue is Earnings Before Interest, Taxes, 00:24 Depreciation, and Amortization or EBITDA. 00:28 So here in the worksheet, you can see that we have the Sales Revenue and 00:33 the Cost of Goods Sold. 00:34 So again what we're going to do is subtract cost of goods sold from sales 00:38 revenue and then divide it by sales revenue to get the gross profit margin. 00:42 So = (B5-B6/B5), press Enter, and there is our ratio, 0.56. 00:52 You can calculate a company's gross profit margin for all of its products, but 00:56 you can also break the numbers down by product to get an idea of how efficiently 01:00 a product contributes to the company's bottom line. 01:02 Some companies follow what's called a Loss Leader Strategy, where they sell one 01:06 item such as a razor at a loss, because the ancillary products such as razor 01:10 blades generate substantial profits. 01:12 Another example of this Loss Leader Strategy comes from the video game console market. 01:16 Companies often sell their consoles at a loss and make up the difference in game sales. 01:20 In those cases it might make sense to consider the products as a group and not individually. 01:24 Calculating a company's gross profit margin tells us how effectively the company 01:28 earns money with its products, but it ignores expenses such as interest, 01:32 depreciation and taxes. 01:34 Because investors need to know more about a company's finances than simply the 01:37 amount of money it makes on each sale, you can calculate the Net Profit Margin, 01:41 which takes interest, tax, depreciation and amortization expenses into account. 01:46 Because of that, it's a true measure of a company's ability to generate cash in 01:50 excess of its expenses. 01:52 To calculate a company's net profit margin, you subtract all interest, tax, 01:56 depreciation and amortization expenses from the company's sales revenue and then 02:00 divide the result by sales revenue. 02:03 That also includes cost of goods sold. 02:05 So on this worksheet, we have the sales revenue and then we have all of the 02:09 cost, not just cost of goods sold. 02:11 So to calculate the Net Profit Margin, we have a formula that subtracts all of 02:18 the cost from B5, the sales revenue. 02:20 So we have sum of B6-B10, and these are the costs, and divide that result by the 02:30 sales revenue, which is in cell B5. 02:33 The result is the Net Profit Margin, which reflects a company's true ability 02:37 to generate profits. 02:39 Calculating the Net Profit Margin for individual products can generate different 02:42 results based on how the company assigns its overhead cost. 02:45 For example, a company might need to take out a loan so they can manufacture a 02:48 product, in which case the company can assign that facility's interest, 02:52 depreciation and amortization expenses to the product's account. 02:55 In many cases though, the company will spread the burden of taxes, interest, 02:58 depreciation and amortization across all of its product lines. 03:02 Also, it's not unusual for a company's gross and net profit margins to vary by season. 03:07 Many retail businesses rely on brisk holiday sales to turn their profit for the year. 03:11 Companies with consistently high profit margins relative to other companies in 03:15 their market sector sell products efficiently and keep their operating expenses in check. 03:19 The cash they generate can be distributed to shareholders as dividends making 03:23 the company more attractive to investors or be used for research and development 03:27 to enhance their products. Collapse this transcript
5. Calculating Liquidity and Activity Ratios
Calculating the current ratio and quick ratio
 00:01 In this lesson I'll discuss two very useful ratios, the current ratio and 00:05 the quick ratio, also known as the acid test. 00:08 If you want to evaluate a company's short -term financial health, one of the best 00:11 ratios you can use is the current ratio, which compares a company's current 00:15 assets to its current liabilities. 00:17 A current asset is an asset that can be reasonably assumed to be converted to 00:21 cash within one year. 00:23 Current assets include cash, accounts receivable, and inventory. 00:27 Similarly, a current liability is a liability that must be paid within one year. 00:32 To find a company's current ratio, you divide the value of current assets by the 00:35 value of current liabilities. 00:37 I have those values here in this worksheet. 00:39 It's just data from a fictitious company, current assets and 00:42 current liabilities. 00:43 To calculate the current ratio, create a formula that divides current assets by 00:49 current liabilities, and there is the result. 00:52 Most healthy companies have a current ratio of at least 1 indicating that their 00:55 assets are greater than their liabilities. 00:58 You can also use the two values from the current ratio, current assets and 01:01 current liabilities, to determine the company's working capital. 01:04 To calculate working capital, which is essentially the cash the company has on hand, 01:08 you subtract current liabilities from current assets. 01:11 So in this case we have B5, Current Assets, - B6, Current Liabilities, and there 01:18 is the amount of working capital that you have. 01:20 The higher the current ratio, the better. 01:22 But a low current ratio most likely won't be a problem for a company that has 01:26 the potential to borrow money to increase its cash on hand. 01:29 Unless a company makes its money exclusively through consulting services or 01:33 licensing its patents and other intellectual property, it will make its money by 01:36 selling physical goods. 01:38 Goods in inventory must be sold to be converted to cash, so analysts 01:42 developed the quick ratio. 01:43 The philosophy behind the quick ratio is that because inventory is the least 01:47 liquid asset, you should exclude it from the current assets calculation by 01:51 subtracting the value of goods and inventory and then divide the new total by 01:55 current liabilities. 01:56 Here we have current assets and inventory is called out separately and we have 02:01 current liabilities. 02:03 So the quick ratio would be B5-B6 and divide that by B8 to get the quick ratio. 02:12 Now be aware that companies can alter their quick ratio by choosing when to make 02:16 major purchases or sell off assets. 02:19 If a company intends to buy patents or other intellectual property from another firm, 02:22 you should take that into account if it looks like the deal has been 02:26 reached before the end of a fiscal year. 02:28 It's vital that you only use the quick ratio to compare companies with 02:31 similar business models. 02:33 A consulting firm, for example, would carry no inventory. 02:36 So its quick ratio would be the same or very nearly the same as its current ratio. 02:40 A department store chain, by contrast, would have a large inventory and 02:44 therefore a much lower quick ratio than the consulting firm. Collapse this transcript
Calculating the average collection period
 00:00 The name of the Average Collection Period ratio is a bit misleading. 00:04 After all, how can an analyst accurately measure how long on average it takes 00:08 a company to collect on its debts without having access to data on every transaction? 00:13 The answer, of course, is that they can't, but you can discover useful 00:16 information about a company's collection from its public data. 00:19 The Average Collection Period ratio is calculated in two parts. 00:23 In the first step, you divide Total Sales by Accounts Receivable, which is the 00:27 amount yet to be paid for completed sales to calculate the Receivables Turnover ratio. 00:32 So on this worksheet, we have Total Sales, Accounts Receivable and Days in Year, 00:36 which we'll use later. 00:37 So to calculate the Receivables Turnover ratio, it's equal B5 divided by B6 and 00:45 there you have that ratio and again, it tells you how many times in a year a 00:49 company earns the money currently owed by its creditors. 00:52 You can compare the Receivables Turnover ratio to the Times Interest Earned 00:55 ratio, which I'll cover elsewhere in this course. 00:58 That ratio calculates the number of times a year a company earns enough money to 01:01 cover its interest payments on its debt. 01:03 In the second step, you divide 365, the number of days in a year by the result 01:07 of the first step, the Receivables Turnover ratio. 01:10 You treat the result as a number of days, which you interpret as the 01:13 Average Collection Period. 01:16 So here we have the days in the year, which are in cell B7, and you divide that 01:21 by the Receivables Turnover ratio, B9. 01:24 Press Enter and you have an Average Collection Period of just over 80 days. 01:29 A company's Average Collection Period provides an indirect measure of its health 01:32 by providing insight into that company's credit granting policies. 01:35 If a company's average collection period is higher than that of other companies 01:38 in the same sector, it could indicate that its customers are running into 01:41 financial difficulties and therefore pay more slowly. 01:44 Finally, I would like to point out that analysts differ on how they calculate a 01:47 company's Average Collection Period. 01:49 So don't be surprised if you are asked to use a different formula. 01:52 As long as you use the same formula consistently, your results will be useful. Collapse this transcript
Calculating inventory turnover
 00:01 Companies must walk a fine line between maintaining enough inventory to 00:04 enable immediate sales, but without producing too many units that end up 00:07 sitting in a warehouse unsold. 00:09 Modern companies do their best to produce goods as needed on a 00:12 just-in-time basis. 00:13 So they limit their upfront investment and don't have unsold products sitting in a warehouse. 00:17 The inventory turnover ratio indicates how well a company handles that problem. 00:21 To calculate the inventory turnover ratio, you divide the cost of goods sold by 00:25 the value of goods currently in inventory. 00:28 So, I have those figures here. 00:30 The cost of goods sold and the inventory, and to calculate the ratio, we divide 00:36 B5, cost of goods sold, by B6, which is the inventory turnover ratio. 00:41 The inventory turnover ratio is a measure of how efficiently a company manages 00:45 its production processes. 00:47 One example of managing inventory is the book publishing industry. 00:51 On a per unit basis, it is much less expensive to print 100,000 copies of a book than 10,000. 00:56 So publishers will often order a huge run of books. 00:59 So, half of them with the regular price, and offer the rest of the stores at a deep discount. 01:03 This process called remaindering enables stores to purchase for \$1 older 01:07 books that might have been sold at a wholesale price of \$10 when the book was still popular. 01:11 Selling the extra books at that deeply discounted price, removes the stock from 01:15 the publisher's inventory, and improves the company's inventory turnover ratio. 01:20 If you're thinking about investing in a manufacturing firm, you should compare 01:23 its inventory turnover ratio to the industry average, and the ratio of other 01:27 companies in the sector. 01:29 A relatively high inventory turnover ratio indicates that goods are sitting in a 01:33 warehouse, taking up space and depreciating. Collapse this transcript
6. Calculating Leverage Ratios
Calculating the equity ratio
 00:01 When a company decides to raise money by selling stock, it holds an Initial 00:04 Public Offering or IPO. 00:07 The company can then use that cash to fund its operations. 00:10 One way to determine how well a company employs its stockholder's investments is 00:14 by calculating the company's equity ratio. 00:17 To calculate an equity ratio, you divide a company's total assets by its total equity. 00:22 A corporation's total equity is the sum of its retained earnings, which is 00:26 unspent cash sitting in a bank account, and the amount of money received from 00:30 investors through sales of stock. 00:32 Total assets are everything the company owns. 00:35 It can include cash, cash equivalents such as short-term notes and also anything 00:40 that's a longer term asset, such as a building or intellectual property. 00:45 So, to calculate the equity ratio, you divide total assets in cell B5 by total 00:52 equity in cell B6, and there you have the ratio. 00:56 Now, a low equity ratio isn't necessarily a bad thing, but a decreasing 01:00 equity ratio could indicate that a company's sales, which generate cash, aren't going well. 01:05 The equity ratio provides an insight into the ability of a company to generate 01:09 return on its shareholder's investments. 01:11 Sudden changes in a company's assets such as through sales of product lines, 01:15 buildings, purchases of buildings and so on, can distort this value in the short term. 01:20 So, be sure to consider the company's equity ratio over time before drawing any conclusions. Collapse this transcript
Calculating the debt ratio
 00:00 Like private individuals, companies can borrow money to finance their operations. 00:04 Some mechanisms to borrow money include taking out loans, issuing stock, and 00:08 selling bonds to raise capital. 00:10 Also, like private individuals, companies can only borrow so much money before 00:14 they start to become bad credit risks. 00:16 One basic measure of a company's relative indebtedness is the debt ratio. 00:19 To calculate the debt ratio, which is also some times called the total debt 00:23 ratio, you subtract a company's total equity from total assets, and divide that 00:27 result by total assets. 00:29 So, I have a worksheet here, with Shareholders' Equity, Total Assets, and Total 00:34 Liabilities, which we'll use in a moment. 00:35 Now, before I type in the debt ratio formula, I'd like to point out that I have 00:39 a copy of the formula here in cell C9. 00:43 You can see the formula up here in the formula bar. 00:46 Now, the reason that Excel doesn't treat this formula as a formula, it displays 00:51 it as text, is because I have an apostrophe here at the start of the entry. 00:56 Anytime a cell's contents begin with an apostrophe, Excel treats the value as 01:01 text, regardless of whether it's a number, a currency value, and date, whatever. 01:05 It just treats it as a string of characters. 01:07 So, that's the formula I'll be entering in, without the apostrophe. 01:11 So Excel treats it as a formula. 01:13 We have =B6-B5, which is again assets minus equity, divided by B6. 01:20 Now, because the two values that Excel was using in this formula, B6 and B5, are 01:25 both currency values, it attempted to display the result as a currency value 01:30 here, except it doesn't make any sense because it's a ratio. 01:33 So, I'll change that cell's formatting to a number. 01:36 Now, we see a debt ratio of 0.39, which is correct. 01:39 There are two useful ways you can extend the debt ratio analysis. 01:43 The first is by dividing the company's total liabilities by its total equity, 01:47 which gives you the debt-to-equity ratio. 01:49 The debt-to-equity ratio is an extremely useful tool for analyzing a company's health. 01:54 The rule of thumb is that manufacturing firms should maintain a debt-to-equity 01:57 ratio of between 0.5 and 1.5. 02:00 If a company borrows too much money, it can run into problems servicing its debt. 02:04 On the other hand, a company that doesn't borrow any money or not enough money 02:08 might be missing out on opportunities because it's being too conservative. 02:11 So, to calculate the debt-to-equity ratio, we divide the company's total 02:15 liabilities, which are in B7 by its shareholders' equity in B5, press Return, 02:21 and there is the result. 02:23 You can also calculate a measure called the Equity Multiplier, which is total 02:26 assets divided by total equity. 02:28 The equity multiplier shows a company's total assets per dollar of 02:32 shareholders' equity. 02:33 A higher equity multiplier indicates higher financial leverage, which means the 02:36 company is relying more on debt to finance its assets. 02:39 So, to calculate the equity multiplier, we divide total assets by shareholders' 02:45 equity, and there's the result. 02:47 The debt ratio, debt to equity ratio, and equity multiplier are all based on the same factors. 02:53 If you can calculate any one of those ratios, you can calculate the other two. Collapse this transcript
Calculating the times interest earned ratio
 00:01 When a company borrows money to finance its operations, it must pay interest on 00:04 those loans so the lender can make a profit on the transaction. 00:08 The more money a company makes, the more comfortably it can handle 00:10 those interest payments. 00:12 The Times Interest Earned Ratio calculates the number of times a company could 00:15 pays its yearly interest bill based on its earnings before interest and taxes. 00:19 To calculate a company's Times Interest Earned Ratio, you divide Earnings Before 00:23 Interest and Taxes or EBIT by the interest paid for the year. 00:28 So, in this worksheet I have Earnings Before Interest and Taxes, Interest, 00:32 and also Taxes. 00:34 Even though we don't use the Taxes figure in this formula, you'll usually find 00:38 it on a worksheet or in an annual report along with the Interest and the EBIT, 00:43 Earnings Before Interest and Taxes. 00:45 But you don't need to worry. 00:46 It's not used in the formula. 00:48 To calculate the Times Interest Earned Ratio, you divide the Earnings Before 00:52 Interest and Taxes by the interest that's due in a given year. 00:55 So, to that you divide B5, the EBIT, by Interest, and press Return, and there 01:02 is your result. 01:03 This company makes six times the money it needs to pay its interest every year, 01:07 which indicates a healthy earning power and that it's a no danger of defaulting 01:10 on any of its loans. 01:12 Now, even though it might seem prudent to keep a company's Times Interest 01:15 Earned Ratio as high as possible, an extremely high ratio might indicate that a 01:19 company either has not taken on enough debt to pursue new opportunities, or 01:24 that it is paying down its current debt too quickly, using funds that could be 01:27 invested elsewhere. 01:29 By contrast, a low Times Interest Earned Ratio won't be a problem for a company 01:32 with a lot of cash in the bank, but the closer the ratio gets to 1, the more 01:36 likely it is that company will run into problems. 01:39 Potential lenders can use the Times Interest Earned Ratio to evaluate a 01:42 company's creditworthiness, and potential investors can use the number to 01:46 determine whether a company is managing its debt load effectively. Collapse this transcript
7. Calculating the Effect of Interest Rates on Loan Repayment
Calculating simple interest and compound interest
 00:00 One of the first rules you learn in business is that no one gives you money for free. 00:05 If you plan to borrow money with the goal of making a profit, you should 00:08 expect to pay interest so that the institution that loaned you money can make a profit too. 00:12 There are two main ways to calculate interest, Simple Interest and Compound Interest. 00:17 Simple Interest is calculated based on the principal, interest rate, and time only. 00:22 In other words if you pay a loan back over time, the unpaid interest doesn't 00:27 part of the principal. 00:28 As an example, take a look at the loan terms we have in this worksheet. 00:33 We have a principal of \$200,000, a rate of 6%, and a term of 5 years. 00:39 So to calculate the total payment on the loan we'll add the principal to the 00:45 principal times the rate times the term. 00:51 And we have \$260,000. 00:54 You can verify that answer is correct. 00:56 6% of \$200,000 is \$12,000. 00:59 So you multiply that by 5, which would be \$60,000, and you have a total of \$260,000. 01:07 One other scenario that uses simple interest would be in annuity that guarantees 01:11 a set return each year. 01:12 Most loans and investments calculate their vales using compound interest. 01:16 But you might counter simple interest investments, so you should know how to calculate it. 01:21 Unlike simple interest, investments that use compound interest include 01:24 previously earned interest when determining how much interest to pay the next 01:28 time a payment is due. 01:30 One example would be a certificate of deposit with an annual percentage rate of 6%. 01:35 So we have the example here in the worksheet and you'll notice that the terms of 01:38 the loan are exactly the same as we had for the simple interest example. 01:43 We have a principal of \$200,000, a rate of 6%, and term of 5 years. 01:49 To calculate the total due over the life of the loan or the life of the investment, 01:52 you use the formula here in cell D7. 01:55 You multiply D3, which is the principal, by 1+ the rate, which is 6%. 02:03 So, that would be 1.06, and you raise 1.06 to the power of 5, which is the number years. 02:10 So, to calculate the future value of the loan, you type in the formula D3, 02:15 again the principal, times 1+B4, the rate, raised to the power of the value in B5, 02:26 the number of years, and press Return. 02:29 There you have the future value of the loan. 02:30 Now, please note that the future value of the investment is \$267,645.12, which 02:37 is greater than the \$260,000 result from the simple interest calculation. 02:42 That is the power compounding interest monthly as opposed to annual and 02:45 for including previously earned interest in future interest calculations. 02:49 Now, you might also be called on to evaluate a loan or an investment where 02:53 interest is compounded continuously and for that you use the formula in cell D9. 03:00 So you multiply the principal in cell B3 in this case, by the natural logarithm, 03:07 and this is a function that you use to calculate it. 03:10 The math is fairly advanced. 03:11 You don't really to know it. 03:13 You just need to know how to calculate continuously compounded interest. 03:17 But you multiply B4, the rate, by B5, the term. 03:25 There you have your answer, \$269,971.76. 03:31 Calculating compound interest is one of the skills you use frequently 03:34 in financial analysis. Collapse this transcript
Applying nominal versus effective interest rates (APR versus APY)
 00:00 Financial institutions like to advertise the interest rates their products 00:04 return to investors. 00:06 Most ads list an annual percentage rate, which you can use to calculate 00:09 the yearly return using the compound interest formula you learned earlier. 00:13 Some ads, however, list an annual percentage yield, which is the net return and 00:17 not the actual interest rate applied to the investment. 00:20 When comparing two or more investment opportunities, you must make sure that all 00:24 of the investments' terms are expressed using the same type of interest rate. 00:28 Annual percentage rate versus annual percentage yield. 00:31 There are two built-in Excel functions you can use to convert annual percentage 00:35 yields to annual percentage rates and vice versa. 00:37 The first is the Effect or Effective function, which you can use to determine 00:42 the annual interest earned in a year expressed as a percentage of the principal. 00:46 You can use the Nominal function to determine an annual percentage rate when 00:50 presented with an annual percentage yield. 00:53 So let's take a look at the example I have here in the worksheet. 00:56 We have two investments, one of which has an annual percentage yield; 01:00 the other which has an annual percentage rate. 01:02 Now, the annual percentage rate of 5% with a principal of \$20 million and 01:09 a term of 10 years, I already have enough information to calculate the future 01:13 value of the investment. 01:14 In other words, because I know the APR, the principal and the term, I can create 01:18 a formula, which you see here on the Formula bar, the compound interest formula, 01:23 to determine the total value of the investment. 01:26 However, with the annual percentage yield, I do not have enough information to 01:30 calculate the future value of the investment. 01:33 Instead, I need to determine the annual percentage rate. 01:36 So to calculate the annual percentage rate, given an annual percentage yield, 01:41 you use the Nominal formula and include the Effective rate, which is the APY in 01:48 cell B5 and then the number of periods per year. 01:51 In other words, if the interest is compounded within the year, how many times is it compounded? 01:57 In this case, we are assuming that the loan is compounded monthly. 02:00 So we use the value from cell B6 and Return. 02:06 So with an annual percentage yield of only 5%, the annual percentage rate is 4.89%. 02:11 That leads to a future value of the investment of \$32,234,973. 02:20 Compare that to the future value of the loan, which has the 5% APR. 02:25 The future value here is about \$300,000 higher, and when you are talking 02:30 in terms of a \$20 million investment, \$300,000 is real money. 02:34 So you need to play close attention to it. 02:36 If you want to express the annual percentage rate of 5% as an APY, to compare it 02:41 more directly to the APY of 5% from the first investment, you can use the Effect 02:46 or Effective function. 02:48 So type =EFFECT, then you input the nominal rate, which is in cell E5. 02:57 Number of periods per year, which is in E6, Return and there you have an APY of 5.12%. 03:07 Every fraction of a percentage point of interest makes a difference in the 03:10 value of a loan or an investment, especially when the amounts are in [00:03:14.00s] the millions of dollars. 03:15 Take the time to ensure the interest rates of the investments you are 03:18 considering are expressed using consistent terms. Collapse this transcript
Calculating the number of days between events
 00:01 Excel 2007 gives you many tools to calculate the number of calendar days or the 00:05 number of working days between two dates. 00:07 When you calculate the number of working days, Excel excludes 00:10 weekends automatically. 00:12 But you also need to maintain a list of business holidays so the functions can 00:15 leave them out as well. 00:16 I want to stress that it is absolutely vital that you store the dates in cells 00:21 with the Time/Date format and not as text. 00:25 I have done that in this worksheet, which I'm using as my example. 00:28 I have an Initiation Date and a Maturity Date for an investment and you'll 00:32 notice that these cells both have the date format that is absolutely vital. 00:38 If you want to determine the total number of days between two dates, all you 00:41 need to do is subtract one date from the other. 00:44 So in this case, we have the later date of December 24th that's in cell B5, when 00:49 we subtract the initiation date which is in cell B3 and we find that we have 174 00:55 days, but that includes weekends and holidays. 00:58 How about if you want to determine the number of working days between those two dates? 01:02 For that, you use the NETWORKDAYS function. 01:05 So you type in =NET and then you have the start date which is the earlier 01:10 date and that is in cell B3, and end date which is in cell B5 and then a list of holidays. 01:18 Now, I can't switch over to the worksheet right now because I'm in the middle of 01:21 entering a formula but the holidays are on the Holidays worksheet in a table 01:26 called Holidays2010. 01:29 So what I'll do is I'll just start typing in the name of the table, holidays, 01:32 and there you see Holidays2010, and they are in the Holiday column. 01:36 So I have completed the formula, hit Return and you'll see that you have 119 01:42 working days between July 3rd and December 24th of 2010. 01:48 Now, let's say that you want to determine the next workday after December 24th of 2010. 01:54 The next calendar day is December 25th and that's Christmas, a holiday in the United States. 01:59 So that will not be a working day. 02:01 To find the next working day after a given number of working days, excluding 02:05 weekends and dates identified as holidays, you use the WORKDAY function. 02:09 Now, when I enter the formula as you would expect, workday, start date in cell 02:14 B3, the number of days which is in cell B9 and then the list of holidays which 02:20 are in the Holidays2010 table in the Holidays column. 02:25 You get an incorrect result. 02:27 Instead of getting the workday after December 24th, you get the day prior to that. 02:33 So what happened? 02:34 Let's take a look at the formula and it appears to be right, but what's 02:38 happening is that Excel is counting the initiation date, July 3rd, as the first day. 02:45 So what you need to do is add one to the working days total. 02:50 So that value comes from cell B9 in this case. 02:53 So you type +1, hit Return and you get the next working day which is December 02:58 27th, the Monday after Christmas. 03:01 Finally, some financial calculations require you to use a 360-day calendar. 03:05 That assumes a 30-day month and 12 months a year. 03:09 You'll use a 360-day calendar for some bond transactions and also for 03:13 loans between banks. 03:15 To calculate the number of days between two dates using a 360-day calendar, you 03:20 use the DAYS360 function. 03:22 So to determine the number of days between our initiation date and our maturity 03:26 date, using a 360-day calendar, we use the start date, E3, the end date of B5 03:35 and the method we leave blank because we'll be using the default which is 360, 03:39 30 days, 12 months and Return. 03:43 And we'll find that using that system, we have 171 days between our initiation 03:48 date and our maturity date. 03:50 Working with dates in Excel does seem confusing at first, but most of the time, 03:54 you can use the techniques in this lesson to determine the number of days you 03:57 have to complete the task or for an investment to mature. Collapse this transcript
8. Determining Future Cash Flows
Computing the future value of an investment
 00:01 One of the more conservative investment strategies available is to purchase an 00:04 instrument such as a certificate of deposit or fixed rate annuity that enables 00:09 investors to trade lower risk for relatively low but known rates of return. 00:14 You can evaluate this type of investment using the future value or FV function. 00:20 The FV function takes five arguments, rate, number of periods, payment, 00:25 present value and type. 00:27 The rate is the annual percentage rate divided by the number of periods in a year. 00:32 Usually, that's 12 representing 12 months. 00:35 NPER is the number of periods in the investment. That's usually the number of 00:39 years times the number of periods per year. 00:42 So a ten-year investment would have 120 periods. 00:46 PMT or Payment is the amount of money paid into the investment each period if any. 00:51 PV is the starting value of the investment and type indicates whether the 00:55 payment is due at the beginning of a period, in which case the value is 0 or 00:59 omitted or at the end of the period, in which case the value is 1. 01:04 If you leave out the Payment argument, you must include a PV or Present Value to 01:08 indicate the starting principal balance. 01:10 So let's take a look at the formula that I have in my worksheet. 01:14 I have a rate of 6%, a term of 5 years and also in this case, we have a payment 01:21 of \$10,000 a month and a present value of \$100,000. 01:26 So what that indicates is that we have an initial starting balance of 01:29 \$100,000 in this investment and then we'll be adding monthly payments of 01:34 \$10,000 to the investment. 01:36 Now, any money you pay into the investment is expressed as a negative number 01:40 because it's a cash flow from your account. 01:43 Therefore, it decreases your net worth by that amount so it's expressed as a negative number. 01:48 In this case, the payment in PV arguments will have negative values. 01:52 If you were to take money out each month, the PMT value would be positive. 01:56 One such case would be if you created a retirement account and started taking 02:00 disbursement after age 55. 02:01 In that case, you would input the present value as a negative number and express 02:06 the PMT or payment as a positive number to indicate how much money you drew from 02:11 the account each month. 02:13 So let's see what the future value of this investment would be. 02:17 We start with the FV function and we begin entering our arguments. 02:22 So we have a rate in cell B3 of 6% but in this example, interest is 02:27 compounded monthly. 02:29 So we divide by 12 the number of months in a year, then we enter the number of periods. 02:34 That is based on the value in cell B4, the term of the loan or the investment. 02:39 But because we are using the number of periods as oppose to the number of years, 02:42 we multiply 5, the value in B4, by 12. 02:46 So we get the number of months the number of times that interest is compounded. 02:50 Then we have the monthly payment, which is in cell B5. 02:53 The present value which is in cell B6 and because we are going with the default 02:57 type where payments were due at the beginning of the period, as oppose to the 03:01 end, we can just leave the Type argument blank and there you have it. 03:06 If you start a loan or an investment with the present value of \$100,000 at 03:11 \$10,000 per month, at an annual rate of 6%, at the end of five years, you 03:16 will have over \$832,000. 03:19 The FV function offers investors a straightforward means of evaluating the 03:22 fixed rate investment. What's more? 03:25 It gives analyst the ability to evaluate annuities for the beneficiary he receives periodic payments. Collapse this transcript
Calculating present value
 00:01 Calculating the present value of an investment enables you to answer this question: 00:05 how much is a proposed investment worth in today's dollars? 00:08 You can answer that question using the PV or Present Value function. 00:13 The PV function has five arguments Rate, NPER, PMT, FV, and Type. 00:20 Rate is the discount rate, which I'll explain in a moment. 00:24 NPER is the number of periods. 00:26 PMT or payment is the amount of money deposited into the investment each period. 00:31 In other words, if you have a one year investment and you make monthly 00:34 contributions of \$1,000, that would be the PMT value. 00:39 FV is the value of the investment when it reaches maturity, and the Type 00:44 indicates whether the payments are due at the end of a period in which case, the 00:47 value is zero, or you can leave it out, or at the start of the period in which 00:51 case you put in the value of one. 00:53 If you are more comfortable seeing all of the values as positive numbers, you 00:57 can multiply the result of the PV formula by -1. 01:00 As an example, consider a one year investment where you are asked to pay \$9,500 01:05 for a return of \$10,000 after 12 months. 01:09 If you assume you can generate 6% return from risk-free investments, you would 01:13 create this formula in cell B7. 01:15 That is =PV for present value, B3, the rate, divided by 12, because we are 01:23 assuming interest is compounded monthly. 01:25 B4, the number of periods, but B4 is the number of years, so we need to multiply 01:32 it by 12, again to account for the number of months. 01:34 We are not making any payments, so we'll leave the payment argument blank, and 01:39 we have a target future value of \$10,000, which is cell B5. 01:44 And we can leave the Type argument blank because we are assuming that all 01:47 payments will be at the beginning of the period. 01:49 When we press Return, we see that the present value is \$9,400.05. 01:54 And again, because this calculation represents a cash flow from you as opposed 02:01 to you, it's expressed as a negative value. 02:04 So the present value of this investment is \$9,400.05, because the asking price 02:11 is greater than the investment's present value. 02:13 You should pass on the opportunity. 02:15 Evaluating an investment using the Present Value function makes your decisions easier. 02:19 Once you establish a discount rate, which you and your colleague should 02:22 reevaluate frequently to account for changes in the market, you can run the 02:26 numbers and arrive at a clear yes or no decision. Collapse this transcript
Calculating net present value
 00:01 The net present value of an investment is the present value of the investment 00:04 minus the amount of money accosted by into the investment. 00:08 All of the investment's cash flows must occur at the same interval for the 00:11 calculation to be accurate. 00:13 In other words, if you have investments on a monthly basis, then all of them 00:17 must be on a monthly basis. 00:19 You calculate an investment's net present value using the NPV function. 00:24 This function has two arguments: 00:26 Rate, which is at the discount rate or the rate of return that you could gain 00:30 from a risk-free investment, and also a range of values that contain the 00:35 investment's future cash flows. 00:37 You can list up to 254 cells that contain cash flow values. 00:41 As always, you should be sure to adjust the rate to account for how many times 00:45 per year interest is compounded. 00:47 In most cases, interest is compounded monthly, so you would divide the rate by 12. 00:52 By accounting convention, if you are required to pay for the investment at the 00:55 start, don't include the payment in the array of future cash flow values. 00:59 Instead, you should subtract the investment amount from the total future cash flows. 01:04 In keeping with standard accounting practice, you should enter the investment 01:08 amount as a negative number and add each of the NPV formula's output. 01:12 Let's evaluate the two investments that I have summarized in this worksheet. 01:15 To do that, we'll create an NPV formula, and we are assuming a discount rate of 01:21 4%, which is in cell B3. 01:24 Because we are assuming that interest is compounded monthly, we'll divide that by 12. 01:29 Now we can enter the values, the cash flows. 01:32 The positive cash flows are in cells, A8 through A12. 01:37 Close the parenthesis and then we add the initial investment, which is 01:41 \$100,000 in the cell A7. 01:45 Hit Enter and we have a net present value for this investment of \$23,759.66. 01:48 Now let's do the same thing for the second investment. 01:55 We'll create another NPV formula. 01:58 Again, we are taking our rate from B3, dividing by 12 for the number of months in a year. 02:02 Our positive cash flows, cash flows to us, run from D8 to D13, and then, we add 02:10 the initial investment which is -\$100, 000 representing a cash flow from us. 02:15 Hit Return, and we see a net present value of \$23,513.51. 02:22 Because the cash flows for investment 1 generate a greater net present value, 02:26 that's the investment you should go with. 02:28 Now we are assuming that both investments have the same level of risk. 02:32 When you use NPV to compare several investments, if all of the investments 02:36 have the same level of risk, then you should go with the investments with the higher net present value. Collapse this transcript
Calculating internal rate of return
 00:00 Calculating an investment's internal rate of return enables you to identify 00:04 the interest rate at which the investment's future cash flows have a net 00:08 present value of zero. 00:10 In other words, your formula tells you the discount rate at which you would 00:13 break even on a given investment. 00:16 If the IRR formula returns a value greater than the interest rate generated by 00:20 risk-free investments, you should take the plunge. 00:22 If not, then you should pass on the opportunity. 00:25 The IRR function has two arguments, a range of values that reflects future cash 00:30 flows, and optionally, a guess at the rate of return. 00:34 Excel starts by guessing at a rate of 10 %, which will work in most cases, but in 00:38 extreme cases, you should start the guessing at a higher or lower value. 00:42 The investment cash flows may be positive representing income to you, or 00:46 negative representing an expense to you but they must all occur at regular 00:50 intervals, such as monthly or annually. 00:53 So, let's take a look at the two investments that I have summarized in my worksheet. 00:58 We have two investments, one, with an initial investment of \$100,000 and 01:04 payments of these amounts for five years, then we have the same \$100,000 01:09 investment but we have payments over 6 years. 01:11 So the idea is to determine which of the two has the better internal rate of return. 01:16 To do that, we input our IRR formulas and the values go from A4 to A9. 01:24 I won't put it in a guess because I think Excel's default starting value of 10% 01:28 will allow it to find the answer that it needs. 01:31 So I'll just close the parenthesis, hit Return, and we see that this investment 01:36 has an internal rate of return of 5.73%. 01:38 Now, let's do the same thing for the second investment, =IRR, and the values are 01:46 in the range of D4 to D10. 01:50 Again, I'm not going to put it in the guess because I think the default value is fine. 01:54 I'll close the parenthesis, hit Return, and we see that the second investment 01:58 has an internal rate of return of 5.47%. 02:02 So we should go with the first investment. 02:05 If an investment's internal rate of return is higher than the discount rate, you 02:08 assume for your risk-free investments, such as your treasury bills. 02:11 You should go ahead with the new plan assuming the risks of the new 02:14 investment aren't too great. 02:16 The riskier in investment, the more you should adjust the internal rate of 02:20 return to account for the possibility that the investment might fail. Collapse this transcript
Calculating NPV and IRR for uneven input periods (XNPV and XIRR)
 00:01 Many businesses combine cash flows from several sources, such as asset sales or 00:05 retail revenues, to fund other investments. 00:09 These revenues don't always come in on a regular monthly or annual schedule, so 00:13 you can't use the NPV or IRR functions to calculate net present value and 00:17 internal rate of return. 00:19 Excel does have two functions, XNPV and XIRR, that enable you to calculate net 00:25 present value and internal rate of return for cash flows that occur at irregular intervals. 00:30 The XNPV function has three arguments, the discount rate, the cash flows, and 00:35 the dates the cash flows occur. 00:37 XIRR also has three arguments: the cash flows and the dates of those cash flows, 00:42 both of which are required, and if you want, a guess at the internal rate of return. 00:47 Both the XNPV discount rate and your guess, if any, at the internal rate of 00:51 return, should be expressed as annual percentage rates. 00:54 So let's take a look at the two examples I have here. 00:57 Actually, it's the same example twice. 00:59 We are just going to calculate XNPV for one and XIRR for the other. 01:03 So to calculate XNPV, type =XNPV, put in the rate, which is in cell B5, 01:12 which we divide by 12 because we assume interest will be compounded monthly. 01:16 Now we enter the values for the cash flows. 01:19 Those run from B8 to B16. 01:24 Now we can enter the dates, those go from A8 to A16, close the parenthesis, and 01:30 we have an XNPV or net present value of \$24,158.80. 01:34 Now let's take a look at the same investment but this time in terms of 01:40 its internal rate of return. 01:42 Type =XIRR. We have the values from cell E8 to E16, the dates from cell D8 to D16. 01:57 We'll leave out a guess. 01:58 I think Excel's default starting value will be fine. 02:01 Close the parenthesis. 02:02 Hit Return. 02:04 This investment has an internal rate of return of 10.77%. 02:08 You are very unlikely to find any sort of a guaranteed investment with that high of a rate. 02:13 So it looks like this investment is a good deal, assuming the risks are in line 02:16 with what your investment strategy holds. 02:19 Whenever you need to evaluate investments, where cash flows occur at irregular 02:22 intervals, use the XNPV and XIRR functions. Collapse this transcript
9. Forecasting Future Growth
Projecting future results using the Forecast function
 00:00 Financial analysts are fond of saying that past performance is no guarantee of future results. 00:06 That's true. 00:07 But it does make sense to see how a business's sales, expenses, or market 00:11 share have progressed over time so you can make judgments about the company's prospects. 00:15 If you have a data series that includes past results, you can use the 00:19 Forecast function to create a formula to estimate future values if the trend stays consistent. 00:24 The Forecast function has three arguments: X, which is the value often a year 00:29 for which you want to project a result, known_y's, which is the set of known 00:34 results such as sales, and known_ x's, which indicate when the known_y 00:39 measurements were taken. 00:40 It's interesting, but the X values can be in any order. 00:44 They don't have to be sorted into ascending or descending order, and you can 00:48 also skip values in the series. 00:50 For example, with the numbers in this worksheet, we could skip the year 2009, 00:55 and still create a projection for 2010 using the Forecast function. 00:59 So let's see how the forecast formula will work. 01:03 Just type in =FORCAST, our X value is 2010, which is in cell A11 and that is the 01:13 year for which we want to project a value. 01:16 Our known_y's are the variable measurements and in this case, those are the 01:20 sales revenue figures in cells B6 through B10. 01:25 And our known_x's are the years in which those measurements were taken, and 01:30 those are in A6 through A10. 01:34 Those are all of the arguments we need for the formula. 01:37 Close the parenthesis and there we have our projection. 01:41 Based on current trends, the sales for the year 2010 will be 46,265 multiplied 01:48 by 1,000 because all of the values in this worksheet are expressed in terms of 01:52 1,000s, which means you can interpret this value as 46,265,000. 01:58 Excel uses linear regression techniques to project future values based on 02:02 the past measurements. 02:03 The math is not that difficult to work through, but you don't need to bother. 02:06 Excel does all the work for you. Collapse this transcript
Performing quick forecasts using the Fill handle
 00:01 If your worksheet contains a data series that you would like to extend into the 00:04 future, you can select the cells containing the existing values and use the Fill 00:08 Handle to extend the series. 00:10 To extend the series using the Fill Handle, select the cells containing the data 00:14 series you want to extend, grab the Fill Handle at the bottom right of the 00:17 selection, and drag. 00:19 So, I'll show you how to do that here with the data in cells B8 through B12. 00:26 So we have the black selection outline, and here at the bottom right corner, 00:30 we have what's called the Fill Handle. 00:31 If you move the mouse pointer over the top of it, it turns into a black cross 00:36 and you can drag it down. 00:37 And Excel extends the data series for you. I'll click Undo. 00:42 I'll come back to this in a moment. 00:43 I would like to note that you can go forward or backward. 00:48 So for example, if the sales revenue cell were blank, I can select here and 00:54 move up, and you'll see that Excel will extend the series backwards in time if you want. 00:58 I do want to emphasis that this technique is only accurate when you have a data 01:03 set with no missing values. 01:05 As compared to the Forecast function, which interpolates a y value for any x 01:09 values that are skipped in the data set, the Fill Handle does not. 01:13 So I'd like to show you the error in this data table, and that is that we have 01:16 the years 2005 to 2008, but we skipped 2009. 01:21 That means that dragging the Fill Handle just works with the existing data and 01:26 it does not attempt to do any sort of calculation to figure out what the year 01:29 2009 would or should have been. 01:31 I will show you over here, how the Forecast function handles the same data set. 01:37 So we have =FORECAST, x is the year 2011 which is in cell D13, the known_y's are 01:44 in E8 through E12 and the known_x's are in D8 through D12. 01:53 Close the parenthesis and there you have the value of 43,660. 01:57 Now when you compare that with the Fill Handle value, 46,265, you see that they 02:05 are two different values even though the inputs are exactly the same, and that 02:09 is because the two functions, FORECAST on the right, the Fill Handle drag-select 02:14 on the left, use different techniques. 02:17 So now let's see what happens if I change the years to 2009 and 2010 in both cells. 02:25 I'll just do 2010 and 2009, and I'll delete the Fill Handle value there. 02:31 So we have our forecast value of 46,265, and if I drag the selection over here, 02:36 you will just see that we get the same value. 02:39 And again, the difference is just in the way that the Fill Handle extension and 02:43 the Forecast formula treat missing values. 02:46 Extending a data series using the Fill Handle is a quick way to predict the 02:50 future value, but you must be certain that you are working with an uninterrupted 02:54 data set for the results to be accurate. Collapse this transcript
Adding a trendline to a chart
10. Determining Amortization and Depreciation Schedules
Introducing amortization
 00:00 When you work with financial transactions, you will often encounter the term Amortization. 00:05 It sounds complicated, but amortization is simply the gradual repayment of 00:09 a debt in over time. 00:11 Loans can be fully amortized, partially amortized, or negatively amortized. 00:17 A fully amortized loan has payments that will pay off the entire principal and 00:21 accumulated interest at the end of the loan's term. 00:24 A partially amortized loan has payments that will pay off only a portion of the 00:28 principal and accumulated interest. 00:30 Many such loans have a large payment called a balloon payment after a set number of years. 00:35 In the case of a home loan, which usually runs 30 years, 00:39 a borrower might have a balloon payment due at the end of the 15th year. 00:43 You can also have balloon payments due at the end of loan's term. 00:46 As an example of how amortization works, let's take a look at the example in the worksheet. 00:52 So we have a \$25,000,000 loan, probably not a home loan, at the rate of 4.9%, 00:58 and it is going to be paid back over a term of 10 years. 01:01 In the first example, we'll assume the loan is fully amortized, and it will be 01:06 paid off at the end of the 10 year term. 01:08 In the second, that there is a Balloon Payment of \$8,000,000 due at the end 01:12 of the loan's term. 01:13 When you calculate the monthly payments on the fully amortized loan, you will 01:17 see that there will be \$263,943.49 per month. 01:22 On the other hand, when you have an \$8, 000,000 Balloon Payment at the end of the 01:26 loan's term, you have a monthly payment of \$212,148.24. 01:29 Now that difference of about \$51,000 could make a very big difference for a 01:37 company in terms of cash flow. 01:39 If they borrow the \$25,000,000, and need to save every penny to make that 01:43 investment work, then the \$51,000 a month might make a huge difference. 01:48 Amortization or the repayment of a debt in over time is the key concept behind loans. 01:54 The borrower gets the use of the capital immediately, while the lender makes 01:57 interest over the term of the loan. 02:00 Most loans will be fully amortized, but there are still quite a few that are 02:03 only partially amortized, and will have an unpaid balance unless the borrower 02:07 makes a large payment to make up the unpaid amount. 02:10 A negatively amortized loan is a loan where the monthly payments do not cover 02:14 the loans interest for that month. 02:16 This type of lending is considered predatory and should be avoided. Collapse this transcript
Calculating payments on a fully amortized loan
Calculating payments on a partially amortized loan (balloon payments)
 00:01 Just like when you determine payments for a fully amortized loan, you can 00:04 use the PMT or Payment function to determine payments for a partially amortized loan. 00:09 If you want the lump sum or balloon payment to be due at the end of the loan's term, 00:13 you can put the balloon payment in the PMT functions, fv or future value 00:18 argument, and then build the formula normally. 00:21 So in this example, we have a loan of \$30,000,000 with an annual interest rate 00:26 of 7%, a term of 12 years, and a Balloon Payment at the End of \$10,000,000. 00:32 So you will be paying out \$20,000,000. 00:35 That's 30,000,000 minus 10,000,000 over the 12 years, and then paying the 00:39 \$10,000,000 in one lump sum at the end. 00:42 So to create the formula, I have =PMT and our five arguments are the rate, the 00:49 number of payments, the present value of the loan, which is the principal, the 00:53 future value, which is the balloon payment at the end, and the type which is 00:57 whether payments are due at the end of a period or at the beginning. 01:01 In this case, the type is zero, so we'll be able to leave it out. 01:05 So we type in the rate, which is in cell B5 and we divide it by 12, which is the 01:11 number of payments per year. 01:13 The number of payments is in cell B7, and we multiply by 12, again for each 01:19 month for a monthly payment. 01:21 Present value is the principal that's in cell B3. 01:24 Future value is in cell B9, and that is \$10,000,000. 01:29 Now note that because the balloon payment is a negative cash flow to you it's 01:35 expressed as a negative number. 01:36 We will close the parenthesis, because we don't have to put in the type 01:39 argument, hit Return, and you can see the Monthly Payment on this loan is 01:44 \$264,010 per month, with a \$10,000,000 payment due at the end of the loan's term. 01:51 Loans with large balloon payments are extremely risky for the borrower, because 01:55 external factors such as economic downturns, labor actions, and natural 01:59 disasters can prevent them from earning the revenue required to make the balloon 02:02 payment when it comes due. Collapse this transcript
Calculating interest and principal components of loan repayments
Introducing depreciation
 00:01 Almost without exception, companies purchase physical assets to help them do business. 00:05 Whether the purchases are relatively small such as computers and 00:08 printers or large such as company cars and office buildings, these assets will 00:13 go down in value over time. 00:15 Reduction in value due to age is called depreciation. 00:19 There is a difference between the physical life of an asset and 00:22 the economic life of an asset. 00:24 Physical life is the amount of time an asset will perform its function, i.e., 00:29 how long will a building be standing and safe to occupy or a printer will turn 00:33 out page of reasonable quality. 00:35 Economic life on the other hand refers to how long the asset will perform its 00:39 function until it is rendered obsolete by wear and tear or falls so far 00:43 behind the current technology that it's worth no more than what it could 00:46 be sold for as scrap. 00:48 According to the Modified Accelerated Cost Recovery System, an office building 00:53 has an economic life of 39 years, while our computer or printer will have an 00:57 economic life of five years. 00:59 You can find more information about MACRS in IRS Publication 946, available 01:05 on the IRS website. 01:07 At the end of an asset's economic life, it has what is called a salvage value. 01:11 For example, if you have an old printer that you want to sell, you can either 01:15 sell the printer at a substantial discount or have the technician pull out 01:19 the usual parts and sell them to other repair shops that service the same model of printer. 01:24 There are many different types of schedules that you can use to account for depreciation. 01:28 Some of these techniques depreciate an asset more quickly than others. 01:32 Why would you use an accelerated depreciation schedule? 01:34 Perhaps because you expect to sell the building after using it for a few years. 01:38 Because you would hold on to the property for far less than its economic life, 01:41 it would be to your advantage to capture the tax benefits of 01:45 depreciation as soon as possible. 01:47 Accountants have developed a wide variety of depreciation schedules for use by businesses. 01:52 The schedules vary in the speed at which the assets are assumed to depreciate. 01:56 Each approach has specific financial advantages and disadvantages. Collapse this transcript
Calculating straight line depreciation
 00:00 The most straightforward method you can use to calculate depreciation is the 00:04 straight-line method. 00:06 As the name implies, the straight-line method assigns depreciation evenly over 00:10 the economic life of an asset. 00:13 The starting amount is the asset's purchase price or initial cost, and 00:17 the ending value is the asset's salvage value. 00:21 If your asset has an economic life of ten years and the salvage value of zero, 00:25 for example, the asset will depreciate by 10% each year, until its value reaches zero. 00:31 To calculate straight-line depreciation, you use the SLN function. 00:36 The SLN function calculates the amount of depreciation for a given period, 00:40 which is usually a year. 00:43 The SLN function has three arguments: the initial cost of the asset, which is in 00:49 cell B5, the salvage value of the asset, which is in cell B7, in this case, and 00:57 the economic life of the asset, which I have in cell B9. 01:02 Close the parenthesis and there is the value. 01:05 You'll see that in each of the five years of this asset's economic life, it will 01:10 depreciate by \$230 until it reaches its salvage value of \$150. 01:16 Straight-line depreciation is the most conservative approach you can take to depreciation. 01:21 It assumes that you will hold on to the asset until the end of its economic life, 01:24 so you should spread out the tax benefits of the depreciation while you still own the asset. Collapse this transcript
Calculating declining balance depreciation
 00:00 The Declining Balance method of calculating depreciation accelerates the rate at 00:05 which an asset loses its value over time. 00:08 You use the DB function to calculate depreciation in Excel. 00:12 The Declining Balance method reduces an assets value by the amount it 00:15 depreciated in the previous years. 00:18 Excel then calculates the new depreciation based on that lower value. 00:21 Hence the name Declining Balance Method. 00:24 The DB function has four arguments, Cost, Salvage Value, Economic Life 00:30 and Period. 00:32 The cost is the asset's initial cost. 00:34 Salvage Value is what the asset will sell for when it becomes 00:37 economically obsolete. 00:39 Economic Life is the number of years that the asset will be economically viable 00:42 and Period is the time frame for which you are calculating depreciation. 00:46 Unless you are dealing with very short-lived assets, that period is 00:49 almost always a year. 00:50 So, to complete the formula we have =DB, the Initial Cost is in cell B7, and 00:59 because we are copying the formula further down, the column in the table, we'll 01:03 make that an absolute reference by pressing F4. 01:06 We have the Salvage Value in cell B9 and make it an absolute reference, the 01:12 Economic Life, B11, same thing, make it an absolute reference. 01:16 The period is in cell A16. 01:21 And because we are copying the formula down and we want the formula to draw its 01:26 value from the cell directly to the left, such as cell A16 in this case, we'll 01:30 leave that as a relative reference. 01:32 And you don't need to specify a month, because the period is one year. 01:38 We can close the parenthesis. 01:40 Press Enter. 01:41 And you can see the amount of depreciation each year using the Declining 01:45 Balance Method. 01:46 But remember that the second part of the Declining Balance Method is that you 01:50 need to know an asset's New Value at the end of each year. 01:53 So, now that we have the yearly depreciation we can calculate the new value, and 01:59 that formula is =B7, which is the Initial Cost. 02:03 We'll make that an absolute reference by pressing F4 and we subtract the sum of 02:08 all of the previous depreciation amounts from the Initial Cost. 02:13 To do that, we have SUM B16, which is the first depreciation amount. 02:21 We make this cell reference an absolute reference so it will not change, then a 02:26 colon to indicate we are entering a range and then B16. 02:29 Now, we have entered B16 twice and the reason is because we want to create a 02:35 range that will change as the formula is copied down the column. 02:40 So, the first B16 is an absolute reference, so it will not change. 02:44 The second reference to B16 is left as a relative reference so it will change. 02:50 In other words, in cell C17, it would be the SUM of B16 to B17, and you'll see 02:57 how that works in a second. 02:58 When I close the parenthesis and hit return. 03:02 You have the depreciation, which is subtracted from the Initial Cost, and you 03:06 have the New Value in cell C16, C17 and so on down to C22. 03:12 The Declining Balance Depreciation Method enables companies to capture more 03:16 depreciation benefits early in an assets economic life. 03:19 Decreasing tax payments and thereby freeing up capital to invest in other areas. Collapse this transcript
Calculating double declining balance depreciation
 00:01 The Declining Balance method of calculating depreciation enables companies 00:04 to accelerate the rate at which they claim the tax benefits inherit in 00:08 asset depreciation. 00:10 As the name implies the double declining balance depreciation method doubles the rate 00:15 at which the declining balance method calculates an asset's depreciation. 00:19 To calculate depreciation using the Double Declining Balance method, you use the DDB function. 00:25 The DDB function has five arguments. 00:27 The first is cost, which is the initial cost of the investment. 00:31 And on this worksheet that's found in cell B7 and I'll press F4 to make that 00:36 reference an absolute reference so it won't change as the formula is copied 00:41 down the table column. 00:43 Then you have the asset's salvage value, found here in cell B9. 00:48 Make it an absolute reference. 00:51 The economic life of the asset found in cell B11. 00:55 Once again, absolute reference. 00:57 And then we have the period. 00:59 That is the year and it's found in cell A16. 01:02 Because we want that cell reference to vary as we copy the formula down the 01:06 table column, we'll leave that as a relative reference. 01:10 The final argument in the DDB function, which is called factor, is the amount by 01:15 which you want to speed up the depreciation schedule. 01:17 For example, if for some reason you want to use triple declining balance 01:21 depreciation, you would set the factor argument to 3. 01:24 You would want to make sure that your firm's accountant signed off on that decision. 01:29 The default value for the factor argument is 2. 01:31 So, we don't have to change it for this function. 01:34 Just close out the parenthesis. Hit Return. 01:37 And there you could see the first ten years of depreciation for this asset. 01:41 To calculate the new value of the asset at the end of each year, you would 01:45 subtract the accumulated depreciation from the asset's initial value. 01:50 To do that we create this formula. 01:53 You type in the initial value, the initial cost, which is in cell B7, and 01:58 make it an absolute reference, so it doesn't change as we copy the formula down the table column. 02:03 And you subtract the sum of all of the accumulated depreciation. 02:07 So, the first cell in the Depreciation column is cell B16. 02:13 We do not want that cell reference to change. 02:16 So, we make it an absolute reference by typing F4, enter a colon to indicate 02:22 we are entering a cell range, and we type B16 again. 02:25 In other words, for the first year the formula will only consider the 02:29 Depreciation Value in cell B16. 02:31 But as you'll see when we complete the formula, 02:35 and it's copied down the table column, that Excel, because the second B16 02:40 reference was a relative reference, 02:42 it changed it as it copied the formula down the table column. 02:46 So, here in C17 the last reference is B17, so it uses the values from these two cells. 02:52 Same thing here. The last reference is to B16 to B18 so it uses 02:58 the depreciation from B16 to B18. 03:02 The Double Declining Balance method assigns a high percentage of an asset's 03:05 depreciation to the first part of its economic life. 03:09 As with the Declining Balance method, companies can use their depreciation 03:12 related tax savings to invest in other areas. Collapse this transcript
11. Analyzing Bond Investments
Introducing bonds and bond terminology
 00:01 Every financial specialty has its own vocabulary and bonds are no exception. 00:05 This lesson introduces some of the terms used when evaluating bond investments. 00:09 A bond is a debt instrument issued for a period of more than one year that an 00:13 institution uses to raise debt by borrowing. 00:17 The length of time the borrower has to use of the money is called the bond's term. 00:20 U.S. Treasury Bond, which is backed by the full faith and credit of the US 00:24 government and therefore has near zero risk, has a term of more than one year. 00:28 A US Treasury Bill or T-Bill is also government-backed but has a term of 00:33 less than one year. 00:35 A bond's maturity or maturity date is the day it comes due for payment. 00:40 The amount that's paid on the day is known as the bond's maturity value. 00:44 A capital gain or a capital loss is the amount that a bond's market price 00:49 increases or decreases after the bond is purchased. 00:52 Coupon or coupon rate is the interest rate on a security with a fixed rate of return. 00:57 A bond's yield is the bond's interest, interest earned on that interest through 01:01 compounding and capital gains if any. 01:04 A bond is said to be callable if you can redeem it, prior to the end of the bond's term. 01:09 Most callable bonds specify a first call date before which the investor 01:13 can't redeem the bond. 01:15 Finally risk, the likelihood that some factor, such as currency fluctuations, 01:20 inflation, interest rates, or external events such as natural disasters or wars, 01:25 will negatively affect the value of a bond. 01:28 This list of bond related terms is by no means all-inclusive. 01:31 You should read as much as you can about bonds, so you can better understand the 01:34 intricacies of bond pricing and trading. Collapse this transcript
Calculating a bond's yield
 00:01 One straightforward way to calculate the value of a bond is to estimate the 00:04 bond's yield given the investments condition such as price, coupon rate, 00:08 and time to maturity. 00:10 In Excel, you can calculate a bond's yield perhaps not surprisingly by using 00:14 the YIELD function. 00:15 The YIELD function has seven arguments, Settlement Date, Maturity Date, Percent 00:22 Coupon which is listed here as its rate, Price, Redemption Value, Frequency, and Basis. 00:29 The Settlement Date is the date that you gain ownership of the security. 00:32 That date will probably be different from the bond's issuance date, which is the 00:36 date the bond is made available for sale. 00:38 The Maturity Date is the date the bond will be paid off. 00:42 Percent Coupon is the bond's interest rate. 00:44 Price is the amount you paid for \$100 of face value. 00:47 Redemption is the bond's redemption value for \$100 of face value that's 00:51 most commonly \$100. 00:53 Frequency is the number of interest or coupon payments per year. 00:56 Finally, Basis reflects the way the investment counts the days of year. 01:00 The western calendar can have days of 28, 29, 30, or 31 days and years of 01:05 either 365 or 366 days. 01:08 So many investments base their calculations on a year that is presumed to have 01:12 12 months of 30 days each for a 360 day year. 01:15 The Excel help system describes the other options available including the 01:19 European 3360 systems, which is slightly different from the US system. 01:23 So to complete the formula using the values that we find in the Yield worksheet. 01:28 The Settlement Date is in cell B3, Maturity Date is in cell B4, Percent Coupon 01:34 is in cell B5, the Price is in cell B6. 01:39 The Redemption Value is in cell B7, Frequency is in cell B8 and the Basis, which 01:44 will add but we don't have to, it's an optional argument and we are using the 01:47 default of 0, but we'll just complete the formula, is in B9. 01:51 Close the parenthesis and you see that this bond has an annual Yield of 4.88%. 01:58 The YIELD function provides a simplified look at bonds. 02:00 The function assumes the bond's interest rate stays constant, which almost never 02:04 happens, that you reinvest your bond interest and that there are no delays in 02:08 getting paid when the bond matures. 02:10 Even so the YIELD function provides a good first look at a bond's potential value. Collapse this transcript
Calculating the value of zero coupon bonds
 00:01 Bonds that issue coupons or pay interests to investors at regular intervals 00:04 offer bondholders the option to invest their earnings elsewhere. 00:08 Some issuers decide to offer zero- coupon bonds, which only pay interest at the 00:12 end of the investment period. 00:14 A zero-coupon bond sale lists the price and the payment due when the bond 00:17 matures, but doesn't usually list the investment's interest rate. 00:21 You can calculate that rate for yourself using the RATE function. 00:24 The RATE function has three required arguments and three optional arguments. 00:30 The required arguments are nper, the number of periods, pmt or payment, which 00:35 are any payments made each period. 00:37 And present value, which is what the investment is worth today. 00:40 That is the cost to purchase the investment. 00:43 The optional arguments are fv, the future value of the bond, type, whether you 00:48 pay at the beginning or end of an investment period and guess, which is a guess 00:52 as to the interest rate. 00:54 If you leave guess blank, Excel assumes a value of 10%, which is often a good 00:58 enough starting point for Excel to zero in on the bonds annual percentage rate. 01:02 So I'll finish filling out this RATE formula. 01:05 So we have the number of periods, and that is the number of years in B3, 01:11 multiplied by the Periods Per Year 12, which are in cell B4, then the Payment 01:17 that in this case is 0. That's in cell B5. 01:20 And then we have the Present Value. 01:22 The Present Value is the amount that you pay to invest in the zero-coupon bond. 01:27 Payment is 0 because you do not make a monthly payment, instead you put all the 01:31 money in upfront and that indicates the present value. 01:35 Present Value is expressed as a negative number, because it represents an 01:38 expense or negative cash flow to you. 01:41 So you have it as a negative number. 01:43 And that is in cell B6, and then you have the Future Value, which is in cell B7. 01:49 And in this case, you have a Future Value of \$12,500. 01:54 I'm not going to use either type or guess. 01:56 So I'll close the parenthesis. 01:58 Now you'll notice that the formula I have here below includes a multiplication 02:03 by the number of periods per year. 02:05 Not multiplying by the number of periods per year results in a formula that 02:09 makes sense, but actually produces a result other than what you want. 02:13 So I'll press Return and you see that you have a result of 0.37%. 02:18 What that means is that is that you have calculated the monthly rate. 02:22 When you multiplied by 12, the number of periods per year you change the 02:27 calculation so that you actually got the rate for a month. 02:31 So each month you make .37% of interest, but if I multiply the result of the 02:38 RATE formula by B4, the number of periods per year then you get 4.47%. 02:44 By way of checking my math, I input 4.47% into a future value formula divided by 02:52 12 and then use the same terms that I used in the RATE calculation to come up 02:57 with a value of \$12,499.27 which is just a rounding error away from the rate as 03:04 expressed in cell B9. 03:06 You should consult with your tax professional about the advisability of 03:09 investing in a zero-coupon bond. 03:11 The reason is that you might owe taxes on interest earned even if you haven't 03:16 received any interest payments. Collapse this transcript
Pricing bonds to be offered to investors
 00:01 If your company needs to raise some cash and has determined that that issuing 00:04 stock isn't in its best interest, you might borrow money by issuing bonds. 00:09 Pricing bonds for sale is a tricky business. 00:11 In essence, you're betting that you can earn a higher rate of return on the 00:14 borrowed money than you pledged to pay bondholders. 00:17 Once you know the parameters of the bond you would like to issue, you can use 00:20 the PRICE function to find the break-even issue price. 00:23 To do that you use the PRICE function, which has seven arguments, Settlement Date, 00:28 Maturity Date, Percent Coupon which is the interest rate, percent Yield, 00:34 Redemption Value, Frequency, and Basis. 00:37 The Settlement Date is the date you gain ownership of the security. 00:41 The Settlement Date is the date that a buyer gains ownership of the security. 00:45 That date will probably be different from the bonds issuance date, which is the 00:48 date the bond was made available for sale. 00:51 The Maturity Date is the date the bond will be paid off. 00:54 So we've a Settlement Date in cell B3 and a Maturity Date in cell B4. 01:00 Percent Coupon is the bond's interest rate. 01:02 Yield is the bond's annual yield and Redemption is the bond's redemption value 01:07 per \$100 of face value, most commonly that's \$100. 01:11 And Frequency is the number of interest or coupon payments per year. 01:15 So we've the rate or Percent Coupon in cell B5, the Yield in cell B6, the 01:22 Redemption Value in B7 and the Frequency in B8. 01:28 Basis reflects the way you count the days of the year. 01:30 The western calendar can have months of 28, 29, 30, or 31 days and years of 01:36 either 365 or 366 days. 01:39 So many investments base their calculations on a year that is presumed to have 01:43 12 months of 30 days each for a 360-day year. 01:47 The Excel help system describes the other options available including the 01:50 European 30-360 system, which is slightly different from the US system. 01:55 So I'll just type in B9, close out the formula and there we have a result. 02:01 The break-even issuance price given the parameters stated would be \$90.36. 02:07 Like the YIELD function, the PRICE function assumes bondholders always reinvest 02:11 their interest and that the bond's interest rate never changes. 02:15 Those assumptions are rarely true. 02:17 But the PRICE function offers a good first look at what you should charge your 02:20 bondholders with the goal of making a profit on the transaction. Collapse this transcript
Conclusion
Goodbye
 00:01 Thanks for joining me for Excel 2007: Financial Analysis. 00:04 You now have a broader set of tools at your disposal to design financial 00:08 worksheets, analyze financial statements, perform ratio analysis, forecast 00:12 future growth, calculate amortization and depreciation schedules and 00:16 analyze bond investments. 00:18 I hope you've enjoyed the course and encourage you to explore the rest of the lynda.com Training Library. Collapse this transcript

Are you sure you want to delete this bookmark?

### Bookmark this Tutorial

#### Description

{0} characters left

#### Tags

Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:
cancel

### bookmark this course

{0} characters left Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:

Error:

go to playlists »

# Create new playlist

 name: description: save cancel

# You must be a lynda.com member to watch this video.

Every course in the lynda.com library contains free videos that let you assess the quality of our tutorials before you subscribe—just click on the blue links to watch them. Become a member to access all 98,609 instructional videos.

If you are already an active lynda.com member, please log in to access the lynda.com library.

# Get access to all lynda.com videos

You are currently signed into your admin account, which doesn't let you view lynda.com videos. For full access to the lynda.com library, log in through iplogin.lynda.com, or sign in through your organization's portal. You may also request a user account by calling 1 1 (888) 335-9632 or emailing us at cs@lynda.com.

# Get access to all lynda.com videos

You are currently signed into your admin account, which doesn't let you view lynda.com videos. For full access to the lynda.com library, log in through iplogin.lynda.com, or sign in through your organization's portal. You may also request a user account by calling 1 1 (888) 335-9632 or emailing us at cs@lynda.com.

Your organization has a limited access membership to the lynda.com library that allows access to only a specific, limited selection of courses.

You don't have access to this video.

You're logged in as an account administrator, but your membership is not active.

Contact a Training Solutions Advisor at 1 (888) 335-9632.

# How to access this video.

If this course is one of your five classes, then your class currently isn't in session.

If you want to watch this video and it is not part of your class, upgrade your membership for unlimited access to the full library of 1,896 courses anytime, anywhere.

You can always watch the free content included in every course.

Questions? Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com.

# You don't have access to this video.

You're logged in as an account administrator, but your membership is no longer active. You can still access reports and account information.

To reactivate your account, contact a Training Solutions Advisor at 1 1 (888) 335-9632.

# Need help accessing this video?

You can't access this video from your master administrator account.

Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com for help accessing this video.

site feedback

Thanks for signing up.

We’ll send you a confirmation email shortly.

By signing up, you’ll receive about four emails per month, including

• new course releases
• special announcements

Keep up with news, tips, and latest courses with emails from lynda.com.

By signing up, you’ll receive about four emails per month, including