Please explain YIELDMAT function

[JoeU2004]

I cannot make sense of the YIELDMAT results, no matter what definition of
YTM that I try. (There seems to be more than one.)

It would help me if someone posted the math formula or algorithm that
YIELDMAT uses.

And it would help me if someone would show how the YIELDMAT example in Excel
2003 help page is computed using that math formula or algorithm.

Some points to note in the help page description:


1. The synopsis at the top says that interest is presumed to be paid at
maturity, not at the coupon frequency.

I think that should impact the YTM (IRR) computation. But in one example
that I tried [*]), YIELDMAT came close (but not that close) to the result of
my reverse-engineered method of computing YTM only when I assume a regular
cash flow of coupons.

On the other hand, I cannot even come close to the results of the help
page result, no matter what frequency of cash flows I assume.

[*] http://www.moneychimp.com/articles/finworks/fmbondytm.htm .


2. The YIELDMAT example suggests that the units of the YIELDMAT rate
parameter is "percent semiannual coupon".

I am not sure how that relates to an annual rate.

Moreover, since that requirement is not stated in the synopsis, I wonder
if the example annotation is simply incorrect.


3. The example uses the 30/360 mode ("basis"), not the actual/365 mode which
I understand better.

If it makes the explanation easier, I would not mind switching the help
page example to actual/365 (mode 3). In that case, YIELDMAT result is
6.09636299211303%, which you can round to Percent with 4 decimal places or
more, as the example does.

But I would also like to understand how 30/360 should be computed; that
is, how it affects the math formula or algorithm.


4. With respect to the 30/360 mode, I would like to understand how the
maturity date of 11/3/2008 might have been determined.

Of course, it could be arbitrary. But my curiosity is piqued by the fact
that it is not 11/8/2007 plus 360 days, but that date plus 1.

I wonder if there is some market convention for determining the one-year
maturity date when using the 30/360 mode.

And I wonder if/how that affects the YIELDMAT mathematical or
algorithmic computation when using the 30/360 mode.


5. I do not understand why the YIELDMAT implementation does not tolerate
settlement date equal to issue date.

I understand that they are usually not equal in the real world. But it
would simplify comparisons with reverse-engineered solutions.

Note: The help page does not identify this limitation. I stumbled upon
it empirically.

 

[Sean Timmons]

I can speak to the logic of it more than anything else.

This is a formula to be used by those buying bonds.

First, we look at the bond's interest rate. The interest is from the issue
to the maturity date.

30/360 assumes 30 day months and 360 day years.

The settlement date is when the purchaser takes over the bond.

Since the bond was not purchased on the date of issue, we have to calculate
what the purchaser will receive from the bond from the date of settlement to
the date of maturity.

In a simple fashion, consider your interest is from issue to maturity,
settlement is somewhere between, so what will the new owner reap in interest
rate from this partially lapsed bond.

Ddi that help or hurt?

I cannot make sense of the YIELDMAT results, no matter what definition of
YTM that I try. (There seems to be more than one.)

It would help me if someone posted the math formula or algorithm that
YIELDMAT uses.

And it would help me if someone would show how the YIELDMAT example in Excel
2003 help page is computed using that math formula or algorithm.

Some points to note in the help page description:


1. The synopsis at the top says that interest is presumed to be paid at
maturity, not at the coupon frequency.

I think that should impact the YTM (IRR) computation. But in one example
that I tried [*]), YIELDMAT came close (but not that close) to the result of
my reverse-engineered method of computing YTM only when I assume a regular
cash flow of coupons.

On the other hand, I cannot even come close to the results of the help
page result, no matter what frequency of cash flows I assume.

[*] http://www.moneychimp.com/articles/finworks/fmbondytm.htm .


2. The YIELDMAT example suggests that the units of the YIELDMAT rate
parameter is "percent semiannual coupon".

I am not sure how that relates to an annual rate.

Moreover, since that requirement is not stated in the synopsis, I wonder
if the example annotation is simply incorrect.


3. The example uses the 30/360 mode ("basis"), not the actual/365 mode which
I understand better.

