template Archives - Page 3 of 7

Creating a Stock Screener in Excel

April 27, 2021

by howtoexcel with No Comment advanced functions investing stocks template vbafree

A stock screener allows you to filter through stocks that meet your investment criteria. It can help you find undervalued stocks and great dividend investments. But sometimes it can be cumbersome to always go back to a website and re-apply filters, even if you save them. In this post, I’ll go over how you can populate a list of stock data into Excel and then run your own filters on it, and thus, creating a screener you can easily access from within your own spreadsheet.

Step 1: Populating the list

The one thing you’ll want to do before you can create the screener in Excel is to download an array of stock data from a database. Personally, I like using Barchart because it has lots of useful information on there and you can get a wide range of data, and it is easily downloadable into an Excel format. It lets you do five free downloads each day and you can download 1,000 rows at a time. That’s thousands of stocks you can add. Using that in conjunction with the STOCKHISTORY function, and you can create a pretty versatile template. After all, since data like earnings, dividends, and other fields won’t often change, downloading a snapshot from Barchart once a month or even less frequently shouldn’t be a big issue. You can obviously use other databases but I’m going to use a free example for the purpose of this post.

On Barchart, I’ve customized the fields I want to use for my downloads, and this allows me to re-use them again and make subsequent downloads easier. To keep it simple, I am going to download just the top 1,000 North American stocks based on market cap. This is what my download looks like in Excel:

Now that the data is loaded, the next step is to create the layout.

Step 2: Organizing the stock screener and setting up the fields

I find it most convenient to always put any inputs on a spreadsheet on the top of the page, and the results below. This way, you can freeze panes to make it easy to scroll through all the rows while seeing your selections.

To start, I will create a field for each major field I have downloaded. After formatting some of my values, this is how my screener looks thus far:

Off to the right, I’ve added a date field because I am going to utilize Excel’s STOCKHISTORY function to pull in the price. This will allow me to calculate the current price to earnings ratio without having to download it from the screener as that multiple will change every day based on the stock’s price.

When downloading so many stock prices, it may take a while for the formulas to update. But once they are loaded, then I can calculate the P/E ratio by just taking the stock price and dividing it by the earnings per share.

Step 3: Creating the formulas to evaluate the criteria

The part that will take the most time is to now evaluate each of the criteria to determine if a stock meets all of it and whether it should be included in the results. Rather than trying to do this in one large formula, I’m going to break this up into one formula per field. I’m going to name these fields exactly the same so that it is easy to reference them.

For the first criteria, Market Cap, my formula looks as follows:

=IF(E2=””,TRUE,IF(D2=”>”,C9/1000000>E2,C9/1000000<E2))

D2 is where I have the dropdown for the > or < symbol and E2 is the value that I want to filter for market cap. C9 is the first row of data. My goal here is to evaluate to either a TRUE or FALSE value. I also divide the value in C9 by 1,000,000 just to make it easier to filter the market cap by millions.

For the % change calculations, I will do a similar calculation. Except this time I don’t need to divide by 1,000,000 and so it looks a lot simpler:

=IF(E3=””,TRUE,IF(D3=”>”,D9>E3,D9<E3))

D3 is my > or < dropdown while E3 is the percent change I am entering. Since I will enter a percentage here, I don’t need to make any special calculations. This is the same format that I will follow for the other fields.

Once I have set up all my calculations for the various criteria, I’m going to add one column that will check to see if the stock meets all of them. This is a simple formula where I can multiple all the values. A TRUE value will compute as 1 and a FALSE will be 0. And so even if there is one FALSE value, the entire result will return FALSE and not meet the criteria. The formula looks as follows:

=(T9*U9*V9*W9*X9*Y9*Z9*AA9*AB9*AC9*AD9*AE9*AF9*AG9)>0

Step 4: Converting it into a table

The final step is a simple one but it’s also important to make this sheet work smoothly. Select anywhere on the data set and on the Insert tab, click on Table. Hit OK and now you should see Excel’s default table applied to your data.

The reason for converting this into a table is that now we can apply slicers to it. And really, only one is needed here. If you go to the Table Design tab, there is a button to Insert Slicer. Click on it and select the one for the field that checks all the other criteria. In my example, it is called Criteria Met.

After hiding all the criteria fields, changing some of the formatting and adding the slicer, this is now how my screener looks like:

Stock screener after applying a table slicer to it.

The beauty of this stock screener is that by clicking on the TRUE button in the slicer, you are automatically refreshing the data in Excel and updating your filters based on the selections. All this is done without macros and it makes the screener easy to change with the press of a button.

You can download my completed template here. Please note that if you do not have STOCKHISTORY available on your version of Excel, some of the values will not populate.

If you liked this post on creating a stock screener in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Create a Form in Excel

April 7, 2021

by howtoexcel with No Comment find forms intermediate macros template

In a previous post, I covered how to add checkboxes. Now, I’m going to go a step further and show you how you can create a form in Excel from start to finish. And at the bottom of the page, you can download a file that you can use for your own custom forms. It will incorporate, list boxes, checkboxes, validation rules, and allow you to move the data onto a separate sheet. For now, let’s start from scratch.

Step 1: Determine the data you want and how it should be entered

The first step in creating a form in Excel is determining what information you want to collect. In this example, I’m just going to include name, address, city, state, email, a checkbox to confirm if it is okay to contact the person, a rating, and an area for comments. It is also important to determine how users should enter these values. While it’s easy to leave everything as text, that can make it difficult to ensure someone doesn’t enter invalid data. And if the data is not useful, it will defeat the purpose of the form.

Here are the types of inputs I’m going to use for my fields:

Name: Text
Address: Text
City: Text
State: List box
Email: Text
Contact confirmation: Checkbox
Rating: Radio button
Comments: Text

Next, let’s work on the form’s design.

Step 2: Designing the form and creating the inputs

One thing I did to help make the form cleaner from the beginning was to turn off gridlines. You can do that by going to the View tab and unchecking Gridlines under the Show group:

This will make your form look more like a form and less like a regular Excel sheet. Another thing you can do is in that same section, unselect the Formula Bar and Headings, which will add more white space and are unnecessary if someone is just filling in a form. However, you may want to save this for the end when your form is done.

Since an Excel form can come in all shapes and sizes, the one thing that may help you in the design process is to set every column to a width of 2. This way, it will be easier to maneuver in case one field needs to be bigger than another without having to try and force everything to be a similar length.

As for the input fields, there are a few things you will want to do:

Make sure they are long enough. A good way to test this is by entering a long value, or what you might think will be the longest value into each field and then adjusting its length so that everything displays correctly.
Assign a named range. This is useful to keep things organized and it will make it easier for you to refer back to the field later on if you only have to remember its name, as opposed to its cell reference.

Now, let’s move on to creating the fields in the Excel form. What you can do for text entries is to just add some outlining and highlighting to existing cells. A subtle light grey can be a good way to indicate that is an input value. And I’ll also add a border to help make these fields stand out. If you set the column width to 2, you’ll also need to merge the cells as needed.

For the State field, I’ll go back to the Developer tab where I will select the option for a List Box from the Form Control section — which is next to the Radio Button on the right. When in doubt, you can hover over each control to see what it is.

After creating the List Box, I need to populate the list plus link to a cell where the selected value should go. I’ll start with creating a range of cells for all 50 states and then assign a named range for them called StateList.

Then, I will set up a named range called StateNumber for the linked cell. Here is what the List Box control shows when I go into Format Controls and select the Controls tab:

Format control properties for a list box.

But this is not enough as the list box returns a number, not the state’s initials. I will need another cell to pull that in. I created a named range for State and here is what my sheet looks like:

List box displayed on an Excel spreadsheet.

In the list box, I selected MT, which returns a value of 26 in the StateNumber range. To extract the state’s initials, I need to use a formula to get that. Since I’m getting the data from one column, I’m just going to use the INDEX function. Here is what the formula in the State named range looks like:

=INDEX(StateList,StateNumber,1)

It is looking at the StateList and pulling out the row that relates to the StateNumber. Since MT is the 26th selection, that is the value that gets returned. So now my List Box is working correctly. What I like to do with these named ranges is to hide them so that the user doesn’t see all these intermediate steps. All it takes is to just move the List Box over top of these cells:

And just like that, the user only sees their selection and not the calculations afterwards. You could certainly use a drop-down list for states but I thought I would try something different and more user-friendly for this example.

