CC Balance Transfer Savings

Calculate Interest Savings on a Credit Card Balance Transfer in Excel

by functionspersonal finance

A balance transfer for a credit card allows people to move the outstanding balance from one or more credit cards to a new card, often with a lower interest rate. The purpose of a balance transfer is to reduce the amount of interest paid on existing debt, which can help cardholders save money. Many credit card issuers offer promotional periods with significantly reduced interest rates, sometimes as low as 0%, for a specified duration, typically ranging from six months to 18 months.

By transferring high-interest credit card balances to a card with a lower or 0% interest rate, cardholders can save a substantial amount of money on interest payments. For example, if an individual has a $5,000 balance on a card with a 20% annual percentage rate (APR), they would accrue approximately $1,000 in interest over a year. However, if they transfer that balance to a card offering a 0% APR for 12 months, they can avoid paying any interest during the promotional period, allowing them to allocate more of their payments towards the principal balance.

Additionally, a balance transfer can simplify debt management by consolidating multiple balances into one monthly payment. This can make it easier to keep track of payments and avoid missed or late payments, which can result in additional fees and negatively impact one’s credit score. However, it’s important to be aware of balance transfer fees, which typically range from 3% to 5% of the transferred amount. Even with these fees, however, the potential interest savings often outweigh the cost, making balance transfers an attractive option for those looking to manage their debt more effectively.

In the example below, I’ll show you how an Excel spreadsheet can help you estimate just how much you might save with a balance transfer.

Calculating credit card interest savings in Excel

You can estimate the interest savings from a balance transfer in Excel using the future value (FV) function. The function takes multiple arguments:

  • Interest Rate
  • Number of Periods
  • Payment Amount
  • Present Value
  • Payment Type (beginning or end of period)

Since we are looking at monthly payments, we will need to convert the interest rate to a monthly percentage. For this, take the annual credit card rate and divide it by 12. If your credit card charges a 20% interest rate annually, dividing that by 12 will give you a monthly percentage of 1.67%. In Excel, this would be input as 0.0167.

The number of periods would represent the number of months. If it’s a six-month promotional period, then six would be your input, 12 if it’s for 12 months, and so on.

As for the payment, let’s suppose that you will pay 3% of the balance. On a $1,000 credit card balance, that would translate into a monthly payment of $30.

For the present value, we’ll enter a negative value to indicate an amount owing. The input will be -$1,000.

The last argument, for the type of payment, can be left blank, assumes that a payment will be made at the end of the period. By setting it to a 1, that will assume the payment is made at the beginning. For the purposes of estimating, however, this won’t have a significant impact on the calculation.

The complete formula for the calculation based on the above assumptions for a 12-month period is as follows:

=FV(0.2/12,12,30,-1000)

This returns a value of $824.49. This is what the balance would be after 12 months of payments. Next, let’s compare this to what the balance would be if the interest rate was 0%. This what that formula would look like:

=FV(0,12,30,-1000)

This returns a value of $640, and it’s the same as if you were to deduct $360 (12 payments of 30) from the balance. Thus, the savings from the balance transfer, before accounting for fees, would be:

824.49 – 640.00 = 184.49

Furthermore, let’s assume there is a 4% balance transfer fee. On a $1,000 balance, that would be $40. The final cost savings would be as follows:

184.49 – 40.00 = 144.49

This is just one example of a savings calculation, but let’s adjust this so that it is more adaptable to other scenarios, and create a template which can be easily modified.

Creating a template to calculate savings from balance transfers

With the logic setup, to create a template is just a matter of determining the inputs to plug into the calculation. The variables which a user should be able to adjust are as follows:

  • The current interest rate
  • The promotional rate
  • The monthly payment amount
  • The credit card balance
  • The promotional period in months
  • The balance transfer fee

For this example, I’m going to use much larger amounts to help emphasize the potential savings. These are the inputs I’ve created in my sheet:

Excel sheet with inputs for credit balances and interest rates.

Now, it’s a matter of setting up the formula which links to these values. To do this, we need to combine the following calculations:

  • The FV based on the current credit card interest rate,
  • The FV based on a 0% credit card interest rate
  • Calculating the difference between the two FV calculations
  • Deducting the balance transfer fee from the above calculation

The first FV calculation is a copy of what was used before, except this time the values are not hardcoded and are instead linked back to the input cells above:

=FV(B1/12,B5,B3,-B4)

For the second FV calculation, we just need to reference the promotional rate:

=FV(B2/12,B5,B3,-B4)

Then, we deduct the difference between these calculations:

=FV(B1/12,B5,B3,-B4)-FV(B2/12,B5,B3,-B4)

The following formula also factors in the balance transfer fee:

=(FV(B1/12,B5,B3,-B4)-FV(B2/12,B5,B3,-B4))-(B6*B4)

Assuming a $20,000 credit card balance, with a monthly payment of $600, an interest rate of 20%, a 12-month promotional period at 0%, and a 4% balance transfer fee, the total cost savings would be approximately $2,889.74. The completed template is setup as follows:

