PRICEMAT Excel Formula

Author Zaheer    Category Formulas     Tags ,

PRICEMAT Excel function can return the price per 100 dollar face-value of the security which pays interest when it matures. In some cases, the function may not be available and can return the error #NAME. In these cases an add-in Analysis ToolPak should be installed and loaded. The path of the add-in may be different in different versions of the Excel program, especially in the 2007 and later versions.

PRICEMAT function syntax is as “PRICEMAT (settlement,maturity,issue,rate,yld,basis)” where the opinion denotes the followings. Settlement denotes the settlement security date. The settlement date of the security is that date when the buyer traded the security. The security is traded after the date of issue. Maturity denotes the maturity date of the security. It is the date when the security will expire. Issue denotes the issue date of the security. It is expressed as serial date number. Rate denotes the interest rate of the security at the date of issue. Yld denotes the annual yield of the security. Basis denotes the day count basis type to use.


If the basis is omitted or zero, then the day count basis will be US (NASD) 30/360; if it is 1, then the day count basis will be Actual / actual; if it is 2 then the day count basis will be Actual / 360; if it is 3 then the day count basis will be Actual / 365; if it is 4 then the day count basis will be European 30 / 360. All the syntax rules should be followed properly.

Some remarks should be remembered when the function will be used. For the use in calculations dates are stored as sequential serial number in the Microsoft Excel. The date 1 January 1900 is the serial number 1, and the date 1 January 2008 is the serial number 39448. In the Macintosh system Microsoft Excel uses a different system. To understand the settlement date this example can help you. Think that a thirty-year bond was issued on the date 1 January 2008, and six months later a buyer purchased it. Here the issue date is the 1 January 2008, and the settlement date is 1 July 2008. The bond will mature after 30 years; hence the maturity date will be 1 January 2038.

Basis, issue, maturity, and settlement are shaped to integers. If the issue, maturity, or settlement is invalid date, then the function PRICEMAT will return an error value. If yld < 0 or rate < 0, the function will return an error value. If basis > 4 or basis < 0, the function will return an error value. PRICEMAT will also return an error value if maturity ? settlement. The formula that PRICEMAT follows is available in the Microsoft site. All the users should use any function with proper attention, the syntax is very important. To learn more about the function and view more examples you can go to the help files of the Excel program; or you can visit the Microsoft site.

Post comment