If it makes the explanation easier, I would not mind switching the help
page example to actual/365 (mode 3). In that case, YIELDMAT result is
6.09636299211303%, which you can round to Percent with 4 decimal places or
more, as the example does.

But I would also like to understand how 30/360 should be computed; that
is, how it affects the math formula or algorithm.


4. With respect to the 30/360 mode, I would like to understand how the
maturity date of 11/3/2008 might have been determined.

Of course, it could be arbitrary. But my curiosity is piqued by the fact
that it is not 11/8/2007 plus 360 days, but that date plus 1.

I wonder if there is some market convention for determining the one-year
maturity date when using the 30/360 mode.

And I wonder if/how that affects the YIELDMAT mathematical or
algorithmic computation when using the 30/360 mode.


5. I do not understand why the YIELDMAT implementation does not tolerate
settlement date equal to issue date.

I understand that they are usually not equal in the real world. But it
would simplify comparisons with reverse-engineered solutions.

Note: The help page does not identify this limitation. I stumbled upon
it empirically.

 

[JoeU2004]

I can speak to the logic of it more than anything else.
[....]
Ddi that help [...]?

Sorry, but not at all.

I understand the logic of it. But that does not seem to explain the results
of YIELDMAT, as I apply the logic.

Addressing each of my questions and comments specifically is the best way to
clear things up for me.


----- original message -----

I can speak to the logic of it more than anything else.

This is a formula to be used by those buying bonds.

First, we look at the bond's interest rate. The interest is from the issue
to the maturity date.

30/360 assumes 30 day months and 360 day years.

The settlement date is when the purchaser takes over the bond.

Since the bond was not purchased on the date of issue, we have to
calculate
what the purchaser will receive from the bond from the date of settlement
to
the date of maturity.

In a simple fashion, consider your interest is from issue to maturity,
settlement is somewhere between, so what will the new owner reap in
interest
rate from this partially lapsed bond.

Ddi that help or hurt?

I cannot make sense of the YIELDMAT results, no matter what definition of
YTM that I try. (There seems to be more than one.)

It would help me if someone posted the math formula or algorithm that
YIELDMAT uses.

And it would help me if someone would show how the YIELDMAT example in
Excel
2003 help page is computed using that math formula or algorithm.

Some points to note in the help page description:


1. The synopsis at the top says that interest is presumed to be paid at
maturity, not at the coupon frequency.

I think that should impact the YTM (IRR) computation. But in one
example
that I tried [*]), YIELDMAT came close (but not that close) to the result
of
my reverse-engineered method of computing YTM only when I assume a
regular
cash flow of coupons.

On the other hand, I cannot even come close to the results of the help
page result, no matter what frequency of cash flows I assume.

[*] http://www.moneychimp.com/articles/finworks/fmbondytm.htm .


2. The YIELDMAT example suggests that the units of the YIELDMAT rate
parameter is "percent semiannual coupon".

I am not sure how that relates to an annual rate.

Moreover, since that requirement is not stated in the synopsis, I
wonder
if the example annotation is simply incorrect.


3. The example uses the 30/360 mode ("basis"), not the actual/365 mode
which
I understand better.

If it makes the explanation easier, I would not mind switching the
help
page example to actual/365 (mode 3). In that case, YIELDMAT result is
6.09636299211303%, which you can round to Percent with 4 decimal places
or
more, as the example does.

But I would also like to understand how 30/360 should be computed;
that
is, how it affects the math formula or algorithm.


4. With respect to the 30/360 mode, I would like to understand how the
maturity date of 11/3/2008 might have been determined.

Of course, it could be arbitrary. But my curiosity is piqued by the
fact
that it is not 11/8/2007 plus 360 days, but that date plus 1.

I wonder if there is some market convention for determining the
one-year
maturity date when using the 30/360 mode.

And I wonder if/how that affects the YIELDMAT mathematical or
algorithmic computation when using the 30/360 mode.


5. I do not understand why the YIELDMAT implementation does not tolerate
settlement date equal to issue date.

I understand that they are usually not equal in the real world. But
it
would simplify comparisons with reverse-engineered solutions.

