In a previous article, I discussed some of the limitations of the INDIRECT function in Microsoft Excel and the relative limited scope of use. I would like to revisit this function, however, as there may be some useful situations in which this function can work well. It will not make the function the most useful one, but there are ways to use it to accomplish some simple goals.
To remind my audience, the INDIRECT function is a method to interpret the result from the formula in a cell, which will be in the form of a cell reference, and return the value found in that particular cell. In other words, if your INDIRECT formula returns "C10", the formula goes to cell C10 and shows the value in that cell.
The form of the function is =INDIRECT(cell_reference). The "cell_reference" need to be in quotes if you are directly referring to a cell, as in =INDIRECT("B4"). This will result in displaying whatever value is in the cell B4. So, if 90 is the value in cell B4, 90 will be displayed. There is an interesting twist here to keep in mind, because if you want to return the reference of a reference, then you do not need the quotes. For example, assume that in the cell D4 you have as a value "E4" and in the cell E4 you have the value of 80. The formula =INDIRECT("D4") will return a value of E4, whereas the formula =INDIRECT(D4) will return a value of 90.
While this is not necessarily mindblowing on its own, you can also use the INDIRECT function for a more useful situation, such as returning values on other tabs. Take the example of a company with multiple regional branches that sell similar products. As the CFO or COO, one may want to track the sales per week or month by each branch of a variety of products and have a summary page for quickly determining trends. To keep this simple, let’s assume there are 25 branches and the management team wants to see annual summaries of sales. As long as each tab is setup in a similar fashion, with the names of the products and the resulting sales in the same rows and columns, the INDIRECT function can allow the financial modeler to do some quick copying and pasting to create a summary.
The general formula for finding something on another sheet is =INDIRECT(Sheetx!RC) (as long as the tab has no spaces, no single quotes are necessary, otherwise the formula is =INDIRECT(‘Sheet x’!RC)). Assume that the model is constructed with the branch names across the top in one row and the years are listed in the one column. If the tabs in the master model are named after the respective branch (i.e, West1, West2, Midwest, South1, South2, MidAtlantic, etc.), then the general formula for obtaining the information is =INDIRECT(R1C1&"!R2C2"). More specifically, if the summary page has headers in row 4 beginning in column J, and each of the tabs has the data in cell E4 (on each sheet) that is required, you would have the formula =INDIRECT(J$4&"!E4") and copy across each column (for each branch).
There are some additional ways to use the INDIRECT function, but as was the case in the original article, there are specific instances under which it can be used. For the most part, the average Excel modeler will not need to use this function, but for those with intermediate to advanced knowledge of Excel, I will concede that there actually are a few instances in which this function can be useful.
As a final note, if one needs to incorporate R1C1-style references, the formula is =INDIRECT(cell_reference, false). The second part omitted or true is the normal A1-style reference. In addition, Microsoft Excel 2007 ignores some previous returns that would yield a #REF!, like external references to workbooks not open. The most current version of Excel will yield an answer, so just be careful to note that there are some subtleties not captured in earlier versions of Excel.
Monday, February 8, 2010
Tuesday, November 24, 2009
More on the IRR Function in Excel
This article is an expansion to a prior article covering the time value of money. Because many financial modelers require information yielding returns, it is important to understand better the IRR functions, not only from an Excel perspective, but also the underlying financial theory behind the functions.
The IRR function is used in Excel to calculate the internal rate of return. What is the internal rate of return? The internal rate of return (or, of course, IRR), is the rate earned on an investment over a defined period of time. In other words, let’s say that there is an investment of $10,000 at the end of the year, and in each of the five subsequent years, the payments are $500, $600, $700, $800, $900 plus the original $10,000 returned at the end of year five. The IRR would be 6.9% and represents the yield earned on the original investment based on the periodic payments and the return of the original investment.
As one may be able to discern, in the prior example the periodic payments are not the same. Thus, one cannot use the RATE function, which assumes that payments are consistent over some timeframe. So the first thing learned from this is that when there are inconsistent periodic payments, one must use the IRR function. The second concept learned is that the payments MUST BE periodic for the IRR function. In other words, cash flows need to be every year, end of quarter or end of month. The third item that needs to be explained is that there can only be on change in sign for any list of payments, e.g., the initial investment would be entered as -$10,000 (cash outflow) with the subsequent payments having a positive sign (cash inflows).
For a bit of background, an underlying assumption of the IRR is that all cash inflows (or outflows) are reinvested at the IRR. Confused? Think of it this way, if the answer to an IRR question is 8.0%, it is assumed that the periodic cash flows are being reinvested at the 8.0% rate. So, even in reality if the flows were invested at varying rates, it is implicit by the calculation that they were invested at 8.0%. For most analyses and financial modeling, and in the absence of reinvestment rate specific, the IRR will yield a result that can help to provide a solution to the project (particularly when used in conjunction with the NPV function). To avoid unnecessary complication, the IRR can be thought of as an iterative process, which thanks to computing power, is done much more quickly than we can do by hand.
If there is information on a different reinvestment rate, then one would use the modified internal rate of return function, or MIRR (=MIRR(values, finance_rate,reinvest_rate)). This function assumes that you can take your periodic cash flows and invest at some rate other than the IRR, and incorporates a finance rate, which is the rate on the initial investment. Said more simply, if the finance rate equals the reinvestment rate, you would only need the IRR function. If, for example, we revisit the original example and set the finance rate equal to the original IRR, but change the reinvestment rate to 5.0%, the overall return is reduced to 6.68% (clearly, lower reinvestment rate than the original IRR). If the reinvestment rate is 8.0%, the overall return is 6.99%. While this is a function that is likely not be used frequently, it may arise and it is helpful to know that there is a formula to handle a situation like this.
Finally, there are instances in which the cash flows are not periodic, and the IRR function will not provide an accurate result. Excel provides the XIRR function, which takes into consideration irregular payments. For example, referring to the original example, assume that those payments occurred as follows: original investment 12/31/04, subsequent payments on 3/31/05, 4/30/06, 6/30/07, 8/31/08 and final payment and return of investment on 12/15/09. Using the XIRR function, the overall return would be 7.12%. As one can see, the timing of the payments impacts the returns, a fundamental truism of the time value of money. Most of the time, the IRR function will be used, but when there is an instance of specific irregular payments, the XIRR function should be used.
The IRR function is used in Excel to calculate the internal rate of return. What is the internal rate of return? The internal rate of return (or, of course, IRR), is the rate earned on an investment over a defined period of time. In other words, let’s say that there is an investment of $10,000 at the end of the year, and in each of the five subsequent years, the payments are $500, $600, $700, $800, $900 plus the original $10,000 returned at the end of year five. The IRR would be 6.9% and represents the yield earned on the original investment based on the periodic payments and the return of the original investment.
As one may be able to discern, in the prior example the periodic payments are not the same. Thus, one cannot use the RATE function, which assumes that payments are consistent over some timeframe. So the first thing learned from this is that when there are inconsistent periodic payments, one must use the IRR function. The second concept learned is that the payments MUST BE periodic for the IRR function. In other words, cash flows need to be every year, end of quarter or end of month. The third item that needs to be explained is that there can only be on change in sign for any list of payments, e.g., the initial investment would be entered as -$10,000 (cash outflow) with the subsequent payments having a positive sign (cash inflows).
For a bit of background, an underlying assumption of the IRR is that all cash inflows (or outflows) are reinvested at the IRR. Confused? Think of it this way, if the answer to an IRR question is 8.0%, it is assumed that the periodic cash flows are being reinvested at the 8.0% rate. So, even in reality if the flows were invested at varying rates, it is implicit by the calculation that they were invested at 8.0%. For most analyses and financial modeling, and in the absence of reinvestment rate specific, the IRR will yield a result that can help to provide a solution to the project (particularly when used in conjunction with the NPV function). To avoid unnecessary complication, the IRR can be thought of as an iterative process, which thanks to computing power, is done much more quickly than we can do by hand.
If there is information on a different reinvestment rate, then one would use the modified internal rate of return function, or MIRR (=MIRR(values, finance_rate,reinvest_rate)). This function assumes that you can take your periodic cash flows and invest at some rate other than the IRR, and incorporates a finance rate, which is the rate on the initial investment. Said more simply, if the finance rate equals the reinvestment rate, you would only need the IRR function. If, for example, we revisit the original example and set the finance rate equal to the original IRR, but change the reinvestment rate to 5.0%, the overall return is reduced to 6.68% (clearly, lower reinvestment rate than the original IRR). If the reinvestment rate is 8.0%, the overall return is 6.99%. While this is a function that is likely not be used frequently, it may arise and it is helpful to know that there is a formula to handle a situation like this.
Finally, there are instances in which the cash flows are not periodic, and the IRR function will not provide an accurate result. Excel provides the XIRR function, which takes into consideration irregular payments. For example, referring to the original example, assume that those payments occurred as follows: original investment 12/31/04, subsequent payments on 3/31/05, 4/30/06, 6/30/07, 8/31/08 and final payment and return of investment on 12/15/09. Using the XIRR function, the overall return would be 7.12%. As one can see, the timing of the payments impacts the returns, a fundamental truism of the time value of money. Most of the time, the IRR function will be used, but when there is an instance of specific irregular payments, the XIRR function should be used.
Wednesday, August 5, 2009
Understanding the Big Picture in Financial Modeling
It is time to revisit a basic but fundamental aspect of financial modeling: The Big Picture. What exactly is The Big Picture ("TBP")? TBP refers to the overall goal of the financial model and what answer the model is specifically being developed to answer.
Many times in my career I have been asked questions about financial modeling in Excel, questions that really indicated that TBP was not yet established. This is not a knock on anyone's intellectual capacity but rather, it confirms what I have always believed: people cannot sit down in front of a computer and miraculously create a financial model that provides the answers to relevant questions without first addressing the overall scope and purpose of the exercise.
Let us assume, for the moment, that you are a budding young financial analyst at Toyota and you have assigned to analyze domestic (U.S.) cars sales. If we assume that Toyota, being a global entity, has all of the reporting and data readily available, there are no information glitches in the exercise and it is all up to you, the financial analyst, to develop this model. So, how would you begin?
If the corporate office was interested in number of Camrys versus Tauruses sold in the state of Michigan, the exercise is straightforward. You could easily pull information on total car sales by make and model and have the answer. If the analysis is, however, to determine how much of an advertising campaign is required to increase market share by 3.0%, there will be more thought required to create the model. In this case, TBP can be thought of in a somewhat formulaic relationship: TBP = advertising impact on market share. This becomes the basis of the model.
Given the way that the formula is presented, this may be an opportunity to use a data analysis function incorporated in Excel like the regression analysis. Whenever an analyst is trying to determine relationships, taking historical advertising expenditures and running that against changes in market share may provide the appropriate regression equation from which the forecast can be created. For example, you may take 5 years of monthly data and determine that increases in advertising translates directly into increases in market share (all other variables held constant), and this will allow you to understand what the future market share over the next year might be based on those relationships. If the model displays profit margins, return on invested capital or reductions in debt, than TBP was not addressed. Those items reflect supporting data, but remember TBP: impact of advertising on market share.
The purpose of this brief article is really to beat into your head the need to focus on TBP. Part of being an effective analyst, consultant or advisor is listening to the problem and providing the relevant solution. If you need 10 apples for a pie, finding 12 oranges is not helpful. Focus on the purpose of the model before you start creating spreadsheets and you will find your efficiency in financial model development increasing significantly.
Many times in my career I have been asked questions about financial modeling in Excel, questions that really indicated that TBP was not yet established. This is not a knock on anyone's intellectual capacity but rather, it confirms what I have always believed: people cannot sit down in front of a computer and miraculously create a financial model that provides the answers to relevant questions without first addressing the overall scope and purpose of the exercise.
Let us assume, for the moment, that you are a budding young financial analyst at Toyota and you have assigned to analyze domestic (U.S.) cars sales. If we assume that Toyota, being a global entity, has all of the reporting and data readily available, there are no information glitches in the exercise and it is all up to you, the financial analyst, to develop this model. So, how would you begin?
If the corporate office was interested in number of Camrys versus Tauruses sold in the state of Michigan, the exercise is straightforward. You could easily pull information on total car sales by make and model and have the answer. If the analysis is, however, to determine how much of an advertising campaign is required to increase market share by 3.0%, there will be more thought required to create the model. In this case, TBP can be thought of in a somewhat formulaic relationship: TBP = advertising impact on market share. This becomes the basis of the model.
Given the way that the formula is presented, this may be an opportunity to use a data analysis function incorporated in Excel like the regression analysis. Whenever an analyst is trying to determine relationships, taking historical advertising expenditures and running that against changes in market share may provide the appropriate regression equation from which the forecast can be created. For example, you may take 5 years of monthly data and determine that increases in advertising translates directly into increases in market share (all other variables held constant), and this will allow you to understand what the future market share over the next year might be based on those relationships. If the model displays profit margins, return on invested capital or reductions in debt, than TBP was not addressed. Those items reflect supporting data, but remember TBP: impact of advertising on market share.
The purpose of this brief article is really to beat into your head the need to focus on TBP. Part of being an effective analyst, consultant or advisor is listening to the problem and providing the relevant solution. If you need 10 apples for a pie, finding 12 oranges is not helpful. Focus on the purpose of the model before you start creating spreadsheets and you will find your efficiency in financial model development increasing significantly.
Subscribe to:
Posts (Atom)
Posts
