{"id":1156,"date":"2019-05-23T17:29:15","date_gmt":"2019-05-23T14:29:15","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=1156"},"modified":"2019-05-23T17:29:20","modified_gmt":"2019-05-23T14:29:20","slug":"how-to-use-the-financial-functions-price-pricedisc-pricemat","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-financial-functions-price-pricedisc-pricemat\/","title":{"rendered":"How to use the Financial Functions PRICE, PRICEDISC, PRICEMAT"},"content":{"rendered":"\n<p>In order to be able to calculate the price per $100 face value we pay for an investment, Excel provides us with 3 tools.<\/p>\n\n\n\n<p>The functions <strong><a rel=\"noreferrer noopener\" aria-label=\"PRICE (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/the-price-function\/\" target=\"_blank\">PRICE<\/a><\/strong>, <strong><a rel=\"noreferrer noopener\" aria-label=\"PRICEMAT (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/the-pricemat-function\/\" target=\"_blank\">PRICEMAT<\/a><\/strong>, <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-pricedisc-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"PRICEDISC (opens in a new tab)\">PRICEDISC<\/a><\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/t43I2GLiNvU?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/05\/PRICE.PRICEMAT.PRICEDISC.xlsx\">PRICE.PRICEMAT.PRICEDISC<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/05\/PRICE.PRICEMAT.PRICEDISC.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>In the current worksheet we can see the details of a\nsecurity.<\/p>\n\n\n\n<p>The issue date is the date the security was issued.<\/p>\n\n\n\n<p>The settlement date is the date after the issue date when\nthe security is traded to the buyer.<\/p>\n\n\n\n<p>The maturity date is the date when the security expires.<\/p>\n\n\n\n<p>In the current worksheet we can see we have already\ncalculated these prices in cells B13 to B15.<\/p>\n\n\n\n<p>We have calculated the price for three scenarios. For a\nsecurity that pays periodic interest, for a security that pays at maturity and\nfor a discounted security.<\/p>\n\n\n\n<p>We see that at the discounted security we pay less money for\neach 100$ of face value.<\/p>\n\n\n\n<p>This doesn\u2019t necessarily mean it is the most profitable\nchoice. To determine which scenario is the most profitable we have to look at\nthe yield values. In this case it happens that the discounted security is the\nway to go since it has higher yield.<\/p>\n\n\n\n<p>It is time to see how these functions work.<\/p>\n\n\n\n<p>In this worksheet we have a different investment plan. We\nhave to calculate the price for each of the three different scenarios.<\/p>\n\n\n\n<p>The first one assumes that the security pays periodic\ninterest. So in cell B13 we type the function PRICE followed by the settlement\ndate, in B4, the maturity date, in B5, the annual coupon rate in B6, the yield\nin B9, the redemption value per 100$ of face value in cell B8 and finally the\nfrequency of payments per year, cell B7.<\/p>\n\n\n\n<p>The last argument, basis, is optional and it helps us choose\nthe type of day count to use. Since we will always use the default 0 value, we\ncan omit it.<\/p>\n\n\n\n<p>Next we will calculate the price for the scenario that\nassumes that the security pays at maturity. We type in cell B14 the function,\nfollowed by the settlement, maturity and issue dates. Then we select the rate\nand finally the yield which in this scenario we can find in cell B10. We\nomitted the basis argument as before.<\/p>\n\n\n\n<p>Lastly we will find out what the price per $100 of face\nvalue is, if the security is a discounted one.<\/p>\n\n\n\n<p>We type in cell B15 the name of the function. As before we\nselect the settlement and maturity dates, followed by the discount rate and the\nredemption value per 100$ of face value.<\/p>\n\n\n\n<p>Take note that here we use the discount rate in cell B12\ninstead of the Yield for the discounted security in cell B11.<\/p>\n\n\n\n<p>This time also, we omitted the basis argument. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>In order to be able to calculate the price per $100 face value we pay for an investment, Excel provides us with 3 tools.<br \/>\nThe functions PRICE, PRICEMAT, PRICEDISC.<\/p>\n","protected":false},"author":2,"featured_media":1158,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[115,119,121,120],"class_list":["post-1156","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-financial","tag-price","tag-pricedisc","tag-pricemat"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/05\/tutorial_price.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-iE","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/1156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=1156"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/1156\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/1158"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=1156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=1156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=1156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}