Note: The help page does not identify this limitation. I stumbled
upon
it empirically.

 

[JoeU2004]

I wanted to avoid "polluting" responses by providing an example of my own.
I did not want to distract the thread with potential misunderstandings of my
own. I would prefer that a knowledgable person (i.e. a person knowledgable
about YTM and YIELDMAT) respond to my original posting, not to this
follow-up.

But just to give some context....

Consider the example on the YIELDMAT help page, but using mode 1
(actual/actual), which I feel is easier for making an apples-to-apples
comparison. The other modes are subject to more interpretation when
reverse-engineering a solution, IMHO.

Assume the following:

B1, issue date: 11/08/2007
B2, settlement date: 3/15/2008
B3, 1st coupon date: 5/08/2008
B4, maturity date: 11/03/2008
B5, 2nd coupon date: 11/08/2008

The interest rate at the date of issue is 6.25% "semiannual coupon", and the
settlement price per $100 face value is 100.0123. I'll explain B3 and B5
below.

Then:

=YIELDMAT(B2,B4,B1,6.25%,100.0123,1)

results in about 6.0967%.

Reverse-engineering....

The YTM should be the discount rate that causes the sum of the discounted
cash flows to equal the price.

Since the synopsis says that YIELDMAT returns the annual yield of a security
that pays interest "at maturity", we might assume that there are only two
cash flows for this example, to wit:

B7, settlement date: 3/15/2008
C7, settlement price: -100.0123

B8, maturity date: 11/03/2008
C8, net cash flow: =100 + 6.25*(B4-B2)/(B5-B1)

C8 is the redemption value (100) plus the interest accrued since the
settlement date.

Then =XIRR(B7:B8,C7:C8) returns about 6.2823%, which is not the same as
YIELDMAT.

Note that I used the "2nd coupon date" (B5) in the denominator when
prorating the accrued interest, a full one year after the issue date.

IMHO, that's correct. But some might argue that I should use the maturity
date (B4). Then C8 becomes:

=100 + 6.25*(B4-B2)/(B4-B1)

and XIRR returns about 6.3705%, which is even more different from YIELDMAT.

Finally, I return to the fact that the YIELDMAT help example says that the
interest is "percent semiannual coupon". So, ignoring the synopsis, we
might model the cash flows as follows:

B7: 3/15/2008 C7: -100.0123
B8: 5/08/2008 C8: =(6.25/2)*(B3-B2)/(B3-B1)
B9: 11/03/2008 C9: =100 + (6.25/2)*(B4-B3)/(B5-B3)

(That is actually common practice for "zeros".)

Then =XIRR(C7:C9,B7:B9) returns about 6.3090% or 6.4460%, depending on
whether we use B5-B3 or B4-B3 in the demoninator in C9, as explained above.
Neither is the same as YIELDMAT.

So I am looking for an explanation of how YIELDMAT works for mode 1
(actual/actual).

Or an acknowledgment that YIELDMAT is simply wrong ;-). But in that case, I
would like to know which of the "4" models above (counting variations) is
correct, if any, or an explanation of the correct model to use.

TIA.


----- original message -----

I cannot make sense of the YIELDMAT results, no matter what definition of
YTM that I try. (There seems to be more than one.)

It would help me if someone posted the math formula or algorithm that
YIELDMAT uses.

And it would help me if someone would show how the YIELDMAT example in
Excel
2003 help page is computed using that math formula or algorithm.

Some points to note in the help page description:


1. The synopsis at the top says that interest is presumed to be paid at
maturity, not at the coupon frequency.

I think that should impact the YTM (IRR) computation. But in one
example
that I tried [*]), YIELDMAT came close (but not that close) to the result
of
my reverse-engineered method of computing YTM only when I assume a regular
cash flow of coupons.

On the other hand, I cannot even come close to the results of the help
page result, no matter what frequency of cash flows I assume.

[*] http://www.moneychimp.com/articles/finworks/fmbondytm.htm .


2. The YIELDMAT example suggests that the units of the YIELDMAT rate
parameter is "percent semiannual coupon".