Cost savings calculation in Excel showing the benefits of a credit card balance transfer.

By setting up this template in Excel, you can adjust these inputs to do your own what-if analysis. These calculations assume that your credit card balance does not change and that you are simply paying it down and not adding to it.

If you like this post on How to Calculate Interest Savings on a Credit Card Balance Transfer 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.

Amortization calculator which calculates values for a specific period.

Create an Amortization Calculator in Excel

by accountingamortizationcalculatorfunctions

Amortization schedules are essential tools for anyone managing a loan or mortgage, providing a clear breakdown of how payments are allocated towards interest and principal over the loan’s term. Typically, these schedules detail each payment’s date, the interest portion, the principal portion, and the remaining balance, offering borrowers insight into the precise cost of borrowing over time. Creating an amortization schedule in Excel can be a time-consuming process, but with the help of some advanced payment functions, you can expedite the process and even eliminate the need for creating an entire amortization table.

Generating the key components of an amortization schedule

Whether you’re creating a full-blown amortization schedule or just want to calculate the balance at a future point in time, you’ll need to know the following values before you can begin:

  • the loan value,
  • the interest rate and compounding,
  • the number of periods, and
  • the start date of the loan

Suppose you have a $500,000 loan with a 5% interest rate, which is for a period of 10 years, and that payments are made on a monthly basis. Given this information, you can start by calculating the monthly payment amount. Here’s what the inputs would look like for the PMT function:

=PMT(0.05/12,10*12,-500000,0) = 5,303.28

This assumes compounding to be monthly, hence the need to divide the interest rate by 12. And the negative 500,000 balance tells the function that this is an amount owing and that it will reduce over time. The following argument, 0, is to signify that the future value is 0.

There is also an additional argument, for whether the payments are made at the beginning or the end of the period. The default is set to the end of the period. If payments are made at the beginning, then the final argument is set to 1. If the payment is at the start, then the payment value would change to $5,281.27, assuming all the other values remain the same. But for the purposes of these examples, we’ll assume payments are made at the end of the period.

Creating a calculator for specific month’s calculations

Now that you have all the necessary variables, including the payment amount, you can start to create the calculator.

In this first case, we’ll look at how to calculate values for just a specific period. Let’s assume the loan’s start date is January 1, 2024. And let’s assume we want to calculate what the balance, interest, and principal payment amount will be for the month of Dec 2025.

How to calculate amortization amounts for a specific period:

1. Start with calculating the period number. One function that can help with this is the DATEDIF function. By using it, you can quickly calculate the difference between two time periods. Here’s how it would work in this example:

=DATEDIF(“1/1/2024″,”12/1/2025″,”m”) = 23

Two full years have not fully elapsed until we get to 1/1/2026. But since we want to calculate the values for the 24th month, we’ll need to add 1 to the equation. The formula to calculate the current period will be as follows:

=DATEDIF(“1/1/2024″,”12/1/2025″,”m”)+1

2. Calculate the interest payment for the period. To get the interest amount, use the IPMT function:

=IPMT(0.05/12,24,120,-500000) = 1,760.19

3. Calculate the principal amount paid down during the period. In this case, we can use the PPMT function. It is the same setup as the previous formula:

=PPMT(0.05/12,24,120,-500000) = 3,543.08

4. Calculate the ending value as of that period. Using the FV function, here’s the formula to get the ending balance as of the end of Dec 2025:

=FV(0.05/12,24,5303.28,-500000) = 418,902.68

By using these functions, you no longer need to make an entire amortization schedule. You can simply do a calculation for the period you need to sum up the values for. By creating inputs for the start of the loan, loan amount, interest rate, # of periods, and the period you want to calculate for, you can setup an amortization calculator as follows in Excel:

Amortization calculator which calculates values for a specific period.

Calculate amortization values for a specific range

Suppose you wanted to calculate the interest and payment values for all the months in 2025. This can be a bit trickier since we aren’t calculating amounts for just a single period anymore. Instead, we need to get a range of values.

How to calculate amortization amounts for a specific multiple periods:

Step 1. Get the correct period numbers. Let’s assume the loan began on June 1, 2023. We need to get the starting period number, which is Jan. 1, 2025. This can be done with the DATEDIF function:

=DATEDIF(“6/1/2023″,”1/1/2025″,”m”)+1 = 20

This tells us that January is the 20th month of the loan. If January is the 20th month, then that also means if we add 11, that December will be the 31st month of the loan. Thus, our range of periods is 20 through 31. You could, however, use the DATEDIF function to do the calculation for that period as well.

Step 2. Calculate the interest payments for the year. Now that we know the periods we need to calculate the interest for, we can use the CUMIPMT function:

=-CUMIPMT(0.05/12,120,500000,20,31,0) = 20,851.88

The present value cannot be set to negative for the cumulative function, or else you will get an error. To adjust for this and avoid a negative value, simply add a dash before the function to ensure the result is flipped from a negative value to a positive one.

