Formulas and Functions Help
- Welcome
- Intro to formulas and functions
-
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- XIRR
- XNPV
- YIELD
- YIELDDISC
- YIELDMAT
-
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MAXIFS
- MEDIAN
- MIN
- MINA
- MINIFS
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
YIELDMAT
The YIELDMAT function returns the effective annual interest rate for a security that only pays interest at maturity.
YIELDMAT(settle, maturity, issue, annual-rate, price, days-basis)
settle: A date/time value or date string representing the trade settlement date, usually one or more days after the trade date. settle must be after the date specified for issue.
maturity: A date/time value or date string representing the date when the security matures. maturity must be after the date specified for settle.
issue: A date/time value or date string representing the date the security was originally issued.
annual-rate: A number value representing the annual coupon rate or stated annual interest rate of the security used to determine periodic interest payments. annual-rate must be greater than 0, and is entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%).
price: A number value representing the cost of the security per $100 of par value. price is calculated as purchase price / face value * 100 and it must be greater than 0.
days-basis: An optional modal value specifying the number of days per month and days per year (days-basis convention) used in the calculations.
30/360 (0 or omitted): 30 days in a month, 360 days in a year, using the NASD method for dates falling on the 31st of a month.
actual/actual (1): Actual days in each month, actual days in each year.
actual/360 (2): Actual days in each month, 360 days in a year.
actual/365 (3): Actual days in each month, 365 days in a year.
30E/360 (4): 30 days in a month, 360 days in a year, using the European method for dates falling on the 31st of a month.
Example |
|---|
Suppose you are considering the purchase of a hypothetical security. The security was originally issued December 14, 2008 (issue), your purchase will settle May 1, 2009 (settle), the security matures at 100 per $100 of face value (price is 100) on June 30, 2015, and the security pays interest only at maturity at an annual rate of 6.5% (annual-rate) calculated based on a 30/360 days basis (days-basis). The security is being offered at 99.002 (price). =YIELDMAT(“05/01/2009”, “06/30/2015”, “12/14/2008”, 0.065, 99.002, 0) returns approximately 6.565%, the yield to maturity based on the assumptions given. The yield to maturity will be higher than the annual interest rate if the security is priced below the maturity price and lower than the annual interest rate if the security is priced above the maturity price. |