Next, let’s go to the email field. This can be tricky because although you want this to be text, you also want to control what a user enters to avoid a possible error. You can’t guarantee the email will be correct but you can take steps to at least prevent some errors. The key here is going to be to create a data validation rule. There are two things that should be present in email addresses: the @ sign and a period. To create a data validation rule, select on the cell and click on Data Validation under the Data tab:

Selecting data validation rules in Excel.

There are many rules you can set up such as limiting the entry to fall within certain dates, making sure it is a whole number, or that it is from a drop-down list. But this situation is unique and will require a custom formula.

Creating a custom formula for data validation in Excel.

To check for both the period and the @ sign, I will need to use the FIND function and check that the value is a number (which means that it was found). Here’s how that looks inside of an AND function:

=AND(ISNUMBER(FIND(“.”,Email)),ISNUMBER(FIND(“@”,Email)))

Since I set the field to a named range of ‘Email’ it is easy to reference it without worrying about whether I have selected the right cell. If I put this calculation in the formula section, now you won’t be able to enter a value that doesn’t include both a period and an @ sign. In addition, you can also specify the error alert and determine what pops up if someone enters something different that violates these rules. However, that’s not necessary as they will get an error anyway.

Now, I’ll add the checkbox for the email. This again comes from Excel’s Developer tab and the Form Control section. Simply select the checkbox and set up a linked cell. If you want more details on this, refer to the link at the top of this post for a more detailed outline of how to add checkboxes. I have positioned the checkbox right below the email field:

Next, I’ll add some radio buttons to allow someone to leave a rating. These are useful if you want to specify a number. Here I will go back to the Developer tab and create some radio buttons and re-size them so they don’t take up much space. Unlike the other controls, you will want them to all have the same linked cell; the purpose of radio buttons is that there is only one selection. Here is how I added them, just below the numbers that they refer to:

The radio buttons will automatically increment on their own so if you don’t pay attention to what order you’ve added them in you may get some unexpected results.

Lastly, I will add a large comment box where people can leave detailed comments. This can just be a large merged cell that takes up more space.

But the one thing you will want to do is make sure that Wrap Text is selected so that the comment fits in the box. And you will probably want to align it so that it is in the top left corner of the cell:

Then, when I enter the text it looks correct:

Comment box filled in with top-left alignment and wrap text enabled.

Here is what my completed form looks like:

It looks good, but we are still not done. Something needs to happen with these inputs otherwise the information goes nowhere. Let’s go over that next.

Step 3: Storing the data from the form in an Excel sheet

If you are sending just a single form over for someone to enter data in, what you can do is create an output page that will link to these values. Since they are all named ranges, you can easily reference back to them as such:

=Name

In the above example, if you created a named range called Name for the first field, it will pull in the data from there. On an output sheet, you might have formulas and values that look like this:

In column B I am showing the formulas. You can keep this tab hidden if you want it out of sight. You can even go one step further and make them very hidden.

Not sure whether your fields should go horizontally or vertically? In most cases, you’ll actually want them going across the top. When in doubt, consider the number of fields you have versus how much data you will be entering into the sheet. If you will have dozens of results that you will need to populate (or more), you probably don’t want to be cycling through that many columns; rows are easier to scroll through and that’s why it will probably make more sense for the fields to go across the top.

Once you have your output tab set up, you can copy the values you get back from these forms and start populating a database.

But what if you are doing data entry and need to make these entries multiple times and need the data to push to the output tab automatically after each entry? This is where you will need to set up a macro and need a button to trigger this movement onto the output tab. If you’d like to see how that code might work or just want a ready-to-use file that you don’t have to mess around with, you can download this free template.

The template will grab the input values and based on the named ranges, it will populate them in the output tab once you click the Post button on the main page. If there isn’t a named range that matches to a header on row 1 on the output tab, the data just won’t get copied over. Give it a try!

One additional step you may want to consider is locking down the form in Excel to make sure people don’t accidentally move things around or delete any formulas. You can protect the workbook and the individual sheets do that. Click here for information on how to lock cells.

If you liked this post on how to create a form in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Play Corporate Bingo

March 4, 2021

by howtoexcel with No Comment business template

Are you stuck doing boring Zoom calls or have a boss that likes to say the same phrases over and over? I’ve got a great way for you to help pass the time at the office and make your meetings more interesting: play Corporate Bingo with your co-workers. This Excel-based template is easy to use and you can quickly hide it if necessary.