I am not sure how that relates to an annual rate.

Moreover, since that requirement is not stated in the synopsis, I wonder
if the example annotation is simply incorrect.


3. The example uses the 30/360 mode ("basis"), not the actual/365 mode
which
I understand better.

If it makes the explanation easier, I would not mind switching the help
page example to actual/365 (mode 3). In that case, YIELDMAT result is
6.09636299211303%, which you can round to Percent with 4 decimal places or
more, as the example does.

But I would also like to understand how 30/360 should be computed; that
is, how it affects the math formula or algorithm.


4. With respect to the 30/360 mode, I would like to understand how the
maturity date of 11/3/2008 might have been determined.

Of course, it could be arbitrary. But my curiosity is piqued by the
fact
that it is not 11/8/2007 plus 360 days, but that date plus 1.

I wonder if there is some market convention for determining the one-year
maturity date when using the 30/360 mode.

And I wonder if/how that affects the YIELDMAT mathematical or
algorithmic computation when using the 30/360 mode.


5. I do not understand why the YIELDMAT implementation does not tolerate
settlement date equal to issue date.

I understand that they are usually not equal in the real world. But it
would simplify comparisons with reverse-engineered solutions.

Note: The help page does not identify this limitation. I stumbled upon
it empirically.

 

[JoeU2004]

Errata....

B7, settlement date: 3/15/2008
C7, settlement price: -100.0123

B8, maturity date: 11/03/2008
C8, net cash flow: =100 + 6.25*(B4-B2)/(B5-B1)

I did not account for the fact that on the settlement date, the buyer must
also pay the unpaid accrued interest. This is consistent with the HP 12C
formula, which is based on a standard securities text.

So I believe my corrected cash flows are:

C7: =-100.0123 - 6.25*(B2-B1)/(B5-B1)
C8: =100 + 6.25*(B4-B1)/(B5-B1)

XIRR(C7:C8,B7:B8) is about 6.1465%. That is closer to the YIELDMAT, but not
close enough, IMHO.

B7: 3/15/2008 C7: -100.0123
B8: 5/08/2008 C8: =(6.25/2)*(B3-B2)/(B3-B1)
B9: 11/03/2008 C9: =100 + (6.25/2)*(B4-B3)/(B5-B3)

Similarly:

C7: =-100.0123 - (6.25/2)*(B2-B1)/(B3-B1)
C8: =6.25/2
C9: =100 + (6.25/2)*(B4-B3)/(B5-B3)

XIRR(C7:C9,B7:B9) is about 6.2760%. That is significantly different from
YIELDMAT.


----- original message -----

I wanted to avoid "polluting" responses by providing an example of my own.
I did not want to distract the thread with potential misunderstandings of
my
own. I would prefer that a knowledgable person (i.e. a person
knowledgable
about YTM and YIELDMAT) respond to my original posting, not to this
follow-up.

But just to give some context....

Consider the example on the YIELDMAT help page, but using mode 1
(actual/actual), which I feel is easier for making an apples-to-apples
comparison. The other modes are subject to more interpretation when
reverse-engineering a solution, IMHO.

Assume the following:

B1, issue date: 11/08/2007
B2, settlement date: 3/15/2008
B3, 1st coupon date: 5/08/2008
B4, maturity date: 11/03/2008
B5, 2nd coupon date: 11/08/2008

The interest rate at the date of issue is 6.25% "semiannual coupon", and
the
settlement price per $100 face value is 100.0123. I'll explain B3 and B5
below.

Then:

=YIELDMAT(B2,B4,B1,6.25%,100.0123,1)

results in about 6.0967%.

Reverse-engineering....

The YTM should be the discount rate that causes the sum of the discounted
cash flows to equal the price.

Since the synopsis says that YIELDMAT returns the annual yield of a
security
that pays interest "at maturity", we might assume that there are only two
cash flows for this example, to wit:

B7, settlement date: 3/15/2008
C7, settlement price: -100.0123

B8, maturity date: 11/03/2008
C8, net cash flow: =100 + 6.25*(B4-B2)/(B5-B1)

