{"id":5281,"date":"2015-12-29T16:07:52","date_gmt":"2015-12-29T21:07:52","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=5281"},"modified":"2015-12-30T08:20:47","modified_gmt":"2015-12-30T13:20:47","slug":"calculating-annual-rate-of-return-of-a-stock-index-in-excel","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2015\/12\/29\/calculating-annual-rate-of-return-of-a-stock-index-in-excel\/","title":{"rendered":"Calculating annual rate of return of a stock index in Excel"},"content":{"rendered":"<p>I use what is below to calculate a total rate of return over a period of time.  This is most useful when computing compounded returns for an individual stock, or an index such as the S&#038;P 500&#8230;<\/p>\n<pre>\r\nPublic Function ror(pv As Currency, fv As Currency, p As Double) As Double\r\n  Dim l As Double\r\n  Dim i As Double\r\n  i = 0.1\r\n  l = pv\r\n  Do While l < fv\r\n    l = pv * ((1 + i) ^ p)\r\n    i = i + 0.00001\r\n  Loop\r\n  Do While l > fv\r\n    l = pv * ((1 + i) ^ p)\r\n    i = i - 0.00001\r\n  Loop\r\n  ror = i\r\nEnd Function\r\n<\/pre>\n<p>You can then download historical prices, such as the following to get historical closing prices for the S&#038;P 500&#8230;<\/p>\n<p>http:\/\/real-chart.finance.yahoo.com\/table.csv?s=%5EGSPC&#038;d=11&#038;e=29&#038;f=2015&#038;g=d&#038;a=0&#038;b=3&#038;c=1950&#038;ignore=.csv<\/p>\n<p>If you setup the following in a given cell for the formula, it will produce the average growth rate for the time period requested.  For example, this will produce the rate for the last ten years when the closing prices are sorted in closing date order&#8230;<\/p>\n<p>=ror(B4,B2515,10)<\/p>\n<p>&#8230;with the output below&#8230;<\/p>\n<p><img alt='' class='alignnone size-full wp-image-5282 ' src='http:\/\/appcrawler.com\/wordpress\/wp-content\/uploads\/2015\/12\/img_5682f4818f178.png' \/><\/p>\n<p>You can also use what is below as a simple cell formula to get the same rate of return&#8230;<\/p>\n<p>=(B2515\/B4)^(1\/10)-1<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I use what is below to calculate a total rate of return over a period of time. This is most useful when computing compounded returns for an individual stock, or an index such as the S&#038;P 500&#8230; Public Function ror(pv&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2015\/12\/29\/calculating-annual-rate-of-return-of-a-stock-index-in-excel\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[67],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/5281"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=5281"}],"version-history":[{"count":6,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/5281\/revisions"}],"predecessor-version":[{"id":5288,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/5281\/revisions\/5288"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=5281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=5281"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=5281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}