You can download it here.

How the game works

If you’ve played Bingo before then you know how to play this game as it works the same way. There are 24 squares (plus the free center square). To win, you need to mark off a row, column, or diagonal set of spaces. Although I have set the squares up as phrases, you could also use this Bingo card for actions. To the left of the Bingo card is where you can enter the values you want to use. I’ve already populated some of my most dreaded corporate phrases/words for you:

You will need to enter something for the center square as well as the others. When you make a new game, the other squares will be randomized. Here’s how the board looks:

If you’re in your meeting and someone says one of these terms, what you can do is double-click on the cells, and then there will be a red ‘X’ that marks that spot.

Once you have a line, whether it is across, down, or diagonally, it will highlight in red:

If you make a mistake or want to create a new game, use CTRL+SHIFT+N and the board will re-populate.

The values and formulas on the board are password-protected. This is to prevent accidentally overwriting the values on there, since they are automatically randomized each time.

Hiding the sheet

I made this game easy to hide in case someone walks past your desk or you want to keep it discrete. Using the shortcut of CTRL+SHIFT+Z, you can quickly hide the sheet. And if the game is hidden, using the same shortcut will unhide it again for you. The one caveat is you need to ensure that the sheet names remain unchanged.

Although you can’t change the sheet names, you can change the name of the Excel file to something other than Bingo.

If you liked this post on Corporate Bingo, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Excel Calendar Template

February 24, 2021

by howtoexcel with No Comment calendar template

Do you need a calendar in Excel to quickly write down your tasks or keep track of deadlines and appointments? With this free template, you can create a calendar within seconds for the month and year that you want. You can copy multiple tabs and create multiple months in advance. The template will also allow you to specify whether you want the day of the week to start on a Sunday or a Monday. If you would like to try it out, you can download it here.

How the template works

The template only has three areas where you need to make inputs. That includes the month, year, and when you want the week to start. To update the month, simply click on the dropdown in the month field, where you will be able to select from any of the 12 months in the year. You can also type in the month but if you make a typo, then you will get an error.

Next up, enter the year for the calendar, which is right next to the month. In this field, you can just enter in a number as a dropdown isn’t necessary here. Then, once you have selected a month and year, the calendar will automatically update based on your selections:

Calendar in Excel showing for January 2021.

By default, I have the calendar set up to start on a Sunday. But if you prefer for the week to start on a Monday, simply scroll over to the right-hand-side of the sheet where you will see a dropdown. There, you can change the selection and specify which day you want the week to start on:

A dropdown option determining which day of the week the calendar should start on.

If I change this to Monday, then my calendar will update again, this time shifting the dates:

The calendar adjusts after changing the day the week starts on.

If you need to create multiple months, you can simply copy the tab for the calendar over and make the selection for another month and year. Whether you need one, two, or a full 12 months, you can set this up easily with this template. This is a file that you can potentially use forever as you can simply adjust the month and year combinations as many times as you like. There are no limitations as to the number of times you can create, copy, or move tabs.

The template is also set up so that there are five cells for each day, one for each potential task or meeting that you need. If you need to squeeze more into there, what you can do is shrink the font down.

However, the sheet is locked down outside of areas where you can enter in data (including the inputs, and the cells below each day). This is simply to prevent people from accidentally overwriting formulas or otherwise causing the file not to function as intended.

If you liked this post and the free Excel calendar template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Road Trip Planner Template

February 7, 2021

by howtoexcel with No Comment template

Whether you’re going on a road trip soon or just want to start planning one, this free road trip planner template can help you with that. Not only can you plot up to 10 destinations, you can factor in breaks, select either a desired start or end time (even if your trip may span more than one day), make notes, and add any points of interest you want to mark along the way. The template will even generate a link for you in case you want to quickly pull up a search of hotels in the area.

If you’d like to give the template a try, download it for free, right here. Below, I’ll go over how it works.

Step 1: Set the initial start or end time

The very first option you’ll find in the road trip planner is whether you want to leave or arrive at a certain date and time. This will determine whether the road trip planner works backwards from your desired ending time or if it starts from your start time and counts forward.

Selecting the arrival or departure time.

Under the Option dropdown, you can select either Fixed Arrival or Fixed Start. Then, next to that cell, you’ll specify the start or end time, which can include the date — this is useful if your trip is overnight or will be a long one that may include a hotel stay.