Step 3: Calculate the total of the principal payments made during the period. To calculate the principal paid during the period, we can use the CUMPRINC function. The inputs are the same as that of the cumulative interest payment calculation:

=-CUMPRINC(0.05/12,120,500000,20,31,0) = 42,787.43

Step 4. Get the ending period’s balance. Just as with the previous section, we can use the same FV calculation to get the ending balance. This time, it will be for period 31.

=FV(0.05/12,31,5303.28,-500000) = 393,684.23

Here is how the calculator could be setup to make these calculations based on a range.

Amortization calculator which calculates values for a specific range.

You can download the amortization calculator I have created from these examples here.

If you liked this post on How to Create an Amortization 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.

RunningTotalsinPQ

How to Calculate Running Totals in Power Query

by functionspower query

Do you have year-to-date values and need to calculate running totals? What about if you have criteria you want to track running totals for? In this post, I’ll show you how to accomplish both and how you can create a custom function in Power Query that you can re-use in other queries. Here is a simple table I am going to use for this example:

Table in Excel showing sales by day, rep, and location.

In this table we have date, sales, salesperson, and location. Initially, I am just going to calculate the running total, regardless of the salesperson. I just want to know how much everyone has generated up to a certain point in time. The one step that I need to do is sort the dates in order, so they are going from smallest to largest. You can do this in either Excel or within Power Query.

Creating a running total for everything in Power Query

Step 1: Load your data into Power Query

To load your existing table into Power Query, click on your table and select the Data tab and click on From Table/Range.

Selecting the From Table/Range button in the Data tab.

Step 2: Sort your data

If your data is not already sorted in Power Query, then select the Date column and click the button to sot in ascending order.

Sorting data in Power Query.

Step 3: Create an Index column

On the Add Column tab, select the option to insert an Index Column, and select to the option to start From 1:

Creating an index column in Power Query.

This now creates a simple column that starts from 1.

A Power Query table with an index column.

Step 4: Create a column for the Running Total calculation

Select the Custom Column button from the Add Column tab, which will let you create a column based on a formula.

Selecting the Custom Column button from the Add Column tab.

There are two functions will be used in the formula. The first is the List.Sum function, which as the name describes, will sum up the values in a list. Within that will be the List.FirstN function which will grab all the index values up until that point. The custom column formula to enter, is as follows:

=List.Sum(List.FirstN(#"Added Index"[Sales],[Index]))

This is how it appears within the Power Query screen:

Creating a formula for a custom column in Power Query.

This now creates a Running Total column which calculates the year-to-date sales values:

A Power Query table after adding a Running Total column.

Creating a running total for when criteria is met in Power Query

This time around, I’m going to show you how to create a running total which resets for each salesperson. By doing this, you can track their individual year-to-date totals. If you’re starting from scratch, you will need to repeat the steps from the previous section in order to arrive at your Running Total formula.

Step 1: Convert the Running Total formula into custom function

Once you have created the Running Total formula, you can convert it into a custom function. If you select the query and click on the Advanced Editor button on the home tab, you will see the code for all the steps which were previously created:

Power Query advanced editor showing multiple steps.

To reuse this code and use it as a function, I need to add a place to enter a variable at the beginning. 

let runningtotal = (x) =>

In the above example, x is the input. It will refer to the table which the custom function will execute on. And at the end of the custom function, there should be another ‘in’ keyword to circle back to the function, such as follows:

in runningtotal

Here is how my custom function looks like after removing the Changed Type step and setting the source equal to the variable:

The advanced editor showing a formula to calculating a running total.

If you remove any steps, however, just remember to update the formulas. In the “Added Index” step, I updated the step I’m referencing to Source. Otherwise, it would reference a step that doesn’t exist and would result in an error.

After saving, you should notice an fx function next to your query, which you may want to now rename to your desired function name. This is what you will reference it to when invoking the function. In this example, I’m going to call it RunningTotal to keep everything consistent.

Now the function is ready to be used.

Step 2: Group the data

On the Home tab, select the Group By button. This will allow you to break your data into multiple tables.

Selecting the Group By button in Power Query.

In the Group By section, select the option to group by Salesperson and use All Rows as the operation, which ensures no data is lost. I’ll name the new column Data.

Grouping data by salesperson in Power Query.

This now creates a column that contains tables based on the salesperson:

Power Query data which has been grouped by salesperson.

Step 3: Invoke the custom function

On the Add Column tab, press the button to Invoke Custom Function

Invoking a custom function in Power Query.

Then, on the details, enter the column name, the function to invoke, and the data which contains the variable. Since the new ‘Data’ column contains the table, that will be the variable in this example. But if you named the column differently, you would use that name.

The invoke custom function dialog box.

This will now create an additional column, which also contains a table.

A Power Query table after invoking a custom function to create a new column.

If you click on any of the cells in the newly created field, you’ll see that the preview contains the running total field:

A preview in Power Query of a table nested within a field.

Step 4: Expanding the table

The last step involves expanding the tables in the newly created field. By pressing on the two arrows going in opposite directions, you will be able to expand all of the columns.

Expanding a table in Power Query.

You can omit including the Index column as that is not necessary, and the Salesperson field is already there. The only other column you may want to remove in addition to this is the Data column, but that has to be removed with an additional step. Here is how my final table looks:

A Power Query table after adding a field for the running total.

You can see that as the name changes to a different salesperson, the running total doesn’t continue adding; it starts from the first date for that salesperson.

If you liked this post on How to Calculate Running Totals in Power Query, 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.

DateStampsExcel

Create a Date and Time Stamp in Excel Using VBA

by datesfunctionsvba

Do you have a spreadsheet that needs to track dates? Whether it’s a shipping log, an inventory tracker, a sales order template, or just something to track when the last change was made to a cell, there’s an easy way you can create a date stamp in Excel with VBA.

Use checkboxes to make your spreadsheet more user friendly

If you have a spreadsheet where you want to track statuses, using checkboxes can be helpful. This way, someone can check or uncheck the status of an order. This can indicate whether it has been shipped, ordered, or completed. Excel has made it easier to insert checkboxes with a recent update. If you’re using Microsoft 365, then on the Insert tab on the Ribbon, you should see an option to insert a Checkbox:

Selecting to insert a checkbox into Excel.

When you click on this button, it will insert a checkbox right into the active cell that you’re on. Want to insert checkboxes into multiple cells at once? Simply select a range of cell and then click on the button:

Checkboxes in an Excel spreadsheet.

If a checkbox is checked, its value is TRUE. If it is unchecked, then the value is FALSE. This is important to know when creating formulas.

Populating the date using the NOW() function isn’t useful for date stamps

If you want to enter the current date into a cell, you can use the CTRL+; shortcut. The problem is that it won’t change if you go to uncheck and re-check a checkbox. It’s a stale value and it isn’t a formula.

What you may be tempted to use is the NOW() function. However, the limitation here is that anytime the cell recalculates, it will refresh with the current date and time. It won’t hold the existing date stamp. You can create a circular reference and adjust iterative calculations. But there’s an easier way you can create a date stamp in Excel with just a few lines of code using VBA.

Creating a custom function using VBA

You can create a custom function with VBA. To do, start by opening up your VBA editor using ALT+F11. On the Insert menu, select the option for Module. There, you’ll have an empty canvas to enter code on. The custom function can simply contain one argument — the cell that contains the checkbox. This is to determine whether it is checked (TRUE) or unchecked (FALSE). If it is checked, then the timestamp will be equal to the current date and time. If it’s unchecked, then the timestamp will be blank, and so will the cell value.

Here’s the full code for the function:


Function timestamp(checkbox As Boolean)

    If checkbox = True Then
        
        timestamp = Now()
    
    Else
    
        timestamp = ""
    
    End If
    

End Function

This function is now created. To use it within your spreadsheet, all you need to do is select a cell where you want the date and time to populate on. Then, assuming your checkbox is in cell A2, enter the following formula:

=timestamp(A2)

This will run through the VBA code to determine whether to populate the current date and time or not. Since there is no NOW() function present in this formula, it won’t recalculate with the current date.

Formatting your date and time

Even if the custom function work, you may notice that the value that it populates doesn’t look right. If you get a number or the time is missing from the date, then you’ll need to modify the cell format. To do that, select the cell and press CTRL+1. Then, select the Date category where you’ll see various date formats:

Formatting cells in Excel

If you scroll down the list, there will be an option that shows the date and time:

Selecting a date format in Excel.

If you use that format, then your date will now look correct, including both the date and time.

If you like this post on How to Create a Date and Time Stamp in Excel Using VBA, 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.

UniqueValuesCSVFormat

How to Extract and List Unique Values in Excel Into CSV Format

by functionsunique

Excel’s new functions help make it even easier to analyze and extract data efficiently and effectively. One example is extracting a list of unique values from a list. What you can also do is sort that list. Plus, you can then put all those values into a single cell, with each value separated by a comma. Data in the form of a comma-separated value (CSV) can make it easy to compile data into one place, without taking on too much space. In this article, I’ll show you how we can combine all this Excel functionality into one supercharged Excel formula that can do everything I’ve mentioned thus far. Let’s get started.

How to create a list of unique values for a specific criteria

For starters, let’s get a list of unique values that meet a certain criteria. While pulling unique values isn’t terribly difficult in excel and there many ways to pull unique values, I’m going to show you how we can extract unique values that meet a given criteria. Here’s the data set I’ll be working with for this example:

An excel table with client number and invoices.

It’s a straightforward list that includes a client number (column A) along with invoices (column B). But if I want to include just a list of the unique values relating to client 1000, the UNIQUE function on its own won’t help me with that. I need to apply a criteria first. To do this, I’ll first use the FILTER function. Using that function, here’s how I can grab all the values relating to client 1000:

=FILTER(B:B,A:A=1000)

The first argument in the formula is where I want to extract values from. The second argument pertains to my criteria, which is based on the values in column A. With this formula, this is my result:

Invoice numbers extracted from a list.

I get a list of values. But the problem is I have repeating values — invoice #8002 shows up multiple times. But I can put that formula to generate that list within the UNIQUE function:

=UNIQUE(FILTER(B:B,A:A=1000))

Now I have a condensed list which only includes unique values:

A list of unique invoice numbers.

If your data isn’t sorted, you can also put this within the SORT function:

=SORT(UNIQUE(FILTER(B:B,A:A=1000)))

Now you have a formula that filters out data, grabs the unique values, and sorts them. It’s a busy formula. But it’s about to do even more.

Putting the list into a CSV format

As of now, the data is in a list. That’s not a convenient format because the danger is that you may have clients which have only a few invoices, perhaps none at all. Others, meanwhile, might have a dozen invoices. If you are creating arrays, they will inevitably vary in size, and the you’re left with a spreadsheet that doesn’t have much consistency to it.

To get around that, you can put your data in a CSV format. By doing so, you can ensure all of your data is contained within just a single cell.

Here’s the step-by-step process as to how you can put your data into a CSV format:

1. Use the TEXTJOIN function and use a “,” as you first argument. The first argument of this function tells you how you want to separate your data. By indicating a comma, you’re already setting up the result to be in a CSV format.

2. Set the next argument to TRUE. The second argument is whether you want to ignore empty values. You’ll likely want to ignore them, otherwise, you will have blank spaces between your commas.

3. Include your list of values. This is the data that you want to convert into a CSV.

Here is what the complete formula looks like, with step 3 relating to the formula we created at the end of the previous section:

=TEXTJOIN(“,”,TRUE,SORT(UNIQUE(FILTER(B:B,A:A=1000))))

Now the result is the following:

A list of unique invoice numbers in comma-separated format.

The list of unique invoice numbers is now within just a single cell. And each invoice is separated by a comma.

But let’s make this formula more dynamic. It should be able to generate a list based on each client, in the following table:

A table with client numbers filled in and invoices left blank.

With the client values in column D, starting with cell D3, this is how I can adjust the formula so that it is not referencing a hardcoded invoice number:

=TEXTJOIN(“,”,TRUE,SORT(UNIQUE(FILTER($B$3:$B$101,$A$3:$A$101=D3))))

Using that formula, the table will now populate the list of unique invoices for each client:

A table with client numbers and invoices filled in.

If you like this post on How to Extract and List Unique Values in Excel Into CSV Format, 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.

ReplaceMultipleValues

How to Replace Multiple Values in Power Query

by functionspower query

Replacing values can be an important part of cleaning up your data and preparing it for data analysis. Below, I’ll outline the steps to take to replace a value in Power Query. I’ll also show you how you can create a formula in Power Query to make it easy to replace multiple values at once.

Replacing a single value in Power Query

In the following data set, I have a list of orders. There are dates, order numbers, and statuses. Some of the statuses may be a bit similar so to reduce the number of them, it can make sense to replace values.

A list of orders and statuses in Power Query.

I am going to replace to the ‘Awaiting Authorization’ status to ‘Pending’.

Here are the steps needed to take to replace a value in Power Query:

1. Load your data into Power Query.

2. Right-Click on the column where you want to replace values and select Replace Values

Selecting the option to Replace Values in Power Query.

3. Enter the value to find and what to replace it with, and then click OK.

Selecting which value to replace.

Now, Power Query will replace the value for you:

Order table in Power Query after replacing a value.

This isn’t an ideal solution, however, because doing it this way would require you to repeat these steps over and over again. Instead, there’s another way to do this.

Replacing multiple values in Power Query at once

If you want to replace multiple values in a single step in Power Query, you can accomplish that through a formula. The Table.ReplaceValue function allows you to specify the values you want to replace. For instance, to replace just a single value, this would be the formula:

= Table.ReplaceValue(#"Changed Type","Awaiting Authorization","Pending",Replacer.ReplaceText,{"Status"})

Where #”Changed Type” is the name of the preceding step. In this formula, any instance of ‘Awaiting Authorization’ is replaced with ‘Pending’.

If you want to replace multiple values, then you can use if statements to check for multiple conditions:

= Table.ReplaceValue(#"Changed Type",each [Status], each if [Status] = "Awaiting Authorization" then "Pending" else if [Status] = "Awaiting Shipment" then "Pending" else [Status], Replacer.ReplaceText,{"Status"})

The same function is used. However, by using the ‘each’ keyword, it will now cycle through the values in the [Status] field. It will do the original search for ‘Awaiting Authorization’ and replace it with ‘Pending’. There is also an else if statement which allows the formula to go even further and also replace ‘Awaiting Shipment’ with ‘Pending’. Finally, if there are no matches for either of those terms, then it will just leave the value that is already in the ‘Status’ field.

You can even add more else if statements to replace more values if necessary. By doing this, you can make the process even more efficient by swapping out even more values through a single step.

If you liked this post on How to Replace Multiple Values in Power Query, 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.

TrendFunction

How to Use the Trend Function in Excel

by analysisforecastingfunctions

The TREND function in Excel is a powerful tool that allows users to perform linear regression analysis and make predictions based on existing data. This function is particularly valuable for professionals dealing with data analysis, financial modeling, and forecasting. In this article, I will go over how the function works, and provide step-by-step instructions on how to utilize it in your Excel worksheets.

Using the TREND function

To use the TREND function, follow the steps below:

1. Organize the data

Before you can use the function, you need to have your data organized so that it includes at least two columns. One needs to be for the independent variables, or the x-values, and another one for the dependent variables, or y-values. It is necessary for the data to be aligned correctly so that the information correctly relates to one another (i.e. you don’t want the wrong values lined up next to one another).

Below is sample data for a company which sells seasonal products. In warmer weather, revenue rises while in cooler temperatures, sales are lower.

Excel table showing sales by month and the average temperature.

2. Calculate the Trend Line

With the data populated, you can now enter it into the TREND function in Excel. This involves specifying the following arguments:

  • known_y’s
  • known_x’s
  • new_x’s
  • constant

In the above example, the known_y’s are the sales, the known_x’s are the average monthly temperatures. If I don’t fill in any new_x’s or specify the constant, the function will still try and plot out the rest of the values:

Excel table showing the effect of a trend function without plotting in new x values.

The problem in this scenario is that it doesn’t take into account the temperature; it simply assumes a similar trend as before. The function is much more useful if I have forecasted monthly temperatures. That way, the trend calculation will take that into account. Suppose I fill in the data, telling Excel that I expect the temperatures to be much warmer over the next 12 months:

Excel table showing forecasted and actual amounts.

With the previous forecast off to the right, you can see that the TREND function has adjusted to reflect the newer information. Thus, the more data you plug into the function, the more reliable the forecast will be. Otherwise, it will simply assume the same patterns will repeat from before, which may not necessarily be the case.

There is an additional argument in the function that you can also adjust, and that is the constant. If you set it to false it will be 0. If set to true, then the formula will calculate it. This is the b variable which is part of the y=mx+b equation. If you expect there to always be a minimum, a constant amount, then you may want this to be calculated. If, however, the data can fluctuate wildly, then you may want to set it to true so that there is no intercept. Here’s a comparison with the above data both when there is a constant and when there isn’t:

Excel table showing actual and forecasted amounts with a constant and without one.

The forecast in green is where the argument is set to false (constant is set to zero) and blue is where it is true and a constant is calculated. From the chart below, you can see that there isn’t a big difference but the highs are higher and the lows are lower when there is a constant. This may, however, not always be the case as it will depend on your individual data set.

Chart showing the trend calculation with both a constant and without one.

Create a chart to differentiate between actuals and forecast

One thing you may find helpful to do when creating a forecast is to put those amounts on a different column:

Actual and forecasted amounts in a table.

By doing this, you leave yourself space to add actuals later on and to compare them against your forecast. You can also create a chart with the forecast being a different series. In the below chart, I have used a dotted line to show the forecast while the actuals remain solid. For the first forecast amount, I set it to the same as the actual. This way, when I create the chart below, there are no gaps and it is merely a continuation of the line.

Actual and forecasted amounts plotted on an Excel chart.

If you liked this post on How to Use the Trend Function 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.

CustomPQFunction

How to Create a Custom Function in Power Query

by functionspower query

Excel’s Power Query is a powerful data transformation and analysis tool that allows users to retrieve, clean, and shape data from various sources. While Power Query provides an extensive set of built-in functions, there may be scenarios where you need to perform custom operations on your data. This is where custom functions in Power Query come into play. In this article, I will go over how to create a custom function in Power Query that you can invoke and re-use.

Steps to creating a custom function in Power Query

Creating custom functions in Power Query involves using the M language, which is the scripting language underlying Power Query. It can be complicated to create but I’ll show you two ways you can create a function. The first method is directly through coding, the other is after converting a query into a function.

In this example, I’m going to pull all the stocks that are contained from a list of exchange-traded funds (ETFs). I’ve created the following table for this purpose, called tblETF:

A table of different exchange-traded funds listed.

Creating a custom function from scratch

If you’re creating a function in Power Query directly from code, here’s how to do that:

1. Go to load the data into Power Query by selecting a cell in your table, then click on the Data tab and click From Table/Range.

Launching Power Query from the From Table/Range button.

2. That will open up Power Query. Once there, on the Home Tab, click on the Advanced Editor button:

Selecting the Advanced Editor option in Power Query.

3. Create a name for the function using the let variable. In this example, I’m going to call it getholdings and it will pull all the holdings from the etf field. The opening line of the code is as follows:

let getholdings = (etf) =>

4. Next, list the commands that the function should execute. I’m going to pull the data from the stockanalysis.com page relating to the etf. This requires using the Web.Contents function and modifying the URL so that it includes the etf symbol:

let

Source = Web.Page(Web.Contents("https://stockanalysis.com/etf/"&etf&"/holdings/")),

Then, reference the data as follows:

Data0 = Source{0}[Data],

The full code looks like this:

Custom macro in Power Query that gets the ETF holdings.

5. Now that the function is created, go into the query for the list of ETFs. Create an additional column from the Add Column tab, and select the button to Invoke Custom Function.

Selecting the Invoke Custom Function option in Power Query.

6. Set a column name for the new column. Then, specify the function query to reference. And you’ll also need to specify where the ETF value is coming from, which involves selecting the column:

Invoking a custom function in Power Query.

7. Next, you’ll expand the table that has been created within the column. This is done by pressing on the icon that shows arrows going in opposite directions. Then, select all the available columns.

Expanding a table in Power Query.

You should end up with something that looks like this:

Fully expanded Power Query table showing holdings by ETF.

You can now click Close & Load and this data will load in your Excel spreadsheet. Now you can add to your ETF list and refresh the data, and the table of all the holdings will populate.

List of ETFs and their related holdings in two separate excel tables.

Converting a query into a function

If you’re not comfortable coding with Power Query, you can first create the steps, and then convert the query to a function.

First, it’s necessary to create the query. In the previous example, I loaded the URL from a dynamic web page. To do that, I’ll start with selecting the From Web button on the Get & Transform Data section:

Using the From Web button on the Get & Transform Data section in Excel.

Next, populate the entire link, without the ETF variable — this will be added later:

Setting the URL that you want to pull data from into Power Query.

Then, select the table that contains the data and click the Load To button and select connection only:

Selecting the table in Power Query that contains the ETF holdings.

Then, right-click on the query to edit it so that you’re back in Power Query. From there, click on the Advanced Editor and you should see this:

Power Query advanced editor showing the code to extract data from a web page.

This is similar to the code in the first approach. To convert this into a function, we need to add another let variable and specify the function name, and any variables that will be used in the function. For the first line, I’ll add the following

let getholdings = (etf) =>

and for the URL, I’ll put the etf variable into there:

Source = Web.Page(Web.Contents("https://stockanalysis.com/etf/"&etf&"/holdings/")),

For the last line, I’ll add another in variable:

in getholdings

Here’s the updated code, with the changes highlighted in yellow:

Now I’ve converted my query into a function that can be invoked.

If you liked this post on How to Create a Custom Function in Power Query, 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.

HstackVstack

Use VSTACK and HSTACK in Excel and Google Sheets to Consolidate Lists

by functionsstocks

Do you have multiple lists in Excel or Google Sheets that you want to combine together? With new functions such as VSTACK and HSTACK, you can do just that. In this post, I’ll show you how you can also filter out duplicates and apply sorting so that your data is organized after consolidating all of your lists.

Combining multiple stock lists into a large one

In this example, I’ll use various stock lists that I want to combine into one large list. On Yahoo Finance, you can find an assortment of different lists to help filter stocks. Below, I’ve pulled the lists of stocks that recently hit new 52-week highs, smart money stocks, medical device and research stocks, and e-commerce stocks:

Lists of stocks from Yahoo Finance posted in an Excel spreadsheet.

The advantage of keeping the lists separate is that you can more easily update them. And by using VSTACK, you can combine these lists into a larger one so there’s no worry about having to consolidate them later on.

Based on the lists above, this is the formula that I use to combine them all together, using VSTACK:

=VSTACK(A2:A31,B2:B10,C2:C31,D2:D20)

Since I don’t want to include the headers, I start from row 2. You’ll notice that I’ve hardcoded the ranges here. One way to make this more dynamic would be to use a COUNTIF or COUNTA function for the individual lists, and then use the INDIRECT function to limit the scope of the list. Another option involves converting the lists into tables. That way, you only have to list the table column and you don’t have to worry about the ranges. The one caveat here is that if you have lists that have different lengths, you’ll want to make each list its own table. Otherwise, Excel will automatically fill in the gaps with blank values:

List of stocks in a table.

While the data looks correct, if I were to use the VSTACK formula for these different table columns, I would get a consolidated list that involves many zero values. To keep it cleaner, it’s easier to just separate them into their own tables, and then reference them afterwards.

List of stocks broken down into multiple tables.

To reference these columns, my formula becomes much simpler:

=VSTACK(Table1[Recent 52-Week High],Table2[Smart Money Stocks],Table3[Medical Device and Research Stocks],Table4[E-commerce])

The advantage of doing it this way is that now I don’t have to worry about hardcoding the ranges, and thus, it’s easier to update.

Whichever method you prefer, the end result should look like a consolidated list:

Consolidated list of stocks in Excel.

Removing duplicates and sorting the list

In some of these lists, there is some overlap. AMZN and META are two stocks that show up twice. This means that my consolidated list will include those values multiple times. To get around this, I can embed the formula within the UNIQUE function:

=UNIQUE(VSTACK(A2:A31,B2:B10,C2:C31,D2:D20))

If you also want to sort your list, then you can add the SORT function as well:

=SORT(UNIQUE(VSTACK(A2:A31,B2:B10,C2:C31,D2:D20)))

Use HSTACK for horizontal arrays

If you have the same lists but instead have them going horizontally, then you can use the HSTACK function. It works the same way as the VSTACK but as the H suggests, it will require horizontal arrays. Here are the same list of stocks as in the first example, this time transposed so that they go horizontally:

In this case, the formula for HSTACK would be as follows:

=HSTACK(B1:AE1,B2:J2,B3:AE3,B4:T4)

You can apply the same steps as for the VSTACK to eliminate duplicates and to sort the results.

These formulas work the same in Google Sheets as in Excel

Whether you’re working in Google Sheets or Excel, these formulas will be the same. The VSTACK, HSTACK, SORT, and UNIQUE functions are all available on the latest version of Excel and on Google Sheets. There is no need to change any of the formulas besides just adjusting for any difference in ranges. The formulas themselves work in the same ways, making it easy to transfer data between Google Sheets and Excel and to replicate these formulas wherever makes sense for you.

If you liked this post on How to Use VSTACK and HSTACK in Excel and Google Sheets to Consolidate Lists, 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.

CountWords

How to Count Words in Excel

by countfunctionslensubstitutetextsplit

Excel has many different functions that can help you parse out text from cells. This includes the LEN, MID, LEFT, and RIGHT functions. By utilizing these and other functions, you can get just the values you want. And by determining the number of blank spaces within a cell, you can also determine the number of words that a cell contains. There are multiple ways you can count cells in Excel, I’ll start with using the easier, and newer TEXTSPLIT function.

Method 1: Counting words using the TEXTSPLIT function

The TEXTSPLIT function is available for users who have Microsoft 365 and so if you do not see that function available as you type it in, you’ll need to move to the second approach. Using the TEXTSPLIT function, you can turn a single text value in a cell into multiple cells or columns. And you can specify how you want to split a cell; which delimiter you want to use.

In the example of counting words, the delimiter you would use is a blank space, as specified with ” ” in the delimiter argument. Here’s a list of article titles that I am going to use for this example:

List of article titles in an Excel spreadsheet.

The article titles are in column A. The formula to split the text every time there is a blank space would be as follows, assuming the first value is in cell A2:

=TEXTSPLIT(A2,” “)

This formula, however, would simply put all the words in different columns. Thus, it is incomplete when your goal is to count the number of words. To fix this, the formula needs to be embedded within the COUNTA function. How COUNTA works is that it simply counts the number of nonblank values.

=COUNTA(TEXTSPLIT(A2,” “))

Copying this formula down, these are the resulting values and the number of words found in each cell:

List of article names in Excel with a corresponding number of words found.

Here’s a closer look at how the formula in B2 works, using the Evaluate Formula feature in Excel:

Using the evaluate formula feature in Excel to determine the number of words within a cell.

The TEXTSPLIT function is breaking out each word as its own separate value. And the COUNTA function is then counting each one of those values. When combined, these functions allow you to count the number of words in a cell.

If you’re using Google Sheets, you can use the exact same formula as shown above, with the only difference being that instead of TEXTSPLIT, you’ll use the SPLIT function. It works in the exact same way.

Method 2: Using the LEN and SUBSTITUTE functions to count words

If you are on an older version of Excel where TEXTSPLIT isn’t available, there’s still a way that you can count the number of words within a cell. It will be a slightly more complex formula that will use the LEN and SUBSTITUTE functions.

The first part of the formula will involve counting the number of characters in a cell, which is what the LEN function does. This is accomplished through the LEN(A2) formula — assuming that A2 is where the article name is.

Next, you’ll need to use the SUBSTITUTE function to replace the blank values ” ” with an empty string that just contains two quotes: “”. To do that, the formula for that portion would be: SUBSTITUTE(A2,” “,””). This formula will need to be enclosed within a LEN function. What this accomplishes is it counts the number of characters in the cell after you’ve replaced all the blank values. If you take the total cell length and subtract this second piece, you’ll be left with the number of blank values in the text.

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)

However, this isn’t entirely correct as you will be off by 1 word. This is because since the formula is counting the number of blanks, it won’t include the first word, which doesn’t come with a space before it. That also means if you only have one word, you’ll have a value of 0 instead of 1. To fix this, you’ll simply need to add a +1 to the end of your formula.

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)+1

This would mean, however, that even blank cells would return a value of 1. And this would technically be the same problem when using the TEXTSPLIT function as well, since it doesn’t check for blanks, either. To correct this, you can simply add an IF function to check if the value is indeed blank. Here’s how the full formula looks:

=IF(A2=””,””,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))+1)

This will return nothing if the cell is blank. If the cell isn’t blank, then it will go ahead and perform the rest of the calculation. As mentioned, this IF function can also be added to the start of the TEXTSPLIT function as well.

If you liked this post on How to Count Words 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.