Agregador de canales de noticias

Weighted Average in Excel [Formulas]

Chandoo (Pointy Haired Dilbert) -

Today we will learn how to calculate weighted average in Excel with percentages.

What is weighted average ?

Weighted average or weighted mean is defined as [from wikipedia],

The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

If all the weights are equal, then the weighted mean is the same as the arithmetic mean.

Why should you calculate weighted average?

Well, it is because, in some situations normal averages give in-correct picture. For eg. assume you are the CEO of ACME Widgets co.. Now you are looking annual salary report and being the numbers-gal you are, you wanted to find-out the average salary of your employees. You asked each department head to give you the average salary of that department to you. Here are the numbers,

Now, the average salary seems to be $ 330,000 [total all of all salaries by 5, (55000+65000+75000+120000+1200000)/5 ].

You are a happy boss to find that your employees are making $330k per year.

Except, you are wrong. You have not considered the number of employees in each department before calculating the average. So, the correct average would be $76k as shown above.

How to Calculate Weighted Average in Excel with Percentages

There is no built-in formula in Excel to calculate weighted averages. However, there is an easy fix to that. You can use SUMPRODUCT formula. By definition, SUMPRODUCT formula takes 2 or more lists of numbers and returns the sum of product of corresponding values. [related: Excel SUMPRODUCT Formula – what is it and how to use it?]

So, if you have values in B4:B8 and the corresponding weights in C4:C8, you can use SUMPRODUCT like this to get weighted average.

Caution: However, the above method works only if C4:C8 contains weights in percentages(%) totaling to 100%.

WAvg Formula Pattern (use this with your data) =SUMPRODUCT(<your values>, <your weights>) What if my percentage weights don’t add up to 100%?

May be your weights are more than 100 percent. Or may be they are less than 100 percent. In both cases, you can use the below formula variation.

The idea is to divide the total of weights with the SUMPRODUCT result so that we can adjust Weighted Average as the weights don’t add up to 100 percent.

WAvg Formula Pattern when weights don’t add up to 100 percent =SUMPRODUCT(<your values>, <your weights>) / SUM(<your weights>) Weighted Average when you have counts instead of weights:

If you have count of observations instead of weights, you can still use the SUMPRODUCT formula to calculate weighted average in Excel.

Here is the formula for above example:

Notice that this formula is same as the formula for weighted average with weights not adding up to 100 percent.

WAvg Formula Pattern when you have counts instead of weights =SUMPRODUCT(<your values>, <your counts>) / SUM(<your counts>) Weighted Average with Extra Conditions

Let’s say you have city wise observations and weights. And you want to calculate the weighted average, only for Boston values. In this case, you can use a variation of the formula like below:

How does this formula work?

  1. SUMPRODUCT calculates the total value for BOSTON by summing up C5:C16 (value column) where B5:B16 is Boston (highlighted portion of the formula) and multiplies that with the counts.
  2. So in the above example, this will just give us the total of Boston – ie 218,600
  3. We then divide this with the total count of Boston (using the SUMIFS formula) – ie 400
  4. This results in the weighted average for Boston values alone – ie 546.50

For more information on how the conditions work inside SUMPRODUCT formula, please read this article.

Download Weighted Average Calculation Example Workbook:

In this workbook, you can find 4 examples on how to to calculate weighted average in excel. Go ahead and download it to understand the formulas better.

Weighted Average in Excel – Formula Explained

Here is a video with Weighted Average formula explained. Please watch it below to learn more. Alternatively, head to my YouTube page to see the weighted averages in Excel video.

In Conclusion

Weighted averages are a great way to explain data and every data analyst should know how and when to use them with their data. Apart from Weighted Average, I suggest learning how to use moving average and average of top n values. These will help you explain the data and trends to your audience better.

Do you use Weighted Mean / Weighted Average?

What do you use it for? What kind of challenges you face? Do you apply any tweaks to weighted average calculations? Please share your ideas / tips using comments.

More examples on Averages and Formulas:

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to get non-adjacent columns with FILTER function in Excel

Chandoo (Pointy Haired Dilbert) -