Step 2: Enter your arrival locations, duration, break times, and any notes

Now comes the part where you enter the details of your trip. The fields in light grey are formulas and are locked. This is to prevent mistakes and avoid errors. Besides the starting departure location, all subsequent departures will automatically populate based on your last arrival location.

To enter the duration of the trip, make sure you enter it in the following format:

hours:minutes

For example, If a trip will take 30 minutes, you would enter 0:30. If it takes one hour and 20 minutes, the entry needs to be 1:20. This is also how you will enter the break times. The break is simply how long until you plan to resume traveling. And so if you’re staying overnight at a hotel, you might put in eight hours, or 8:00 for a break. The break time will add on top of your arrival time to determine your estimated departure time for your next destination.

If you want to mark places along the way you want to visit in your road trip or just make some notes, you can use these last two columns for this purpose. This might be a good place to mention where you want to fill up for gas.

Additional features

If you want to be able to quickly check the price of hotels in your arrival destinations, click on the the dropdown box after the notes column and select Y for whether you need a hotel:

Dropdown option asking whether a hotel is required.

Doing this will generate a link to TripAdvisor’s hotel page for the arrival location that you specified for each line. For example, if my first location was London and I specified Y for Hotel, I would have a link that I could click on to take me to the TripAdvisor page:

Link to click on a hotel in the arrival location.

It’s a quick way to get to that page and scan hotel availability and rates.

If you’ve got the latest version of Excel, there’s another cool feature you can make use of, and that is its new Data Types (it is location in the Data tab). If I click on the cell for London, under Data Types, I can select the Geography button:

Now, on the cell there’s a little icon indicating a map on it:

If I click on that icon, I get the following pop-up:

Pop-up that comes up after selecting the map icon.

It’s a new feature in Excel that can help you pull up some details on your destination and it gives you a way to bring in even more data into the spreadsheet. However, please note that using this custom data type will break the link for hotels as it will no longer read as a normal text entry.

If you have any suggestions as to other items you’d like to see in this template please let me know. I’m also planning to work on a broader vacation template that considers costs in addition to just travel time.

If you liked this post and my free road trip planner template please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Mortgage Payment Calculator in Excel

October 23, 2020

by howtoexcel with No Comment personal finance template

Many people are out buying homes this year as interest rates are at record lows. But figuring out what you can afford can sometimes be a bit challenging. You need to factor in how much of a downpayment you can afford, what your monthly payments will be, and your income. Oftentimes you’ll end up doing multiple what-if scenarios. However, with the free mortgage payment calculator in Excel, you can get a quick snapshot of all the pertinent information to help you determine which house prices are within your range. You can download the template here.

How the calculator works

There are three sets of inputs on the calculator. The first section relates to the mortgage itself — how much of a downpayment you can make, the interest rate that’s available to you today, and how many years long your mortgage will be.

The second section relates to how much income you make as well as how much of your income you want going to cover your mortgage. This is a good way to gauge affordability to ensure that the monthly mortgage payment is within your means. For the monthly income, you’ll want to use after-tax income since this is what will actually be available to you to pay your mortgage payments and other expenses. This is also tied to the worksheet’s conditional formatting. When the % of the monthly mortgage rises above your maximum%, the cells will highlight in red to show that these house prices will be too expensive based on your threshold.

This is an optional section and if you don’t enter it then the spreadsheet simply won’t populate the % of monthly income and there won’t be any conditional formatting applied.

The last section is simply what house price you want to start at, the minimum value that you want to look at. There’s also an area where you can determine at what increments each option should increase by. For instance, if you want to look at a very narrow range, you might put $10,000 to see the different scenarios if the house price increased by $10,000. If you’re looking at a much wider range, you could increment the values by more, such as $50,000 or $100,000.

Starting house price and increment levels.

As you enter values in these fields, the mortgage payment calculator will update its results and show you how the different scenarios look like at the different prices.

Monthly mortgage amounts by various housing prices.

The above table will be updated immediately as you make changes to your inputs. Please note the spreadsheet is locked and you only can enter data in the inputs. This is to prevent user error and the possibility that formulas are overwritten.

If you liked this post on the mortgage payment calculator in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Calculate Discounted Cash Flow in Excel

October 10, 2020

