Using Excel For Financial Analysis
May 05, 2009
IRR is a commonly used term to measure the value of an investment. Boiled down to its most basic components, it is the measurement of cash invested vs. cash returned. The follow exercise will demonstrate a simple IRR calculation.
For this example, we are assuming an investment is going to be made in a business for an expected return of future profits. Our assumptions are as follows
Based on these assumptions we can develop a quick 5 year income statement as follows:
Excel has a built in formula to calculate IRR. The function is "=IRR(range)". The range is a range of cash flows. Negative numbers are cash outflows and positive numbers are cash inflows. Based on the above information, we can calculate the IRR of the investment to be 11.4%.
Another way to think of IRR is to visualize it similar to interest expense for a loan. In this case the loan is the initial investment of $150,000, the interest is calculated annually on the beginning balance and the payments are the 5 years of positive cash flow. At the end of the 5 years, the loan is paid off. See the illustration below for an example of how the IRR acts as interest in a loan amortization schedule.
In the next posting, I will demonstrate how to use the Data Analysis function to create a sensitivity table. This function allows a user to see the impact that two variables may have on a calculation. In our example, we will see how the IRR can move based on changes to the Revenue Growth and Gross Margin percentages.