C8 is the redemption value (100) plus the interest accrued since the
settlement date.

Then =XIRR(B7:B8,C7:C8) returns about 6.2823%, which is not the same as
YIELDMAT.

Note that I used the "2nd coupon date" (B5) in the denominator when
prorating the accrued interest, a full one year after the issue date.

IMHO, that's correct. But some might argue that I should use the maturity
date (B4). Then C8 becomes:

=100 + 6.25*(B4-B2)/(B4-B1)

and XIRR returns about 6.3705%, which is even more different from
YIELDMAT.

Finally, I return to the fact that the YIELDMAT help example says that the
interest is "percent semiannual coupon". So, ignoring the synopsis, we
might model the cash flows as follows:

B7: 3/15/2008 C7: -100.0123
B8: 5/08/2008 C8: =(6.25/2)*(B3-B2)/(B3-B1)
B9: 11/03/2008 C9: =100 + (6.25/2)*(B4-B3)/(B5-B3)

(That is actually common practice for "zeros".)

Then =XIRR(C7:C9,B7:B9) returns about 6.3090% or 6.4460%, depending on
whether we use B5-B3 or B4-B3 in the demoninator in C9, as explained
above.
Neither is the same as YIELDMAT.

So I am looking for an explanation of how YIELDMAT works for mode 1
(actual/actual).

Or an acknowledgment that YIELDMAT is simply wrong ;-). But in that case,
I
would like to know which of the "4" models above (counting variations) is
correct, if any, or an explanation of the correct model to use.

TIA.


----- original message -----

I cannot make sense of the YIELDMAT results, no matter what definition of
YTM that I try. (There seems to be more than one.)

It would help me if someone posted the math formula or algorithm that
YIELDMAT uses.

And it would help me if someone would show how the YIELDMAT example in
Excel
2003 help page is computed using that math formula or algorithm.

Some points to note in the help page description:


1. The synopsis at the top says that interest is presumed to be paid at
maturity, not at the coupon frequency.

I think that should impact the YTM (IRR) computation. But in one
example
that I tried [*]), YIELDMAT came close (but not that close) to the result
of
my reverse-engineered method of computing YTM only when I assume a
regular
cash flow of coupons.

On the other hand, I cannot even come close to the results of the help
page result, no matter what frequency of cash flows I assume.

[*] http://www.moneychimp.com/articles/finworks/fmbondytm.htm .


2. The YIELDMAT example suggests that the units of the YIELDMAT rate
parameter is "percent semiannual coupon".

I am not sure how that relates to an annual rate.

Moreover, since that requirement is not stated in the synopsis, I
wonder
if the example annotation is simply incorrect.


3. The example uses the 30/360 mode ("basis"), not the actual/365 mode
which
I understand better.

If it makes the explanation easier, I would not mind switching the help
page example to actual/365 (mode 3). In that case, YIELDMAT result is
6.09636299211303%, which you can round to Percent with 4 decimal places
or
more, as the example does.

But I would also like to understand how 30/360 should be computed; that
is, how it affects the math formula or algorithm.


4. With respect to the 30/360 mode, I would like to understand how the
maturity date of 11/3/2008 might have been determined.

Of course, it could be arbitrary. But my curiosity is piqued by the
fact
that it is not 11/8/2007 plus 360 days, but that date plus 1.

I wonder if there is some market convention for determining the
one-year
maturity date when using the 30/360 mode.

And I wonder if/how that affects the YIELDMAT mathematical or
algorithmic computation when using the 30/360 mode.


5. I do not understand why the YIELDMAT implementation does not tolerate
settlement date equal to issue date.

I understand that they are usually not equal in the real world. But it
would simplify comparisons with reverse-engineered solutions.

Note: The help page does not identify this limitation. I stumbled
upon
it empirically.

 

[pshepard]

Hi Joe,

The formula for YIELDMAT is

1 100 + DIM x rate x 100
___ x _______________________
DSM (price + DIS x rate x 100) -1

where

