You will learn to find IRR in Excel 2007, 2010 and 2013 by using a simple formula that accepts a series of periodic uneven cash flows. You will have an option to specify whether interest compounding is periodic or continuous. Excel IRR calculates rate of return only when compounding of interest is periodic thus leaving you without an option for continuous compounding. The following section provides more detail on difference between discrete and infinite compounding of interest.
Discrete Compounding
Savings and loans are quoted on annual basis as you will find banks advertising an annual effective rate of 8% on a savings account. Similarly lenders such as mortgage brokers quote APR or annual percentage rate in their adverts that target home buyers. Depending on the number of annual payments in a loan or number of compounding periods for savings account, the interest paid on loans and earned on savings is determined on a periodic base. For example, a bank that promises 10% on savings account would likely compound interest on a monthly basis. This means that at the end of each month your savings in the account will earn interest at a rate of 10%/12 or 0.833% rather than 10%. Similarly if you have borrowed money from a mortgage lender and the quoted rate of interest is 5% then the monthly interest payment will be calculated at a rate of 5%/12 or 0.4167%.
Continuous Compounding
In finance and banking, it is a common practise for banks and financial institutions to pay and charge interest on a ever small portion of time thus making it possible for interest to be earned at an infinite time scale. This type of compounding is called continuous and allows a lender to earn more interest from borrower. Thus a savings account that pays interest on continuous scale has a higher worth when compared with interest that is compounded periodically.
Excel IRR function
Excel IRR function takes two values the first of which is a series of cash flows, and the second one is a guess rate. The cash flows are entered with a range of cells such as B1:F1 or as an array of values. The guess rate is used when Excel is unable to find the internal rate of return using the default rate of 10%. The following sample data shows you that Excel has calculated a IRR of 7.71%. As stated earlier this is the periodic rate where interest is compounded discretely. Please move down to the second example where I have used tadIRR function to calculate two different rates of return. This is a 3rd party user defined Excel function that is part of a large library of financial functions called TADXL.
| A | B | C | D | E | F | |
| 1 | Cash Flow | -10000 | 3000 | 3000 | 3000 | 3000 |
| 2 | Excel IRR | =IRR(B1:F1, 10%) | 7.71% | |||
How to use tadIRR function
Excel IRR function calculates internal rate of return for periodic compounding of interest, in comparison tadIRR function finds two different rates of return for both discrete and continuous compounding of interest. The following example data that uses the same cash flows from our previous examples shows how tadIRR has calculated a rate of 7.71% for periodic compounding and a second rate of 7.43% for continuous compounding of interest.
| A | B | C | D | E | F | |
| 1 | Cash Flow | -10000 | 3000 | 3000 | 3000 | 3000 |
| 2 | TADXL IRR | =tadIRR(B1:F1, 10%, 0) | 7.71% | =tadIRR(B1:F1, 10%, 1) | 7.43% |
Excel IRR calculator
You can download this Excel template to find internal rate of return by using tadIRR Excel function.
