Credit card interest is a cost that cardholders incur when they carry a balance beyond the grace period, which is typically between 25 to 30 days after a billing cycle ends. This interest is calculated based on the Annual Percentage Rate (APR) provided by the credit card issuer. Understanding how this interest is computed is vital for managing financial liabilities and making informed decisions. This article goes over how to calculate credit card interest in Excel, in a step-by-step process.
Getting the correct rate
Before we get started, they key is finding out what your APR is. This is a yearly interest rate which is provided by credit card companies. It tells you how much you’ll be paying. The APR is usually stated in the credit card agreement or on the credit card statement. It’s essential to note that different transactions may have varying APRs; for instance, cash advances often have higher APRs compared to purchases. If you have cash advances to consider that are at difference rates, then you may need to break this out into two separate calculations.
Once you have APR, you need to convert that into a daily rate as credit card interest is usually calculated on a daily basis. To calculate the Daily Periodic Rate (DPR), this involves taking the APR and dividing it by 365.
DPR = APR / 365.
This can involve a lot of decimal places so you may need to do some rounding. But if you do this in Excel, you don’t have to, and that means a more precise calculation.
Calculating your average daily balance
To determine your interest expense, you’ll also need to determine what your balance was during each day that fell within your billing cycle. To calculate the Average Daily Balance (ADB), sum up the total of those daily balances and divide it by the number of days. The formula is the same as if you were to calculate any average:
ADB = Sum of daily balances / Number of days in billing cycle
It’s important to note that you’ll also need to carry over any balance from your last bill if it was unpaid. And you’ll also want to deduct any payments you make from the balance and add any purchases to ensure the balance is always correct and up to date.
Calculating the interest charge
To get your daily interest charge, simply multiply the two variables, DPR and ADB by one another.
Daily interest charge: DPR x ADB
And if you want to calculate the monthly charge, then you take the daily charge and multiply it by the number of days in your billing cycle.
Monthly interest charge = Daily Interest * Number of days in billing cycle
Creating a template to calculate monthly interest costs in Excel
Now it’s time to create a template in Excel which will make it easy to adjust for different scenarios. First off, we need to get the daily balances in a table format. Ideally, there should be a column for the starting balance, the day, purchases, payments, and the ending daily balance. This way, you can easily account for purchases and payments should you want to determine what your balances and interest expenses will be ahead of time.
The formula for the ending balance will be as follows:
The values highlighted in dark grey are reserved formulas while the yellow cell pertaining to APR indicates that this is value that requires manual entry.
The formula for DPR just needs to reference the APR and divide it by 365:
DPR = APR/365
This returns a value of approximately 0.047%.
This will also have a named range of DPR to make it easier to reference later on. The benefit of using Excel for these calculations is that they will be more accurate; there’s no need to do any rounding.
For the ADB, a simple AVERAGE function can be used on column F, which in my spreadsheet, contains the ending balances.
ADB = AVERAGE(F:F)
The average in my spreadsheet is a value of $301.67.
Since there is nothing else in column F, we can just average everything that’s in there. The function will ignore any blank values. This will be another named range, ADB.
To calculate the daily interest, the formula will should look familiar, this time, it’s within Excel as this involves named ranges:
Daily Interest = DPR * ADB
This returns a value of $0.14 (rounded).
Lastly, to collect monthly interest we take the Daily Interest and multiply it by the number of days. For this example, I’ve set a named range of DailyInterest. And to calculate the number of days, I can use the following MAX formula to get the largest value in column B, which has the day numbers:
There are 30 days within the billing period in my example.
Then, this gets multiplied by the DailyInterest named range to arrive at the total monthly interest cost. Here is the full formula within the cell for Monthly Interest:
Monthly Interest =MAX(B:B)*DailyInterest
The monthly interest in my example computes to $4.22.
Understanding this process sheds light on the significance of the APR and how maintaining a lower balance or paying off the balance before the end of the grace period can mitigate the interest charges. It also underscores the importance of being aware of the different APRs for various types of transactions.
Many credit card issuers offer a grace period during which no interest is charged on new purchases if the previous month’s balance was paid in full. Utilizing such grace periods effectively can lead to substantial savings on interest charges.
If you liked this post on How to Calculate Credit Card Interest, 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A good way to gauge the strength of the U.S. economy and how well it is returning to normal level is by looking at Las Vegas’ visitor data. The Las Vegas Convention and Visitors Authority (LVCVA) has plenty of important metrics that it tracks on its website. From the number of visitors to the city to occupancy levels to daily room rates, and other key performance indicators (KPIs). Using data from that website, which you can find here, I’ll guide you through the step-by-stop process as to how you can build a dashboard to track some of those key metrics.
Step 1. Preparing and consolidating the data
One of the most important parts of data analysis is to clean up your data from the beginning. By doing this, you’ll avoid headaches later on. It’ll also make it easier for you to do analysis in the first place. To get a proper glimpse of how Las Vegas is doing now, it’ll be useful to track multiple years. On the LVCVA website, you can download data for multiple years. For this example, I’m going to download data from 2019 through to 2023 YTD.
This is what one of the files looks like:
As of writing this article, data for 2023 is available up until the end of July. Since the data is organized in the same format on all of the files I’m downloading, I can just copy and paste one year after another. The key is for the rows to line up.
But I still need to clean up this data. One problem is that there are gaps between the months. Once I’ve loaded all the years together, I’ll remove those blank columns. The easiest way to do this is the highlight the top row. Then, press F5 and select Special. There will be an option to select Blanks:
Then, all those gaps are selected:
If your right-click on any one of them, select Delete, choose Entire Column, and press OK. Now those columns are gone:
There’s still one problem here. The way the data is structured right now isn’t useful when creating pivot tables. And if you’re creating a dashboard, you’ll want to be able to create pivot tables easily. Doing so can make it easy to create reports on the fly and easy to make changes. It’s easier to have dates going vertically than horizontally to scroll through data. So what I will do is use the TRANSPOSE function to flip it. All that’s necessary here is to use the function and select your entire data set. Then, voila:
Before I make any further changes, I want to convert this into values. Since I used the TRANSPOSE function, it’s sitting as an array. To change this, I’ll select the entire data set, press CTRL+C, and then press CTRL+SHIFT+V to paste as values. If you don’t have that functionality on your version of Microsoft Excel, right-click and select Paste Special and click on Values.
I will also add a few more columns to make the analysis easier. I’ll create a column for the month and year. This will involve using the MONTH and YEAR functions. The only argument that is needed is the original date, which in the screenshot above, appears under ‘Tourism Indicators.’
And since I want to compare 2023 to 2019, I’ll add a column for ‘Current Period’ and ‘Comparable Period.’ The point of this is to make sure that I can filter the current YTD values against the same values from 2019. Since I have data up until July, any comparisons should also run up until July 2019. For the Current Period, I’m using the MAXIFS function to grab the maximum value for the Month field for the current year (I can use the TODAY function to make it dynamically pull in the current year). Then, for the Comparable Period column, I can compare the Month field to see if it is less than or equal to the Current Period. If it is, then I’ll set the value as a “Y” to indicate it falls in the comparable period or “N” if it doesn’t. This way, if I come across month 8 and my current period only goes up to 7, it will mark that as an “N” which will allow me to easily filter out those results.
Lastly, I will convert all this data into a table. The purpose of this is so that I can easily reference the different fields later on, without having to remember column letters. To convert this into a table, select Insert and click on Table. Then, on the Table Design tab, you can name the table something that’s easy to remember. In my example, I’ll refer to it as tblConsolidated.
Step 2: Identifying the KPIs to track
Before rushing out to create the pivot tables, it’s important to know what you want to track. You don’t want to create a pivot table and track everything possible, otherwise it won’t be a useful summary, which is what a good dashboard should aim to do. That’s why you should devote some time to identify what some of your KPIs should be.
There are a lot of metrics on here and these are the ones that I am going to use, which will help gauge how active and busy Las Vegas is:
Visitor volume. Obviously the number of people visiting the city is a great indication of how many people there are.
Occupancy levels. If hotels are booked up, that’s another positive sign that the city is busy.
RevPAR. This takes the room revenue divided by the number of available rooms. It shows how well a hotel is filling up its rooms at a given rate.
Average Daily Rate. This is partly reflected in RevPAR but it can be a useful indicator as people are more familiar with room prices than they are with RevPAR, especially those who visit Las Vegas often.
En/Deplaned Passengers. This is a helpful metric to know how much out-of-town traffic there is coming to the city.
Average Daily Auto Traffic. With this metric, readers can see how busy the roads are.
Gaming Revenue (Las Vegas Strip). This is another important KPI because it tracks how much people are spending at casinos.
Step 3: Creating the pivot tables
Now it’s on to creating a pivot table for each KPI you want to track. To make this process easier, just create a pivot table one time, and then copy it for as many charts that you want to create. This way, you don’t have to go back and select Insert->Pivot Table over and over again. Just make sure to leave enough room so that they don’t overlap, otherwise you’ll encounter errors.
It’s also a good idea to label your pivot tables by going into the PivotTable Analyze tab. For a pivot table to track visitor volume, you might want to call that ptVisitorvolume, for example. This will be helpful later on if you want to change charts and aren’t sure what PivotTable1 relates to. You’ll also likely want to change the default formatting for a pivot table:
To change the format, don’t just highlight the cells and make the changes, otherwise they’ll revert back once you update the data. Instead, right-click on one of the values and select Value Field Settings. Then, select Number Format and apply the formatting you want to apply to that field.
What I also like to do is put all the pivot tables on a separate tab to keep them organized, while all the charts will go on a main tab dedicated for the dashboard.
Step 4: Creating the charts
When creating your charts, one thing to consider is how you want the data to be visualized. You can do this as part of the stage to identify KPIs. For visitor volume, for example, I’ll use a line chart since I want to see the month-over-month progression. This will also make it easy to compare against multiple years.
Since these are charts created from pivot tables, they are pivot charts, and they come with drop-down options:
They aren’t terribly appealing and to get rid of them, click on the chart, select the PivotChart Analyze tab and unselect the option for Field Buttons:
One thing that can help with creating charts is by using Excel’s existing Chart Styles, which are on the Design tab (which is visible if the chart is selected):
This can be an easy way to customize your charts without having to do so manually.
You may also want to adjust how the data is displayed. Visitor volume, for example, may make sense to leave as the default, which is a summation. But when looking at ADR or RevPAR, you wouldn’t want to sum those values up. Instead, you may want to calculate the average instead. To do that, right-click on one of the fields and select Summarize Values By and select Average
Now, you’ll see an average based on period, which makes more sense than summing up prices.
At this point, it comes down to your personal preferences as to how you want to design the charts, and it would be far too deep to try and get into all those options here. However, I’d suggest mixing up a bit of bar and column charts and also changing up the colors so your dashboard doesn’t look like the same item over and over.
Some additional things you may want to consider are:
Adding data labels. And if you do use them, consider not using axis labels;
Using legends where and when make sense to do so;
Adding background images to your charts to have a different look and feel to them;
Having descriptive titles to help summarize what the chart is displaying;
Not plotting too much on on chart. You may want to consider plotting years instead of months;
Not using a border color so that your charts blend in with the background.
Here are a couple of charts I created with images in the background to make it clear what they are showing:
Step 5: Adding key numbers at the top for further emphasis
Charts are good, but what can also be useful is to put key numbers right at the top so that readers don’t have to spend much time looking for the most important metrics. For example, using formulas, you can pull in the total number of visitors for the period, the occupancy rate, the ADR, RevPAR, and other items, based on the latest information.
While these can be good to include in charts, by making them big and allowing them to stand out as soon as you open up the dashboard, it can help drive the point home even further.
In the example above, I have a list of the current metrics along with the growth rate or comparable percentage from 2019, to help show how the metric is doing compared to that year. You could also add conditional formatting to this to highlight where there is an improvement and where things may have worsened.
Step 6: Finishing touches
Once you’ve got your charts and metrics all on there, the last piece of the puzzle is to add a title as well as any icons or images that may be relevant to help give some added pop to your dashboard. If you go to the Insert tab, you can use that to pull in pictures from the internet. Excel also has built-in icons and stock images that you can use, just by doing a search:
This can be an easy way to help your dashboard stand out even further. Here’s a snapshot of the dashboard I created:
If you liked this post on How to Create a Dashboard to Track Las Vegas’ Visitor Data, 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
If you’ve got a big, complex spreadsheet with lots of formulas, it can be slow to run. In those situations, turning off calculations can be a life saver. But the downside of doing so, is that you might forget that those calculations haven’t been updated. Relying on stale values can be risky and lead to poor decision making and analysis.
Thankfully, there’s a new feature in Excel that now helps you find and identify those values easily.
Finding stale values
For this example, I’m going to use a simple table. It shows product IDs, prices, quantities, and total sales.
The only calculation that happens here is in the total sales column, where price is multiplied by quantity. If the calculations are on, changing either the price or quantity fields will change the value in the total sales field automatically. But if I turn on Manual Calculations, then the calculation won’t happen until I either set the calculations to Automatic, or to manually force calculations (e.g. by pressing F9).
To turn off calculations in Excel, go to the Formulas tab and select Calculations Options, where you’ll see the following options:
The one danger is that if you set your calculations to Manual, it will change the setting for all the workbooks you currently have open. This change isn’t just set to one sheet or workbook.
In the above screenshot, the calculations are set to Manual. And if you’ve updated to the latest version of Excel, you’ll see the option at the bottom: Format Stale Values. If you check this off, you will now see different formatting for calculations that Excel hasn’t updated.
After checking that off and making changes to some of the quantities in my table, some of the values in the total sales column haven’t updated. And it’s easy to see which ones those are:
There are now strikethroughs showing for the values which aren’t updated. This tells you that those values are no longer accurate. As you can see from the value of $172.50 where the corresponding quantity is 50 and the price is $5.75, the total sales based on that calculation should be $287.50. Without applying the formatting for stale values, it would be difficult to notice that the value of $172.50 is incorrect.
Once the values are recalculated, either by manually triggering them (F9) or by changing them back to automatic, then the strikethrough goes away. And that’s because the value has also been updated:
If you never turn your calculations off and set them to manual, you’ll never need to use this feature of stale formatting. But if you do occasionally turn off calculations, then it can be valuable to you as it can help you avoid errors and making incorrect decisions based on outdated information.
If you don’t see this option available yet then it may not be available on your version of Excel. You need Microsoft/Office 365 and for the latest beta updates to be installed. Eventually, however, it will be rolled out to all 365 users.. But if you want new features as soon as they are available, be sure to sign up for the (free) Office Insiders Program to ensure that you get them earlier than the general rollout.
If you liked this post on How to Find Stale Values 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
In this post, I’m going to show you how to group dates in a pivot table by month. By doing this, you can do analysis by month rather than individual day. And that will also make it easier to plot the data on a chart.
For this example, I’m going to use TSA passenger volumes as my data set and analyze them by month and year. Here is the data I’m going to use, which runs up until Aug. 6, 2023:
If I load this into a pivot table, my fields are as follows:
I have the date field which shows the current year’s dates. And there is also a field for each year, which contains the passenger volumes. If I put the Date in the Rows section of the pivot table and then years into the values section, then my pivot table looks like this:
There are a few things that I need to fix for this analysis to work:
I need to change each year field so that it is taking an average instead of summing the values. If I leave it as is, summing the values may not be helpful as the months are not going to be identical eah year. Taking an average will help smooth the data.
The formatting should be changed so that the values are separated by commas. This will make it easier to visually see the data. The numbers are too big and can be difficult to interpret in their current format.
The Row labels are broken down by year. But I already have the year values going across. This is not necessary and I need to have only the month values.
Here’s how to address these items.
To change the year field so that it takes an average, right-click on the field and select the option to summarize as an average:
Repeat this for each field, so that everything says average. To fix the number formatting, right-click on each field and select Value Field Settings:
Change the formatting to Number and check off the option for the 1000 separator. Repeat these steps for the other fields as well.
Next, for the date grouping, right-click on any of the date values and select the Group button:
At the following dialog box, uncheck years and quarters and just leave Months:
After making all those changes, my pivot table now looks like this:
It’s now easier to compare the different months and years. And it’s also easier to put it on a chart. If I insert a line chart, it’s easy to spot the trends by a monthly and yearly basis:
This is a PivotChart, as it evident from the grey drop-down options. If you prefer to get rid of the filters, go to the PivotChart Analyze tab and uncheck the Field Buttons option. Now you’ll have a cleaner chart layout. In the below example, I have also moved the legend to the bottom:
As you can see, by grouping your pivot table dates by month, it becomes easier to analyze data. And by not doing a daily analysis, it’s possible to look at the data from a year-to-date view to compare the monthly averages. This way, you are able to still see the story behind the data without having a crowded chart.
If you liked this post on How to Group Dates by Month in a Pivot Table, 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Microsoft Excel is a powerful spreadsheet software that people use for data analysis, calculations, and reporting. While Excel is primarily designed for managing and manipulating tabular data, many users have explored using it as a database. In this article, I will go over the pros and cons of using Excel as a database, highlighting its limitations and advantages compared to SQL and other alternatives.
Benefits of using Excel as a database
1. Familiarity and Accessibility
Excel enjoys widespread usage, and millions of users are already familiar with its interface and basic functionalities. It is easily accessible and requires no additional software or technical expertise to get started. This means no costly support bills as there are many users all over the world who can provide expertise on spreadsheets. And it’s already included in Microsoft 365, which many businesses already pay for.
2. Quick and Easy Data Entry
Excel provides a user-friendly environment for entering and editing data. Its intuitive grid layout allows for easy data input, and its familiar formula syntax enables simple calculations and data manipulation. You can also create templates for data entry so that it is customized to your company’s needs. Through userforms and visual basic, you can even create wizards that walk users through personalized data entry screens.
3. Simple Sorting and Filtering
Excel offers basic sorting and filtering capabilities that can be helpful for simple data analysis and organization. Users can sort and filter data based on specific criteria to extract relevant information quickly. This can make it easy to review and analyze data on-the-fly. Slicers also add convenience and can make filtering options even easier, giving users the ability to quickly apply filters with just a few clicks of a mouse.
4. Flexible Data Visualization
Excel provides various charting and graphing tools to visualize data effectively. Users can create professional-looking charts and graphs without the need for complex coding or external software. Pivot tables and pivot charts can be created within a few seconds and Excel has many chart templates available that can quickly summarize and display data. You can even create complex 3D bubble charts for more advanced models.
5. Low Learning Curve
Excel’s user-friendly interface and widespread familiarity make it more approachable for non-technical users. In addition to Microsoft’s tutorials, you can find help on message boards, and other websites, like this one, that can help you learn how to use Excel. There are also many YouTube videos covering tutorials as well. Oftentimes, you’ll find users with similar or even the exact problems you are experiencing, making it easy to find a solution with a simple search. In contrast, SQL and other database systems often require specialized knowledge and training.
Excel is usually included in the Microsoft Office suite, which is commonly available in many organizations. Dedicated database systems may require additional licensing costs and infrastructure investments. With Excel, you just pay a recurring fee for Microsoft 365. And if you have an older off-the-shelf Excel product, you can use it indefinitely without having to pay a subscription fee.
7. Quick Prototyping and Ad Hoc Analysis
Excel’s ease of use allows for rapid prototyping and ad hoc analysis. Users can quickly create and modify data structures, perform calculations, and experiment with different scenarios without complex setup or formal data modeling.
Disadvantages of using Excel as a database
1. Limited Scalability
Excel is not designed to handle large datasets or complex data relationships. It has a practical limit on the number of rows (1,048,576 in Excel 2019) and can become sluggish when dealing with vast amounts of data. Additionally, as the file size grows, it can lead to performance issues and increased chances of data corruption.
2. Lack of Data Integrity and Security
Excel lacks built-in mechanisms for ensuring data integrity and enforcing strict security measures. It offers limited data validation features and minimal control over user access and permissions. This makes it prone to human errors, accidental data modifications, and unauthorized access. While macros, locked cells, and additional controls can be added to make a file more sure, they’re by no means ironclad; if you need to keep information confidential, then it’s best not to hold the data in Excel.
3. Lack of Concurrent Access and Collaboration
Excel files are typically stored on local machines, making it challenging for multiple users to collaborate simultaneously. Sharing and managing Excel files across different users can lead to version control issues and data inconsistencies. And if you’re using macros, then multiple users cannot be in the same file at once. This is one of the biggest drawbacks of trying to use Excel as a database and it’s one of the first questions I ask people who want to create a file that multiple people are using — do they need to be in it at the same time? If so, then Excel isn’t the right solution.
4. Limited Data Analysis and Reporting Capabilities
Excel’s analytical capabilities are limited compared to dedicated database systems like SQL. It lacks advanced querying capabilities, complex aggregations, and data mining functionalities, which can hinder advanced data analysis and reporting needs. While advanced users can create complex and custom reports, for those who aren’t comfortable doing it themselves, they may prefer using a different system.
Should you use Excel as a database?
Excel has lots of great functionality and by now it should be clear that you can use it as a database. However, the more important question is whether you should do so. There are three questions you can ask yourself to help make that decision:
Do multiple people need to be in the file at the same time?
Do you have a large database that may require more than 1 million rows in a single table?
Are you holding sensitive information (e.g. credit cards, social security numbers) in your database?
If you answer yes to any of those questions, then Excel probably isn’t going to work for you. But if you answered ‘no’ to all of them, then you may benefit from storing your data in Excel and using it as a database.
Regardless of what IT experts may tell you, there are situations where Excel can be used as a database and where it makes sense to do so, especially when the alternative is a costly system which requires ongoing maintenance and where support can be expensive.
If you liked this post on Whether You Can Use Excel as a Database, 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
The Relative Strength Index (RSI) is a popular trading indcator that investors use for trading purposes. In this article, I’ll go over details as to what RSI is, why it’s useful, and how to calculate it in Excel.
RSI is a bounded oscillator that fluctuates between 0 and 100, providing insights to investors as to whether a stock is overbought or oversold. It compares the magnitude of recent price gains relative to recent price losses over a specified period of time, typically 14 days, and generates a value that indicates the potential for a price reversal or continuation.
The higher the losses are relative to the gains, the lower the RSI value becomes. And the opposite is also true, with the RSI value rising when a stock has been accumulating more gains than losses. Generally, an RSI value above 70 indicates an overbought condition, suggesting a potential price correction or reversal to the downside. Conversely, an RSI value below 30 indicates an oversold condition, implying a potential price bounce or reversal to the upside. Traders often use these overbought and oversold levels to identify possible entry or exit points in the market.
Why RSI Is a Useful Indicator for Traders
It’s important to note that the RSI is just one tool among many in technical analysis, and it should be used in conjunction with other indicators and analysis methods to make well-informed trading decisions. However, here are 4 reasons traders might find it useful:
1. Finding overbought and oversold levels
RSI can help investors identify buying and selling opportunities. When a stock is deeply oversold and the business is still in good shape but perhaps is down due to a bad quarter, it could be a sign to buy the beaten-down stock. In essence, it can help find market overreactions. At the same time, it can spot a stock that perhaps has become too hot when its RSI level is over 70 or 80, and that perhaps it has risen too much and too quickly.
It’s useful to also look at a stock’s historical RSI levels to gauge what kind of an opportunity it is. If it frequently dips in and out of oversold/overbought territory, it could simply be that the stock is volatile. But if it is rare for the stock to become oversold/overbought, then it could make for a good opportunity to buy or sell the stock depending on what the indicator says.
2. Measuring momentum and confirming a trend
The RSI provides insights into the strength and momentum of a price trend. When the RSI is rising and stays above 50, it indicates that buying pressure is dominant and the price trend may continue. Conversely, when the RSI is falling and stays below 50, it suggests that selling pressure is dominant and the price trend may continue downward. This information can help investors confirm the strength of a trend and make informed decisions about entering or exiting positions.
3. Identifying divergence patterns
Another valuable aspect of the RSI is its ability to identify divergence patterns. Divergence occurs when the direction of the RSI differs from the direction of the price. Bullish divergence happens when the price makes lower lows while the RSI makes higher lows, indicating a potential trend reversal to the upside. On the other hand, bearish divergence occurs when the price makes higher highs while the RSI makes lower highs, suggesting a potential trend reversal to the downside. Investors can use these divergence patterns as early warning signals of potential trend shifts and adjust their investment strategies accordingly.
4. Confirmation with other indicators
The RSI can be used in conjunction with other technical indicators to confirm signals and strengthen investment decisions. For example, if a stock shows overbought conditions based on the RSI, investors may look for additional indicators such as bearish candlestick patterns or negative volume divergences to support their decision to sell or take profits.
Other technical indicators investors can use alongside RSI
Investors often use many different indicators to make investment decisions. Here are a few commonly used indicators that can be used in conjunction with the RSI:
1. Moving Averages
Moving averages are trend-following indicators that smooth out price fluctuations over a specific period. The most commonly used moving averages are the simple moving average (SMA) and the exponential moving average (EMA). Investors often use moving averages in combination with the RSI to identify trend direction and potential support or resistance levels.
2. MACD (Moving Average Convergence Divergence)
The MACD is another trend-following momentum indicator that consists of two lines, the MACD line and the signal line. It helps identify potential buy and sell signals by measuring the relationship between two moving averages. Traders often look for convergence or divergence between the MACD and the RSI to confirm potential trend reversals or continuations.
3. Bollinger Bands
Bollinger Bands consist of a centerline (typically a moving average) and two bands that are plotted above and below it. These bands represent volatility levels. When the price reaches the upper band, it suggests that the asset is overbought, while reaching the lower band suggests oversold conditions. Combining Bollinger Bands with the RSI can provide additional insights into potential price reversals or breakouts.
4. Stochastic Oscillator
The Stochastic Oscillator is a momentum indicator that compares the closing price of an asset to its price range over a specific period. It consists of two lines, %K and %D, which oscillate between 0 and 100. Traders often look for oversold or overbought conditions on the Stochastic Oscillator in conjunction with the RSI to confirm potential trading signals.
5. Volume indicators
Volume indicators, such as On-Balance Volume (OBV) or Volume Weighted Average Price (VWAP), provide insights into the buying and selling pressure behind price movements. By analyzing volume alongside the RSI, investors can assess the strength and validity of potential price trends or reversals.
6. Fibonacci retracements
Fibonacci retracements are based on the mathematical relationships found in the Fibonacci sequence. They are used to identify potential support and resistance levels. Combining Fibonacci retracements with the RSI can help investors identify areas where a price correction or reversal may occur.
These are just a few examples of indicators that investors can use alongside the RSI. The choice of indicators depends on the investor’s trading strategy, timeframes, and personal preferences. It’s important to test and evaluate different combinations of indicators to find a system that works well for individual investment goals and risk tolerance.
Why you shouldn’t buy a stock just because the RSI is low
Buying a stock solely based on a low RSI level is not a recommended approach for several reasons:
1. It lacks context
The RSI is just one indicator and provides a snapshot of the stock’s recent price performance relative to its own historical price movements. It doesn’t take into account other fundamental factors or external market conditions that may impact the stock’s future prospects. For example, a stock may have a very low RSI because investors are selling it off due to liquidity issues or problems that may significantly impact the investing thesis behind a stock. Therefore, solely relying on the RSI without considering other relevant information may lead to an incomplete assessment of the stock’s potential.
2. False signals
The RSI is a bounded oscillator that fluctuates between 0 and 100. While an RSI below 30 may indicate an oversold condition, it doesn’t guarantee an immediate rebound or a profitable buying opportunity. Stocks can remain oversold for extended periods, and the RSI alone may not accurately predict the timing or magnitude of a price reversal. It’s essential to consider other technical and fundamental indicators to validate the potential opportunity.
3. Downtrends and value traps
A low RSI reading can sometimes be an indication of a stock in a prolonged downtrend. Just because a stock is oversold does not mean it will necessarily recover or provide substantial returns. There may be fundamental reasons behind the stock’s decline, such as poor financial performance, unfavorable industry conditions, or negative news. Investing solely based on a low RSI without understanding the underlying reasons for the low reading can lead to falling into a “value trap” not unlike how investors may buy a stock simply because its price-to-earnings multiple is low.
4. Confirmation bias
Relying solely on the RSI to make investment decisions may lead to confirmation bias, where investors seek information that supports their preconceived notions. It’s crucial to consider a broader range of indicators, conduct thorough research, and evaluate multiple factors to make well-informed investment decisions.
5. False oversold signals in strong downtrends
In strong downtrends, a stock can remain oversold for an extended period as selling pressure continues. Attempting to catch a falling knife solely based on a low RSI reading can result in further losses if the stock continues its downward trajectory. It’s important to assess the overall trend, market conditions, and other technical and fundamental factors to increase the probability of making successful investment decisions.
While the RSI can be a useful tool to identify potential opportunities, it should be considered as part of a comprehensive analysis that incorporates other indicators, fundamental analysis, and market conditions. By taking a holistic approach to investment decision-making, investors can make more well-rounded and informed choices.
How do you calculate RSI?
Here are the steps to take when determining how to calculate RSI:
1. Determine the timeframe
Traders usually use a 14-day timeframe for calculating the RSI, but it can be adjusted to suit different trading strategies and timeframes.
2. Calculate the average gain and average loss
The RSI compares the average gains and average losses over the chosen timeframe. To calculate the average gain, sum up all the positive price changes (gains) over the period and divide them by the number of periods. Similarly, calculate the average loss by summing up all the negative price changes (losses) and divide that by the number of periods.
3. Calculate the relative strength (RS)
The relative strength is the ratio of the average gain to the average loss. RS = Average Gain / Average Loss.
4. Calculate the RSI
The RSI is derived from the relative strength and is calculated using the formula: RSI = 100 – (100 / (1 + RS)).
Calculating RSI in Excel
Using the STOCKHISTORY function in Excel, you can easily download a stock’s historical prices. In this example, I’ve downloaded Amazon’s stock price between the period Jan 1, 2021 and Dec 25, 2022.
The next step is to calculate the gains and losses for each day. This just involves looking at the current closing price and the previous. If the price went down, the difference goes into the loss column. If it’s a gain, it goes into the gain column. Here’s an example of the formula for the gain column:
Here’s a look at what the sheet looks like with the formulas filled in for the gain and loss columns:
Next up, I need to calculate the average gains and average losses. I’ll do this for the past 14 trading days. For the first value, I just need to calculate a simple average:
For subsequent cells, however, I’ll use an exponential average. That way, I’ll apply more weighting to the the most recent calculation:
Next, I will calculate the RS Value. To do this, I take the average gain and divide it by the average loss:
Lastly, that leaves the RSI calculation, which contains the following formula:
With all the fields filled in, this is what the spreadsheet looks like:
If you want to follow along with the file that I’ve created, you can download it from here. You can also watch the corresponding YouTube video that goes along with this tutorial:
If you liked this post on How to Calculate the Relative Strength Index (RSI) 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A weighted average is a type of average that assigns different weights or values of importance to each element in a dataset. Unlike a simple average that treats all elements equally, a weighted average adjusts the contribution of each element based on its relative significance. This means that some elements have a greater impact on the final result than others, depending on their weights.
Why use a weighted average?
Weighted averages are useful because they provide a more accurate representation of the data by taking into account the importance of each element. For example, in financial analysis, a weighted average may be used to calculate the average interest rate of a portfolio of loans or investments, where the weight of each loan or investment is based on its size or duration. In schools, a weighted average may be used to calculate a student’s overall grade by assigning different weights to assignments, quizzes, and exams based on their importance or difficulty. Anytime you don’t want everything to have the same weighting or importance is when you’ll want to use a weighted average.
Calculating a simple weighted average in Excel
A common way to apply a weighted average is by using a points system. Suppose you are looking to buy a house and have many different criteria that you want to take into consideration, such as square footage, location, if it has a basement, etc. But not all of these items are equally important, and so you may want to say that location is worth 30 points and square footage is worth 25 points, and so on.
The first step is to assign a weight, or point value, to each one of these criteria. Then, assign a score to each one of them criteria, perhaps within a range of 1 to 100. Once you’ve done that, you multiply the score by the points. Total that up, and divide it by the total points, and you’ve got your score, or weighted average. Here’s an example:
This particular house scored high on the most important items, and thus, resulted in a high weighted average. The total of the score x points column was 10,190. Taking that value and dividing it by 145, the total points, results in a weighted average of 70.28.
Here’s another house, which scores far lower, with a weighted average of just 45.48:
Although it scored high on areas such as school and kitchen, because of its low scores on the top two weightings — location and square footage — that kept its weighted average down.
Creating a template like this in Excel and comparing your different scores can be a way to help compare houses and other things, while giving each criteria an appropriate weighting. By simply scoring everything on a value of 1-100 without weighting, the problem would be that each criteria would effectively be equal, saying that things like layout and the garage are just as important as the location and size of the house, which most people likely wouldn’t agree with. By using weights, you can better take into account the value of each individual criteria.
Calculating grades using weighted averages
Another use for calculating weighted averages is when it comes to grading. In a class, you might have a specific weighting scale that says assignments are worth 10% of your grade, quizzes are 20%, a project is worth 5%, a mid-term is 25%, and the final exam accounts for 40%.
In this case, you’re using percentages that add up to 100% rather than weights, which may be more subjective. This still works in largely the same way as you are multiplying a score by the weight. Except now, since the weights add up to 100%, you don’t need to worry about taking the total and dividing it by the total weights. Whatever your result is, that is the total score. Here’s an example of how a student scored in a class:
When using percentages for weighting, it’s important to double check they add up to 100% to ensure everything is accounted for. In this example, the student had a score of 72.25, which would be the same as saying they scored 72.25%, which would be their grade for the course. As you’ll notice, the student’s high scores on the quizzes and mid-term exam were unfortunately offset by a poor final exam mark.
In this example, since we’re just looking at percentages, you can do without the extra column for value, which takes the weight x the score. Instead, you can use SUMPRODUCT. If the weightings are in cells A2:A6 and the scores are in B2:B6, the grade can be calculated with the following formula:
The formula will multiply each value by the corresponding value in the same row, thereby eliminating the need to use an extra column. By using an Excel formula, you can save yourself the extra step of having to tally up the values and then dividing them by their weights again.
If you liked this post on How to Calculate Weighted Average 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Net Present Value (NPV) is a financial metric used to determine the current value of a series of cash inflows and outflows. It takes into account the time value of money, which means that a dollar received in the future is worth less than a dollar received today due to factors like inflation and the opportunity cost of not having that money available to invest in other projects.
The calculation of NPV involves discounting the expected future cash flows of a project or investment back to their present value using a specified discount rate. The result is the difference between the present value of the expected cash inflows and outflows.
NPV is an important calculation because it helps you evaluate the profitability and feasibility of an investment. It can also allow you to compare the expected returns of different investment opportunities, and to make informed decisions about which projects to pursue.
If the NPV is positive, it means that the project is expected to generate more cash inflows than outflows, and thus, it’s a profitable investment opportunity. However, if the NPV is negative, the project is expected to result in a net loss and is therefore not considered a viable option.
The NPV calculation is an important tool in finance as it can help decision makers determine whether to move forward on a project.
What is the Internal Rate of Return (IRR)?
The Internal Rate of Return (IRR) is used to measure the profitability of an investment project or opportunity, often in conjunction with calculating NPV. It is the discount rate where the present value of expected cash inflows equals the present value of expected cash outflows, or when NPV is equal to 0.
IRR represents the rate of return at which an investment will break even over its lifetime. It is shown as a percentage. And if you use the IRR percentage as your discount rate in the NPV calculation, the result will be an NPV of 0.
With Excel, you can quickly calculate the IRR through a simple formula, rather than having to go through a time-consuming process that might otherwise involve trial and error.
Calculating NPV and IRR in Excel
To illustrate how to calculate NPV and IRR, I’ll use the following example. Suppose that you are investing $1,000 into a project that will generate the following cost savings:
Year 1: $50
Year 2: $100
Year 3: $250
Year 4: $300
Year 5: $600
In total, that is $1,300 in cost savings. Although that’s more than the original $1,000 investment, those savings are spread out over a period of five years. To get a true picture of whether the project is worthwhile, you need to adjust for the time value of money and adjust those amounts and calculate their present values — what their values are today. This is where the NPV function comes into play.
However, before using the NPV function, you need to determine the discount rate that you are going to use. The discount rate is important as it tells you the interest rate that you will be using when adjusting the cost savings back to today, and to calculate the present value. If the discount rate is high, then it’ll be more difficult for the NPV calculation to be positive (and hence, suggest that the investment should be taken on). And if the discount rate is too low, then it could be too easy to clear the bar and for the NPV formula to suggest the project is worthwhile.
The discount rate should be higher than the risk-free rate since you are taking on some risk, and thus, you should be compensated for doing so. If you were to use the same rate as what you could earn on a treasury bill or a bank deposit, there would be little incentive to go ahead with the project even with a positive NPV. After all, what’s the point of taking on the risk if you’re not getting a better return?
In this example, I’m using a discount rate of 5%. This is what the NPV formula will look like with all of the inputs:
As you can see, the order of the values is important as that will determine how many periods each value will be discounted by. The result of this formula is a value of $71.21. It’s a positive amount, indicating that the project should be undertaken as the present value of the future cost savings offset the current investment.
To prove that calculation out, I’ll show you how this calculation could be done manually. Here, for example, is how the present value would be calculated for the $50 in cost savings that is achieved in year 1:
One plus the discount rate is raised to a power of negative one to bring the value back one period, using the discount rate. That returns a value of $47.619. Here are the other present value calculations:
Year 2 ($100) : $90.703
Year 3 ($250) : $215.959
Year 4 ($300) : $246.811
Year 5 ($600): $471.116
If you add all of these present values up, they total $1,071.21. And that is $71.21 more than the $1,000 initial investment, which is the same result as the NPV formula.
One thing you may be wondering is at what point does the value equal 0 — where is the breakeven? This can be calculated using the IRR formula. In Excel, this is a simple formula that just takes all the inflows and outflows. For example, if you had the negative investment amount of $1,000 in cell A1 followed by the cost savings in the the adjacent columns (until column F), then the formula for IRR would be as follows:
The end result is a value of 6.8576%. If you use this as the discount rate in the NPV calculation, you will get an NPV value of 0. This tells you that if you use a discount rate higher than this percentage, your NPV value will be negative as the level of discounting will be too high for the project to have a positive NPV value. On the other hand, anything below the IRR rate will result in a positive NPV value and thus indicate that the project should move forward.
If you liked this post on How to Calculate Net Present Value and Internal Rate of Return 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Creating a dashboard can be an effective and efficient way to pool in many data points. In this post, I’ll show you how to create a dashboard that factors in several economic indicators, including inflation, interest rates, housing starts, GDP, unemployment, and the performance of the stock market. It will utilize power query and allow you to easily refresh the data.
Creating and collecting the data points
To make the data that I’m dynamic, I will also use a variable for the current date, so that the data will automatically update. In this example, it will be called todaysdate which is equal to the following formula:
Below are the sources for the data that I will use in creating this dashboard along with the Power Query links I will use (along with the variable for the date). I’ll also set up the Power Query links as named ranges in the Excel spreadsheet, making it easy to reference them within the queries.
The name is case-sensitive so if you use a named range that is all in lowercase as I have done, then those references also need to be in lowercase in Power Query. However, for the purposes of this example, you don’t need to use named ranges and it is an optional step.
Creating the Power Query connections
To create a Power Query connection, I’m going to start by going into the Data tab and selecting From Web under the Get & Transform Data section. For the unemployment rate data, I’ll use the link for that:
After click on OK, I’ll select the table that I want to use, which is the first one on the list:
I’ll click on the Transform Data button before loading it. What I will do is split the Month column so that I have both a Month and Year field. To do this, I’ll select the column, right-click and select the option to Split by Delimiter and use a space. I’ll also use this opportunity to put in my named range for the data link. In the Power Query window, under the Home tab, there’s an option to click on the Advanced Editor. Here, I’ll enter my NamedRange variable and use that when referencing the Source:
When you’re running a query for the first time, you may see a warning asking you about Privacy Levels. Set these to Public and select Save.
Now it’s time to repeat the steps for the other data sources.
Transforming the data in Power Query
There will be some adjustments that need to be made along the way when loading the data. For example, for the data that comes from the FRED website, there are some rows at the top that need to be removed:
In this case, I’ll need to click the Remove Rows button at the top, and specify that I want to Remove Top Rows and enter a value of 11, to remove the first 11.
For the housing and inflation data, I need to make additional adjustments since the data is raw and doesn’t show the percent change, which is what I want. Here are the steps I’m going to take for those queries:
Unpivoting the data. This is important for the sake of making sure that months are not going across and are instead going vertically. Refer to this post on how to flip and unpivot data in Power Query.
Generating previous and current period data. I’ll create a calculated column to calculate the current period and the previous period. After the current period column is created (by simply joining the month and year together), I’ll duplicate the query so that there is an additional table for the inflation data. As for the previous period, this involves subtracting 1 from the year to get the previous year’s values. Then, the year and month are concatenated:
Doing a lookup of the prior-year period. I’ll now merge the query with the one I copied earlier (the other inflation period). This involves doing a lookup of the previous period on the other table’s current period. The goal here is to get the prior-year period’s value. Here’s an overview of how to merge queries in Power Query.
Calculating the percent change. Once the prior-year period’s value is loaded and on the same row, I can create a custom column to calculate the year-over-year change, which is just the new value / old value -1.
Removing unneeded values. The final steps involve removing any blank values from the inflation rate and removing and periods that contain the word “HALF” indicating half-year values. Lastly, I’ll split the columns back out so I again have the year and month broken out, this time, along with the inflation rate %:
These steps will be similar for the housing data, except I won’t need to unpivot the data since it isn’t broken out by month and year.
Creating the pivot tables and linking to the data
Now that the data is loaded, the next step is to link to it or create pivot tables, to populate the dashboard. For the unemployment data, I will summarize the average by year:
For the GDP tab, I’ll pull in just the four most recent quarters. To do this, I can use the INDEX function and the COUNTA function to grab the furthest values. For the most recent period, I can use the following formula:
For more recent periods, I’ll deduct 1, 2, and 3 from the COUNTA value:
The interest rates I will leave as is as that data can chart smoothly given that there normally aren’t many interest rate changes.
For the inflation rate, I will again take the average annual rate using a pivot table but only looking at data since 2010:
On the housing tab, I will break out the average housing starts by quarter, again using a pivot table:
Creating the dashboard
Now that the pivot tables are set up, I can start putting together the dashboard.
For starters, I’m going to go for a clear, dark background, setting it to black. I’m going to create headers for each of the different categories: Unemployment, GDP, Interest, Inflation, Housing Starts, and Stock Market. I’ll link to the key data, referencing the key metric that I want from each tab. Each header will take up three columns, with a space between each one:
What I will also do is create some conditional formatting rules for these values so that they can appear green or red based on their values. Refer to this post for an in-depth overview on conditional formatting. Below the values, I will also extract the date of the most recent data and put it within a formula, to show when the data was last updated:
Next, I’ll create the charts for the different pivot tables. This is really down to preference and style, but I’ll use a combination of bar, column, and line charts to display the data. Here’s how the dashboard looks after adding a title:
And with the data all coming from the web and utilizing Power Query, you can simply just refresh the data to pull the latest numbers, making your dashboard dynamic and easily updateable.
If you liked this post on How to Create a Dashboard in Excel to Track Economic Indicators, 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.
A key performance indicator (KPI) is a way to track how well you’re progressing towards a particular goal. Oftentimes, you might have many KPIs that you will want to track. You can create these visuals in Excel using donut charts, and below, I’ll show you how you can also have them change color based on whether you’re on track for hitting your goal or not.
Start by categorizing your results
One thing you should consider doing is to create different groups to categorize your results. For example, suppose a key metric was to ensure operating expenses were no more than $10,000 for the current period. If my actual expenses are at $9,000, I would want the chart to show green and to indicate I’m on track versus if my actuals were over $15,000 and I was way over budget.
I can classify these values based on how close they are to the target amount. Here are three categories I will set up and the rules for them:
On Target: If the actual amount is <= 100% of the target.
Slightly Over: If the actual amount is >100% and <=125%.
Well Over: If the actual amount is >125%.
One field I will also create to help track the progress will be % of Target where I take the actual and divide it by the target. Your rules could vary depending on KPI. With expenses, obviously the goal will be to come in under them whereas with sales the incentive will be to come in higher. So you don’t want to assume that your calculations will always be the same in every situation.
I also created a field called Remainder which will capture the unfilled part of the circle. Think of the top half of a circle adding to 1 and the bottom half to another 1, together they total 2. And for the remainder, I use the a formula that takes the maximum of 0 and 2 – the % of Target amount. The purpose of this is to ensure that the remaining amount isn’t negative and that everything adds up to 2.
By creating these classifications, it will be easier to set up the chart to show different colors based on which category a result falls into. Here’s an example of how this might look on Excel. These categories have been created using IF statements based on the rules noted earlier.
The key goal of creating these categories is by ensuring no result shows up in more than one place. For Expense 1, it was on target so that’s the only category it falls under. Expense 2 was 20% higher than the target, so it goes into the ‘slightly over’ category. And Expense 3, which was 50% higher, it falls into the ‘well over’ category.
Now that these categories are set up, I can go about and create the actual chart.
Creating the donut chart
Using the table shown above, I’ll create a donut chart for Expense 1.
This includes all the categories I have set up, which isn’t what I want. There are multiple changes I’m going to make to this chart:
Remove the unneeded fields.
Apply different colors for the categories.
Adjusting the chart so it goes from left to right.
Adding some text boxes.
To remove the fields that aren’t needed, I’ll right-click on the chart and click on Select Data. Then, uncheck the first three field:
Next up, I’ll adjust the colors. The easiest way to do this is to click on the different colors in the legend box:
I’ll click on the blue box for ‘On Track’ series and select the color Green from the Home tab for that (note: you’ll first have to select the legend, and then click on the individual series). After setting all the different colors, this is what my chart looks like thus far:
I still need to adjust the starting point of the chart as the green slice starts from the middle, not the left. To fix this, I right-click on the chart and select Format Data Series. Then, I’ll change the angle of the first slice to 270:
Here you can also change the hole size. The smaller the hole, the larger the slices will be. If I adjust it down to 50%, here’s what my updated chart looks like:
At this point, the legend really isn’t necessary anymore since the colors will do the job and I don’t really need the labels.
One final step you may want to consider is to use a text box instead of a label. Once you’ve added a text box, you can link it to the name of your metric (this can be done through the formula bar). Repeat the same steps for the Actual, and you can have both the name of the metric and the value to automatically update:
When using textboxes, I always format them to remove the background fill and remove the border. You can do this by right-clicking Format Shape and select No Fill and No Line
Now if I were to update the Actuals for Expense 1 to $15,000, pushing me into the ‘well over’ category, my chart would automatically update:
If you liked this post on How to Create and Track KPIs in Excel Using Donut Charts, 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.