馃攳
YIELD Function in Excel | How to use Excel YIELD Function? - YouTube
Channel: WallStreetMojo
[12]
welcome to wallstreetmojo to know
about this video yield function in Excel
[17]
watch the video till the end and if you
are new to this channel then you can
[22]
subscribe us by clicking the bell ican
that's given below
[24]
welcome everyone let's understand this
function in excel how exactly Yield
[30]
function we have all studied yield in
our books in probably in our MBA program
[36]
or probably know CA or a CFA program but
what does or how exactly the
[44]
implementation part comes when we see
the exit or the real life into picture
[50]
well let's begin on the first and the
foremost note I want you to understand
[56]
first let's see yield function is
basically it's a very sort of we can say
[61]
the advance function that is used to
calculate the amount of the income that
[65]
is generated each year from the
investment amount so generally income
[70]
generally you know Yield function is in
Excel that is used to calculate the
[74]
yield on bonds to determine income that
would be generated each year on a
[79]
security bond that words periodical
investment now the Yield formula in Excel
[86]
how exactly it's calculated okay if you
start with yield over here this right ye
[94]
yield as you can see just once have
written YI
[98]
it shows meeting formula it says that
returns the yield on the security that
[103]
pays the periodical interest well if I
press tab button they have so many other
[110]
things we'll start with the explanation
see there are seven parameters that are
[115]
used in as you can see there are see so
many parameters that are there to
[120]
calculate the bond yield in Excel in
which you know six parameters are
[123]
completely compulsory 1 2 3 and 4
[127]
5 and 6 this basis is optional
[129]
we'll understand each of them now first
the settlement part see the date this is
[135]
basically the date on which the open is
purchased by the buyer or the date on
[140]
which the bond is purchased or the
settlement at the date of the security
[145]
now the maturity part no maturity date
of the security or the debtors it is a date
[150]
on which the purchased coupon expires
the rate rate is the annual coupon rate
[157]
of the security PR PR represents the
security price per 100 stated value then
[164]
comes the redemption no redemption is
the redemption value of the security per
[168]
$100 stated value frequency
frequency means the number of the
[172]
coupons that is paid in a year it can be
one-off or annual payment to for
[178]
semiannual and for for quarterly payment
now the basis the optional parameter
[184]
over here is always appearing in
brackets as you can see in an excel
[190]
formula here the basis is optional
argument so it comes as basis so basis
[195]
is basically optional integer parameter
which specifies the day count basis that
[200]
is used by security and the possible
values for basis are zero you know that
[207]
is us 32 361 actual to actual 363 actual
365 and European is 30 and 360 now how
[220]
to use the yield function in Excel see
yield in an Excel can be used in a
[223]
worksheet function like normally in
formulas that is used in Excel now let's
[228]
understand this working on bond yield
function in Excel with few examples see
[232]
bond yield calculation Excel for
quarterly payment let's consider the
[236]
settlement date as 17th of May 2018 and
the maturity date is 17th May 2020 the
[244]
purchase coupon so the rate of interest
over here is 5% the rate of interest
[249]
over here is 5% and the price is 101
the redemption over here is value is 100
[257]
and the payment terms of the frequency
is quarterly so what will be our yield well
[264]
we'll start with is equal to a yield
first is the settlement so settlement
[270]
will be settlement date then is the
maturity date that is C4 the rate of
[277]
interest that is 5%
then is the PR the right PR is the price
[284]
100 FV then is our Redemption Redemption
value is 100 and because it is quarterly
[295]
so we'll say quarterly the frequency so
once you have written c7 for just write
[304]
frequency 0 will go over the US 1 so it
says 4.475 now
[312]
let's take another example for
semi-annually will say yield the
[318]
settlement date the maturity date the
rate of interest the prize the
[325]
redemption value and to here that is
semi-annual comma over here again we'll
[332]
take zero for us that is 4.472
now the bond yield in Excel
[340]
for yearly payment is different but
let's consider the settlement date over
[345]
here as 17th of May 2018 and the
maturity as 17th may 2020 the rate of
[351]
interest price another Redemption value
is let's say 5% 101 and 100 annually so for
[357]
similarly the payment frequency or will
be 1 the output he'll will be 4.46
[362]
now how exactly that will work
so is equal to yield settlement maturity
[370]
rate of interest price Redemption value
the frequency is going to be annual zero
[380]
close the bracket that is 4.466 well this was the example that
[385]
you need to understand now I want you to
understand some of the things to
[391]
remember about the yield function no
there are some error detail which can
[400]
come across in the bond yield function
you to to type mismatch the first one is
[405]
called the num error and there may be
two possibilities for this error in the
[409]
bond yield in Excel first if the
settlement
[413]
in the yield function in Excel I'm just
setting SD settlement it in the yield
[418]
function is greater than the or equal
to the maturity date then num error
[425]
occurs second invalid numbers are given
to rate PR and redemption frequency or
[432]
places parameter
now if rate is less than zero then the
[439]
yield in Excel returns the num error if
the PR is less than or equal to zero and
[447]
redemption is also less than equal to 0
then the yield in function returns to
[453]
the num error now if the given
frequency is not let's say 1 2 and
[460]
4 then the yield excel function
returns the num error of the basis now
[468]
if the basis if that is less than zero
or if the basis is greater than 4 then
[477]
the yield excel function returns to the
num error right so if any given
[483]
parameters are non numbers or the data
are not provided in the in the date
[487]
format it will show us your value error
right like Microsoft Excel stores the
[494]
data sequence from 1st January 1900 as
number one and some numbers days it's as
[499]
you know 17th January over here so if you have learned and enjoyed watching this
[507]
video please comment on this video and
subscribe to our channel for the latest
[510]
updates thank you everyone
Most Recent Videos:
You can go back to the homepage right here: Homepage