by howtoexcel with No Comment accounting cashflow intermediate template

Do you need to calculate the present value of future cash flows or assess two options that will impact your cash flow over many years? Excel’s a great place to do that and below I’ll show you how you can easily set up a template to calculate discounted cash flow that you can adjust for changes in the discount rate and cash flow. And if you don’t want to create your own template, you can download mine at the bottom of this post.

In this example, I’ll compare a lump sum lottery win versus a scenario where you receive an annual amount for 25 years. Step one is knowing to calculate present value, which is what I’ll cover next:

Calculating the preset value

To calculate the present value of future cash flow, you need to know what discount rate to use. What you can use is the rate that you can earn on a typical investment. For instance, if you invest in stocks and assume you can make 5% per year, on average, then you might want to use that as your discount rate. If you want to be more conservative, you could use a rate of 2%. Below, you’ll see how the discount rate can play a big impact in your calculations.

That’s because when calculating today’s present value, you have to use the discount rate to bring the future value back to what it would be worth today. For example, suppose you were to receive a $10,000 payment a year from now, and your discount rate was 5%. An easy way to calculate this is as follows:

You might see other formulas on the web involving fractions to calculate present value but just using a negative power does the trick. This calculation yields a result of $9,523.81. Because you’re not getting the payment today, the value of that money is worth less than the full amount. Consider that if you were to receive $10,000 today and invest it and earn 5%, then a year from now it would be worth $10,500 — more than if you were to receive the $10,000 in a year.

Now, suppose you used a discount rate of just 2%. In that scenario, the $10,000 payment a year from now would be worth $9,803.92 today. Since the discount rate is lower, there’s less of a cost associated with waiting for your payment. If the discount rate was 0%, then there would be no incentive for you to invest your money since a year from now it would still be worth the same value it is today. That’s why when interest rates fall and get closer to zero, people will be less inclined to keep their money at the bank and there’s more demand for gold — since that can be a better way to store wealth at that point.

Creating a template to calculate discounted cash flow in Excel

Now that we’ve gone over how to calculate discounted cash flow in Excel, we can set up the template. All that’s really necessary here is to map out the payment schedule, including how much cash you’ll receive every year. Here’s an example scenario of receiving $100,000 for 25 years:

Receiving 100,000 every year for 25 years.

All the payments don’t have to be the same, but for the lottery example, I’m going to keep them that way. What I can do is create another column that will tell me the present value of each one of those payments. To do that, I’ll use a formula that takes the cash flow value, multiples it by the discount rate (I’ll use 5%) raised to a negative power (the year). Here’s how that looks:

I created a discount rate named range so that it’s easy to reference the percentage and to change it. The only thing left here is to calculate the total of all these payments, to arrive at the present value of all of them:

Present value calculation of all the payments over 25 years.

The total present value of the payments comes in at just over $1.4 million. Even though the total of all the payments over 25 years is $2.5 million, we’re losing a lot of that value because of the time value of money, at a rate of 5% per year.

However, let’s prove this out, and to do that let’s look at the future value of all these payments. Let’s assume that these funds will be reinvested and earning a rate of 5% every year. Here’s how much we’d have by the end of year 25:

Calculating future value of payments over 25 years.

In this situation, we’re benefitting from compounding and earning 5% on each year’s ending balance, which includes the prior-year return. By the end of year 25, if we were to invest all of these $100,000 payments at a rate of 5%, we’d have a future ending value of $4,772,709.88.

Now, remember, the equivalent of these annual payments is a present value of $1,409,394.46. Let’s assume that rather than receiving annual payments of $100,000, we simply receive a lump sum payment of this and invest it and also earn 5% every year. Here’s how that will look like:

The ending value after 25 years is the same, $4,772,709.88. This tells us that if you’re given the option of 25 annual payments of $100,000 or a lump sum of $1,409,394.46 today, there’s no difference to you (if the discount rate you’re using is 5%). If the discount rate is 2%, then the present value climbs to $1,952,345.65.

As you can see, depending on which discount rate you use, it can have a significant impact on your present value calculations. This template will allow you to quickly change the discount rate and see how the calculation looks under different scenarios. You can also add more years to this calculation by just extending the formulas down. The amounts also don’t need to be identical, they were only set up this way purely for the purpose of comparing lottery winnings in a scenario where you earn one lump sum amount versus equal payments over multiple decades.

