If you have a stock portfolio (or simply have a list of companies you like to follow), Excel makes it easy to create a return on investment calculator and to pull in all kinds of useful stock tracking information from the internet. In this article, you’ll learn how to create a return on investment calculator in Excel and, you’ll also receive a free template download at the end. Once you know how to create your own return on investment calculator, you can tweak the template to fit your individual financial needs. Note that this template will only work in Excel 2013 and later versions.
Creating a Return on Investment Calculator
The basic structure of this template is built around the WEBSERVICE function, which makes creating a macro to download the data from the internet (as was necessary in older versions of Excel) unnecessary. To build your return on investment calculator, you’ll use the WEBSERVICE function to call the Yahoo Finance API, which will pull the appropriate stock data into your spreadsheet. The formula looks like this:
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&[Ticker Reference]&"&f=[Yahoo Tag]"))
It’s usually easier to just set the Ticker Reference to a particular cell (such as A2). This allows you to quickly change (or copy) the formula without having to edit the formula itself. The Yahoo Tag refers to an alphanumeric tag code that tells the formula which data elements to download. You can find a list here.
So, if you wanted to download the last trade price of Coca-Cola, you would place the Ticker Reference (KO) in cell A2, and use the Tag Code “l1”. Your formula would look like this:
When you input the formula, Excel will download the last trade price of Coca-Cola and input it into the cell that contains the formula. You would simply repeat this process for however many metrics you want to track. To create your return on investment calculator, you’ll simply need the purchase price and the last trade price to determine your return.
Get Your Free Template!
To see what this looks like in practice, download a free copy of our Return on Investment Calculator. The calculator is currently set up to track 5 different stocks. Simply replace the data in the yellow with the information from your own portfolio and hit the Refresh button to download the latest data. The template also includes several charts that allow you to track the performance of your portfolio visually.
You can also sign up for our free email newsletter, where you’ll receive free Excel tips and tricks right in your inbox! You’ll also receive exclusive discounts on our upcoming training classes. Sign up here!