How to use the PRICEMAT function
What is the PRICEMAT function?
The PRICEMAT function calculates the price per $100 nominal value of a bond that pays interest at maturity.
What is the nominal value for a bond?
The nominal value, also called the par value or face value, is the amount a bond is issued and redeemed for by the bond issuer. It's the stated value of the bond.
What is interest?
Interest refers to the periodic coupon payments made by a bond issuer to bondholders over the bond's lifetime. However, the PRICEMAT function works for bonds that pay interest at maturity.
What is bond maturity?
Bond maturity is the date when a bond's term ends, at which point the issuer must repay the bond's par value and any final interest due to bondholders. A 30-year bond issued today will have a maturity date 30 years from now.
PRICEMAT function Syntax
PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
PRICEMAT function Arguments
| settlement | Required. The bond's settlement date. |
| maturity | Required. The bond's maturity date, in other words, when it expires. |
| issue | Required. The bond's issue date. |
| rate | Required. The bond's interest rate at date of issue. |
| yld | Required. The bond's annual return. |
| [basis] | Optional. Day count basis.
0 (zero) -Â US (NASD) 30/360 (default value) 1 - Actual/actual 2 - Actual/360 3 - Actual/365 4 - European 30/360 |
What is the bond's settlement date?
A bond's settlement date is the date when the trade is finalized and the bond is delivered to the buyer in exchange for payment to the seller. Settlement for US treasury bonds is usually T+1 day.
What is the bonds issue date?
A bond's issue date is the original date when the bond was first offered and sold to investors on the primary market by the issuing entity. The bond's coupon payment schedules are aligned with the issue date.
What is the bond's interest rate at date of issue?
The interest rate at a bond's issue date, also called the coupon rate, is the annual interest rate the bond will pay throughout its lifetime based on the bond's par value. A 5% coupon rate means the bond will pay 5% of par annually.
What is the bond's annual return?
A bond's annual return is the gain or loss in value over a one year holding period plus any interest income. It factors in both bond price appreciation/depreciation and the interest coupons received.
PRICEMAT function example
Formula in cell C10:
PRICEMAT function not working
Settlement, maturity, issue, and basis are shortened to integers.
The PRICEMAT function returns:
#NUM! error value if
rate < 0
yld < 0
basis < 0
basis > 4
settlement >= maturity
#VALUE! error value if
settlement, issue or maturity is not a valid date. Use the DATE function in the PRICEMAT function to avoid errors. Example DATE(2010, 11, 5) is 5th November, 2010.
How is the PRICEMAT function calculated?
The following formula demonstrates how the PRICEMAT function calculates.
| B | Days in year. |
| DSM | Days from settlement to maturity. |
| DIM | Number of days from issue to maturity. |
| A | Number of days from issue to settlement. |
Functions in 'Financial' category
The PRICEMAT function function is one of many functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form