Excel 2010: Financial Functions in Depth

with Curt Frye

In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
Topics include:
• Analyzing loans, payments, and interest
• Discovering the interest rate of an annuity
• Determining depreciation using the straight line, declining balance, double-declining balance, and other methods
• Calculating the future value of an investment with variable returns
• Finding the discount rate of a security
• Converting between fractional prices and decimal prices
• Determining the yield of securities that pay interest periodically
author
Curt Frye
subject
Business, Finance
software
Excel 2010, Office 2010
level
Intermediate
duration
2h 20m
released
Jun 28, 2011

Share this course

Learn more about lynda.com

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:04 Hi! I am Curt Frye. 00:05 Welcome to Excel 2010: Financial Functions In Depth. 00:09 In this course, I'll show you how to use all of the financial functions in Excel 00:13 2010 to analyze your data. 00:14 To start, I'll show you how to calculate a loan payment, calculate interest paid 00:19 during a specific period, and calculate the number of periods in an investment. 00:24 Next, I'll tackle depreciation, showing you how to calculate it using the 00:27 straight line and the declining balance methods plus many more. 00:32 I'll then move onto analyzing cash flows by calculating the future value, net 00:37 present value, and internal rate of return of your investments. 00:41 Next, I'll show you how to analyze coupon bonds, discounted securities, 00:46 and treasury bills. 00:48 Finally, I'll show you how to analyze bonds with unusual payment schedules. 00:52 Whether you use Excel to analyze your personal finances or to examine the 00:55 business investment, you'll find the information you need in this course. 00:59 In short, I'm sure you'll find that your time with Excel 2010: Financial 01:03 Functions In Depth will be time well spent. Collapse this transcript
Using the exercise files
 00:00 For those of you, who have an annual subscription to the Online Training Library 00:04 or who have a monthly premium subscription, you'll have access to the exercise 00:08 files for this course. 00:10 I've put my Exercise Files folder on my Desktop. 00:13 So if I want to use the files, I can just double-click the folder and then 00:17 double-click the chapter that I'm working in. And then if I want to open a file, 00:23 I can just double-click it and it will open in Excel. 00:25 If you aren't an annual or a monthly premium subscriber then you won't have the files, 00:29 but you can follow along or create your own resources so that you can 00:33 work through the steps that I described. Collapse this transcript
Disclaimer
 00:00 This course, Excel 2010: 00:02 Financial Functions in Depth, is designed to be a guide to implementing 00:06 financial techniques and equations typically used in the United States. 00:10 Some of these techniques might be different in other parts of the world. 00:13 Nothing in this course should be considered investment or financial advice. 00:17 This course should not be considered a substitute for consultation with a 00:21 licensed financial advisor. 00:23 If you would like financial planning or investment advice, please consult your 00:26 certified financial planner. Collapse this transcript
1. Analyzing Loans, Payments, and Interest
PMT: Calculating a loan payment
 00:00 Most loans, whether between businesses or made by businesses to individual 00:04 borrowers, are fully amortized. 00:06 Fully amortized means that the monthly payments made over the term of the loan 00:10 pay off the principal and all of the accrued interest. 00:14 In Excel, you can calculate the monthly payments required to pay off a fully 00:17 amortized loan by using the PMT function. 00:20 I've set up this workbook so that it has all the arguments that we need for the 00:25 PMT function to calculate our monthly payment. 00:28 First is the Rate and this is exactly what you would expect, the annual interest rate. 00:34 Next is the Number of periods and that's just the number of payments that you need to make. 00:39 In this case, it's 12 payments a year over 30 years for 360 payments. 00:44 Then we have the Present value and that is the amount that you owe and you'll 00:49 notice that it is represented as a negative number because, as I said, it's the 00:54 amount that you are owing. 00:55 Next is the Future value of the loan and this is the amount that you want to end 01:00 up with after you make your payments. 01:02 And because this is a fully amortized loan that value is 0. And then finally, 01:08 you have the Type of payment you're going to be making and that can either be 0 or 1. 01:14 If it's 0, you make your payment at the end of a period, for example the 01:18 last day of the month. 01:19 If the Type is 1 then you're making your payment at the beginning of a period. 01:24 That is the first day of the month. 01:25 So 1 would be May 1st and 0 would be May 30th. 01:30 So with that information in hand, we can create our PMT function and to do that, 01:35 just type an equal sign and then pmt and left parenthesis and then we can start 01:42 identifying the cells that contain the values. 01:45 The rate is in cell c3, but because it's an annual rate and we're going to be 01:50 making monthly payments, we need to divide it by 12. 01:53 So /12 and then a comma, then we have a number of periods which is in c4, comma, present 02:00 value is in cell c5, comma, and then if you look at the tooltip underneath the formula 02:06 that I'm entering, you'll see that the future value and type arguments are 02:11 enclosed in square brackets. 02:13 The square brackets indicate that those arguments are optional. 02:16 You don't have to provide a value for them. 02:18 In this case, Excel would assume that the future value is 0 and that the type is 02:22 also 0, but because this is the first time through I'll go ahead and add those 02:27 arguments just for completeness. 02:29 So future value is in c6, comma, and type is in c7. 02:37 Type a right parenthesis and check over the formula. Everything looks good. 02:42 Press Enter and we see that our monthly payment will be a little bit over \$3100. 02:49 Now let's see how that would change if we made our payment at the beginning of a 02:53 period instead of at the end. 02:55 To do that, we change the Type from 0 to 1. 02:59 So our payment in cell C10 is a little bit over \$3100. Change the Type to 1 to 03:05 pay at the beginning of the month and it goes down by about \$14. 03:08 Now that doesn't seem like a lot, but over 360 payments it comes out to a bit over \$3750. 03:16 The PMT function only calculates a monthly payment to cover principal and interest. 03:21 Many loans have other charges included such as property taxes and perhaps 03:25 private mortgage insurance. 03:26 So you should be sure to include those other fees and charges when you 03:29 calculate your loan payments. Collapse this transcript
PPMT and IPMT: Calculating principal and interest per loan payment
 00:00 Calculating the amount of principal you've paid on a loan lets you determine the 00:03 amount of equity you have in your purchase. 00:05 Your equity is simply your down payment plus any loan principal paid, plus 00:10 appreciation of the asset. 00:11 Calculating the interest paid on a loan lets you find the amount of interest you 00:15 pay in the year, which you can often right off in your tax return. 00:18 I have set this workbook up so that we can calculate the principal component 00:22 and interest component of an individual payment. 00:25 The PPMT function, as you probably guessed, lets you calculate the principal 00:30 and IPMT the interest. 00:32 So let's go through the arguments. 00:34 The Rate is exactly what you would expect, the annual interest rate, and Period 00:41 Identifies the period for which you want to identify the principal and 00:45 interest components. 00:46 Number of periods is the number of payments for the loan. In this case it's 360 00:52 which is 30 years of 12 monthly payments. 00:55 Then there is Present value of \$750,000. 00:59 Now it's a negative number because it's an amount that you owe, then Future 01:03 value is the amount that you want to end up either owing or having in surplus 01:09 and in this case it's 0. 01:11 And then Type can be either 0 or 1 and that indicates whether you pay at the 01:16 end of a period, which is 0 which is the default value, or at the beginning of a period, which is 1. 01:22 With those arguments in place, let's go ahead and create the formulas. 01:25 So we have the Principal component, which is =ppmt(, and then we have the rate. 01:33 That is in cell c3, but because we're making monthly payments we need to 01:37 divide that by 12, and then the period for which we want to perform our 01:41 calculations is in cell c4. 01:45 Then the number of periods is in cell c5, present value is c6, and then you see 01:51 that the future value and type arguments are in square brackets and the square 01:55 brackets means that those arguments are optional. 01:58 Excel will fill in default values if you don't put them in, but in this case our 02:02 worksheet contains the values we need. 02:04 So I'll go ahead and type c7, comma, and then c8, indicating that the payment comes at 02:10 the end of the period. 02:11 Type a right parenthesis. 02:12 Just make sure everything is okay. 02:14 It's especially important that we divide the interest rate by 12 which we are. 02:18 So I press Enter and we see that our principal component of the first payment is about \$729. 02:26 Now I'll move down using my keyboard to cell c13 and I create the IPMT function. 02:32 And I'll go through this more quickly, because it uses exactly the same arguments. 02:35 So we have IPMT and then the rates' in c3, again dividing by 12. Period is in c4. 02:44 And then we have the number of periods. That's in c5. Present value c6, future 02:53 value c7, and the type in c8. 02:56 Everything looks good. 02:57 I'll press Tab so I don't scroll down and we see that the interest component is over \$3800. 03:05 Over the life of a loan, the principal component and interest component will change. 03:10 You pay more interest at the beginning of the loan and more principal toward the end, 03:14 even though individual payment amounts never vary. 03:18 So for example, let's change the Period from 1 to 120 and that would be after 10 03:24 years of the 30 year loan. 03:26 So I type 120 into cell c4 and press Enter and we see that the 03:31 principal component has gone up and the interest component has gone 03:35 down fairly substantially. 03:37 Now I'll move back up to cell C4 and type in 180 which is exactly the 03:42 midpoint of the loan. 03:43 See the principal is catching up, but it's not quite there to the interest. 03:48 Then I'll change that period in c4 to 240 and we see that we're now paying off 03:53 more principal interest than interest. 03:55 And then for the last loan period, which is 360, we see that we are paying almost 04:00 exclusively principal and practically no interest at all. 04:04 Calculating the amount of principal and interest you have or will pay off on 04:08 your loans reflects both the equity you have in your purchase and the amount of 04:12 money you can write off on your taxes. 04:14 Always be sure to watch your interest expenses closely. Collapse this transcript