If you’d like to download this template to follow along, the free version is available here, which goes up to year 15. For the full and unlocked version, which has no ads and goes up to 30 years, please refer to the product page here.

If you liked this post on how to calculate discounted cash flow in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Stock History Template

September 30, 2020

by howtoexcel with No Comment investing stocks template

Several weeks ago, I discovered that Excel had a new function called STOCKHISTORY. It’s able to pull stock prices and a great way to track stock prices and can help calculate returns. Excel does make it clear that it is not for trading purposes. However, it’s still a great way to stay on top of tracks and see how they’re performing. Below, I’ve created a template that will allow you to track stock prices and arrange them from best-to-worst.

Note that for this template to work, you need to have the STOCKHISTORY function on your computer, otherwise you’ll get nothing but errors. So your first step will be to check if it works on your file. Refer to the original post on the function as it will also explain how you can get it on your computer if you don’t already have it. If you’re running on old versions of Excel, you’re out of luck.

But for those that aren’t and that have access to the function, read on.

Using the template

You can download the template here.

There are three main inputs on this template:

Selecting the stocks you want to track.
Setting the date ranges you want to look at.
Entering the ranges that you want the macro to sort.

Let’s start with the first one, selecting stocks. I’ve already created three stock sections in this template, which you can of course change. Let’s look at one of them as an example:

The Start, End, and Return values are formulas. The only things you need to enter are the ticker symbols. Off to the left, shaded in light grey, I’ve also entered the code for the exchange. For the New York Stock Exchange, it’s XNYS, while the NASDAQ is XNAS. For a full list of the codes, refer to the original post on the STOCKHISTORY function. If it’s a popular stock that’s on one of the major exchanges, you may not need to enter it. I’ve included the exchange code for the sake of avoiding errors as it’s possible Excel might not know which ticker you’re looking for and select the wrong one.

You can extend the ranges to accommodate more tickers, you’ll just need to copy the formulas down in the Start, End, and Return sections.

Next: the date ranges.

Off to the right of the template, there’s a section where you can enter the start and end dates.

The template will adjust for weekends but not for holidays. If you see a #VALUE! error in the values, that likely means there’s an issue with the date, so you’ll just need to change one of the dates to ensure it doesn’t fall on a holiday.

Lastly: the ranges to sort.

To the right of the dates, there’s another area where you can enter which cells to sort:

Cells E8, K8, and Q8 on this template are where my ‘RETURN’ headers are located, and where the percentages are. If you add sections or modify this template, you’ll need to update the cells to sort. When you update the start or end dates, the template won’t automatically re-sort until you click on this button:

If you get an error on the re-sort button, make sure you check which cells are in the Cells to Sort area and ensure that they’re correct.

#CONNECT! errors

One thing you may run into on this template are #CONNECT errors. I’ve noticed this happens once you start adding too many ticker symbols. Sometimes it’s hit or miss and you’ll get all the prices updated, but if you’re planning to list every ticker out there, just be forewarned that you might run into issues here. It’s a separate error from the #VALUE! error and one that can’t be fixed through the template, without removing some ticker symbols, anyway.

If you liked this post on the StockHistory Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Live T-Account Template

July 18, 2020

by howtoexcel with No Comment accounting t-accounts template

If you’re an accountant, you know that quickly doing a t-account can sometimes help you plan your journal entries and save you some headaches later on. But sometimes it can be time-consuming and a bit cumbersome to go through the process of setting everything up in an Excel spreadsheet. That’s where my new, live t-account template can help you.

Simply go to this link and you’ll be taken to a page where you can start creating your t-accounts on the fly. All you need to do is first make sure you name the accounts along the top and then record the entries on the left-hand-side. The accounts will automatically update as you enter the data.

Here’s a quick demo of how the page works:

It supports 20 line items and five accounts. And if you make a mistake or want to make another set of t-accounts, you can just refresh the page to clear what you’ve entered.

If you like the live t-account template and find it useful, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download.

Color Calculator Template: Sum by Color in Excel

July 11, 2020

by howtoexcel with No Comment conditional formatting formatting template

Unless you’re still stuck on old versions of Excel, you probably know that you can filter data using colors. And by doing so you can use the SUBTOTAL function in excel to tabulate those amounts. But in this post, I’m going to show you something a little different. By using a macro, I’ll highlight cells containing values and then sum them by color in Excel, without needing a subtotal or a filter. This free template will just use VBA to populate the totals.