Excel 365’s FILTER() function is great for getting a cut of data that meets your criteria. But what if you need to filter and then show non-adjacent columns? Something like below. In this article, let me show you a few options to get discrete columns after filtering with the FILTER function.

Using CHOOSECOLS with FILTER()

Imagine you have a table data named “staff” and you want to see all the staff who joined in year 2021. We can use below FILTER function for that.

=FILTER(staff, YEAR(staff[date of join])=2021)

This will provide a list of all staff who joined in year 2021, as depicted below.

But we don’t want all columns, just ID, Gender, Salary and Leave Balance.

To see just columns 1,2,7 & 8 of this filtered data, we can use below formula.

=CHOOSECOLS( FILTER(staff, YEAR(staff[date of join])=2021), 1,2,7,8)

This will give you exactly what you need without anything else.

What if I need to get data, but the column order is different from original data…

Say, you do want the columns 2,6,8&9 but you want them to show up in the order 6,8,2&9 in the final output.

You can still use the CHOOSECOLS function like below.

=CHOOSECOLS(
FILTER(staff, YEAR(staff[date of join])=2021),
6,8,2,9)
How to get columns from a list of header names

If you want to use a range of column names and show filtered data for only those columns, we can use XMATCH along with CHOOSECOLS and FILTER, as demoed below.

  1. Set up your column headers in a range like Z5:AC5
  2. Now, we can use XMATCH to find the positions of these headers. =XMATCH(Z5:AC5, staff[#headers])
  3. When you pass the result of XMATCH to CHOOSECOLS, you can pick these columns.
=CHOOSECOLS( FILTER(staff,YEAR(staff[Date of Join])=2021), XMATCH(Z5:AC5,staff[#Headers])) How does this work…
  • Let’s go inside out.
  • The FILTER() function gets all the staff data for people whose joining date is in 2021.
  • Range Z5:AC5 holds the names of the columns we want to see.
  • XMATCH(Z5:AC5, staff[#Headers]) will tell you the column numbers for the columns you want by looking them up in the table header row.
  • CHOOSECOLS() will then return those exact columns
Learn more about these functions:

The post How to get non-adjacent columns with FILTER function in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

What is XLOOKUP and how to use it in Excel?

Chandoo (Pointy Haired Dilbert) -

Think of XLOOKUP as an improved version of VLOOKUP. In this article, learn all about the XLOOKUP function, it’s syntax, parameters and how to use it with real-world xlookup examples.

What is XLOOKUP?

Using XLOOKUP, we can search for an item in a list using the lookup value and return a matching item. For example, you can lookup for salesperson “Jackie” and return their sales amount from the data below using the XLOOKUP function, as depicted above.

It is the newest member of Excel’s lookup function family. You may already know the other members of this group – VLOOKUP, LOOKUP, HLOOKUP, INDEX+MATCH.

How to use XLOOKUP – step by step instructions

Let’s say you have data for salespeople like above and you want to find the Net Sales for “Jackie”. Follow below steps to create the XLOOKUP function in Excel.

  1. Write =XLOOKUP( in a cell
  2. For the lookup_value, Type the name of the salesperson in double quotes (ex: “Jackie”)
  3. Tip: If you have the name of the person in a cell (like G4), you can point to the cell instead of typing the name
  4. Now for the lookup_array, select the names column of your data.
  5. and for the return_array, select the net sales column of your data.
  6. Close the brackets and hit enter.
  7. Congratulations, you’ve just created your first XLOOKUP formula in Excel.

Here is how the formula and result look in my data:

What if the lookup value is not in the lookup array?

One of the most common scenarios of lookups in Excel is not finding the value you are looking for. Imagine, you are looking for the salesperson “Chandoo”, but he doesn’t exist in the dataset. In this case, XLOOKUP will return #N/A error.

But we can use the 4th parameter of XLOOKUP – if_not_found to set an optional value to display when there is an error.

Here is an example formula with that:

=XLOOKUP("Chandoo", B4:B21, D4:D21, "Not found") How to lookup in the middle with XLOOKUP?

Let’s say you want to lookup the sales amount of $726 and return the name of the person (in this case, Jessy). Previously, you needed to use the INDEX+MATCH combination for this. But XLOOKUP let’s us specify any range or column for lookup_array portion. so, no more INDEX+MATCH… Yay!

To perform the “net sales” lookup and return the name of the person, we can use the below formula:

=XLOOKUP(G4, G4:G21, G4:G21, "Not found")

The above XLOOKUP example assumes that G4 contains the net sales value you want to lookup.

How to lookup higher than / lower than using XLOOKUP? (approximate match)

Say, you want to find out the closest person with the net sales of $1300. In this case, we don’t have anyone with that value in the data. You can still use XLOOKUP to get the approximate matching value, either next higher or lower in the data.

For this we can use the 5th parameter of the XLOOKUP function – match mode.

There are 4 match modes in XLOOKUP.

  • 0 or Exact match. This is the default value for XLOOKUP.
  • 1 or next larger match. This looks up for the exact or next highest value in the lookup array.
  • -1 or next smaller match. This looks up for the exact or next lowest value in the lookup array.
  • 2 or wildcard match. This looks up based on a pattern you have mentioned in the lookup value. More on this further down in the article.
To get the next highest matching value: =XLOOKUP(G4,D4:D21,B4:B21,,1) Note: 1 refers to next higher value for match mode.

The above formula returns “Jonathan” in the sample data, as he has the next highest amount – $1316.

To get the next lowest matching value: =XLOOKUP(G4,D4:D21,B4:B21,,-1) Note: 11 refers to next lower value for match mode.

This formula returns “John” as he has the next lower value – $1088.

Getting Partial Matches with XLOOKUP (Wildcard / Pattern Matching)

XLOOKUP also allows for a powerful and elegant pattern matching in your data. Let’s say you want to find the net sales for the person whose name begins with the letters Jam. In this case, we can use the match mode 2 (wild card) along with the wildcard operators * (asterisk) and ? (question mark) to create our XLOOKUP.

To find the net sales of the person whose name begins with Jam,

  1. Use the formula =XLOOKUP(H4&”*”,B4:B21,D4:D21,”No such person”,2)
  2. Here H4 contains the first few letters of the name, i.e. Jam
  3. The lookup value is H4 & “*”. This tells XLOOKUP that we want the name to begin with the value of H4 (Jam) and then there can be any number of characters.
  4. Rest of the XLOOKUP parameters are as per usual.
  5. Don’t forget the match mode operator as 2. We need this for Wild card – pattern matching.

Refer to above illustration for more on the pattern matching xlookup.

Additional Pattern Matching Tricks with XLOOKUP

Refer to below handy table for some extra tips on using the partial matching feature of XLOOKUP.

SituationFormulaExplanationSample ResultName ends with ved=XLOOKUP(“*ved”, B4:B21,D4:D21,”No such person”,2)To find a word ending with, we use * (asterisk) at the beginning of the patternJaved, $2277Name contains ack=XLOOKUP(“*ack*”, B4:B21,D4:D21,”No such person”,2)We can use two * symbols – one at the beginning of the pattern and one at the end. Jackie, $1610Name has 5 letters and begins with Je=XLOOKUP(“Je???”,B4:B21,D4:D21,,2)In this case, we can use the ? (question mark) symbol to indicate that we can have any one character. So the pattern is Je???Jessy, $726Name begins with J and ends with d=XLOOKUP(“J*d”, B4:B21, D4:D21,”No such person”, 2)J*d means the lookup value begins with J, contains any number of letters and ends with dJaved, $2277 What if there are two matching values?

If you have more than one matching item for the lookup value in your data, XLOOKUP, just like all other lookup functions in Excel, will always return the first matching item’s corresponding value.

As you can see in the above illustration, we have two sales persons with the name Johnson in our data.

When using =XLOOKUP(“Johnson”, B4:B21, D4:D21) we are going to get the net sales of the first Johnson ie $1540.

But what if I need to get the second or third or all the matching values?

In this case, you can use the new FILTER() function in Excel to get the second or all matching items.

Here is the formula.

=FILTER(D4:D21,B4:B21="Johnson")

For more information on FILTER function, refer to this article.

Horizontal Lookup with XLOOKUP:

XLOOKUP works just as good with horizontal data too, thus replacing any need for HLOOKUP function.

In the above example, I have monthly budget table and I want to lookup the budget value for April 2024.

We can use this XLOOKUP formula to do just that.

=XLOOKUP(C10,C3:N3,C4:N4) Returning entire row of information with XLOOKUP:

Another powerful feature of XLOOKUP is that it can return multiple values all corresponding to the same lookup value. For example, I want to see the budget, actual and balance information for the month of April 2024, from my budget spreadsheet below. We can use XLOOKUP for that easily.

=XLOOKUP(C10,C3:N3,C4:N6)

In the above formula, by using multiple rows (C4:N6) as the return array, we can return all corresponding values for the lookup value in C10 – ie April 2024. Excel will automatically spill these values into separate cells on the worksheet.

XLOOKUP – things to keep in mind:

When using XLOOKUP, you must keep these points in mind:

  • XLOOKUP needs Excel 365 or Excel on the web or Excel 2021: This function is not available in all the versions of Excel. So check your version of Excel before you start using xlookup. If you notice #NAME errors when working with XLOOKUP, that means your version of Excel does not support this function. Instead, use INDEX+MATCH formula.
  • Use the If not found option to fix errors: To avoid any lookup errors (#N/As), use the fourth argument of the XLOOKUP function – if_not_found.
  • Lock cell references when creating lookups: If you are writing multiple XLOOKUPs in a range, don’t forget to lock your cell references to absolute mode (change B4:B21 to $B$4:$B$21) so that when you drag or fill the formula down, your lookup and return array ranges don’t change. This is a common mistake and you will end up with wrong results. (absolute vs. relative references)
  • Or better yet, convert your lookup data to a table: A simple fix to the relative reference issue is to use tables on your data. This way, you can write simple XLOOKUP formulas like this: =XLOOKUP(“Jackie”,sales[Sales Person],sales[Net Sales])
  • In case of multiple matches, XLOOKUP always returns the first (or last matching item, if you used search mode option) value. This is why it is important to also learn how to use the FILTER function in Excel.
How is XLOOKUP better?
  • XLOOKUP makes the most used formula in Excel straight forward and less error prone. You just write =XLOOKUP(what you want to find, the list, the result list) and boom, you get the answer (or #N/A error if the value is not found)
  • Looks up exact match by default: One of the annoyances of VLOOKUP is that you must mention FALSE as last parameter to get correct result. XLOOKUP fixes that by doing exact matches by default. You can use match mode parameter to change the lookup behavior if you want.
  • 4th parameter to support value not found scenario In most business situations, we are forced to wrap our lookup formulas with IFERROR or IFNA formulas to suppress errors. XLOOKUP offers 4th parameter (read more about it below) so you can tell what default output you want if your value is not found.
  • XLOOKUP offers optional parameters to search for special situations. You can search from top or bottom, you can do wildcard searches and faster options to search sorted lists.
  • It returns reference as output, not the value. While this may not mean much for normal users, pro Excel user’s eyes light up when they discover a formula that can return refs. That means, you can combine XLOOKUP outputs in innovative ways with other formulas. For example: XLOOKUP to create dependant drop down in Excel.
  • It is so much cooler to type, you just type =XL. I am not sure if this is a happy coincidence but saying =XL to get this formula is just awesome.
XLOOKUP Syntax

Simple case:

=XLOOKUP(what you want to look, lookup list, result list)

=XLOOKUP("Jackie", sales[Sales Person], sales[Net Sales])

returns Jackie’s [Net Sales] if the name can be found in [Sales Person]

Optional parameters:

By default, you just need 3 parameters for XLOOKUP, as shown above. But you can also use 4th, 5th and 6th parameter to specify how you want the lookup to be done.

4th parameter for XLOOKUP: IF not found (no more IFERROR!!!)

The newly introduced XLOOKUP has an even newer feature. It now supports if not found option. This is the 4th parameter.

For example, use:

=XLOOKUP(“Chandoo”, sales[Sales Person], sales[Net Sales],”Value not found”) to return “Value not found” if the lookup value is not available in the search column – sales[Sales Person].

5th parameter for XLOOKUP: Match mode or type:

Use this to tell Excel how you want your MATCH to happen. The default is 0 (exact match) but you can also use these other options, shown below.

6th parameter for XLOOKUP: Search mode

Try this if you want to search from bottom to top. The default direction is top down (1).

XLOOKUP – Video Tutorial:

Here is a simple but effective video tutorial on how to use XLOOKUP function and how it can replace VLOOKUP and INDEX+MATCH functions. You can also watch the xlookup tutorial on my channel.

Download XLOOKUP examples – workbook

Click here to download the 13 XLOOKUP examples file.

And one more example file, this with INDEX+MATCH replacements.

Final thoughts on XLOOKUP

In my opinion, XLOOKUP is a terrific function and a must have for any data professional. It is part of my essential Excel formulas list for data analysts.

When we nest XLOOKUP formulas, we can also perform more complex lookups like 2-WAY lookups or search across different worksheets. I discuss some of these advanced scenarios in a recent video on my YouTube channel. Please watch it here.

The only downside of XLOOKUP is the compatibility. It doesn’t work in all versions of Excel. For this reason, I still think there is value in learning how to use VLOOKUP and INDEX MATCH functions.

Do let me know what you think about XLOOKUP and if you have any questions about it using the comments section.

Learn more about Data Analysis with Excel:

This post is part of my data analysis with Excel series. Please learn other topics too and improve your data skills with Excel.

The post What is XLOOKUP and how to use it in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Extract BOLD portion of a cell in Excel using getBoldText() function

Chandoo (Pointy Haired Dilbert) -

Ever need to extract just the bold portion of some cells automatically? You can use my getBoldText VBA custom function to do that. This function takes a cell and returns the bold portion if any. See these sample results:

Using Custom Functions in Excel

To extract the bold text from cells, we are going to use User Defined Function (UDF)  feature of Excel.

This feature let’s us build custom functionality that is not part of Excel. 

  1. Step 1: Go to Visual Basic Editor (Press ALT+F11 or click on Developer Ribbon > Visual Basic)
  2. Step 2: Right on your workbook in the left-hand panel and insert a new module (refer to below illustration).
Add the getBoldText() Code to your workbook

In the module, copy paste below code.

Function getBoldText(fromthis As Range) As String Dim i As Integer, size As Integer Dim output As String size = fromthis.Characters.Count If fromthis.Font.FontStyle Like "Bold*" Then output = fromthis.Value Else For i = 1 To size If fromthis.Characters(i, 1).Font.FontStyle Like "Bold*" Then output = output & fromthis.Characters(i, 1).Caption End If Next i End If getBoldText = output End Function

Save your workbook (as .XLSM file).

Now, you can use =getBoldText() function in your file to extract the bold values from any cell contents.

How to use this function?

Save your workbook (as .XLSM file).

Now, you can use =getBoldText() function in your file to extract the bold values from any cell contents.

The syntax is:

=getBoldText(cell_address)

See this quick demo to understand how to use the function.

How to install this function so it works in all files?

You can also install this function using your personal macros workbook.

Follow below process:

  1. Click on “Excel Add-ins” button in either Developer Ribbon or Options > Add-ins > Excel Add-ins > Go
  2. Enable “Personal” macro workbook. This is a macro file that let’s you store reusable macros & custom functions like getBoldText.
  3. Now go to Visual Basic Editor (ALT+F11) and locate the personal macro file. It would be named personal.xlam
  4. Expand the file and add a module if necessary.
  5. Copy paste the VBA code for getBoldText into this module.
  6. Save and restart Excel.
  7. Now, you should be able to use the getBoldText across any of your workbooks (on your computer).

 

Known issues and bugs

In my preliminary testing this function worked fine in Excel 365. I have not tested it in older versions of Excel like 2013 or 2010. If you notice any bugs or issues please report using the comments feature on this page.

 

More VBA Examples

Check out below VBA / Automation examples to simplify your work:

The post Extract BOLD portion of a cell in Excel using getBoldText() function appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to fix SPILL Error in Excel Tables (3 easy solutions)

Chandoo (Pointy Haired Dilbert) -

So you have a SPILL error in your Excel tables? In this quick article, let me show you 3 easy fixes to the problem.

Fix 0: See if Excel can auto-fix the formula

This is not really a fix. But if you write certain types of formulas in table, Excel will warn you about the potential spill error and fix it for you. See this example:

One lesson we can take away from this auto-correction of the formula is that if you are using a table column style formula, change it to [@ column] style.

For example:

  • Instead of LEFT([Name]) use LEFT([@Name], 1)
  • =IF(LEN([Value])>6, “Too long”, “Short enough”) can be =IF(LEN([@Value])>6, “Too long”, “Short enough”)

Note: This auto-correction of formulas is seems to be a new feature, so may not be active in all Excel 365 versions.

Fix 1: Change to a non-spillable formula

If an Excel formula can result in more than one value, it automatically spills. Such formulas are called Dynamic Array Excel Functions. A simple example is the SEQUENCE function.

=SEQUENCE(10)

will return the numbers 1 to 10.

If you use them in a normal cell in Excel, they work ok.

But when you type the same formula in a table, it gives the SPILL error (see this demo).

So an easy fix is to change your formula to a version that doesn’t spill.

Refer to below table to see non-spillable alternatives for some common situations.

PurposeSpill VersionNon-Spill AlternativeTo generate row numbers in the table automatically=SEQUENCE(10)=ROW()-ROW([#Headers])Show all matching values with FILTER=FILTER(data, data=10)Return only the first matching value:
=XLOOKUP(10, data, data)
Concatenate all matches into one list:
=TEXTXJOIN(“, “,, FILTER(data, data=10)) Fix 2: Convert Table to a Range

If you must have a spillable formula in the table column, an easy fix is to convert the table to a range. You will however, loose all the table features (such as structural references, data model connectivity and ability to send data to Power Query).

To convert table to a range, just select the table, go to Table Design ribbon and click on “Convert to Range” button.

Once your table is in a range format,

  1. Remove any spill formulas in the row 2 onwards in your range
  2. This will fix the #SPILL! error, as demoed below.

Fix 3: Move the formula outside the table

Tables do not support any sort of spill behavior. So another easy fix is to move the spill formula outside the table to an adjacent column. Something like this:

But what if I need the formula along with my table?

Unfortunately Excel currently doesn’t support having SPILLABLE formulas inside tables. But if you still need a formula result along with your table data (for some calculation purposes), you can use the HSTACK function, like below:

=HSTACT(your_table, spill_formula_here)

For example, I want to add a ID number column to my table. Here is the HSTACK for that:

=HSTACK(my_table, SEQUENCE(ROWS(my_table)))

Why do we even get this error?

It’s not because Excel hates you. There are two things at play here.

  • Dynamic Array Formulas: want to spill the formula results down (or sideways) when there is more than value returned by the formula (example: SEQUENCE(10)). They throw a SIPLL error whenever something is preventing the formula from spilling.
  • Excel Tables: want to apply the same formula for all cells in the table column.

So when you type an array formula in a table cell, Excel tries to apply the same formula for all cells of the table. This creates a situation where each table row has a formula that wants to return multiple values. So while first row’s formula is trying to spill, second row has a formula of its own (as Excel tables automatically apply the same formula across). Thus the SPILL errors.

Know more about Excel Tables & Dynamic Arrays

Please read below articles to understand Excel Tables & Dynamic Array features of Excel.

The post How to fix SPILL Error in Excel Tables (3 easy solutions) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Loan Amortization Schedule in Excel – FREE Template

Chandoo (Pointy Haired Dilbert) -

Do you want to calculate loan amortization schedule in Excel? We can use PMT & SEQUENCE functions to quickly and efficiently generate the full loan amortization table for any number of years.

Set up input section for loan amortization table

First, you need to set up the parameters for calculating loan schedule. We need below information.

  • Interest Rate (annualized)
  • Loan duration (in years)
  • Payments per year (for ex: 12 for monthly, 52 for weekly and 26 for fortnightly)
  • Loan amount

For the sake of the example on this page, I am using 5.35% annual interest rate (cell D3), 30 years loan duration (cell D4), 12 payments per year (cell D5) and $100,000 loan amount (cell D6).

Write formulas to calculate the amortization schedule

Then, we need to calculate the amortization schedule or table. For this we can use the PMT, IPMT, PPMT functions along with SEQUENCE dynamic array function.

Setting up the amortization schedule in Excel Grid

First create a range of cells like below to do all the calculations.

Formula for generating all payment periods

To generate all payment periods, we can use the SEQUENCE function below.

For example, if you have 12 payments per year for 30 years, then the sequence function below generates numbers 1 thru 360.

=SEQUENCE(D5*D4) Calculating the equal payments for each period

We can use PMT function to calculate the equated payment or installment amounts.

The syntax for PMT function is

=PMT(rate_of_interest,
number_of_payments, amount_borrowed)

In our case, as we have 12 payments per year, we need to divide the interest rate by 12 and multiply the number of years with 12. Finally, we want the same amount for all the periods. So our PMT formula will be:

=IF(C10#>0, PMT($D$3/D5,D4*D5,D6))

This formula will automatically spill down for 360 periods with the same value. Refer to below illustration to see sample calculations.

Calculating the Principal portion
of each payment in the amortization schedule

We can use PPMT function to calculate the principal paid at each point of time. The syntax for this is…

=PPMT( rate_of_interest, payment_number total_number_of_payments, amount_borrowed)

As we need to calculate this value for all the periods, we can use the SPILL RANGE in C10# as the payment_number.

Our PPMT formula looks like this:

=PPMT(D3/D5,C10#,D4*D5,D6) Formula for Interest portion for each payment in amortization schedule

We can use IPMT function to calculate the interest portion in our schedule. The syntax and logic are same as PPMT.

Here is the IPMT formula to use:

=IPMT(D3/D5,C10#,D4*D5,D6)

 

Formula for Balance at the end of each period

The final formula in our amortization schedule is balance. For this, we can use a variety of Excel formulas. I particularly like using SCAN function for this as this is simple and automatically scales up or down depending on how many payments we make.

 

Know more about SCAN function in Excel

SCAN is a new Excel 365 function that scans a list or array and runs a calculations on it. Then it returns the results the values for each step of the calculation.

Example of SCAN:

=SCAN(0, A1:A10, SUM)

Result:
Returns the running total of the values in A1:A10

Explanation:

  • SCAN starts the calculation with 0.
  • Then it reads the values of A1:A10, one at a time.
  • It then adds the values to 0 (because the third argument is SUM) and returns the intermediate results.
  • So, the results will become:
    • First value: 0+A1
    • Second value: first value + A2
    • Third value: second value + A3…

Learn more about SCAN function here.

We can use below SCAN function to get the balance at the end of each payment in our amortization table.

=SCAN(D6,E10#,SUM)

This function will start the calculations with D6 (amount borrowed) and scans thru the principal paid column (E10#) and calculates the running total of balance by using the SUM function.

Calculating SUMMARY of Amortization Table

Often, you may want to calculate the summary from an amortization table like below:

We can use simple arithmetic formulas like SUM or division (/) to calculate such values.

As you can see, with a 30 year payment of $100,000 loan at 5.35% interest rate, more than half of the payments (50.26%) go towards interest.

Limitations of Amortization Schedules

While amortization schedules are great to understand and model cashflows (or plan for future payments), they are quite rigid and do not reflect real-world scenarios. Here are a few limitations of this approach.

  • Amortization tables assume interest rates stay same for the entire duration.
  • They do not cater for excess payment. If you want to prepare an amortization or loan schedule with excess payments, use this template.
  • The calculation does not consider any fixed or recurring fees or charges (such as loan administration charge once a year).
Download Loan Amortization Schedule Excel Template

Please download my loan amortization schedule template and use it to see the schedule for your data.

If you have an older version of Excel (other than 365), use the “non-365” worksheet to run the calculations.

Other Loan Calculators & Schedules Have a question about Amortization Schedule Template?

Leave a comment with your question so I can help you.

The post Loan Amortization Schedule in Excel – FREE Template appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Subscribe to necesitomas.com agregador