CUMPRINC and CUMIPMT: Calculating cumulative principal and interest paid between periods
 00:00 When you pay back a loan, each payment has a principal component and an 00:04 interest component. 00:05 Payments early in the loan's life consists mostly of paying down the interest. 00:09 All payments late in the loan's life are almost entirely principal. 00:13 You can determine the cumulative interest and principal you've paid on the loan 00:16 by using this CUMIPMT function and CUMPRINC function. 00:23 In this workbook, I have added the arguments that we need. 00:27 The first is the annual interest rate and the second is the Number of periods 00:32 and that's simply the number of payments in the loan. 00:34 In this case, I'm assuming a 30 year loan with 12 monthly payments per year, so 00:39 it's a total of 360 and then we have the Present value of 615,000. 00:45 Now in previous movies in this course you've seen that the present value is 00:49 negative, because it's an amount that you owe. 00:51 However, the way that these functions work we need to have the present value a positive number. 00:56 So it's a little bit different, but hopefully you'll remember that. 01:00 The Starting period is the first payment for which we want to start our 01:04 calculations of cumulative interest or cumulative principal payment. 01:08 The Ending period is the last period and then Type is either 0 or 1. 01:14 And this indicates when you make your payment. 01:16 If it's 0, which is the default, then Excel assumes that you're making your 01:21 payment at the end of a period, in this case the last day of the month, or May 30th. 01:25 If it's 1, then that indicates the beginning of a period, so for example May 1. 01:31 With all that information in place we can create our formulas. 01:34 So for the principal, I'll =CUMPRINC( and now I can start typing in the cell 01:44 references. So the Rate is in cell c3, but that's an annual rate. 01:48 And we are making monthly payments so we need to divide that by 12, twelve 01:53 months in the year, then a comma, and the number of periods is in cell c4. 01:59 Present value c5, the starting period is c6, and then a comma, the ending period 02:06 is in c7, a comma, and the type is in cell c8. 02:13 Type a right parenthesis and looking over the formula, I am dividing the 02:17 interest rate by 12. Always important. 02:20 My intention in creating this formula with these arguments is to find the 02:25 cumulative principal that I paid off from payments 1 through 12, or in the 02:29 first year of my loan. 02:30 So with that in mind I can press Enter and see that I have paid off over 02:34 \$9,279 in principal. 02:38 And the number is displayed as a negative number in red because that is 02:43 money that is going out of my account and we can do the same thing to find 02:47 the total interest. 02:48 I'll move through the formula more quickly this time because I use all the same arguments. 02:53 So it's equal, then cumulative interest payment, so CUMIPMT(, and then the rate is 03:00 in cell c3, divided by 12 for monthly payments, then a comma, number of periods in c4, 03:05 comma, present value c5, comma, starting period c6, comma, ending period c7, and then the type is 03:14 in cell c8 and right parenthesis. 03:16 Everything looks good. 03:17 I'll press Tab so I don't make the screen scroll, and in the first year of this loan, 03:22 I would have paid off almost \$30,000 in interest. 03:25 Now let's see how those numbers change as we move through the loan's period. 03:30 So let's say that we start after 10 years so it would be during year 11, so we 03:35 would have period 121 and then the ending period would be 132. 03:43 So I press Enter and then we see that the principal is over 15,000 and 03:48 interest is almost 24. 03:50 So the ratio is closer. 03:51 Now let's go to 241 to 252 and press Enter and we see that the principal has 04:02 outstripped the interest and we're paying almost twice as much principal as 04:06 we are our interest. 04:08 And then in the last few months of the loan if we go from period 349 to period 04:15 360 we'll see that we're paying almost exclusively principal. 04:19 Pay down principal on a loan gives you equity in the asset you're paying off and 04:23 you might be able to right off the interest. 04:25 You should check with your tax advisor to see how you can use those factors 04:28 to your advantage. Collapse this transcript
ISPMT: Calculating interest paid during a specific period
 00:00 When you take out a loan, either you or your accountant might need to know how 00:04 much interest you paid for a given period. 00:06 For example if your business borrows \$250,000 for some facility upgrades, you 00:11 might be able to write off some or all of the interest paid for a given 00:14 month, quarter, or year. 00:16 You can discover how much interest you paid on a specific loan payment by 00:20 using the ISPMT function. 00:23 I've set up the sample workbook with all of the arguments that we need for 00:27 the ISPMT function. 00:28 We have the Rate, which is simply the annual interest rate on the loan, then we 00:33 have the Period, which is the payment for which we're interested in 00:36 calculating the interest component. So this would be period 14 which is the 00:41 second payment of the second year. 00:43 Then we have the Number of periods and the Number of periods is the number of 00:48 payments throughout the life of the loan. 00:50 In this case we have 12 monthly payments a year over two years for the total 00:55 of 24. And then finally, we have the Present value, which is the amount that we've borrowed. 01:00 It's 250,000. 01:02 And with all those arguments in place we can create our formula. So type =ISPMT( 01:10 and then first we have the rate which is in cell C3. 01:14 Now that's an annual rate and we're making monthly payments, so we can type /12 01:20 to divide the value in C3 by 12, and then type comma and the period is in C4, comma, 01:27 number of periods is in C5, and then the present value of the loan is in C6. 01:33 Type a right parenthesis and then look through all of the arguments. 01:38 Everything looks to be good. 01:39 We've divided the interest rate by 12 for monthly payments and then press Enter. 01:44 And we see that the interest paid for that payment was about \$695. 01:49 Now if we change it to the last period by typing 24 in cell C4 and press Enter, 01:54 we see that we paid no interest. 01:57 It was exclusively principal. 01:59 You can use the ISPMT function to find out exactly how much of a payment was interest. 02:04 You should check with your tax professional to find out if you can write off the 02:07 interests you pay. If so, the ISPMT function will tell you how much that is. Collapse this transcript
EFFECT and NOMINAL: Finding nominal and effective interest rates
 00:00 Financial institutions have to advertise the interest rates their products 00:04 return to investors. 00:05 Most ads list an annual percentage rate which you can use to calculate the year 00:09 of the return by using the compound interest formula. 00:12 Some ads, however, list an annual percentage yield which is the net return and not 00:17 the actual interest rate applied to the investment. 00:19 When you compare two or more investment opportunities, you have to make sure 00:23 that all of your investments terms are expressed using the same type of interest rate. 00:27 That is, an APR or an APY. 00:30 Excel has two functions that let you convert annual percentage rates which are 00:34 also called nominal rates to annual percentage yields which are called effective rates. 00:41 So let's say that you want to convert an effective rate or APY to a nominal rate or APR. 00:47 To do that you need to have two arguments, the first is the effective rate or 00:51 APY and the number of periods per year. 00:54 If you look at the numbers on the left- side of my worksheet here, you'll see 00:58 that I have a space for APR and then the Principal and Term and all of those 01:04 arguments are used here in cell B13 which calculates using the compound interest 01:11 formula, the future value of a \$20 million investment over the term of 10 years, 01:17 with an APR that we will calculate here. 01:20 Now let's say that the APR is 5% and I'll type that into cell B8. 01:25 When I type it in, we see that the future value of the investment is about \$32.5 million. 01:32 But now let's calculate the APR by converting this annual percentage yield to an 01:37 annual percentage rate. 01:39 To do that, type =NOMINAL( and we type in the cell addresses of our two 01:48 arguments. We have B5 for the effective rate, type a comma, and then the number 01:53 of periods per year is in cell B6, type a right parenthesis, and remembering 01:59 that the future value in cell B13 is 32.5 million, press Enter and we see that 02:06 the future value drops by a substantial amount because we're dealing with 02:09 fairly large numbers. 02:10 Now let's do the same thing by converting an annual percentage rate to an annual 02:15 percentage yield and I have my same formula set up over here and I have a future 02:20 value of \$32.5 million, as we did on the other side. 02:25 The point of my calculation on the right side of the worksheet isn't to 02:28 calculate the future value. We already know that because we have the annual 02:32 percentage rate that we use in our compound interest formula. 02:35 Instead what I want to do is calculate the annual percentage yield based on an 02:41 APR of 5%, so that we can compare how much switching between an APY and an APR 02:47 affects the numbers that are used. 02:49 So to convert we type equal and then the function is EFFECT, left parentheses, 02:56 and the nominal rate or the APR is in cell E5, comma, number of periods per 03:02 year is in cell E6. Everything looks right, type a right parenthesis, press 03:07 Return, and we see then annual percentage rate equates to an annual percentage yield of 5.12%. 03:14 Every fraction of a percentage point of interest makes a big difference in the 03:18 value of a loan or an investment, especially when the numbers are in the 03:21 millions of dollars. 03:22 You should use the NOMINAL and EFFECT functions to ensure the interest rates of 03:27 the investments you're considering are expressed using consistent terms. Collapse this transcript
ACCRINT and ACCRINTM: Calculating accrued interest for investments
 00:00 When you invest in a security that pays interest, you can receive your 00:03 interest payments in one of two ways: either periodically or all at once when 00:08 the security matures. 00:09 Excel has a function for each of these cases. The ACCRINT function lets you find 00:15 accrued Interest that's paid periodically, and of the ACCRINTM function 00:21 calculates interest that's paid in a lump sum when the security matures. 00:25 You can remember the difference between the two functions by remembering that 00:28 the M at the end stands for at maturity. 00:31 In this workbook, I have two worksheets, one for periodic interest calculation 00:36 and the other for calculating interest at maturity. 00:39 On the Periodic worksheet, I have all of the arguments I need to calculate 00:43 accrued interest that's paid periodically. 00:45 So for example, I have the Issue Date, which is the date the security is first 00:49 issued, not the date that you bought it. 00:51 Then we have the First Interest Date and that is the date that it first pays 00:57 interest to its investors. 00:59 Settlement Date, that's the date you bought it, and then the Annual Rate, which is 01:03 the annual rate that the investment pays. 01:06 Par Value is the face value of the investment and that's listed in the prospectus. 01:11 Frequency is the number of payments per month. 01:14 That can either be 1, 2, or 4, so annual, semiannual, or quarterly. 01:20 Then we have Basis and a basis is the number of days in a month and in a year. 01:27 So for example, some accounting systems use 30-day months and assumed a 360-day year. 01:33 Others use actual days and so on. 01:36 Basically, you're going to have to know which one you're using based on your 01:40 jurisdiction's accounting rules. 01:42 And then finally, we have the Calculation method. 01:45 The calculation method can either be true or false. 01:48 If it is true, then Excel starts calculating interest from the issue date to 01:54 the settlement date. 01:55 If you set it to false, then it calculates interest from the settlement date to 01:59 the first interest date. 02:01 In most cases the value will be true and again that's something you'll just have to know. 02:05 So let's go ahead and create our first formula. 02:08 So we're in cell C12, type =ACCRINT for accrued interest, and then a left 02:15 parenthesis, and then the issue date is in C3, first interest date is in C4, 02:21 type a comma, then we have the settlement date in C5, comma, annual rate is in 02:26 cell C6, comma, par value C7, comma, then we have our frequency which is quarterly, that's in 02:34 cell C8, comma, the basis we'll use the standard US which is 30-day months and 360 day 02:42 a year, that's in cell C9, and then our calculation method, we're going to use 02:46 the default which is true. That is in cell C10. Type a right parenthesis. 02:51 Take a quick look to make sure I haven't mistyped any numbers. Everything appears to be 02:54 correct and press Enter. 02:57 When we do, we see that the accrued interest paid quarterly will be \$66.67. 03:00 Now let's switch over to the At Maturity worksheet by clicking its sheet tab and 03:07 you'll see that the calculation is a bit simpler. That's because we only need to 03:11 know the interest that accumulates throughout the life of the investment. 03:14 So here we have the Issue Date, the Settlement Date, the Annual Rate, the Par 03:19 Value, and the Basis. 03:21 So in cell C10, type =ACCRINTM because we're calculating interest at maturity. 03:30 Then a left parenthesis and we have the issue date which is C3, settlement 03:35 date C4, comma, and then the annual rate is in C5, comma,par value C6, comma, and the basis is in cell C7. 03:46 So I type a right parenthesis. Quick look, everything is right, and I'm going to 03:50 press the Tab key so I don't scroll down. 03:52 And when we do, we see that the interest at maturity is \$800 and that makes sense. 03:57 There is a \$10,000 investment at an annual rate of 4% over two years. 04:03 Depending upon when you receive interest on an investment, whether it's 04:07 periodically or when the investment matures, you can use these functions in 04:11 Excel to calculate how much you're due and when. Collapse this transcript
RATE: Discovering the interest rate of an annuity
 00:00 When you evaluate any investment opportunity, most of the time the first thing 00:04 you want to know is the interest rate. 00:06 However, there are some investments that are presented to you without that information. 00:11 What they tell you are the number of periods, the payment each period, the 00:14 present value, in other words the amount you're borrowing and so on, but they 00:18 don't tell you the interest rate. 00:19 However, if you want to find that interest rate using Excel, you can do so 00:24 using the RATE function. 00:26 The RATE function has three required arguments and three optional arguments. 00:31 I've listed them here in this worksheet. 00:32 The first required argument is the number of periods and that is simply the 00:36 number of payments that you're going to have to make. In this case it's monthly. 00:41 So it's a period of five years multiplied by 12 months per year, and then the 00:45 payment per period and that is expressed as a negative number because it is an 00:50 outflow from your account. 00:52 Next is the present value and that's basically the amount that you're borrowing. 00:55 In this case it's \$45,000. 00:58 Then we get to the three optional arguments. 01:00 The first one is the future value which is zero and this is how the accounting 01:05 format displays a zero. 01:07 Type can be one of two values. 01:09 It can either be zero, in which case you're making your payment at the end of an 01:13 accounting period, in this case a month, or 1 which means that you're making 01:17 your payment at the beginning. 01:19 Both future value and the type, if you don't include them in the function, 01:22 are assumed to be zero. 01:24 And then finally you have guess, and this is your guess at the interest rate. 01:29 If you don't put in a value then Excel uses a value of 10%. 01:34 So I put in 10%, which is the default just for explanatory purposes. 01:38 Okay, so let's go ahead and create the formula. So we'll type =RATE( and then 01:46 the first argument is the number of periods and that is in cell C3, type a comma, 01:52 then the payment which is in C4, and again that is a negative number because 01:56 it's an outflow from your account, comma, and then the present value, the amount you're 02:00 borrowing, and that is in cell C5, and then we get to the optional arguments 02:05 which are indicated in the formula tooltip by enclosing them in square brackets. 02:10 So we have C5 and then the future value's in C6. We're paying it down to zero. 02:14 Then we have the type which is when we make our payment and that is going to be 02:19 in cell C7, and then our guess which in cell C8. So type a right parenthesis, 02:24 make sure all the arguments are good, and press the Tab key. 02:29 When we do, we see that the rate is about 1%. 02:32 If you're ever faced with an investment where you don't know the interest 02:35 rate but you have this other information, you can use the RATE function to 02:38 discover it very quickly. Collapse this transcript
NPER: Calculating the number of periods in an investment
 00:00 Managing personal and corporate finances takes careful planning. 00:03 Many individuals and businesses try to save a certain amount of money whether as 00:07 part of an individual retirement account or a corporate building fund. 00:11 Once you determine how much you can set aside each month, you can use Excel's 00:15 NPER function to see how long it will take you to reach your goal. 00:18 The NPER function has five arguments. 00:22 The first is the annual interest rate, self-explanatory, and then the 00:26 payment each period. 00:28 And in this scenario what I want to do is to invest an initial \$2000 and then 00:35 make monthly payments of \$250 with a goal of reaching \$20,000. 00:40 So I have my monthly payment of \$250 and my present value or initial investment 00:46 of \$2000 and the payment each period and present value are expressed as negative 00:50 numbers because they represent an outflow from my account. 00:54 Next we have the future value and future value is the amount in this case that I 00:59 want to save and that goal is \$20,000. 01:02 And then finally we have when the payment is due and this can either be one of two values. 01:08 It can be 0, in which case the payment occurs at the end of an accounting period, 01:13 in other words at the end of the month, or it can be 1 which it is in this case 01:17 and that means that the payments are due at the beginning. 01:20 So normally, the way it works is that if you borrow money, then you pay at the 01:25 end of an accounting period. 01:26 That way interest has time to accumulate. 01:28 Whereas, if you're trying to save money then you want to get the money in as 01:32 quickly as you can, so that you will accumulate the interest and for that you 01:36 make the payment in the beginning of the month. 01:37 So we're using a savings example here, so we make our payments at the 01:42 beginning of the period. 01:43 So now the question we're asking is how many periods, in this case how many 01:47 months, will it take us to accumulate \$20,000 given the other parameters. 01:53 A 4% interest rate, payment each period of \$250 and a starting value of 2000? 01:58 Well, here is how we do it. 01:59 We'll go down to cell c10 and type =NPER and then a left parenthesis and then we 02:07 have the rate in cell c3, but because that's an annual rate and we are making 02:12 monthly payments, we need to divide the rate by 12, /12, then the payment each 02:18 period, so that's in C4. 02:20 Then we have the present value. I typed a comma to the separate the Arguments. 02:24 Present value is in cell c5 and then we have the future value of 20,000 in c6, 02:31 and the type that is when the Ppayment is due, after we type a comma, is in cell c7. 02:36 Then I type a right parenthesis and check through the formula. 02:39 Everything looks good and we'll press Enter. 02:41 So our value of 62.9 indicates that it will take us about 63 months to save 02:48 \$20,000 given the parameters that we've set. 02:51 But now let's see what happens if we increase our monthly payment, in other words 02:55 the payment each period to 300. 02:57 So remember this value here is 62.9. 03:01 If we make the payment each period 300, again a negative number because it's an 03:05 outflow from your account, the number in the c10 changes from 62.9 to 53.5. 03:12 So, just that extra \$50 a month reduces the number of periods that we need to save 03:16 by about 11, in other words getting into your goal about 1 year faster. 03:21 The NPER function gives you and your business insights into how long it will 03:24 take you to reach your savings goal. 03:26 Remember that even small increases in your monthly contributions to an 03:29 investment will get you to your goal that much faster. Collapse this transcript
2. Calculating Depreciation
SLN: Calculating depreciation using the straight-line method
 00:00 The most straightforward method you can use to calculate depreciation is the 00:04 straight line method. 00:05 As the name implies, the straight line method assigns depreciation evenly over 00:09 the economic life of the asset. 00:11 The starting amount is the asset's purchase price and ending value is the 00:15 asset's salvage value. 00:16 If your asset has an economic life of 10 years and a salvage value of 0 for 00:20 example, you will depreciate by 10% each year until its value reaches 0. 00:25 To calculate straight line depreciation, you use in the SLN function. 00:30 The SLM function calculates the amount of depreciation for a given period 00:34 which is usually a year. 00:35 And the function takes three arguments. Those are the initial cost, the salvage 00:41 value which is the amount at which you could sell the item for scrap, and then 00:46 the economic life, which for a computer is assumed to be 5 years. 00:49 So let's go ahead and create the formula. 00:52 So clicking in cell B11, =sln, which is our functions, and a left parenthesis. 00:59 We have the cost which is in cell b5. Type a comma. 01:03 Salvage value is in b7, type a comma, and then the economic life in years is in 01:09 cell b9. Type a right parenthesis. 01:11 Everything looks good. 01:12 And then I will press the Tab key so I don't scroll down. 01:16 And we see that there is a depreciation of \$230 per year and we can verify 01:21 that's correct by multiplying the depreciation per year, which is 230, by 5 and 01:26 that gives us a \$1,150 and then if we add the salvage value of 150, we get our 01:32 total of the initial cost. 01:34 Straight line depreciation is the most conservative approach you can take to depreciation. 01:39 It assumes that you will hold on to the asset until the end of its economic 01:42 life so you should spread out the tax benefits of the depreciation while you 01:46 still own the asset. Collapse this transcript
DB: Calculating depreciation using the declining balance method
 00:00 The declining balance method of calculating depreciation accelerates the rate at 00:04 which an asset loses its value over time. 00:07 You use the DB function to calculate depreciation with the declining 00:11 balance method in Excel. 00:12 The declining balance method is so named because it reduces an asset's value by 00:17 the amount it depreciated in the previous years. 00:19 You then calculate the new depreciation based on that lower value, hence the 00:23 name declining balance method. 00:25 The DB function has four arguments. 00:27 Cost, salvage value, economic life and period. 00:31 The Cost, which is in cell B7, is what you pay for the asset. 00:35 The Salvage Value, which is in cell B9, is what you can sell the asset for as scrap. 00:41 And then next we have the Economic Life, which in this case is assumed to be 7 years. 00:46 And then you have the Period and that is the year in the depreciation schedule 00:50 and I have a series of those years set up here in cells D6 through D12. 00:56 So I'll be calculating depreciation for years 1 through 7. 01:00 There is one final argument and that is whether you're going monthly or yearly. 01:05 Almost every asset you deal with will depreciate over years instead of months. 01:09 So I've decided to stay with years for this example. 01:12 Now I'll create my first formula and to do that I'll click in cell E6 and then 01:18 type an =db, to start entering formula using the Declining Balance function. 01:25 Type a left parenthesis and then we have the cost that's in b7, type a comma, 01:30 then the salvage value that's in b9, the economic life in years and that is in 01:35 cell b11, then a comma, and the period. 01:39 Now the first period is in cell D6, so that is the first year. So type d6. 01:46 Now what's going to happen when I press Enter to put this formula into this 01:50 table cell is that Excel will copy it down to the rest of the cells in a table column. 01:55 When it does that, the references to cells B7, B9, and B11 will change because 02:00 they are currently relative references. 02:02 However, there aren't other values here in the side of the worksheet. 02:06 I want those references to stay the same. 02:08 So I need to convert those cell references to absolute values as opposed 02:13 to relative values. 02:15 And to do that in the formula, I click in the cell reference that I want to make 02:19 absolute and then press F4. 02:21 That changes the reference from a relative reference to an absolute reference. 02:25 Then I do the same thing for B9, click inside of it in the formula and then 02:29 press F4 and the same for B11. Click inside of its reference and then press F4. 02:35 Now D6 which is the cell here for the year, I do want to change because as it 02:40 copies down I want to look to D7, then D8, then D9 and so on. 02:45 So with those changes in place I'll click to the right of the D6 reference, then 02:50 type a right parenthesis and press Enter. 02:53 And when I do, Excel fills in my table. 02:55 I now have depreciation for years 1 through 7. 02:58 The declining balance depreciation method enables companies to capture more 03:02 depreciation benefits early in an asset's economic life, decreasing tax payments 03:07 and thereby freeing up capital to invest in other areas. Collapse this transcript
DDB: Calculating depreciation using the double-declining balance method
 00:00 The declining balance method of calculating depreciation enables companies 00:04 to accelerate the rate at which they claim the tax benefits inherent in 00:07 asset depreciation. 00:09 As the name implies, the double declining balance depreciation method doubles the 00:13 rate at which the declining balance method calculates an asset's depreciation. 00:18 To calculate depreciation using the double declining balance method, you use the 00:22 DDB function and the DDB function has five arguments. 00:26 The first is the Initial Cost of the asset, in this case 34 million. 00:31 Then the Salvage Value and that is the value for which you can sell the asset as 00:35 scrap at the end of its economic life. That's \$1 million. 00:38 Then we have the Economic Life and for a building that's assumed to be 30 years. 00:43 And then the fourth argument is the Period and in this case it is years. 00:48 So I have a series of years in cells d6 through d15, for years 1 through 10 of 00:55 this 30-year economic life. 00:56 And then finally we have cells to calculate the depreciation using the DDB function. 01:02 Now the fifth argument we're not going to use and that is called a factor. 01:06 The factor allows you to identify how quickly you want the declining 01:10 balance method to work. 01:11 So for example, the double declining balance method has a factor of two and 01:15 that's assumed it's the default value; however if you want to use triple 01:19 declining balance method then you could make the factor 3. 01:23 But in this case, we'll stay with the basic double declining balance method, so 01:28 in cell E6 I'll press of the equals key and then type ddb and then a left 01:33 parenthesis and now I can start adding my arguments. 01:36 So the cost is in cell b7, type a comma. 01:39 Salvage value is B9, then a comma. 01:42 Then the life is in cell b11. 01:44 That's the economic life, the number of years the asset has economic value. 01:49 And then finally the period and that is in cell d6. 01:52 Now before I type a right parenthesis and press Enter, I need to make a couple 01:56 of changes to my references. 01:58 When I complete this formula and press Enter, Excel will copy it down to the 02:02 remaining rows in this column of the table. 02:05 When it does that, the references for B7, B9, and B11 will change because I 02:10 currently have them listed as relative references. 02:14 To make them absolute references I need to do a little something with them. 02:17 So to change B7 from a relative reference to an absolute reference, I click in 02:23 the reference itself and then press F4 and the dollar signs indicate that it's 02:28 now an absolute reference. 02:30 I'll do the same for B9. Click inside the reference and pressing F4 and the same 02:35 for B11 because I don't want those references to change. 02:39 Now d6 I can leave as-is and the reason is that as the cell formula gets 02:45 copied down, I want the reference to change from d6 to d7 so it uses 2, d8, so 02:50 it uses 3, and so on. 02:53 So with that formula in place, I can type a right parenthesis, verify one more 02:57 time that everything looks good, and press Enter. 02:59 And when I do, Excel calculates the double declining balance depreciation for 03:04 each of the first 10 years. 03:06 The double declining balance method assigns a high percentage of an asset's 03:09 depreciation to the first part of its economic life. 03:13 As with the declining balance method, companies can use their depreciation and 03:16 related tax savings to invest in other areas. Collapse this transcript
SYD: Calculating depreciation for a specified period
 00:00 There are many acceptable methods for calculating depreciation schedules, some 00:04 of which are more aggressive than others. 00:06 The straight-line depreciation method is very conservative and the declining 00:09 balance method is more aggressive. 00:11 The sum of years depreciation method is somewhere between the two and in this 00:15 movie I'll show you how to use the SYD function to calculate the sum of years 00:19 depreciation value for a given year. 00:21 The sum of years method is named for the way the number of the year in the 00:25 asset's used for life is used to calculate the amount of depreciation allowed. 00:29 The SYD function uses four arguments. 00:32 The first is the initial cost of the asset. The second is its salvage value. 00:36 That is the amount for which you can sell the asset when it's reached the end 00:40 of its useful life. 00:41 Then the number of years of its economic life and in this case that's 30 00:45 years, and then finally the period for which you're calculating sum of years depreciation. 00:50 So let's go ahead and create our function. 00:53 I click in cell C9, type in equal sign, and then syd for sum of years 01:01 depreciation, left parentheses and then we can start filling in our arguments. 01:06 So the cost is in cell C3, the salvage value in C4, typing a comma, then the 01:12 life is in cell C5, comma again, and then the number of the period is in cell C6. 01:19 Type a right parentheses, make sure everything looks right, and I believe it is, 01:22 then press the Tab key so I don't scroll down. 01:24 And we can see that the depreciation on this asset in year one is about \$20,900. 01:31 Let's see how that changes throughout the life of the asset. 01:33 So I'll change the period from 1 to 11, so it's in its 11th year, and then press 01:38 Enter and the depreciation goes down to about 14,000. 01:42 Now I'll change it to 21 so it's in its 21st year of its 30 year economic 01:47 life and press Enter. 01:48 depreciation drops by about half and then finally in its last year, which is the 01:53 30th year, we see that we get a very small amount of depreciation. 01:57 The way that you calculate sum of years depreciation is actually fairly 02:00 interesting, but it's beyond the scope of this movie. 02:03 If you want to use the sum of years depreciation method you can use the SYD function. 02:07 You'll only need these four arguments and you can calculate the 02:10 depreciation values you need. Collapse this transcript
VDB: Calculating declining balance depreciation for a partial period
 00:00 The declining balance and adouble declining balance methods let you capture 00:04 most of an asset's depreciation early in its economic life. 00:07 For a building, which has the useful life of 30 years, these aggressive 00:11 depreciation schedules generate huge tax offsets in the first few years. 00:15 The downside of this type of schedule is that you get to offset almost no 00:19 depreciation toward the end of the asset's economic life. 00:22 If you use a variable declining balance approach, you can mix declining 00:26 balance and straight-line strategies to capture more of the benefits toward 00:30 the end of the asset's life. 00:32 In Excel you use the VDB function to calculate variable declining 00:36 balance depreciation. 00:38 I've set up this worksheet so that I have all of the data that I need, so I have 00:42 the initial cost of the asset. In this case that's \$50,000. 00:46 Then the salvage value which is the amount for which we can sell this item for scrap. 00:51 Then the item's economic life, which is expressed in years, in this case 10. 00:56 And then you need to tell Excel the start period and the end period for which 01:00 you want to calculate the balance. 01:02 So in this case the start period will be your number eight and the end period 01:06 will be your number nine. 01:08 And then the final two arguments are optional. The first is a factor and the 01:13 factor is the same as in the double declining balance function, which is DDB. 01:18 If you want to use double declining balance you set the factor to 2, or leave 01:22 it blank because 2 is the default value. 01:24 If you want to use triple-declining balance then you can set the factor to 3, and so on. 01:29 The final argument is called switch or switch method. 01:33 The idea behind the variable declining balance method is that you take the 01:37 double-declining or triple-declining balance depreciation until that value is 01:43 less than what the straight- line depreciation would be. 01:46 When the double declining balance method depreciation would be less than the 01:50 straight-line depreciation setting the argument to true causes Excel to stay 01:54 with double-declining balance instead of switching, while setting the argument 01:58 to false would cause it to switch to the straight-line method. 02:01 So with those arguments in place we can create the VDB function, so I'll type equal 02:07 And then vdb, again for variable declining balance, then type a left 02:11 parentheses and we can start filling in the cells that contain the arguments. 02:16 So our vost is in cell C3 ,then a comma, salvage value C4, the economic life 02:22 which is the total number of years is in C5, the start period for which we're 02:27 calculating is in cell C6, the end period is in C7, and then the factor is in 02:33 cell C8, and we do have it set to 2 for double declining balance, and then we 02:38 have these method, whether we're going to switch method or not. 02:41 And in this case we will, so it set to true and that's in cell C9. 02:46 Then we type right parentheses to close out the functions arguments, make sure 02:49 everything looks good, and I will press Tab. 02:52 So I don't scroll down on the worksheet. 02:55 And when I press Tab, Excel completes the formula and we receive that 02:58 depreciation for the years 8 and 9 it will be \$1677.72. 03:05 If we were to change the start period to 1 and the end period to 2 then we would 03:10 see that we have substantially more depreciation. 03:13 Again, we use the double declining balance method at first and then when the 03:17 DDB calculation would generate less depreciation than straight-line we switch 03:22 to straight-line. Collapse this transcript
AMORDEGRC: Calculating depreciation using a depreciation coefficient
 00:00 Different countries have different methods of calculating depreciation. 00:03 Excel includes the AMORDEGRC function for use with French accounting rules. 00:10 In this case you can assign the depreciation coefficient based on the 00:13 economic life of an asset. 00:15 So for this function we have the cost argument and that is simply the cost and 00:20 because we're using French accounting rules I have the currency denomination as Euros. 00:25 Then we need to know the date the asset was purchased, the end of the 00:31 first period, then the salvage value in this case 10,000 Euros. 00:36 The period for which we're calculating our depreciation, and then the rate, 00:41 and the rate is something that your accountant will know based on the economic life of the asset. 00:46 There is a table that's published where you can look at that. 00:49 And then finally we have the basis, and the basis is how you count days 00:54 and months in a year. 00:56 So for example in the US, investment banks typically use a 30-day month and 12 01:02 months a year or 360 day a year. 01:05 In this case option number four is the European method so I've used that as the 01:10 value for the basis argument. 01:12 And with all those arguments in place we can create our formula. 01:16 So I'll type equal and then the function is AMORDEGRC, left parentheses, and 01:23 then we have the cost which is in C3, type a comma, the date purchased is in 01:28 C4, comma, first period, which is again the end of the first depreciation period that's in cell C5. 01:36 Salvage value is in C6. The period for which we're calculating depreciation 01:41 is in cell C7, then type a comma, and the rate is in cell C8, comma, and then the 01:47 basis is in cell C9. 01:50 Then we type right parentheses, verify that all of the arguments look to be 01:55 correct, I believe they are, and then press Tab. 01:57 When we do, we see that the depreciation for the first year is 31,276 Euros. 02:04 Now if we change the period to 2 for the second year by typing 2 in cell C7 and 02:09 then Enter, we see that the depreciation goes down to about 23,500 Euros. 02:15 When you calculate depreciation, be sure you know which set of rules you're using. 02:19 If you operate under the French system, you'll probably find that the AMORDEGRC 02:24 function to be useful. Collapse this transcript
AMORLINC: Calculating depreciation for each accounting period
 00:00 Like the AMORDEGRC function which lets you calculate depreciation using 00:06 coefficients defined under French accounting rules, the AMORLINC function lets 00:12 you find the depreciation for a specific accounting period and again this is a 00:16 function that you will use within the French system. 00:18 So to calculate depreciation for an accounting period you need the 00:23 following arguments. 00:24 The first is the cost. That's simply the amount that you've paid for the asset. 00:28 The date you purchased it, and then the first period, and that date is the end of 00:33 the first depreciation period. Then you have the salvage value, which is the 00:37 amount that you can sell the asset for after it's reached the end of its life. 00:41 Then you have the period for which you're calculating depreciation and that 00:46 refers to a year. So in this case we have here number one. 00:49 And then the Rate, which we've set to 10% and the Rate for using a depreciation 00:54 coefficient like this is set under French accounting rules and it's something 00:58 that you can look up. 01:00 Then the final argument is the basis and this is how you count the days in a 01:03 month and in a year. 01:05 Zero is the American system standard, which is 30-day months times 12 months for 01:11 a 360 day year, then there's also the European method which the French use and 01:15 that is option number 4, so that's what I have in cell C9. 01:19 So with all that information in place, I can click cell C12 and start 01:23 creating the formula. 01:24 So type in an equal sign and then AMORLINC, then a left parentheses, and the 01:32 first argument is the cost that's in C3, comma, the date purchased is in C4, comma, end of the 01:39 first period is C5, comma, then the salvage value is in cell C6, comma, the period is C7, comma, 01:47 the rate, which is again the depreciation coefficient, that's in C8, comma, and then the 01:54 basis, in other words how we count days in a month and in then a year, and that 01:58 basis is number four. It's in cell C9. I type a right parentheses to make sure 02:03 everything is correct. It appears to be and now I press Tab. 02:06 And when I do, I see that the depreciation is 37,500 Euros. 02:12 Now if I would change the period from 1 to 2 in cell C7 and press Enter, you'll 02:18 see that the depreciation doesn't change. 02:20 Now if I change the period to 9 you'll see that the depreciation goes down quite a bit. 02:25 That's because we are approaching the salvage value and the depreciation 02:28 coefficient operates differently in the calculation. 02:32 When you calculate depreciation, be sure that you know which set of rules you're using. 02:36 If you operate under the French system, you'll probably find that the AMORLINC 02:41 function to be useful. Collapse this transcript
3. Determining Values and Rates of Return
FV: Calculating the future value of an investment
 00:00 One of the more conservative investment strategies is to purchase a certificate 00:04 of deposit or another fixed-rate annuity that trades lower risk for a relatively 00:09 low but known rate of return. 00:11 You can evaluate this type of investment using the future value or FV function. 00:16 The FV function has five arguments and I've laid those out in this workbook. 00:22 The first is the rate and that is the annual percentage rate. 00:25 Then next is periods and that is the number of periods over which interest 00:31 will be accumulated. 00:32 So in this case, I have it set as 5 years. 00:35 Then the next argument is the payment and this is the payment that you make every period. 00:41 So for example in this case every year you'll put in an additional \$10,000. 00:47 Then we have the present value and that is \$100,000 and that's the amount 00:51 that you start with. 00:52 You can think of it as a down payment on a house for example. 00:55 Now both present value and payment are negative numbers, so they're displayed in parentheses. 01:01 And the reason they're negative is because it's money that is flowing out of 01:05 your account that you don't have control over anymore. 01:08 And then the final argument is type and type can either be 1 or 0 and it 01:14 indicates when you make your payment. 01:16 Anytime that you're paying off a house or a car or any other loan where the 01:20 bank collects the interest, then typically you'll pay at the end of a period. 01:25 Now on the other hand if you have an investment where you capture the benefits 01:29 of the interest then it's in your best interest to make your payment at the 01:32 beginning of a period and to indicate that in the type argument you give it the value 1. 01:38 Okay, so now with those arguments in place, we can click in cell B9 and 01:43 create the formula. 01:44 So I'll type equal and then fv for future value, then a left parenthesis and the 01:50 rate is in b3, comma, period is in b4, the payment is in b5, the present value is in 01:58 b6, and the type is in b7. 02:02 Again 0 at the end of the period, 1 at the beginning. Then type a right 02:06 parenthesis to close out the formula. 02:08 Everything looks good. Press Enter and when we do we see that the future value 02:12 of our investment would be a little bit over \$193,000. 02:17 Now let's see what would happen if we change the type to 0 in other words making 02:21 our payments at the end of a period. 02:23 So to do that click in cell B7, then type a 0 and remembering that the current 02:30 value of our investment is 193,000 and change, press Enter and we see that the 02:36 future value drops by about \$3000 and the reason that happens is because we 02:41 make our payment later. 02:43 So if you capture the interest, it's always in your best interest to pay early. Collapse this transcript
FVSCHEDULE: Calculating the future value of an investment with variable returns
 00:00 Sometimes investments don't have a single interest rate applied to them. 00:04 For example, you could put money into a fund that guarantees a return of 4% for 00:08 the first year, 5% for the second year, and 6% for the third year. 00:11 In this case, you can use of the FVSchedule function to determine the future 00:16 value of your investment. 00:17 FV schedule has two arguments, which are the present value which is the principal 00:22 of the investment, and the schedule of interest rates. 00:25 Those arguments are set out in this workbook. 00:28 You can see that I have the principal, the starting value, in cell C4 and then 00:32 the rates in cells C5 through C7. 00:36 When you put in the interest rates they almost be applied for the same timeframe. 00:39 So for example, in this case I have an annual interest rate. 00:43 If you're evaluating over months then you would need to divide those rates by 00:47 12, so that they represented a monthly rate. 00:50 Now I can create my formula in cell C10. So it's =fvschedule and as soon as fvs 00:57 appears in the cell I get the formula autocomplete for FVSCHEDULE, so I can 01:02 press the Tab key to fill it in. I also get a left parentheses and I can type the 01:06 formula C4, then type a comma, and now I can enter the rates. 01:11 And I enter the rates by selecting cells C5 through C7. 01:16 So with that formula in place I can type a right parentheses to close it out. 01:19 Everything looks good and I'll press the Tab key so I don't scroll down and I 01:23 see that the future value of my investment after three years is \$11,575.20. 01:27 Now let's say that you want to have a rate that would extend over two years. 01:33 So for example, you would have 4% in year 1, 5% in year 2, 6% in year 3, and then 6% again. 01:40 To do that you can just add another entry and I'll call it 6%. Press Enter and 01:46 then I'll go down to cell C10, double- click the cell so I can edit the formula, 01:50 and I will change C7 by backspacing over the 7 and typing in 8. 01:55 So now my schedule of rates goes from cells C5 through C8. 02:01 And if I press the Tab key I see that the future value is \$12,269.71. 02:07 The FVSchedule function provides a shortcut for calculating the interest 02:10 earned by series of rates. 02:12 You could create a series of formulas to see how your investment progresses 02:15 each step of the way, but if all you care about is the answer then FVSchedule 02:19 is the way to go. Collapse this transcript
PV: Calculating the present value of an investment
 00:00 Calculating the present value of an investment lets you answer this question. 00:04 How much is the proposed investment worth in today's dollars? 00:07 You can answer that question using the PV or present value function. 00:11 The PV function has five arguments the first argument is the rate and that is 00:15 simply the annual percentage rate. 00:17 Then the second is the number of periods and in this case we're assuming that we 00:21 have interest that is compounded annually and that we have a three-year 00:25 investment so the number of periods is 3. 00:28 Next is the payment and that is \$250 per year and in this case we're 00:33 assuming that the payment is a negative number, because it is an outflow from your account. 00:37 So that's why the number appears in parentheses. 00:41 The next argument is the future value and that is \$10,000 and that is the amount 00:45 you'll receive at the end of the investment term and then finally we have type. 00:50 Type can be either 1 or 0. 00:52 If you type in 1 then you are making your payments at the beginning of an 00:56 investment period such as at the beginning of the year on January 1, or if it's 01:00 set to 0, which is the default value, then you are making your investment payment 01:04 at the end of the period, so for example, at the end of a year on December 31. 01:10 Now the question in this scenario is trying to answer is whether you would pay 01:15 \$7,800, the asking price for this investment? 01:18 To answer that question we can create the PV function. 01:21 So I'll type in equal sign in cell B9 and then pv, left parentheses, then we can 01:28 type in the cell that contains the rate, which is B3, comma, the number of periods b4, comma, 01:35 the payment and that is in cell b5, comma, the future value in B6, comma, and then the type 01:42 and that is in B7. We will be paying at the end of period. 01:46 So everything looks good. Type a right parentheses to close out and press Enter. 01:50 And when we do we see that the present value of this investment is \$7,727.94 01:57 which is less than the asking price of \$7,800. 02:01 That means that we would be overpaying for this investment by about \$73. 02:06 Now let's change the scenario a little bit so that we have interest that 02:10 is compounded monthly. 02:12 And I'm not going to change the asking price. We're just going to ask what 02:15 would be the present value or the present worth of an investment according to these terms. 02:20 So let's change the number of periods from 3 to 36 and press Enter. 02:25 So now we have 36 years with a present value of \$2,400 and change. 02:31 So the question is how do we edit our formula, which I'll show by 02:35 double-clicking on cell B9? 02:37 How do to we edit that formula to reflect the fact that the periods are no years, 02:41 they are in fact months? 02:43 Well to do that we need to divide the interest rate in cell B3 by 12 to indicate 02:47 that the interest is being compounded monthly. 02:49 So for that I can click just to the right of the B3 cell reference in the 02:54 formula, then type a forward slash for divides, and then 12 to divide by 12 02:59 months in the year. 03:00 Then when I press Enter we'll see that the present value is actually quite low. 03:04 And that is \$138.70 and that's most likely due to the fact that we are making 36 03:10 payments of \$250, which is an extremely high figure, and would get us a very close 03:16 to the \$10,000 future value on it's own. 03:18 So now let's change the Payment to \$50. 03:21 Excuse me, that needs to be a negative number. -50 because it's an outflow. 03:26 So now we'll be paying \$1800 into the investment, which was the total of 36 \$50 03:32 payments, and when I press Enter we'll see that the present value is \$6,712.90. 03:38 That means that you should pay no more than that amount to get into this investment. Collapse this transcript
NPV: Calculating the net present value of an investment
 00:00 The net present value of an investment is the present value of the investment 00:04 minus the amount of money it costs to buy in. 00:06 You can calculate the net present value of an investment using the NPV function. 00:11 When you do all the investments cash flows must occur at the same interval for 00:15 the calculation to be accurate. 00:17 The NPV function has two arguments: rate, which refers to the discount rate, and a 00:22 range of values that contain the investments future cash flows. 00:25 So I'll start with the discount rate. 00:27 The discount rate which is here in cell B3 and it's 4% is the amount of 00:31 income that you can receive from a guaranteed investment such as, the U.S. Treasury bill. 00:37 So if T-Bills are paying 4% then that is what you are comparing in these other 00:41 investments against. 00:42 Now the other arguments that you will need are a series of cash flows. 00:46 So in this case for Investment 1 and we are comparing two different investments here, 00:50 we have an initial investment of \$100,000 and that number is in 00:56 parentheses because it is a negative number. 00:58 And then we have 5 years of \$25,000 payouts and we're comparing that to a second 01:04 investment which again has a \$100,000 buy-in and then it has five payments of 01:08 \$20,000 and then a final payment of \$25,000. 01:11 So what we're trying to decide is whether investment 1 or investment 2 makes 01:17 more monetary sense. 01:19 In other words, which has the higher net present value? 01:22 To do that, we can create an NPV function. 01:25 So I'll click in cell B15 and type =npv left parentheses. 01:31 Now we can add the rate which is in cell B3 and I'm going to assume that 01:35 interest is compounded monthly. 01:37 So I will type / 12 to divide the rate by 12, then type a comma, and 01:43 now we can enter in the cash flow values. 01:45 If you type in the cells individually or individual values if you like then you 01:50 would type in up to 254 values, but the easiest way to do it is to select the 01:55 cash flows themselves so that the formula has a range to work with. 01:59 So I'll select cells A8 through A12 and yes, I left cell A7 out on purpose. 02:04 I'll show you why in a second. 02:05 So those are the cash flows that I will receive. 02:08 Now I'll type a right parentheses, but before I press Enter I need to include 02:12 the \$100,000 buy-in. 02:14 So I'll add the amount and cell a7 which is -\$100,000. 02:18 So I'll type A7 and I don't need a parentheses or anything. 02:22 So with that in place I'll press Tab and I see that the net present value 02:26 of this investment, 02:28 investment 1, is 23,000 and about \$750. 02:31 So now I'll go over to cell E15 and I'll do the same thing for the cash 02:37 flows for investment 2. 02:38 So in cell E15 type =npv( and then the rate, once again 02:45 it's in cell B3, and compounded monthly so divide by 12, and then I'll select 02:50 cells D8 through D13, type a right parentheses then a plus sign, and D7 for 02:58 the \$100,000 buy-in. 03:00 So everything looks good in the formula. Press the Tab key and I see that the 03:05 net present value in this investment is \$23,513.51. 03:10 So it's close, but the net present value of investment 1 is higher so that's the 03:15 one that makes more economic sense. Collapse this transcript
IRR: Calculating internal rate of return
 00:00 Calculating an investment's internal rate of return lets you identify the 00:04 interest rate at which your investment would have a net present value of zero. 00:08 In other words, your formula tells you that discount rate at which you would 00:12 break even on a given investment. 00:14 If the formula, which uses the IRR function, returns a value greater than the 00:18 interest rate generated by risk-free investments such as T-Bills, then you 00:22 should make the investment. If not, you should pass. 00:26 The IRR function has two arguments: a range of values that reflect future cash 00:30 flows and optionally a guess at the rate of return. 00:34 Excel starts it's guessing with the rate of 10%, which will work in most cases. 00:38 The cash flows may either be positive, which is income, or negative, which is an 00:41 expense, but they must all occur at regular intervals such as either every 00:46 month or every year. 00:48 So let's go ahead and compare these two investments. 00:51 The first has a \$100,000 buy-in and then payments of 20, 20, 20, and \$40,000 00:58 totaling 120 over the course of five years, and then the second investment also 01:03 has the total payout of 120,000, but it's over six years and our guess is at 10% is in cell b3. 01:12 So I'll click now in cell B14 and create the formula for investment number 1. 01:16 So it's =irr( and then the values, which are in cells A6 through A11. 01:23 So I'll just select those cells, type a comma, and then type in the reference to 01:28 cell b3, which has our guess at the interest rate. 01:31 Type a right parentheses to close up the formula, verify that everything looks 01:35 right, and then press Tab. 01:37 When I do I see that the internal rate of return for investment 1 is 5.73%. 01:43 Now I can click over in cell E14 and create the same formula for 01:48 investment number 2. 01:49 So it's =irr( and then the values are in cells D6 through D12, so 01:56 I'll just select them, type a comma, and then the guess for the interest rate is 02:02 in cell b3 so I'll type b3, right parentheses, and then press Tab, and we see that 02:09 that investment has an internal rate of return of 5.47%. 02:13 So if an investment's internal rate of return is higher than the discount rate 02:17 you assume for a risk-free investment, you should go ahead. 02:20 You can also use internal rate of return to compare two separate investments. 02:24 Just figure out the cash flows, make sure they're regular intervals, add to 02:28 an internal rate of return for each of the investments and take the one 02:31 that's higher. Collapse this transcript
XNPV: Calculating net present value given irregular inputs
 00:00 In the previous movie I showed you how to use the NPV function to find the net 00:05 present value of an investment. 00:07 That function assumes that all of the cash flows happen at regular intervals, 00:10 every month, every two months, every year, and so on. 00:13 If you have a series of cash flows that occur at irregular intervals, you need to 00:17 use the XNPV function to find the net present value. 00:21 So for the XNPV function, you have two sets of arguments. The first is the 00:26 discount rate and the discount rate is the return that you can expect on a 00:32 guaranteed investment. 00:33 So for example, if you were able to invest in U.S. Treasury bill with a 5% rate, 00:38 then that would serve as the discount rate. 00:40 Next you need the values anddates. 00:44 So the first of the values and those are simply the cash flows. Negative 00:50 cash flows in this number format are shown in parentheses so we have a 00:54 buy-in of \$108,000. 00:56 And the next each one of these cash flows you have the date. 01:00 So we have May 1, 2011, June 1, 2011, August 1, 2011. 01:06 So you see that those dates occur at irregular intervals and again if you're 01:10 cash flows occur at regular intervals then it's simpler to use NPV. 01:13 So now let's go ahead and create our formula. I'll click in cell C15, type an 01:19 equal sign, an then xnpv( and then the discount rate which is in 01:25 cell c2, then a comma, then the values and those are in cells B5 through B12. 01:31 So I'll just select those cells, type a comma, and then the dates of those cash 01:35 flows are in cells C5 through C12, type a right parentheses. All the references 01:42 look good, press Tab, and we see that the investment has a net present value of \$27,221.31. 01:49 Now what would happen if the discount rate went down? 01:52 In other words, what would happen if you were only able to get say a 2% 01:56 return from T-Bills? 01:58 Well, that would increase the net present value because you have lower 02:01 guaranteed income from a guaranteed investment. 02:04 So for example, let's say that I change the value in cell C2 to 2% then I just 02:09 typed the 2, because I already have the percentage format applied. 02:13 So I haven't the formula at all. Just changing that one input, then I can press 02:17 Enter, and we see that the net present value goes up substantially. 02:21 So remember that if you have cash flows that occur at regular intervals then use NPV. 02:26 If your cash flows occur at irregular intervals then use XNPV. Collapse this transcript
XIRR: Calculating internal rate of return for irregular cash flows
 00:00 Many businesses use cash flows from several sources to fund other investments. 00:04 These revenues don't always come in on a regular monthly or annual schedule so 00:08 you can't use the IRR function to calculate internal rate of return. 00:13 However, you can use the XIRR function to calculate the internal rate of return 00:17 for cash flows that occur at irregular intervals. 00:21 The XIRR function has three arguments. 00:25 The first is the cash flows themselves, so we have those in cells B5 to B12. 00:31 The next are the dates when those cash flows occur and those are in cells C5 to C12. 00:36 Now the third and final argument is your guess at the internal rate of return 00:41 and I have it here in cells C2. 00:44 Excel uses this as a basis for its calculations because it goes through 00:47 iteration to find the internal rate of return for these cash flows. 00:52 If you leave the guess argument blank, Excel guesses 10% and that's good in most cases. 00:56 I just put it in here as 5% to give you a visual aid of what it would look like 01:01 when we put it into the formula. 01:02 Okay, now I'm going to click back in cell C15 and create the XIRR function. 01:08 So it's =xirr( and then the values are in cells B5 to B12, the dates of those 01:18 cash flows are in C5 to C12, and then my guess at the internal rate of return is in cell C2. 01:26 Type a right parenthesis to close out the formula. 01:28 Everything looks good and press Tab. 01:31 And when I do, I see that the internal rate of return is about 6 and a half percent. 01:35 Whenever you need to evaluate the internal rate of return of an investment for 01:38 the cash flows occur at irregular intervals, use the XIRR function. 01:44 If the cash flows occur at regular intervals, then you can use IRR. Collapse this transcript
MIRR: Calculating internal rate of return for mixed cash flows
 00:00 Investing money can become quite complicated. For example, you might need to 00:04 borrow the money you invest in some other project. 00:06 You could also reinvest any proceeds from that project into another investment 00:10 and get a gain on that money. 00:12 If you need to calculate the internal rate of return for a series of mixed cash 00:16 flows then you can use the MIRR function. 00:19 The MRR function has arguments which are the interest rate on the initial loan, 00:25 and the interest rate on any reinvested funds, and then also the cash flows themselves. 00:30 So in this worksheet I have the interest rate on the initial loan. So let's 00:35 say that you have a construction project and you borrow this money for use in that project. 00:40 And then let's say that you are getting money back on your investment and the 00:45 interest rate on the reinvested funds is 5%. 00:48 So in other words, you're borrowing \$150,000 and then you're paying 9% per year 00:53 on that and then all the moneys you get back, which are in cells B7 to B13, 00:59 you're receiving interest on. 01:01 So let's go ahead and create our formula in cell C16. 01:04 Now type =mirr( and then we have the values, which are in cells B6 to B13. 01:14 And note that the value in cell B6 is in parentheses. 01:19 That's because it is a negative number because it's an outflow from your account. 01:23 Money that you no longer have use of. 01:24 So we have B6 to B13, then a comma, and then we have the finance rate, which is 01:32 the interest rate on the loan that is in cell C2, and then we have the interest 01:37 rate on reinvested funds, which is in cell C3. 01:41 Type a right parenthesis. 01:43 Check to make sure everything looks right. It does. 01:45 Then press the Tab key and we see that the internal rate of return on this 01:48 investment is about 6.5%. 01:51 With that information you can evaluate this investment against others such as 01:55 government bonds and if you get a higher rate of return than you would on a 01:58 guaranteed investment you should take the plunge. Collapse this transcript
DISC: Calculating the discount rate of a security
 00:00 When you evaluate an investment, you will know such things as settlement date, 00:04 the maturity date, the amount you have to invest, and the amount you get back. 00:08 What you might not know is the investment's discount rate. 00:12 And the discount rate is the rate of a guaranteed investment such as a T-Bill. 00:17 So in other words what is the discount rate that the creators of this investment 00:21 assumed when they created it? 00:23 In Excel you can use the DISC function to discover the discount rate of a security. 00:30 The DISC function has the following five arguments. 00:33 the first is the Settlement date and that is the date that you buy into the investment. 00:38 Next is the maturity date. That is the date that you get the proceeds from the investment. 00:44 Then we have the price per \$100, just the amount it takes to buy in. 00:49 Redemption value per 100, that's the amount you get back at the end of the 00:52 investment on the maturity date. 00:54 And then the basis and a basis is how interest is calculated based on the length 00:59 of months and years. 01:00 In the US the standard is to use 30-day months and that means it is a 01:05 360-day year but you can also have actual where it counts the actual days in a given year. 01:11 And Europe has its own method as well. 01:13 There are five different methods that you can choose from and when you're 01:16 creating the formula those options pop up. 01:18 But in this case we're going to use the standard North American basis and that 01:23 is option 0, which is also the default if you leave that argument blank. All right! 01:27 Let's go ahead and create the function. 01:29 We are in cell C10, type in equal sign, and then disc. 01:35 Then a left parenthesis and we can start adding the arguments. 01:38 Settlement is the settlement date. That's in cell C3, type a comma. 01:42 Maturity refers to the maturity date. That's in C4, comma. 01:46 PR is the price for 100. That's in C5, type a comma. 01:51 The redemption value is in C6 and the basis and there is a list of items and the 01:56 formula autocomplete list. 01:57 But in this case we have our basis in a cell so I'll type in the address, which is C7. 02:02 Then I'll type a right parenthesis to close up the functions argument list and 02:06 press Tab so I don't scroll down. 02:08 And I see that the assumed discount rate for this bond is 6.017%. 02:14 Bond prices are usually given as \$100 price and then the redemption value is 02:20 based on that \$100 purchase. 02:22 So I'll just switch the arguments here, so we have 100 for the price per 100, 02:27 which is typical, and then redemption value per \$100. 02:32 So before we had about a 6% discount rate and when I changed it to 100 and 106 02:37 you see that the discount rate goes down to 5.67%. 02:41 So even though there was a \$6 spread between the price per 100 and redemption 02:45 value per 100, changing the price from 94 to 100 and redemption value from 100 02:51 to 106 made a substantial difference in the discount rate. 02:55 The DISC function lets you find an investment's discount rate when you know the 02:58 investments term, initial cost and return. 03:01 You can then compare that discount rate to the return from other investments to 03:04 evaluate your options. Collapse this transcript
4. Calculating Bond Coupon Dates and Security Durations
COUPDAYBS: Calculating total days between coupon beginning and settlement
 00:00 Bonds that pay interest prior to maturity are know as coupon bonds. 00:04 When you purchase a bond you should know how far into a coupon period you are 00:08 buying the bond so you can tell how long enough you'll have to wait to receive your 00:11 first interest payment. 00:12 You can calculate the number of days between the coupon beginning and settlement 00:16 date using the coupon days between beginning settlement function, which is 00:21 abbreviated COUPDAYBS. 00:22 So to create the formula you need to now the following are four values. 00:29 The first is the settlement date. That is the date that you take possession of 00:34 the investment or you basically by in. 00:36 Then you have the maturity date, which is the date that proceeds from the 00:40 investment are due to you. 00:41 Then you have coupon frequency. 00:44 Now I mentioned that coupons are payments for a bond. 00:47 Coupons can occur within Excel either once a year, twice a year, or four times a year. 00:54 So annually, semiannually or quarterly. 00:57 And then finally we have the basis and the basis is the method that you use for counting days. 01:02 In North America typically we use a 30- day month and with 12 months that means 01:08 that we have at least in financial terms a 360-day year. That is option 0. 01:13 In this case I'm using option 1 which is actual. 01:16 That means you count the actual days. 01:18 So 28 days in February, 31 days in January, and so on. 01:22 And with those arguments in place we can create our formula. 01:26 So I'll click in cell C9, type in equal sign and then type coupdaybs, left parenthesis. 01:35 And then the cell addresses for our arguments. 01:37 So we have settlement date in C3, type a comma. 01:40 Maturity date in C4, type a comma. 01:42 Coupon frequency C5, type a comma, and then the basis and basis is in cell C6. 01:49 And in the formula autocomplete list you see the other options that 01:52 are available to you. 01:53 So with all those arguments in place I'll type right parenthesis to close out 01:57 the formula and press Enter. 02:00 When I do, we see that the days between beginning and settlement is 135 and the 02:06 reason that the days between beginning and settlement are 135 it's because we 02:11 have 31 days in January, 28 in February, 31 of March, 30 of April, and then the 02:17 additional 15 in May which lead up to the settlement date. 02:20 If I were to change the maturity date from the 12/31/2012 to 09/30/2012, so I 02:26 just type 9/30/2012 and press Enter, we see that the number of days changes to 45. 02:34 In this case the coupon period now starts on April 1 based on our 02:38 semiannual coupon frequency. 02:40 So that means that there are 30 days in April and the 15 days in May leading 02:44 to the total of 45. 02:47 Calculating the number of days between the coupons periods beginning and 02:50 settlement date gives you valuable information on how soon you'll start seeing 02:54 your return on your investment. 02:55 Depending on your goals and your need for cash flow you should consider buying 02:59 bonds with staggered coupons dates and schedules. Collapse this transcript
COUPDAYS: Calculating days in the settlement date's coupon period
 00:00 When you invest in a coupon bond, that is a bond that pays interest before 00:04 maturity, you can evaluate your investment more accurately if you know the 00:08 number of days in the coupon period. 00:10 To find that value you can use the COUPDAYS function, which is short for coupon days. 00:17 To make that calculation you need to know four things. 00:20 The first argument for the function is the settlement date. That is the date that 00:25 you take possession of the investment or basically when you buy it. 00:29 Then we have the maturity date and that is the date that the bond investment ends. 00:34 Then we have the coupon frequency and that is the number of interest 00:38 payments you get per year. 00:39 So for the coupon frequency of 1 it's annual. You get one per year. 00:43 The other possible values are 2, which is every six months or semiannual, and 00:48 then 4, which is quarter. 00:50 Any value other than the 1, 2 or 4 in that slot will generate a formula error. 00:55 And then finally we have the basis and basis is how you count the days in the year. 01:00 In North America the standard is to use a 30-day month and 12 months for 360-day year. 01:06 However, in this case we're going to be using basis number 1 which is actual. 01:11 so that means in January we have 31, on most Februarys we have 28 days, 01:16 leap years 29, and so on. 01:18 So with all these arguments in place we can create our formula. 01:21 So I click in cell C9, type equal and then our function is coupdays, then a left 01:29 parenthesis, and I'll start filling in the cell references. 01:32 Settlement is in cell C3, then a comma, maturity date in C4, then a comma, then we have our coupon frequency 01:40 which is C5, then a comma, and the Basis which is in C6. Type a right parenthesis. 01:46 Just verify everything looks right. 01:48 It does, press Enter, and we see that we have 365 days in the coupon settlement period. 01:53 And that's because there are 365 days in the year 2011. 01:58 So just to show the point of what happens if you're operating during a leap year 02:03 I'll change the dettlement date from 5/ 15/2011 to 5/15/2012 and then press Enter. 02:10 And when we do we see that the formula as expected returns a result of 02:14 366 because 2012 is a leap year and that means that there is an extra day in February. 02:20 One other thing you can do is change the basis. 02:23 In North America the standard basis as I said is to use a 30 day month and 12 02:28 months for 360 day year, so if I would have to change the basis from 1 to 0 and 02:33 press Enter, the days in the coupon settlement period change to 360. 02:37 Knowing the number of days in a coupon period helps to evaluate your investment. 02:41 Always be sure that you're using the correct basis. 02:43 If you're not, the formula could give you an unintended result. Collapse this transcript
COUPDAYSNC: Calculating days from the settlement date to the next coupon date
 00:00 Coupon bonds pay interest at regular intervals, either 1, 2 or 4 times a year. 00:05 In Excel, you can use the COUPDAYSNC function to calculate the number of days 00:12 from the settlement date to the next coupon date. 00:14 To do that you need to know 4 items. 00:17 The first item is the settlement date and that is the date that you buy into the investment. 00:22 The next is the maturity date and the maturity date is the date that the bond 00:27 ends, in other words that you stop making interest off of it. 00:31 Then we have the coupon frequency and that is the number of coupons per year. 00:35 And as I mentioned earlier, that can either be one, which means annual payments, 00:39 two, which means semi-annual payments, or four, which is quarterly payments. 00:45 And then you have the basis. 00:47 Basis is how you count the days in a month and a year. 00:50 In North America the standard is for a 30-day month and with 12 months a 360 day 00:56 a year. That's option 0. 00:58 In this case I'm using a basis of 1, which is actual. That means that it counts 01:02 the number of days that are actually in a month and a year. 01:05 So in a non-leap year you'd have 31 days in January, 28 in February and so on. 01:11 In a leap year, you'd have 31 in January again, but then 29 in February. 01:16 So in this case, I'm assuming that we're counting based on actual days. 01:20 So now I click in cell C9 and create my formula. 01:23 So I type an equal sign and then again the function we're using is 01:28 coupdaysnc, then a left parenthesis, and we can start filling in the cells 01:35 that have our arguments. 01:36 So the settlement date is in C3, then a comma. 01:39 Maturity date is C4, comma. 01:42 Then the coupon frequency is in C5, then a comma, and our basis is in C6. 01:48 And you can see a list in the formula auto complete list of the other basis that you can use. 01:54 If you leave this argument blank then it uses 0, which is the 30 day month and 01:58 360 day standard in North America. 02:01 So I will type a right parenthesis to close out the formula and just a quick check. 02:05 Make sure everything looks right. 02:06 It does, then I press Enter. 02:08 And when I do, I see the based on a settlement date of 3/15/2011 the next coupon 02:14 payment will occur in 291 days and because those coupons are coming annually 02:19 that means that would be 12/31/2011. 02:23 So now let's see what happens when I change the coupon frequency. 02:26 Let's go to semi-annual. So I'll type in a 2, which means that the payment will 02:30 occur on 1/30/2011, and we see that it's 107 days to that coupon. 02:36 If we're to get quarterly, which means it would come at the end of March, so 02:40 we changed it to 4, press Return and we see that we have 16 days to the next coupon. 02:45 Again that happens on March 31 and we can see that with the settlement date of 02:49 March 15 that value is correct. 02:51 When you own a coupon bond you'll receive a payment every time a coupon comes due. 02:55 If you know how many days it will be before you get paid, you can manage your 02:58 cash flow more effectively. Collapse this transcript
COUPNCD: Calculating the next coupon date after the settlement date
 00:00 Coupon bonds are bonds that pay interest prior to maturity and those interest 00:04 payments are paid on a regular schedule which can occur either one, two or four times a year. 00:10 After you take possession of the bond, you can calculate the calendar date of 00:13 your first coupon payment by using the COUP and the CD function, and that 00:19 function name is short for coupon next coupon date. 00:22 So, to calculate the date the next coupon payment is due, you can use the 00:27 following four arguments and the first value you need to know is the settlement 00:32 date and that's just the date that you buy into the investment. 00:34 The next argument is the maturity date and that is the date that the 00:38 bond investment ends. 00:39 So, that would probably be your last coupon payment and any other money due to you. 00:45 Then we have the coupon frequency and that is the number of coupon payments for year. 00:50 And again, for this function that can be either one, two, or four and then 00:54 you have the basis. 00:55 And basis is how you calculate the number of days in a month and a year. 01:00 The default in North America, which is option zero, is for a 30 day month and a 360 day year. 01:07 In this case, we are using basis 1, which is actual so that means you count the 01:11 number of days in January, the number of days in February, 28 in a regular year, 01:16 29 in a leap year and so on. 01:19 With that information in place, we can create our formula to calculate the date 01:24 the next coupon is due. 01:26 So type =coupncd( and then the settlement is in c3, the maturity date is in 01:37 c4, then type a comma, the coupon frequency is in c5, then a comma, and the basis is in c6. 01:46 And you can see the other bases that are available to you. 01:49 As I said 0 is the standard used in North America for a 30 day month and a 01:54 360 day year and if you leave the basis argument blank then that's the one that Excel uses. 01:59 So, to close out the formula, I will type a right parenthesis. 02:04 Everything looks good, press Enter, and we see that the date of the next coupon 02:07 is due is December 31 of 2011. 02:11 And I will just change the coupon frequency to show you how the dates change. 02:15 So, let's say that we go to a semiannual coupon. 02:17 For that we change the frequency in cell C5 to 2 and the date the next coupon is 02:23 due changes to June 30th of 2011. 02:26 If we were to go quarterly by typing in a 4 then the date the next coupon is due 02:31 would be at the end of the first quarter, which is March 31. 02:34 When you are on a coupon bond, you'll receive a payment every time the coupon comes due. 02:38 Calculating the date your first payment is due will let you manage your cash 02:41 flow more effectively. Collapse this transcript
COUPNUM: Calculating the number of coupons between settlement and maturity
 00:00 A coupon bond is a bond that pays interest before maturity. 00:04 Once you own the bond, you can calculate the number of coupon payments you will 00:07 receive over the life of the bond by using the COUPNUM function. 00:13 To use that function, you need to know the following four things. 00:16 The first is the settlement date and that is the date that you buy into the 00:20 investment and take possession of the bond. 00:22 Then next you have the maturity date and that is the date that the investment ends, 00:27 that you receive your last coupon payment and any other money due to you. 00:31 Then next you have coupon frequency and in Excel that means it can either be 00:36 annual which is one coupon per year, this is an annual frequency, then two, 00:42 which is semiannual, or four which is quarterly. 00:45 Then the next argument is the basis and this is the way that you count the 00:49 number of days in a month and a year. 00:51 In North America, the standard is for a 30 day month leading to a 360 day year. That is option 0. 00:59 In this case, I am using option 1, which is actual and that means you count 01:03 the actual number of days in the year instead of abstracting it to a 30 day 01:07 month and a 360 day year. 01:09 So, in non-leap years, February will have 28 days and in a leap year it will have 29. 01:14 So, now let's go ahead and click in cell C9 and create our formula. 01:20 Now, I will type an equal sign and then our function is COUPNUM( and then start 01:27 filling in the arguments. 01:28 So, our settlement date is in C3, comma, maturity is in C4, comma, coupon frequency C5, comma, and 01:37 then the basis is in cell C6. 01:41 Now, basis is an optional argument. 01:43 If you don't leave it in then Excel uses the North American standard which is a 01:47 30 day month and a 360 day year. 01:50 And I will type a right parentheses to close out the formula. 01:53 Make sure all the references look good and press Enter. 01:56 So, when I do, I see that the number of coupons to be paid between the time that 02:00 I buy into the investment which is in February of 2011. 02:04 I will receive four coupons over the next four years and just from looking at 02:10 the information, I can see that those will be paid on December 31 of 2011, 02:14 2012, 2013 and 2014. 02:16 So, now let's change the coupon frequency. 02:19 If I were to go to semiannual, and then let's change the value to 2, 02:23 see that the number of coupons is 8. 02:25 And if I go back to cell C5 and type a 4 and press Enter, see that the number of 02:31 coupons to be paid is 16. 02:33 And if I were to change the settlement date to something after the end of the 02:37 first quarter, let's do 7/ 14/2011 and press Enter, 02:42 see that the number of coupons to be paid has been reduced by two because I 02:46 missed the first two quarterly payments in the year 2011. 02:50 When you own a coupon bond, you will receive a payment every time a 02:53 coupon comes due. Collapse this transcript
COUPPCD: Calculating the date of a coupon due immediately before settlement
 00:00 A coupon bond is a bond that pays interest before the bond matures. 00:05 That interest is paid on a regular schedule and in Excel that can be either 00:10 annually, which is one-time a year, semiannually for two times a year, or 00:14 quarterly, which is four times a year. 00:16 If you're thinking about investing in a coupon bond before its initiation date, 00:20 that is the first day that the bond is available, then you can calculate the 00:24 calendar date of the most recent coupon payment by using the COUPPCD function. 00:30 To make that calculation, you need to know the following information. 00:33 The first is the settlement date and that is the date that you take 00:37 possession of the bond. 00:38 Then next you have the maturity date and that is the date that the bond 00:43 investment ends and you'll receive your final coupon payment and any other money due to you. 00:48 Then you have the coupon frequency and again that can be annual, which once 00:52 a year, semiannual which is twice, and quarterly which is four times a year and then basis. 00:59 Basis is the way that you calculate the number of days in a month and a year. 01:03 In North America, the standard is to assume the 30 day month, which means a 360 day year. 01:09 That would be option 0 and that's the default if you leave this argument blank. 01:13 In this case, I'm going to use the actual days. That is option 1, and actual 01:17 counts as the name indicates the actual number of days in a month. 01:21 So for example, January would have 31 and in a regular year, February 28 and 01:27 then in a leap year February would have 29 days. 01:30 So, with that information we can create our function. 01:33 So, I will click in cell C9, type an equal sign, and then the function again is 01:39 COUPPCD for coupon previous coupon date, then a left parenthesis, and start 01:46 filling in the arguments. 01:47 Settlement is the settlement date that's in C3, maturity is the maturity date 01:52 that's in C4, then coupon frequency is in C5 and then the basis is in C6. Type a 02:01 right parenthesis. Make sure all our references look good. 02:04 They do and press Enter. 02:06 So, based on a settlement date of September 13, 2011, we see that the previous 02:11 coupon date would have been December 31 of 2010 and the reason that occurs is 02:16 because the coupon frequency is 1 or annual and the maturity date, which marks 02:21 the end of the year of a bond investment, is December 31. 02:25 Now if I change the coupon frequency to 2 for semiannual and press Enter, we see 02:31 that the previous coupon date changes to June 30th. 02:34 And if I were to change the frequency to 4, then nothing should change because 02:38 the previous coupon date would have been June 30th. 02:40 But if we change the settlement date to 10/13, a date in quarter four and press 02:48 Enter, then the previous coupon would've been paid in September of 2011. 02:53 When you own a coupon bond, you will receive a payment every time a coupon comes due. 02:57 If you'd like to know when the most recent coupon payment occurred, you can use 03:01 the COUPPCD function. Collapse this transcript
DURATION: Calculating the annual duration of a security
 00:00 Comparing coupon bonds with different investment periods can be difficult. 00:04 However, there is a method that you can use to calculate a common value that 00:08 let's you evaluate a bond based on its sensitivity to changes in its yield. 00:12 In Excel, you implement that method using the DURATION function. 00:16 To calculate duration, you need to know the following six things. 00:20 The first of those is the settlement date and that is the date that you purchase 00:25 and gain control of the investment. 00:26 Second is the maturity date and that is the date that the bond investment ends 00:32 and you are paid any proceeds due to you. 00:34 Then next is the coupon interest. 00:37 A coupon bond pays interest prior to the bond's maturity date. 00:41 So, you can receive interest payments per coupon either annually, which 00:46 is once a year, semiannually, which is twice a year, or quarterly, which is four times a year. 00:52 Next is the yield and the yield is the interest on the bond itself. 00:56 So, as distinct from the coupon interest, the yield is the interest that is used 01:02 to calculate the final value of the bond at its maturity. 01:05 Then next you have frequency and frequency is the number of coupons per 01:09 year, again 1, 2 or 4. 01:11 And then finally we have basis and basis refers to the way that you calculate 01:17 the days in a month and days in the year. 01:19 If we were to set this value to 0 or leave it blank, the default is to use 30 01:25 day months which lead to a 360 day year. 01:28 In this case, we are going to use option 1 and that is actual days. 01:32 So, in a non-leap year, February will have 28 days while in a leap year it 01:37 would have 29 and the year length would be 365 and 366 respectively. 01:43 With that information in hand, we can now calculate the duration and that would 01:46 be equal and I am typing in cell C11, so that would be equal =duration( and then 01:53 we can start filling in the cell references for our arguments. 01:57 So, I have a settlement in C3. 01:59 That's the settlement date, then a comma, maturity date is C4, comma, comma, oupon interest 02:05 is in C5, comma, the yield is C6, comma, frequency is again the number of coupons per year, 02:12 that's in C7, then a comma, and the basis is in C8. 02:17 Type a right parentheses and make sure all of our references look good. They do. 02:21 So, I'll press Enter and we see that the bond has a duration of 1.74. 02:26 Now that value doesn't mean much unless we have something to compare it to. 02:30 So, remembering the value 1.74, I'm going to change the maturity date from 02:35 12/31/2012 to 21/31/2041. 02:40 So, I will make it a 30 year investment. 02:42 So, for that, 12/31/2041 and press Enter. 02:47 When I do, you see that the duration changes to 14.3. Now, let's change the yield 02:52 with the same settlement date and maturity date to 8%. 02:55 So, remembering the duration is 14.3, if we change the yield in cell C6 to 8% 03:02 and press Enter, you see that the duration actually goes down to 13.2. When you 03:08 make a long-term investment in a coupon bond, you will find at the yield in 03:12 essence determines the value of the bond. 03:14 You can use the DURATION function to discover how sensitive your investment is 03:18 to changes in its yield. Collapse this transcript
MDURATION: Calculating the duration of a security using the modified Macauley method
 00:00 In the previous movie, I showed you how to evaluate a bond by using the DURATION function. 00:05 Another way to evaluate a bond is to use the Modified Duration or 00:09 MDURATION function. 00:10 The MDURATION function is more conservative than the straight DURATION 00:13 calculation because it is less sensitive to changes in yield. 00:17 To use the MDURATION function, you need to know the exact same information that 00:21 you do for the DURATION function. 00:24 So, you start with the settlement date and that is the date that you take 00:28 control of the investment. 00:29 Then you have the maturity date and that is the date that the investment comes 00:34 to an end and you're paid all proceeds due to you. 00:37 Next is the coupon interest argument and that value represents the amount of 00:42 interest you get per coupon. 00:44 Then you have yield, which is the annual percentage yield for the investment, 00:47 the amount that increases per year, then the frequency and this is the number of 00:52 coupons that you receive per year. 00:54 That value can either be 1 for annual coupons, 2 for semiannual, or 4 for quarterly. 01:00 And then finally there is basis. 01:03 Basis refers to how you calculate the number of days in a month and in year. 01:07 Option 0, which is the default and is the North American standard, assumes a 30 01:11 day month for every month and then a 360 day year. 01:16 In this case, we are going to use and basis 1 and that is actual. 01:19 So, in the leap year you'll have 366 days and in a regular year 365. 01:24 So, now let's go ahead with that information and create our formula. 01:29 So, I'll click in cell C10, type =mduration, then a left parentheses and I can 01:38 start adding the cell references. 01:39 The first is the settlement date. 01:41 That's in cell c3, comma, maturity date is in c4, the comma, coupon is in c5, comma, the yield is 01:50 c6, comma, frequency c7, comma, and the basis is in c8, and then a right parenthesis to close out the entry. 02:01 Check to make sure all my references are right. 02:02 Everything seems to line up so I will press Enter. 02:05 And see that the modified duration is 1.64. 02:09 Now, just to see how the duration changes as the parameters of the investment 02:13 change, I'm going to change the yield to 12%. So I am clicking cell C6, type 02:19 12 and press Enter. 02:21 When we do, the modified duration goes down to 1.57, so it's a very small change. 02:27 Now, I'm going to press Ctrl+Z to undo that change and I'm going to change the 02:32 maturity date to 12/31/2041 so to make it a much longer investment. 02:38 So, I will edit the value in cell C4 so it reads 12/31/2041 and press Enter. 02:46 And when I do, we see that the modified duration goes up quite a bit to 13.73. 02:51 Now, if I were to change the Yield to 10% and press Enter, so I just change the 02:56 value in C6 to 10%, press Enter, we see that the duration goes down to 10.65. 03:03 When you evaluate coupon bonds, you should strongly consider using both the 03:06 DURATION and MDURATION functions to measure the investment using multiple tools. 03:11 Be sure though that you're comparing results of the same function together and 03:16 don't cross them, because they use two different methods. Collapse this transcript
5. Calculating Security Prices and Yields
DOLLARDE and DOLLARFR: Converting between fractional prices and decimal prices
 00:00 When you listen to a financial report, you might hear a price such as 1 and 5/16 or 1.3125. 00:07 Those two statements actually represent the same value. 00:10 The trick is to convert between decimal notation and fractional notation. 00:14 In Excel, you can do that by using the DOLLARDE and DOLLARFR functions. 00:20 You read a fractional dollar amount by putting the part to the right of 00:23 the decimal point of the dollar value over a fraction which has to be named separately. 00:28 So, for example the value that I have in C4 is 1.05. 00:33 So, that's 1 and 5 something and then in cell C5, we see that we have the value 16. 00:40 So, we are looking for the value 1 and 5/16. 00:43 Now, if we want to convert that value to decimal, all we need to do is create a 00:47 formula using the DOLLARDE function. 00:50 So, I have clicked in cell C7, then I will type an equal sign, and then type 00:54 DOLLARDE( and then we have our fractional dollar, which is in cell c4, a roma, and then 01:01 the fraction and in fact this is the denominator, the number that goes under the 01:05 line when you write a fraction. That's in cell c5. 01:08 Type a right parenthesis. 01:09 Everything is good and press Enter and we see that the decimal value is 1.3125. 01:15 Now, let's do the same thing in reverse, converting a decimal number to 01:19 a fractional number. 01:20 So, here we have our decimal number of 1.375 and the fraction we will have 01:25 the denominator of 16. 01:27 So, I will click in cell C15, type equal, and then it's DOLLARFR( and then 01:34 the first argument is the decimal dollar value, that's in c12, comma, and then the 01:39 fraction which again is the denominator, the number underneath the line when 01:44 you write a fraction. 01:45 That's in cell c13. 01:47 Type a right parenthesis to close out the function and then press Tab so I don't scroll down. 01:52 And we see that the fractional value is 1.06 and because we know that the 01:57 fraction is 16 that means it's 1 and 6/16 or 1 and 3/8. Now to demonstrate what happens 02:05 when you change the fraction value, let's click in cell C13 and change the value 02:10 there from 16 to 32. 02:12 When we do and press Enter, we see that the value in C15 changes to 1.12. 02:18 So, when we doubled the value in cell C13, the value to the right of the decimal 02:23 point in C15 doubled as well. 02:26 It went from 1.06 to 1.12. 02:29 So, we are looking at a value of 1 and 12/32 which also reduces down to 1 and 3/8. Collapse this transcript
INTRATE: Calculating the interest rate of a fully invested security
 00:00 Excel has many functions that let you evaluate your investments. 00:03 If you buy a bond at a set price and receive a payment when the instrument 00:07 matures, you are said to be fully invested. 00:10 If the bonds or prospectus doesn't list the investment's annual interest rate, 00:14 you can find it by using the INTRATE function. 00:17 To use the INTRATE function, you need to know the following five things. 00:20 The first is the settlement date and that is the date that you take control of the investment. 00:26 Then next is the maturity date and that is the last date of the bond and that is 00:31 when all interests and other payments are due to you. 00:34 Next is the investment and that's simply the amount that you pay for the bond. 00:38 The redemption value is the amount that you get at the maturity date and the 00:43 basis is how you calculate the number of days in a month and a year. 00:47 The default value for North America, which is what we are using here, is 0 and 00:52 that means that you work with 30 day months and a 360 day year. 00:57 There are other values that are available depending upon the basis that you're 01:00 using and I'll show those to you when we create the formula. Let's do that now. 01:05 I will click in cell C11, type equal, and we're going to use the INTRATE 01:09 function, so it's intrate(. 01:14 Now, we can fill in the cell references for our arguments. 01:17 The first is the settlement date that's in cell c4, comma, the maturity date is in c5, comma, 01:24 the investment, the amount we put it is in c6, redemption value, the amount we 01:28 get at the end is in c7, type a comma, and then we have the basis. 01:33 So, the basis we are going to use is 0. 01:35 That's in cell C8, but we could also use actual, which would be 1, actual with 01:40 a 360 day year, actual with a 365 a year, or the European standard which is also 01:46 30 day months and a 360 day year, but durations are calculated a little bit differently. 01:52 So, you will have to be told which one you're going to use by your financial advisor. 01:55 When in doubt, use option 0 which is the default for North America. 01:58 So, the basis is in cell c8. Type a right parenthesis to close up the function. 02:05 It looks good and I'll press Tab so I don't scroll down and we see that the 02:09 Interest rate for this bond is 6.87%. 02:13 Now, just to show you how this calculation will change, I'm going to change the 02:16 redemption value from 126,500 to 150,000 and press Enter. 02:22 When I do, you see that we have an annual interest rate of 10%. 02:26 One of the interesting ways that you can use the INTRATE function is to find out 02:30 what interest rate you would need for your investment to double in value given a 02:35 settlement date and a maturity date. 02:37 And in this case, with June 1st 2011 and ending on May 30th of 2021, you would 02:44 need 10%, approximately, interest rate to double the value of your investment. Collapse this transcript
RECEIVED: Calculating the value at maturity of a fully invested security
 00:00 In the previous movie, I showed you how to calculate the interest rate behind an investment. 00:05 In this movie I'll show you how to discover the amount you'll receive when you 00:08 know the length of the investment and its interest rate. 00:11 To make that calculation you will use the RECEIVED function. 00:15 For the RECEIVED function you need to know five things. 00:18 The first is the settlement date, which I have in cell C4 and that is the date 00:23 that you gain control of the investment. 00:25 Next is the maturity date and the maturity date is the date that final payment 00:30 is due to you and the investment ends. 00:32 Then you have the investment. That's the amount you pay to get into the investment. 00:37 Next you have the discount rate in C7 and the discount rate is the interest 00:42 rate on the investment. 00:43 Then next in cell C8 we have the basis, and basis is the way that you calculate 00:49 days in a month and a year. 00:51 We have it set value 0 which is the default and that is the North American 00:54 standard which uses 30 day months and a 360 day a year. 00:59 There are other ways to count including actual which is option number 1 and that 01:03 has all of the days in the year and all of the days in a month. 01:07 So in a regular year you would have 365 days with 28 days in February and then 01:13 in a leap year you would have 366 days with 29 days in February. 01:18 But in this case, we'll go with the default and stay with option 0. 01:22 So now I am going to click in cell C11 to create our formula to find the 01:28 full value at maturity. 01:30 So I'll type equal and then we are going to use the RECEIVED function, 01:34 received(, and then we can start typing in the cell references for the arguments. 01:42 So we have C4 for the settlement date, C5 for the maturity date, then type a 01:47 comma, the investment value is in cell C6, comma, 01:52 discount rate C7, comma, and then basis is in C8 and that's our last argument. 01:58 So type a right parenthesis to close out. 02:01 And then I'll press Tab so I don't scroll down. 02:04 So given this investment and a discount rate of 4.825% we see that the received 02:10 value is \$144,890. So it's just under 145,000. 02:16 So just to see how this investment value would change if you had a higher 02:21 interest rate we will change 4.825% in cell C7 to 5 and a quarter or 5.25%. 02:28 So if that change in place, press Enter and we see that the received value goes 02:33 up by almost \$13,000, which is quite a substantial improvement. 02:37 So when you want to find the amount you receive from an investment or you know 02:41 the term, starting price, and discount rate, use the RECEIVED function. Collapse this transcript
PRICE: Calculating the price of a security that pays periodic interest
 00:00 If your company needs to raise some cash and has determined that issuing stock 00:04 isn't in its best interests, you might borrow money by issuing bonds. 00:08 Pricing bonds for sale is a very 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 your bond holders. 00:17 Once you know the parameters of the bond you'd like to issue you can use the 00:20 PRICE function to find the break even issue price. 00:23 The PRICE function has seven arguments and those are settlement date here in B3, 00:28 maturity date in B4, percent coupon in B5, yield in B6, redemption value B7, 00:35 frequency in B8 and basis in B9. 00:39 The settlement date is the date that you gain ownership of the security. 00:43 That date might be different from the bond's issuance date, which is the date the 00:47 bond is made available for sale. 00:49 The maturity date is the date the bond will be paid off. 00:52 The percent coupon is the bonds interest rate that is used to determine how much 00:56 money is paid to an investor every time a coupon is due. 01:00 The yield is the bond's annual yield, in other words the amount of interest that 01:04 humiliates during a year. 01:06 The redemption value is the bond's redemption value per \$100 of face value and in 01:11 almost every case that will be \$100. 01:13 If it's not, your financial advisor or investment strategist will tell you that it's not. 01:18 Next frequency is the number of coupons that are paid every year. 01:22 So the value in B8 can be either 1, 2 or 4, which means that coupons will either 01:28 be paid annually, semi-annually, or quarterly. 01:31 And finally, basis reflects the way that you count the number of days in a month and a year. 01:36 So basis is 0, which is the default and is what we're using here, assumes a 30 01:41 day month and a 360 day year. 01:44 Other options include option number 1, which is actual which uses a 365 day year 01:49 with 28 days in February for a non- leap year and 366 days with 29 days in 01:57 February for a leap year. 01:59 So with all that information in place let's go ahead and create our function. 02:03 So I'll click in cell B11, type an equal sign, and then type price,left 02:09 parenthesis and then we can fill in the cell references for our values. 02:14 First is settlement date that's in B3, comma. 02:17 Maturity date is in B4, comma. 02:20 The rates is the percent coupon and that is in cell B5, comma. 02:25 The yield is B6, comma. 02:28 Redemption value is B7, comma. 02:31 The frequency and again it can either be 1, 2 or 4. That's in cell, B8 comma, and 02:37 then the basis, and again that's how you count days and the month and the year, is in cell B9. 02:42 Type a right parenthesis. 02:44 Make sure all my references look good. They do and I'll press the Enter key 02:48 to enter the formula. 02:50 And when I do we see that the price is \$90.36. 02:55 So that means that to break even on this investment you would need to sell 02:58 it for \$90.36 cents. 03:01 Like the YEILD function, the PRICE function assumes bondholders always reinvest 03:06 their interests and that the bonds interest rate never changes. 03:09 Those assumptions rarely hold completely true but the PRICE function offers a 03:13 first look at what you should charge your bond holders with the goal of making a 03:17 profit on the transaction. Collapse this transcript
PRICEDISC: Calculating the price of a discounted security
 00:00 When you evaluate a bond, either one that you're creating or one that you want to 00:04 buy, you need to evaluate its price. 00:07 If you know the start and end date the interest you'll pay and the redemption 00:11 value, you can use the PRICEDISC function to calculate the bonds price. 00:16 The information you need to know appears here in the sample workbook and cell 00:21 C3 is the settlement date and that is the date that you take possession of the bond. 00:26 Then the maturity date, that's the last date of the bonds life. 00:30 That is the day it matures. 00:32 Then the discount rate and that is the interest rate applied to the investment. 00:36 Then we have the redemption value and that's usually set to \$100, but in this 00:41 case there is no default value so we set it to 100. And then in cell C7 we have basis. 00:47 The basis is how you count the days in a month and a year. 00:51 So in this case we have option number 1 and that is actual. 00:55 That means that you count the actual number of days instead of using some 00:59 sort of an abstraction. 01:00 So in a non-leap year you will have 365 days which include 28 days in February, 01:06 whereas in a leap year you would have 366 days with 29 days in February. 01:12 The default value for the basis argument is 0 and that is 30 day months for a 360 day year. 01:19 That is the North American standard but in this case we are going to calculate 01:23 the actual days so that means we use basis number 1. 01:26 So we can create a formula by clicking in cell C10, typing an equal sign, and 01:32 then pricedisc, then a left parenthesis and we can fill in the cell references for the arguments. 01:41 We have the settlement date in C3, comma. 01:44 Maturity date in C4, comma. 01:46 The discount rate is in cell C5, comma. 01:49 The redemption value is in C6, comma, and the basis is in C7 and that's our last 01:57 argument so we type a right parentheses. 02:00 Make sure all the references look right, they do, and press Tab. 02:05 Pressing Tab completes the formula and we see the price per \$100 for a bond with 02:10 these terms should be \$83.95. 02:13 One other change I'll make just to show you how the formula would return a 02:17 different value if we change the discount rate. 02:19 I will increase the discount rate to 4.25%. So I'll click in cell C5, 4.25 and 02:27 it's already formatted as a percentage so you don't need to type percent sign. 02:31 So what we should see because of a higher discount rate is the price per \$100 go 02:35 down and when we press Enter we see that it does to \$81.81. 02:41 So with this information in hand, you can evaluate a discounted security using 02:45 the PRICEDISC function. Collapse this transcript
PRICEMAT: Calculating the price of a security that pays interest at maturity
 00:00 One common type of investment is a bond that pays interest at maturity. 00:04 If you want to evaluate one of these bonds you can calculate its price per \$100 00:08 of redemption value by using the PRICEMAT function. 00:12 The PRICEMAT function requires the following six bits of information. 00:16 Those are the settlement date and that is the date that you take control of the investment. 00:22 The maturity date which is the last day of the investment when all money is due 00:26 are payable, then the issue date and this is the first day that the bond was 00:32 initiated, so it's the day that it was first available for sale. 00:35 Then the discount rate which is the interest rate applied to the bond funds. 00:39 Then the yield which is the amount of the bond pays per year. 00:43 and then finally the basis. 00:45 Basis is the way you count days in a month and in a year. 00:48 The North American standard, which would be option 0, is for a 30 day a month and 00:53 12 months a year for a 360 day year. 00:56 In this case, we are going to use option number 1 which counts actual days. 01:00 So in a non-leap year, you would have 365 days which include 28 days of February 01:05 and in a leap year you would have 366 days with 29 days in February. 01:11 So now with all this information in place you can click in cell C11, then 01:15 type an equal sign, then the function we are going to use is PRICEMAT, so 01:18 pricemat, left parenthesis, and then we can start filling in the cell 01:25 references for the arguments. 01:27 Settlement date is in C3, comma. 01:30 Maturity date C4, comma. 01:32 The issue date C5, comma. 01:34 The rate is in C6, comma. 01:37 The yield is C7, comma, and that the basis is in C8. 01:42 Type a right parenthesis. 01:44 Make sure all the arguments line up. 01:46 They do and press the Tab key. 01:48 Doing so, displays a value of \$92.90, which given these parameters is the fair 01:54 market value of this investment. Collapse this transcript
TBILLEQ: Calculating the bond-equivalent yield for a Treasury bill
 00:00 Treasury bills, which are also called T-bills, are popular investments. 00:04 If you want to compare T-bill's yield to a bond's yield you can do so using 00:09 the TBILLEQ function. 00:11 To use that function you need to know the following three things about the 00:15 T-bill you're evaluating. 00:16 The first is the settlement date and that is the date that you take 00:20 possession of the investment. 00:21 Then you have the maturity date which is the date that the T-Bill payments come 00:27 due, in other words the money due to you is paid to you. 00:30 And then finally you have the discount rate and the discount rate is the 00:34 interest rate applied to the investment. 00:36 So with that information in place you can click in cell C8 and create the formula. 00:40 So I'll type equal tbilleq, left parentheses, and then just fill in the 00:46 cell references for the three arguments. So we have our settlement date in C3, comma, 00:52 maturity date C4, comma, and the discount rate which is in C5. 00:58 Type a right parenthesis just to make sure everything looks right. Everything is good. 01:03 Press Tab key and we get a bond equivalent yield of 4.36%. 01:08 So to interpret these results, that means that if you have the opportunity to 01:12 purchase a T-bill at a rate of 4.25%, you should do so unless you can find a 01:19 bond that would pay 4.36% or more. 01:23 Now one thing that you should know about bills as opposed to bonds is that a 01:27 bill by definition has a life of less than one year. So for example, 01:31 if we were to change the maturity date in cell C4 to 12/31/2012 and press Enter, 01:39 then, the formula in cell C8 would generate a #NUM error 01:43 and that's because we violated the definition of a T-bill by giving it a 01:47 life of more than one year. Collapse this transcript
TBILLPRICE: Calculating the price for a Treasury bill
 00:00 When you evaluate a treasury bill which is usually called a T-bill, you should 00:04 determine the fair market value of the investment. 00:07 In Excel you can use the TBILLPRICE function to find that value. 00:12 To find the price of a T-bill you need know three separate things. 00:15 The first is the settlement date and the settlement date is the date that you 00:21 take possession of the investment. 00:22 Then you have the maturity date and that is the date that the interest plus your 00:27 original principle is due. 00:28 And then finally you have the discount rate which is the annual percentage rate 00:33 assigned to the investment. 00:35 So to find the fair market price of that investment, you can click in cell C8, 00:42 type an equal sign, and then type in the name of the function and that is 00:47 tbillprice, then a left parenthesis. 00:51 And now we can type in the cell references for the arguments. 00:55 So we have settlement date in C3, comma, maturity date C4, comma, and then the discount rate, 01:02 which is in C5, and a right parenthesis to close-up the function. 01:07 Everything looks good. 01:08 And when I press Tab we see that the T-bill price for this investment should be \$90.93. 01:16 Now sometimes it can happen that you evaluate a T-bill and your formula 01:20 displays a NUM error. 01:22 If that's the case you should check your settlement date and your maturity date. 01:25 T-bills have a life of one year or less so you might have entered a date incorrectly. Collapse this transcript
TBILLYIELD: Calculating the yield of a Treasury bill
 00:00 When you evaluate a Treasury bill, you will occasionally know the start date, 00:05 end date, and price but not the yield. 00:07 If you know those first three values you can calculate the yield by using the 00:12 TBILLYIELD function. 00:13 To make that calculation you need to know three things. 00:16 First is the settlement date and that is the date that you take possession of the investment. 00:21 Then the maturity date which is the date that your original principle and any 00:27 accrued interest is due to you. 00:29 And then finally, you have the price. 00:31 So the price in this case is \$97.50 and that is the amount that you need to pay 00:37 to invest in this T-bill. 00:38 And the redemption value is assumed to be \$100. 00:43 That's just an assumption that the function makes. 00:46 So now, we can click in cell C8 and create our formula. 00:50 So to start type an equal sign and then tbillyield, left parentheses, 00:59 and then we can enter in the cell references for these values. 01:02 So settlement date is C3, comma, 01:05 maturity date C4, comma, and the price is in C5. 01:11 Type the right parentheses. Make sure that all of my arguments line up, they do and press Tab. 01:19 When I do, we find out that the T- bill yield for this investment is 10.14%. 01:25 As with the T-bill price function I covered in the last movie, you might find 01:29 your formula displays a NUM error. 01:31 If it does, check your settlement date and maturity date. 01:35 T-bills by definition have a life of one year or less, so you might have 01:39 entered a date incorrectly. Collapse this transcript
YIELD: Calculating the yield of a security that pays periodic interest
 00:00 One straightforward way to calculate the value of a bond is to estimate the 00:04 bond's yield given the investment's conditions. 00:06 Those include the price, the coupon rate, and time to maturity. 00:11 In Excel, you can calculate a bond's yield perhaps not surprisingly by using 00:15 the YIELD function. 00:16 The YIELD function has seven arguments and those are the settlement date which 00:20 is the date that you gain control of the security. 00:23 Then maturity date which is the date that the investment ends and all money is payable to you. 00:29 Then there is the percent coupon argument and that argument gives you the 00:34 interest rate, representing the amount of money that you'll receive for each of 00:38 your coupon payments. 00:39 Next is the price and this is the price per \$100 that you have to pay to gain the security. 00:45 Then you have the redemption value which is the base value that you get in 00:50 return for your \$100 investment. 00:52 Your redemption value is almost always \$100 and the reason that is, is because 00:57 you're receiving coupon interest and the bond's creators basically don't want to pay you twice. 01:03 It's not unheard of for you to have a redemption value that's higher than the 01:06 price, but it is somewhat uncommon. 01:09 Next we have the frequency and that is the number of coupon payments per year. 01:14 It can be 1 which is annual, 2 which is semiannual, or 4 which is quarterly. 01:20 Then finally, we have the basis and basis is how you count the number of days 01:24 in a month and a year. 01:26 Basis number 0, which is what we are using here, is the North American default 01:30 and that assumes a 30 day month and a 360 day year. 01:34 If this value were 1, then we would be counting actual days. 01:38 So a 365 or 360 day year, depending upon when it fell, and also 28 or 29 days in 01:46 February depending upon a leap year versus a non-leap year. 01:50 So with all this information in place we can calculate the yield of this investment. 01:55 So in cell B11 I'll type in equal sign and then yield, 01:58 left parentheses and then I can start filling in the cell references for the arguments. 02:05 So, settlement date is in B3, comma, maturity date B4, comma, the rate B5, and next is the 02:13 price that's in B6, comma, the redemption value is in B7, comma, the frequency B8, comma, and then 02:22 the basis in B9 and a right parenthesis because that is my last argument. 02:28 Now I'll just make sure that all of my references look good. They do and 02:32 I'll press the Tab key. 02:33 When I do, I see that the yield of this bond is 4.2499% or basically 4.25%. 02:41 The YIELD function provides a simplified look at bonds. 02:44 The function assumes the bond interest rates stays constant which almost never 02:48 happens, that you reinvest your bond interest, and that there are no delays in 02:52 getting paid when the bond matures. 02:54 Even so, the YIELD function provides a good first look at a bond's potential value. Collapse this transcript
YIELDDISC: Calculating the annual yield for a discounted security
 00:00 When you're offered the opportunity to invest in a discounted security, you might 00:04 know everything about the investment except for its annual yield. 00:08 If so, you can find that yield by using the YIELDDISC function. 00:13 To use that function and to find the annual yield of an investment, you need to 00:17 know the following five things. 00:18 The first is the settlement date that is the date do you take possession of the investment. 00:23 Next is the maturity date and that is the date that the investment comes to an end. 00:28 Next is the price and that is the amount that you pay per \$100 of face value for the bond. 00:34 Next is the redemption value. That's almost always going to be \$100, but 00:39 it doesn't have to be. 00:41 Then next you have the basis and basis refers to the way that you count days in 00:46 the month and a year. 00:47 Basis 0, which is what we're using here, is the North American standard and it 00:52 says that every month has 30 days and therefore there are 360 days in the year. 00:58 If we were change to basis number 1, then we would have actual days. 01:02 So that would be 365 days in a non-leap year, 366 in a leap year, and then either 01:08 28 or 29 days in February, respectively. 01:12 So with that information in mind, we can calculate the annual yield of this security. 01:17 So click in cell C10, type an equal sign and then type yielddisc 01:27 and a left parentheses so we can start entering in the cell 01:32 references for the arguments. 01:34 First is settlement date that's in C3 comma. 01:37 Maturity date is C4, comma. 01:39 The price and again that's price for \$100 of face value is in C5, comma. 01:45 Redemption value C6 and the basis C7, then a right parentheses. It looks like 01:53 everything is in order in the formula, all the references are good, so I'll 01:57 press Tab and see that the annual yield of this investment is 6.35%. 02:03 Finding a discount security's yield lets you compare it to other potential 02:06 investments, which helps you make sound decisions. Collapse this transcript
YIELDMAT: Calculating the annual yield of a security that pays interest at maturity
 00:00 Some bonds pay interest throughout the bond's life, while others pay interest 00:04 when the bond matures. 00:06 If you received the accumulated interests in one payment at maturity, you can 00:10 use the YIELDMAT function to find the bond's yield. 00:13 To do that you need to know the following six things. 00:15 First is the settlement date, which is the date that you take possession of the investment. 00:20 Next is the maturity date and that is the last day of the investment. 00:25 In other words the day when the principle you've invested plus any interest or 00:29 other monies due will be paid to you. 00:31 Then next is the issue date and this is the date that the security was first 00:36 made available for sale. 00:38 Then there's the rate, which is the discount rate of the investment, the rate at 00:42 which your accumulate interest. 00:43 Then there is the price and price is expressed in terms of price per \$100 a face 00:50 value of the bond and in this case, we've set the price to \$100. 00:54 Then finally, we have basis. 00:56 Basis refers to the way that you count the number of days in a month and a year. 01:00 We've chosen option zero which is the default option. 01:04 Option 0 says that every month has 30 days and that means that there are 01:09 360 days in a year. 01:11 It is artificial, but it is the North American banking standard so we'll go with it here. 01:16 So now to find the yield to maturity for this investment, you can click and 01:20 cell C11, then type equal and name of the function which is yieldmat, 01:27 left parentheses, and then we can start typing in the cell 01:31 references for the function. 01:34 So at first we have the settlement date that's in cell C3, comma. 01:38 Maturity date C4, comma. 01:41 The issue date is in cell C5, comma. 01:44 The interest rate is in C6, comma. 01:47 The price per 100 is in C7, comma, 01:51 and the basis is in C8. 01:53 Now I can type a right parentheses having entered values or cell references in 01:57 this case for all of the arguments, and press Tab. 02:01 When I do, we see that the yield to maturity for this investment is 6.17%. 02:07 The yield to maturity represents the total increase of the investments value 02:11 expressed as an annual interest rate. 02:13 You can use that value to compare this investment with others you 02:15 are considering. Collapse this transcript
6. Calculating Prices and Yields of Securities with Odd Periods
ODDFPRICE: Calculating the price of a security with an odd first period
 00:00 The bond related formulas and functions in the previous chapters assume that 00:04 every period is the same length. For example, 00:07 that coupon payments occur every 3, 6 or 12 months. 00:11 Some bonds have irregular first or last periods where the payment dates don't 00:15 fit any of those patterns. 00:17 So Excel includes a set of functions you can use to evaluate those securities. 00:21 In this movie, I'll show you how to calculate the price for a security that has 00:25 and odd or unusual first period. 00:27 To do that we need to know the following things. The first argument for our 00:32 function is the settlement date. 00:34 That is the date that you take possession of the security. 00:38 And next, we have the maturity date and that is the last date of the investment 00:42 when all monies are due to you. 00:44 Then the issue date and that is the date that the security was initiated and 00:48 again that can be different from the date you take possession of it. 00:51 Then we have the first coupon date and that is the first day that interest is 00:56 paid to you the bondholder. 00:59 Then we have the coupon percentage and that is the amount of money paid based 01:03 on your investment. 01:04 Then the percent yield and that is the amount that your investment 01:08 appreciates over year. 01:09 Then we have the redemption value and the redemption value is the amount of 01:14 money you get for the security for \$100 of face value. 01:18 Next, we have frequency and that is the number of coupons paid per year. 01:23 So that can be either annually which would be 1, semiannually which is 2, or in 01:28 this case quarterly which is 4. 01:31 and then finally, we have basis. 01:33 Basis is the way that you count the number of days in a month and a year. 01:37 The standard which is option 0 is for a 30 day month and a 360 day year. 01:44 In this case, we're going to use option number 1 and that is actual days. So for example, 01:49 in February, you would count 28 days in a non-leap year and 29 days in a leap year. 01:56 So now with all that information in place we can create our function. 02:00 So click in cell C14 and type equal oddfprice and then a left parentheses and 02:10 the function name means odd or unusual. f is first period and price means that 02:16 we're trying to find the price that someone should pay for the security if it 02:20 were priced at exactly market value. 02:23 So the first argument is the settlement date that is in cell C3, comma. 02:29 Then the maturity date is in C4, comma. 02:32 The issue date is C5 comma. 02:34 The first coupon is in C6 comma. 02:39 The rate is in C7 and the yield is in C8, then a comma. 02:45 The redemption value per 100 is in C9, comma. 02:50 Frequency and again that's the frequency of coupon and that is in C10, comma, 02:56 and then we have the basis and that's in C11. 02:59 So now I'll type a right parentheses to close out the function and press Tab. 03:04 When I do, we see that the price for this security should be \$111.86. 03:10 So when you want to calculate the price of a bond with an odd or usual first 03:14 period, use the ODDFPRICE function. Collapse this transcript
ODDFYIELD: Calculating the yield of a security with an odd first period
 00:00 The bond related formulas and functions in the previous chapters assume that 00:04 every period is the same length. 00:06 For example, that coupon payments occur every 3, 6 or 12 months. 00:10 Some bonds have irregular first or last periods where the payment dates don't 00:14 fit any of those patterns. 00:16 So, Excel includes a set of functions you can use to evaluate those securities. 00:20 In this movie, I'll show you how to calculate the yield of a security with an 00:23 odd, meaning unusual, first period. 00:26 We're calculating yield, so we need to know the following things. 00:29 In the worksheet you can see that we have the settlement date and that is the 00:33 date that you actually take possession of a security. 00:36 The maturity date is when the investment ends and all monies and interest are due to you. 00:41 Then the issue date. 00:42 That is the date that the security was first offered. 00:47 Then we have a first coupon date and that is the date when the first interest 00:51 was paid to investors. 00:52 Next have the rate that is the annual interest rates applied to the investment. 00:57 Then we have price and price is the amount of money that you pay per \$100 of 01:02 face value for the bond. 01:03 In this case, it's \$104 which is higher than 100, but you have to remember that 01:07 you're getting interest payments throughout the life of the bond so that is 01:10 going to be a little bit higher than the face value. 01:13 Next, we have the redemption value and for \$100 investment that's almost 01:17 always going to be 100. 01:19 Then next is frequency and frequency is the number of coupon payments that you get per year. 01:24 That value can either be 1, meaning an annual payment, 2 meaning semiannual, or 01:30 4 meaning quarterly. 01:31 In this case, we're going semiannual. 01:33 Finally, we have the basis. 01:35 Basis is how you count the days in a month and a year. 01:39 The default is 0, which means that you assume a 30-day month for all 12 01:43 months and a 360-day year. 01:46 In this case, basis number 1 means that you count the actual days. 01:49 So in a regular year, February would have 28 and in a leap year February would have 29. 01:54 With all that information in place we can now create our yield formula. 01:57 So I'll click in the cell C14, type in equal sign, and the name of the function 02:02 we're going to use, which is oddfyield. 02:06 So odd, f meaning first, and yield. 02:11 Then a left parenthesis and we can start filling in the cells that contain the arguments. 02:16 So first is the settlement date that's in C3, comma. 02:20 Maturity date is in C4, comma. 02:22 Issue date C5 comma. 02:24 First coupon date that is in C6, comma. 02:28 The rate is in C7, comma. 02:31 The price in C8, comma. 02:33 The redemption value and again that's expressed per 100. 02:36 That is in C9, comma. 02:39 The frequency is in C10, comma and then the basis is in C11. 02:44 Type a right parentheses. Make sure all of my arguments line up in the worksheet above. 02:50 They do so I'll press Tab and see that the annual yield for this investment is 5.79%. 02:56 So, when you want to calculate the price of a bond with an odd meaning unusual 02:59 first period, you can use the ODDYFIELD function. Collapse this transcript
ODDLPRICE: Calculating the price of a security with an odd last period
 00:00 The bond related formulas and functions in the previous chapters assume that 00:04 every period is of the same length. For example, 00:06 that coupon payments occur every 3, 6 or 12 months. 00:10 Some bonds have irregular first or last periods though which means that the 00:13 payments dates don't fit any of those patterns. 00:16 For those securities Excel includes a set of functions you can use to evaluate them. 00:20 In this movie, I'll show you how to calculate the price for security with an odd, 00:23 meaning unusual, last period. 00:26 For this calculation we need to no the following items. The first is the 00:30 settlement date and that that is the date that you purchase and gain control of the security. 00:35 Next is the security's maturity date. That is the last day of the investment and 00:40 the day that all proceeds and return of principle or due to you. 00:44 Then you have the last coupon date. That is the last time interest is paid. Then, 00:48 you have the coupon interest rate. That's the amount of money you get for each 00:52 payment every time you paid interest. 00:54 The annual yield is the interest rate applied to your principle. 00:58 Redemption value is the amount of money you get back per \$100 of face value for the bonds. 01:03 It's almost always going to be \$100. 01:06 Next, you have the frequency and frequency is the number of coupons that 01:10 you receive per year. 01:12 That can either be 1 for annual, 2 for semiannual, or 4 for quarterly. 01:16 And then finally, there is basis. 01:19 Basis refers to how you count the number of days and month in a year. 01:22 Basis 0, which is the North American standard, assumes all month with 30 days long 01:27 and that there is a 360 day year. 01:29 In this case, we're using basis number 1 and what that means is they are 01:33 counting actual days. 01:35 So in a non-leap year February has 28 days and in a leap year February has 29. 01:40 So with all that information in place we can calculate the price. 01:43 I'll click in cell C13, type an equal sign, and then I'll type in the name of the 01:49 function we're going to use and that is as oddlprice. 01:54 So that means odd, l for last, and we're calculating price. 01:59 Type a left parenthesis and we can start filling in the arguments. 02:03 First argument is the settlement date that's in C3, comma, maturity date C4, comma, last 02:09 coupon date is in C5, comma, coupon interest rate C6, comma, annual yield C7, redemption value 02:17 C8, comma, frequency is in C9, type a coma, and then we have the basis in the C10. 02:23 Then type a right parenthesis to close out the function, make sure that all my 02:26 references line up, and press Tab. 02:29 When I do, I see that the price for the security should \$99.84 per \$100 of face value. 02:36 So would you want to calculate the price of a bond with an odd, meaning unusual, last period, 02:41 use the oddlprice function. Collapse this transcript
ODDLYIELD: Calculating the yield of a security with an odd last period
 00:00 The bond related formulas and functions in the previous chapters assume that 00:04 every period is of the same length. For example, 00:06 that coupon payments occur every 3, 6 or 12 months. 00:10 Some bonds have irregular first or last periods though, which means that the 00:13 payments dates don't fit any of those patterns. 00:16 For those securities Excel includes a set of functions you can use to evaluate them. 00:20 In this movie, I'll show you how to calculate the yield of a security with an 00:24 odd, meaning unusual, last period. 00:26 To make that calculation, the first thing we need to no know is the settlement 00:30 date and that that is the date that you take possession of the security. 00:33 Next we have the maturity date and that is the last day of the investment, when 00:38 all principle and any other proceeds are returned to you. 00:41 The last interest date is the last day that any interests through coupons are paid to you. 00:46 The rate is the annual percentage rate of the investment. 00:49 Then we have the price which is the price per \$100 of face value that you pay. 00:54 Now in this case the price is more than the face value, but remember that 00:58 you're also receiving interest as part of your investment. 01:02 Next is the redemption value. 01:03 That's also per \$100 of face value and it's almost always going to be 100. 01:08 Next we have frequency and frequency is the number of times that you are given 01:12 coupon interest per year. 01:14 The value can either be one, meaning annual payments, two for semi-annual, 01:19 or four for quarterly. 01:20 In this case, we have two so we're getting interest every six months. 01:23 And then, finally is basis and basis refers to how you count the number of days 01:28 in a month and year. 01:29 The North American standard is for a 30 day month and a 360 day year. 01:35 However in this case reason option one which is actual and that means that 01:40 you count every day in the year, differentiating between regular years and leap years. 01:46 So for example, 01:47 in a regular year February has 28 days while in a leap year it has 29. 01:52 So with all that information in place we can create our formula. So I'll click 01:56 in cell C13, type an equal sign, and then type in the name of the function that 02:01 I'm going to use and that is odd, l for last, yield. 02:07 So we have odd meaning unusual, l for last period, and then yield because we are 02:12 calculating the yield. 02:14 Type a left parenthesis and now we can start filling in the cell references for the arguments. 02:18 First is the settlement date. 02:20 That's in C3, comma, maturity date C4, comma, the last interest date is C5, comma, the rate is in C6, comma, 02:30 price is in C7, comma, redemption value C8, comma, frequency is in C9, comma, and the basis is in C10. 02:40 Then type a right parenthesis to close out the function and press Tab. 02:44 When we do that the yield is 5.7 on this investment. 02:49 So when you want to calculate the yield the of a bond with an odd, 02:52 meaning unusual, last period, 02:54 use the oddlyield function. Collapse this transcript
Conclusion
Additional resources
 00:00 Thank you for working with me through Excel 2010: Financial Functions in Depth. 00:05 I hope you have learned a lot and that you've learned about some capabilities 00:08 that you might not have known Excel had. 00:10 If you want to dealt further into financial analysis using Microsoft Excel, 00:14 I want to point out three separate resources for you. 00:16 The first is my Excel 2007: Financial Analysis course which is available here in 00:21 the lynda.com Online Training Library. 00:24 Even though the title says Excel 2007, it also applies to Excel 2010 and Excel 00:29 2003 and there are also two books that I would like to recommend. 00:32 The first is called Principles of Finance with Excel by Simon Benninga. 00:37 It's a wonderfully complete reference. 00:39 So, if you really want to get in depth into finance with Excel, that's a great way to go. 00:44 The final resource I'd like to point out is the book Microsoft Excel 2010: Data 00:49 Analysis and Business Modeling by Wayne L. Winston. 00:52 That book has a broad variety of techniques that you can use for finance and for 00:56 other business applications. 00:58 Once again, thank you very much for your time and I hope you've had fun with 01:02 Excel 2010: Financial Functions in Depth. Collapse this transcript

Suggested courses to watch next:

Managing and Analyzing Data in Excel (1h 32m)
Dennis Taylor

Excel 2010: Data Validation in Depth (59m 45s)
Dennis Taylor

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:
loading
cancel

bookmark this course

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

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,466 instructional videos.

start free trial learn more

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.

Access to lynda.com videos

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,893 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

• newsletter
• new course releases
• special announcements

We’ll only use your email address to send you these mailings.

Here’s our privacy policy with more details about how we handle your information.

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

• newsletter
• new course releases
• special announcements

We’ll only use your email address to send you these mailings.

Here’s our privacy policy with more details about how we handle your information.

submit Lightbox submit clicked