How the template works

In this template, all you need to do is enter your data and then assign whatever formatting you want to use to identify a cell to the corresponding category. As long as a cell has the exact same formatting as the category, it will be included in that category’s calculation.

I got the idea when I was trying to quickly analyze expenses and didn’t want to go through a whole process of putting it into a complex budget template. Rather than setting up logic to classify whether an expense falls into one category or another, I thought color-coding could be another way you could quickly group expenses.

Here’s a quick video showing you the template in action from color-coding cells to calculating the totals:

Setting up the data

In the template, there’s one section dedicated to the raw data where you’ll enter your inputs:

You can input data up until column N, although I’ve left the column blank for a buffer. In this example, I’ve put in random numbers and grouped them based on a store value in column A. This can be all numbers, it doesn’t really matter, I just preferred to have some grouping.

Next to the data entry, I’ve got a list of categories set up in column O:

You can add as many categories as you like. How they’re color-coded here is how you’ll need your cells will need to look to ensure they’re in the correct categories. For instance, any cells that are highlighted in light blue will go into Category I. Whereas anything in a dark red will belong to Category E.

You may think this would be a painful process to try and color-code your data based on all these different categories. After all, what if you get the shade wrong or the font color is wrong. The solution’s really simple and you just need to use our trusty friend, the Format Painter. If you’re not familiar with it, this is what it looks like:

It’s on the left-hand-side of the Home tab where the copy buttons are. What you can do is select the category you want to be assigning data to, click once on the Format Painter and then click on the cell you want to highlight with that exact same formatting.

If you’ve got multiple cells that you want to apply the formatting to and don’t want to keep repeating this exercise, then Double-Click on the Format Painter. You can now continue selecting cells and the Format Painter will take care of the formatting for you. You won’t need to re-select it each time. Once you’re done with the formatting and want to stop applying it, click on the Format Painter again to stop.

I’ve color-coded some of my data based on the categories, and here’s how it looks:

Updating the calculations

On the right-hand-side of the page there’s a button that says Update. This will update the totals based on the color-coding. You’ll need to have macros enabled for this to work. Above the button you’ll see the total of all the values in columns and how much is unallocated.

Clicking on the update button will trigger the macro to run the calculations. After pressing the button, here’s what my categories look like and the totals corresponding to their color-coding:

You’ll notice I’ve also created visuals to see the relative size of each category using the REPT function. If you’re interested in learning how to use this function, check out this post.

Anytime you make changes to the color-coding, be sure to hit the Update button. I didn’t want the formulas to update every time there was a change on the sheet because that can sometimes slow a spreadsheet down, especially since it would involve recalculating all the totals.

The code

Here’s the VBA code itself on how the update button works:

Sub Oval1_Click()

Dim clvalue, clcolor As Double
Dim cl, colorrange As Range
Dim lstrow As Integer

lstrow = ActiveCell.SpecialCells(xlLastCell).Row

Set colorrange = Range("colorrange")

'clear data range
colorrange.Offset(0, 1).ClearContents


For Each cl In ActiveSheet.Range("A1:O" & lstrow)

    If IsNumeric(cl) Then
    
        clcolor = cl.Interior.Color
        
            For Each lookupcl In colorrange
    
                If lookupcl.Interior.Color = clcolor Then
                    lookupcl.Offset(0, 1) = lookupcl.Offset(0, 1) + cl.Value
                    GoTo nextcl:
                End If
    
            Next lookupcl
    End If

nextcl:

Next cl

End Sub

There’s a named range called “colorrange” that it cycles through, and those are the categories in column O on the spreadsheet.

Using the template

This isn’t a terribly complex template to use. However, it can help if you want to quickly group items. It’s a unique way to classify expenses rather than just using drop-downs and complicated formulas. And it makes it easy to sum data by color in excel. The way I found it useful was to list your vendors or stores in column A. Then, arrange transactions by date (e.g. the first transaction is in column B, then C, and so on). But this can be used in a variety of different purposes to help classify data.

If you want to reset the calculations, just change the data back to the default formatting or something that doesn’t correspond to a category you already have, and then click update.

Download

The template is free to download and it’s available here.

If you liked this post on how to sum by color in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.