Manuals/calci/YIELD
YIELD(SettlementDate, MaturityDate, Rate, Price, Redemption, Frequency, Basis)
where,
SettlementDate - represents the settlement date.
MaturityDate - represents the maturity date.
Rate - represents the rate.
Price - represents the price per $100 face value.
Redemption - is the redemption value per $100 face value.
Frequency - the number of coupon payment per year.
Payments frequency
annual 1
semiannual 2
quarterly 4
Basis - type of day count basis
Basis Day count basis
0 or omitted US 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360
It returns the yield that pays periodic interest.
Formula:-
If 1 coupon period or less than Redemption
YIELD={[(Redemption/100)+(Rate/Frequency)]-
[(Par/100)+((B/C) x (Rate/Frequency))]} / [(Par/100)+((A/C) x (Rate/Frequency))] x( Frequeny x C)/N
where,
B - number of daysfrom the beginning of the period to the settlement date.
C - number of days in the coupon period.
N - number of days from settlement to redemption value.
If Basis < 0 or Basis > 4 , YIELD returns the #ERROR.
If Rate < 0 or Price <= 0 or Redemption <=0, it returns the #ERROR.
If SettlementDate >= MaturityDate, YIELD returns #ERROR.
If Frequency other than 1, 2 or 4 returns the #ERROR.
YIELD
Lets see an example in (Column2, Row1)
=YIELD(R1C1, R2C1, R3C1, R4C1, R5C1,R6C1,R7C1)
YIELD returns 47.950201.
Consider another example
=YIELD(date(2007,1,1), date(2008,2,20), "15%", 89.25,100,1,5)
It returns #ERROR(Basis=5).
| Column1 | Column2 | Column3 | Column4 | |
| Row1 | 1/2/2008 | 47.950201 | ||
| Row2 | 2/20/2008 | |||
| Row3 | 12% | |||
| Row4 | 56.268 | |||
| Row5 | 100 | |||
| Row6 | 1 | |||
| Row7 | 4 |
