{"id":1148,"date":"2019-05-23T17:08:32","date_gmt":"2019-05-23T14:08:32","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=1148"},"modified":"2019-05-23T17:08:37","modified_gmt":"2019-05-23T14:08:37","slug":"how-to-use-the-financial-functions-accrint-accrintm","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-financial-functions-accrint-accrintm\/","title":{"rendered":"How to use the Financial Functions ACCRINT, ACCRINTM"},"content":{"rendered":"\n<p>Excel provides us with 2 functions for calculating accrued interest. <strong><a rel=\"noreferrer noopener\" aria-label=\"ACCRINT (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/the-accrint-function\/\" target=\"_blank\">ACCRINT<\/a><\/strong>, <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-accrintm-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"ACCRINTM (opens in a new tab)\">ACCRINTM<\/a><\/strong>.<\/p>\n\n\n\n<p>But what exactly is accrued\ninterest?<\/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\/-k4UNa7YkCY?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\/ACCRINT.ACCRINTM.xlsx\">ACCRINT.ACCRINTM<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/05\/ACCRINT.ACCRINTM.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>Accrued interest is the interest\nthat has been earned, but not yet been paid by the bond issuer, since the last\ncoupon payment. Because the security hasn&#8217;t expired or the next payment is not\nyet due, the owner hasn&#8217;t officially received the money. If he or she sells the\nsecurity, accrued interest should be added to the sale price.<\/p>\n\n\n\n<p>So in our example we see all the\ninformation of a bond we bought. The previous coupon payment was a few months\nearlier. <\/p>\n\n\n\n<p>We have two scenarios for this\nbond. One that pays periodically and one that pays at maturity.<\/p>\n\n\n\n<p>We have calculated the price in\nboth of them using the Price functions. Take note here that the price functions\nreturn the per 100$ value so we multiplied this result by 100 to have the per\n10000$ value which is the par value of our investment.<\/p>\n\n\n\n<p>The par value is the face value\nof the investment, which is also called the principal of the investment.<\/p>\n\n\n\n<p>In cell B14 and C14 we have\ncalculated the clean price which is the price without the accrued interest.\nBelow them we calculated the accrued interest and then the dirty price which is\nthe total price of the bond, including accrued interest.<\/p>\n\n\n\n<p>We have used both of the accrued\ninterest function to achieve this. One for the bond that pays periodically, and\nthe other for the one that pays at maturity.<\/p>\n\n\n\n<p>To see how exactly we did this,\nlet\u2019s go to the next sheet where we have another bond for sale.<\/p>\n\n\n\n<p>As we can see the accrued\ninterest is yet to be calculated, thus the dirty price is still equal to the\nclean price.<\/p>\n\n\n\n<p>We go to cell B15 to find out\nwhat the accrued interest for the bond that pays periodically is.<\/p>\n\n\n\n<p>We type the name of the\nfunction, followed by the issue date and the first interest date. This is where\nwe have to mold the function to our demands.<\/p>\n\n\n\n<p>The ACCRINT function of excel, calculates\nthe amount of interest that has been earned since the day that the bond was\nissued. This would not be correct, or fair to the buyer, since we would\ncalculate in the accrued interest, money we had already received, up to the\ndate of the settlement.<\/p>\n\n\n\n<p>In our example we want to sell\nthe bond in February of 2016 which means that we have already received all the\ncoupon payments previous to that date. So the accrued interest should be\ncalculated from the date of the previous coupon payment, which is the last we\nhave received in January of 2016 up to the date of the settlement.<\/p>\n\n\n\n<p>To mislead excel into doing our\nbidding we set as both issue date and first interest date of the bond the date\nof the previous coupon payment. This way Excel will think that the bond was\nissued on the date of the previous coupon payment and it will correctly\ncalculate the accrued interest from that date to the date of the settlement.<\/p>\n\n\n\n<p>So we select for the first two\narguments cell B6. Then the date of settlement which is the date our sale is\ngoing to take place, the rate in cell B9, the par value of the investment in\ncell B3 and the frequency of payments from cell B10. <\/p>\n\n\n\n<p>The next argument, basis, is\noptional and it helps us choose the type of day count to use. We will always\nuse the default 0 value. And then the last argument, which is optional as well,\nis a logical value that specifies the way to calculate the total accrued\ninterest when the date of settlement is later than the date of first interest.\nA value of TRUE returns the total accrued interest from issue to settlement. A\nvalue of FALSE returns the accrued interest from first interest to settlement.\nIf you do not enter the argument, it defaults to TRUE. In our case both methods\nprovide us with the same result since our issue date and first interest date\nare the same.<\/p>\n\n\n\n<p>So we can omit both of the two\nlast arguments. By pressing Enter we can see the accrued interest and notice\nthat the total price has changed accordingly.\n\nThen we have to calculate in cell C15 the\naccrued interest for a bond that pays at maturity. We use the appropriate\nfunction ACCRINTM followed by its arguments. Here we don\u2019t need to trick excel\nso we use the proper issue date from cell B4, and then the settlement date, the\nrate and the par value. We again omit the basis argument and we have our result\nand the total sell price of the bond in both scenarios.\n\n\n\n<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Excel provides us with 2 functions for calculating accrued interest. ACCRINT, ACCRINTM.<br \/>\nBut what exactly is accrued interest?<\/p>\n","protected":false},"author":2,"featured_media":1150,"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":[116,117,115],"class_list":["post-1148","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-accrint","tag-accrintm","tag-financial"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/05\/tutorial_accrint.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-iw","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/1148","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=1148"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/1148\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/1150"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=1148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=1148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=1148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}