Syntax. IRR(Values,Estimated_Value)
Definition. This function calculates a value of the dynamic investment calculation: The internal interest rate that turns the cash value of all disbursements and deposits in connection with an investment into zero.
Arguments
Values (required) The (actual and/or expected) surpluses from disbursements and deposits, arranged without gaps in a column. Each value represents a period (usually one year) in ascending order and without gaps. Negative surpluses have a minus sign.
Guess (optional) For mathematical reasons, a calculation of the internal interest rate for more than two periods can only be approximate, and therefore this approximation calculation can lead to different and sometimes nonsensical results (such as a negative interest rate). You can influence the calculation with the optional argument Guess as the basis for the approximation calculation. If you don’t use this argument, Excel calculates with a base value of 10 percent. In practice, this calculation interest rate is quite common, because it is close to a financially realistic investment.
If the cells of the Values argument do not contain numbers or are empty, Excel calculates as if these cells didn’t exist. If the functions returns the #NUMBER! error, the approximation calculation was not successful. This might be because of the estimated value used or the data itself (such as only disbursements or only deposits).
Background. The cash value of all disbursements and deposits (also called net cash value) is considered the capital value of an investment. It is assumed that the payments take place at the end of a year (the start period gets the number 0, and it usually has only one disbursement). These payments are discounted at a calculation interest rate that in practice lies at 10 percent plus or minus risk premium/deduction. Finance investments are exceptions, because calculations can be made with the regular market yield.
The internal rate of return (internal interest rate) is now the value that turns the capital value into zero:
Investments where money is lent or borrowed are a special form of investing. When it comes to loans, the rate is called effective yield, and for savings investments/bonds it is called yield.
Examples. The following examples illustrate how to use the IRR() function.
Investment in Material Assets. The purchase cost for a machine is $80,000.00. The expected annual surpluses (deposits minus disbursements) are estimated as shown in Table 15-4.
Table 15-4. Estimated Annual Surpluses for the Use of a Machine
Year | Surplus (in Dollars) |
|---|---|
1 | 15.000 |
2 | 19.000 |
3 | 25.000 |
4 | 27.000 |
5 | 17.000 |
6 | 7.000 |
Does this investment make sense, if the interest of the amount used should be at least 10 percent? To answer this question, enter the purchase cost (with a minus sign) in the first row of a table in a worksheet, and below that enter the data from Table 15-4 without gaps. Using IRR() on these values returns an internal interest rate of 10.47 percent, which is slightly above the requested interest rate.
Note that the decimal places are not necessarily important when you are working with real investments whose future surpluses are only estimated.
Financial Investment. Type A federal savings bonds are investments that have future annual payments that are fixed down to the penny. On August 30, 2010, the German Federal Bank issued the information in Table 15-5 and set a yield of 1.44 percent for the last year.
Table 15-5. Terms for Federal Savings Bonds
Duration Year | Nominal Interest |
|---|---|
2010/2011 | 0.25% |
2011/2012 | 0.50% |
2012/2013 | 1.00% |
2013/2014 | 1.75% |
2014/2015 | 2.50% |
2015/2016 | 2.75% |
You can recreate this information (with the internal rate of return of 1.44 percent) directly in Excel. The table can look like that shown in Figure 15-5. IRR() returns the desired result.