DIM {Days from issue to maturity}, calculated as
YEARFRAC(Issue, Maturity, Calendar_Type)
DIS {Days from issue to settlement}, calculated as
YEARFRAC(Issue, Settlement, Calendar_Type)
DSM {Days from settlement to maturity}, calculated as
YEARFRAC(Settlement, Maturity, Calendar_Type)


therefore, YIELDMAT =

(1/YEARFRAC(Settlement, Maturity, Calendar_Type))*((100+YEARFRAC(Issue,
Maturity, Calendar_Type)*Rate*100)/(Price+YEARFRAC(Issue, Settlement,
Calendar_Type)*Rate*100)-1)

Using the example from Excel help, Excel calculates YEARFRAC:

=YEARFRAC(A2,A3,0) = 0.633333333

OR

=((A3-A2)-5)/360 = 0.633333333


Because March, May, July, August, and October have 31 days; 5 days were
subtracted from the number of days between the start date and end date before
dividing by 360 for calendar type 0 {30-day months, 360-day years, American
method}.

If calendar type 1 {Actual months, actual years} were used; year 2008 was a
leap year with 366 days so YEARFRAC then subtracts start date from end date
to get the number of days which would then be divided by 366 instead of 365.

YEARFRAC=(A3-A2)/366 for calendar type 1 {Actual months, actual
years}

A B
1 Data Description
2 15-Mar-08 Settlement date
3 3-Nov-08 Maturity date
4 8-Nov-07 Issue date
5 6.25% Percent semiannual coupon
6 100.0123 Price
7 0 30/360 basis (see above)


--
If this post helps click Yes
---------------
Peggy Shepard

I cannot make sense of the YIELDMAT results, no matter what definition of
YTM that I try. (There seems to be more than one.)

It would help me if someone posted the math formula or algorithm that
YIELDMAT uses.

And it would help me if someone would show how the YIELDMAT example in Excel
2003 help page is computed using that math formula or algorithm.

Some points to note in the help page description:


1. The synopsis at the top says that interest is presumed to be paid at
maturity, not at the coupon frequency.

I think that should impact the YTM (IRR) computation. But in one example
that I tried [*]), YIELDMAT came close (but not that close) to the result of
my reverse-engineered method of computing YTM only when I assume a regular
cash flow of coupons.

On the other hand, I cannot even come close to the results of the help
page result, no matter what frequency of cash flows I assume.

[*] http://www.moneychimp.com/articles/finworks/fmbondytm.htm .


2. The YIELDMAT example suggests that the units of the YIELDMAT rate
parameter is "percent semiannual coupon".

I am not sure how that relates to an annual rate.

Moreover, since that requirement is not stated in the synopsis, I wonder
if the example annotation is simply incorrect.


3. The example uses the 30/360 mode ("basis"), not the actual/365 mode which
I understand better.

If it makes the explanation easier, I would not mind switching the help
page example to actual/365 (mode 3). In that case, YIELDMAT result is
6.09636299211303%, which you can round to Percent with 4 decimal places or
more, as the example does.

But I would also like to understand how 30/360 should be computed; that
is, how it affects the math formula or algorithm.


4. With respect to the 30/360 mode, I would like to understand how the
maturity date of 11/3/2008 might have been determined.

Of course, it could be arbitrary. But my curiosity is piqued by the fact
that it is not 11/8/2007 plus 360 days, but that date plus 1.

I wonder if there is some market convention for determining the one-year
maturity date when using the 30/360 mode.

And I wonder if/how that affects the YIELDMAT mathematical or
algorithmic computation when using the 30/360 mode.


5. I do not understand why the YIELDMAT implementation does not tolerate
settlement date equal to issue date.

I understand that they are usually not equal in the real world. But it
would simplify comparisons with reverse-engineered solutions.

Note: The help page does not identify this limitation. I stumbled upon
it empirically.

 

[JoeU2004]

From Microsoft's documentation:

Thank you oh-so-much for this.

First and foremost, where in the world (literally ;-) did you find this?

If from the web, please post the URL, and let me know how you found it
(search engine and parameters).

If from your Excel help, what revision of Excel are you using? (UK
revision?)

