DURATION, YIELD, PRICE for monthly frequency (12)

Originally Posted by jcc3508

I have tried it but it doesn't work, it brings a "#NUM!" result.
I have no idea why I can't upload attachments in here but I used the following values:

Bonds Settlement Maturity Coupon Rate Price Frequency Day Count
Bond A 1/9/2015 1/9/2025 4.50% 100.95 12 1
Bond B 1/9/2015 1/9/2025 4.50% 100.95 4 1

I couldn't calculate the YIELD for the "Bond A" which has a frequency of 12, but I was able to calculate the yield for Bond B, which has a frequency of 4.

Originally Posted by jcc3508

Anyone has an idea on whether this could be done?

You neglect to show us how you call YIELD exactly. I assume it is something like:

=YIELD(A2,A3,A4,A5,100,A6,A7)

where:
A2: 1/9/2015
A3: 1/9/2025
A4: 4.50%
A5: 100.95
A6: 4 [or 12]
A7: 1

When A6=12, YIELD returns a #NUM error because the help page states:

"If frequency is any number other than 1, 2, or 4, YIELD returns the #NUM! error value."

In other words, YIELD supports only annual, semi-annual and quarterly coupons.

Since YIELD is the IRR of the cash flows, in this simple case, we can approximate the YIELD with the following formula:

=RATE(A6*(YEAR(A3)-YEAR(A2)),100*A4/A6,-A5,100)*A6

That assumes that maturity date minus settlement date is exactly 1 or more years. That is, the maturity date is an anniversary of the settlement date.

Since you specify the actual/actual day count basis (1), technically we should use XIRR instead of RATE. That would require that we enumerate the cash flows in a table [1].

But ironically, when A6=4, the RATE formula result differs from YIELD by an infinitesimal amount (7.25E-15), whereas the XIRR formula result differs by about 0.0693% [2].

(I can only speculate why. TBD.)


-----
[1] Set-up for XIRR:
B1: =A2
C1: =-A5
B2: =EDATE($B$1,(ROW()-ROW($B$1))*12/$A$6)
C2: =100*$A$4/$A$6
Copy B2:C2 down through B41:C41 for A6=4 and through B121:C121 for A6=12
Change last formula in column C to:
=100*$A$4/$A$6 + 100
Calculate:
=XIRR(C1:C121,B1:B121) [2]
formatted as Percentage. That assumes rows B42:C121 are empty for A6=4.

[2] [Errata] The primary reason for the difference between XIRR and YIELD is: XIRR returns an effective (compounded) annual rate, whereas YIELD appears to return a nominal (simple) annual rate. I don't know which is "right" insofar as how bond yields should be calculated conventionally (actual practices). But the comparable way to use XIRR is:

=((1+XIRR(C1:C121,B1:B121))^(1/A6) - 1) * A6

Then the difference is only about 0.003136%. Other reasons for the difference:

1. XIRR calculates the annual rate by compounding by days divided by 365. Apparently, YIELD annualizes the periodic rate.

2. I use EDATE to calculate coupon dates. The comparable method is:

=$B$1 + (ROW()-ROW($B$1))*($A$3-$A$2)/($A$6*(YEAR($A$3)-YEAR($A$2)))

3. With the correction in #2, XIRR truncates to a date (i.e. whole days), whereas apparently, YIELD effectively uses fractional dates.