Calculating IRR for periodic cash flows is easy. In real life situations, however, cash inflows and outflows often happen at irregular intervals. Thankfully, Microsoft Excel has a special function to find IRR in such cases, and this tutorial will teach you how to use it. Continue reading
IRR in Excel is one of the financial functions for calculating the internal rate of return, which is frequently used in capital budgeting to judge projected returns on investments. Continue reading
Microsoft Excel has a special function for calculating NPV, but its use can be tricky especially if you have little experience in financial modeling. The purpose of this tutorial is to show you how the Excel NPV function works and point out possible pitfalls when calculating the net present value of a series of cash flows. Continue reading
Variance is one of the most useful tools in probability theory and statistics. In science, it describes how far each number in the data set is from the mean. In practice, it often shows how much something changes. In this article, we will analyze different methods of calculating variance in Excel. Continue reading
The interest portion of a loan payment can be calculated manually by multiplying the period's interest rate by the remaining balance. But Microsoft Excel has a special function for this – the IPMT function. In this tutorial, we will go in-depth explaining its syntax and providing real-life formula examples. Continue reading
When you make periodic payments on a loan or mortgage, a certain part of each payment goes towards the interest and the remainder is applied to the loan principal. In this tutorial, we will look at how to use the PPMT function to calculate the principal portion of the payment. Continue reading
These days when technologies, markets and customer needs are changing so rapidly, it is critical that you move with trends, and not against them. Trend analysis can help you identify underlying patterns in data movements and project future behavior. Continue reading
In Microsoft Excel, there are several functions that can help you create linear and exponential smoothing forecasts based on historical data such as sales, budgets, cash flows, stock prices, and the like. Continue reading
The fastest way to get some specific information about a cell in Excel is to use the CELL function. Among other things, it can tell you whether a cell is protected or not, retrieve the cell address and value, show a full path to the workbook that contains the cell, and a lot more. Continue reading
To create a cell reference in Excel, you can type the column and row coordinates manually. Alternatively, you can get a cell address from the row and column numbers supplied to the ADDRESS function. Continue reading
When doing correlation analysis in Excel, in most cases you will deal with the Pearson correlation. But when the underlying assumptions of the Pearson correlation (continuous variables, linearity, and normality) are not met, you can do the Spearman rank correlation instead of Pearson's. Continue reading
One of the simplest statistical calculations that you can do in Excel is correlation. Though simple, it is very useful in understanding the relations between two or more variables. Microsoft Excel provides all the necessary tools to run correlation analysis, you just need to know how to use them. Continue reading
IF is one of the most popular Excel functions and very useful on its own. Combined with the logical functions such as AND, OR, and NOT, the IF function has even more value because it allows you to test multiple conditions in desired combinations. Continue reading
Subtraction is one of the four basic arithmetic operations, and every primary school pupil knows that to subtract one number from another you use the minus sign. This good old method also works for subtracting numbers and cells in Excel. Continue reading
Some Excel users prefer organizing data vertically in columns while others choose horizontal arrangement in rows. In situations when you need to quickly change the orientation of a given range, TRANSPOSE is the function to use. Continue reading
In this tutorial, intended for beginners, we will touch only lightly on theory and calculations behind the Excel LINEST function. Our main focus will be on providing you with a formula that simply works and which you can easily customize for your data. Continue reading
Normally, Microsoft Excel recognizes numbers stored as text and converts them to the numerical format automatically. However, if the data is in a format that Excel cannot recognize, numeric values can be left as text making calculations impossible. Continue reading
One of the fundamental things we know about numbers is that they can be positive and negative. But sometimes you may need to use only positive numbers, and that's where the absolute value comes in handy. Continue reading
CHOOSE is one of those Excel functions that may not look useful on their own, but combined with other functions give a number of awesome benefits. At the most basic level, you use the CHOOSE function to get a value from a list by specifying the position of that value. Continue reading
Excel VLOOKUP and IFERROR - these two functions may be pretty hard to understand separately, let alone when they are combined. In this article, you will find a few easy-to-follow examples that address common uses and clearly illustrate the formula logic. Continue reading
Although there is no such thing as Substring function in Excel, there exist three Text functions to extract text of a given length from a cell. Additionally, there are FIND and SEARCH functions to get a substring before or after a specific character. Continue reading
As with other basic math operations, Microsoft Excel provides several ways to divide numbers and cells. Which one to use depends on your personal preferences and a particular task you need to solve. Continue reading
"Give me a formula, and I shall make it return an error," an Excel user would say. In this tutorial, we won't be looking at how to return errors in Excel, we'd rather learn how to prevent them in order to keep your worksheets clean and your formulas transparent. Continue reading
You want to put today's date in some cell? Or you wish to highlight the current date in your Excel calendar? Or you'd like to find the date closest to today? All this can be done by using the Excel TODAY function and this tutorial will teach you how. Continue reading
The Excel MOD function is designed to get the division remainder, but it can do much more than its original purpose. In this tutorial, you will find a few non-trivial uses of MOD in Excel that may prove useful in your worksheets. Continue reading
When you need to determine the relative position of a number in a list of numbers, the easiest way is to sort the numbers in ascending or descending order. If for some reason sorting is not possible, the Excel RANK function is the perfect tool to do the job. Continue reading
Being primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile when it comes to calculating numbers or solving math and engineering problems. This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel. Continue reading
Squaring a number and finding a square root are very common operations if mathematics. But how do you do square root in Excel? Either by using the SQRT function or by raising a number to the power of 1/2. Continue reading
In statistics, the arithmetic mean and standard deviation are two closely related concepts. But while the former is understood by most, the latter is comprehended by few. The aim of this tutorial is shed some light on what the standard deviation is and how to calculate it in Excel. Continue reading
When analyzing numerical data, you may often be looking for some way to find the "typical" value. For this purpose, you can use one of the so-called measures of central tendency such as arithmetic mean, median or mode. Continue reading