This not on the offline help page in my (US) Excel 2003. And I do not see
it in the online content. But I might have overlooked it there, since I am
not used to looking at online content.

For those who cannot read attachments, the key is in the GIF that Stephen
attached. I will reproduce it in text here, hopefully without error.

YIELDMAT =

( B/DSM ) * ( 1 + rate*DIM/B - ( par/100 + rate*A/B ) )
/ ( P/100 + rate*A/B )

where:

A = days from issue to settlement
B = days in year, according to the "basis" mode
DIM = days from issue to maturity
DSM = days from settlement to maturity
P = price on the settlement date, I presume
rate = coupon rate, I presume
par = face value, I presume


My first reaction was: "Huh?! What does that have to do with the price of
tea in China?". (Don't take that literally. Translation: "non
sequitur!".)

I don't believe that can compute YTM by the "standard" definition, namely
the discount rate that causes the sum of the discounted cash flows to be
zero. (I have never seen any other definition of YTM.)

Then I thought: perhaps it would work for bonds that mature in one year or
less, assuming interest is paid at maturity -- like the example on the
YIELDMAT help page.

Nope! Even that does not work -- unless I made a mistake. Please
double-check me.


L19, Issue: 11/8/2007
L20, Settle: 3/15/2008
L21, Mature: 11/3/2008
L22, Rate: 6.25%
L23, Price: 100.0123
L24, Basis(30/360): 0
L25, Par: 100

L26, A(128): =L20-L19
L27, DIM(361): =L21-L19
L28, DSM(233): =L21-L20
L29, B: 360


YIELDMAT (6.0954%; same as help page):

=YIELDMAT(L20,L21,L19,L22,L23,L24)


YTM based on Stephen's GIF (6.1134%):


=(L29/L28)*(1+L22*L27/L29 - (L25/100 + L22*L26/L29))/(L23/100 + L22*L26/L29)


I'm surprised that the percentage rates are so close. I even tried all
combinations of off-by-one errors in A, DIM and DSM to no avail.

Aha! But the formulas do agree, just about, when I swap P (L23) and Par
(L25) in the formula.

Coincidence? I don't know, since I don't recognize the formula, and I am
taking the time to understand it.

In any case, YTM is very different from YIELDMAT with another set of
parameters, namely: issue 4/28/1982, settle 4/29/1982 (because of a
limitation of YIELDMAT), mature 4/28/1997, coupon rate 6.75%, price at
settlement 88.375, all interest paid at maturity.

Nonetheless, thanks again, Stephen.


----- original message -----

 

[JoeU2004]

The formula for YIELDMAT is

Thanks for that. Where did you find this? If the YIELDMAT help page, what
version of Excel are you using?

Anyway, I think the formula is essentially the same as the one that Stephen
posted. Terms are multiplied by 100/100, and the use of B (days in a years,
depending on the calendar "basis") is replaced with the use of YEARFRAC.

Unless the use of YEARFRAC makes a material difference -- I don't think it
does -- I think my analysis of Stephen's formula applies to yours as well.

In a nutshell, for the example on the YIELDMAT help page, I don't think the
formula will closely match the YIELDMAT result unless I swap the par and
price terms in your formula. The par value is not clearly identified in
your formula; it is the 100 in "100 + DIM*..." in the numerator.

But even with that correction (?), I don't believe the formula works for
very long-term bonds, such as my example of a 31-year bond, which I
described in my response to Stephen.

Forgive me if I'm FOS because I am not actually trying your formula. I did
work extensively with Stephen's formula, and as I said, I believe the two
formulas are mathematically equivalent. (Am I wrong?)

