IntroductionWelcome| 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 InterestPMT: 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 DepreciationSLN: 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 ReturnFV: 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 DurationsCOUPDAYBS: 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 YieldsDOLLARDE 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 PeriodsODDFPRICE: 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 |
|
|
ConclusionAdditional 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 |
|
|