Nonetheless, thanks again. And I am still interested in where you found
this formula. I cannot find anything in my searches :-(.


----- original message -----

Hi Joe,

The formula for YIELDMAT is

1 100 + DIM x rate x 100
___ x _______________________
DSM (price + DIS x rate x 100) -1

where

DIM {Days from issue to maturity}, calculated as
YEARFRAC(Issue, Maturity, Calendar_Type)
DIS {Days from issue to settlement}, calculated as
YEARFRAC(Issue, Settlement, Calendar_Type)
DSM {Days from settlement to maturity}, calculated as
YEARFRAC(Settlement, Maturity, Calendar_Type)


therefore, YIELDMAT =

(1/YEARFRAC(Settlement, Maturity, Calendar_Type))*((100+YEARFRAC(Issue,
Maturity, Calendar_Type)*Rate*100)/(Price+YEARFRAC(Issue, Settlement,
Calendar_Type)*Rate*100)-1)

Using the example from Excel help, Excel calculates YEARFRAC:

=YEARFRAC(A2,A3,0) = 0.633333333

OR

=((A3-A2)-5)/360 = 0.633333333


Because March, May, July, August, and October have 31 days; 5 days were
subtracted from the number of days between the start date and end date
before
dividing by 360 for calendar type 0 {30-day months, 360-day years,
American
method}.

If calendar type 1 {Actual months, actual years} were used; year 2008 was
a
leap year with 366 days so YEARFRAC then subtracts start date from end
date
to get the number of days which would then be divided by 366 instead of
365.

YEARFRAC=(A3-A2)/366 for calendar type 1 {Actual months, actual
years}

A B
1 Data Description
2 15-Mar-08 Settlement date
3 3-Nov-08 Maturity date
4 8-Nov-07 Issue date
5 6.25% Percent semiannual coupon
6 100.0123 Price
7 0 30/360 basis (see above)


--
If this post helps click Yes
---------------
Peggy Shepard

I cannot make sense of the YIELDMAT results, no matter what definition of
YTM that I try. (There seems to be more than one.)

It would help me if someone posted the math formula or algorithm that
YIELDMAT uses.

And it would help me if someone would show how the YIELDMAT example in
Excel
2003 help page is computed using that math formula or algorithm.

Some points to note in the help page description:


1. The synopsis at the top says that interest is presumed to be paid at
maturity, not at the coupon frequency.

I think that should impact the YTM (IRR) computation. But in one
example
that I tried [*]), YIELDMAT came close (but not that close) to the result
of
my reverse-engineered method of computing YTM only when I assume a
regular
cash flow of coupons.

On the other hand, I cannot even come close to the results of the help
page result, no matter what frequency of cash flows I assume.

[*] http://www.moneychimp.com/articles/finworks/fmbondytm.htm .


2. The YIELDMAT example suggests that the units of the YIELDMAT rate
parameter is "percent semiannual coupon".

I am not sure how that relates to an annual rate.

Moreover, since that requirement is not stated in the synopsis, I
wonder
if the example annotation is simply incorrect.


3. The example uses the 30/360 mode ("basis"), not the actual/365 mode
which
I understand better.

If it makes the explanation easier, I would not mind switching the
help
page example to actual/365 (mode 3). In that case, YIELDMAT result is
6.09636299211303%, which you can round to Percent with 4 decimal places
or
more, as the example does.

But I would also like to understand how 30/360 should be computed;
that
is, how it affects the math formula or algorithm.


4. With respect to the 30/360 mode, I would like to understand how the
maturity date of 11/3/2008 might have been determined.

Of course, it could be arbitrary. But my curiosity is piqued by the
fact
that it is not 11/8/2007 plus 360 days, but that date plus 1.

I wonder if there is some market convention for determining the
one-year
maturity date when using the 30/360 mode.

And I wonder if/how that affects the YIELDMAT mathematical or
algorithmic computation when using the 30/360 mode.


5. I do not understand why the YIELDMAT implementation does not tolerate
settlement date equal to issue date.

I understand that they are usually not equal in the real world. But
it
would simplify comparisons with reverse-engineered solutions.

Note: The help page does not identify this limitation. I stumbled
upon
it empirically.

 

[Stephen Bye]

Open Office have documentation at:
http://wiki.services.openoffice.org...lc:_Derivation_of_Financial_Formulas#YIELDMAT
or you can search online for Microsoft's documentation by looking for
"Equations for Financial Functions for Microsoft Excel"
or just:
"XE0962.doc"

I can send you a copy of the Word document if you like.