Posts Tagged ‘IRR’
This week, in the final article of this series, guest blogger Nick Williams discusses calculating the Internal Rate of Return for an irregular cashflow. Nick is an Access tutor based in the U.K.
XIRR – Internal Rate Of Return
XIRR function returns the value for an Internal Rate Of Return for a supplied series of cash flows, set of values. The key difference between IRR and XIRR is that XIRR can deal with cashflows that aren’t evenly spaced. Because it can deal with cashflows that aren’t evenly spaced you need to tell is both the series of cashflows and also the dates of each cashflow.
The syntax of the XIRR function in Excel is XIRR (values, dates, [guess])
values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values.
dates is another required parameter, it is a series of dates. You need to have a date that corresponds to each cash flow for the calculation to run. The first date is the date of the investment/loan, and subsequent dates refer to income values.
guess is an optional parameter, and works as it does for IRR. Again, if this parameter is omitted, it is set to 0.1 (10%) by default
Continuing with our previous example, let’s introduce dates into our cash flow set.
We now have dates for the initial investment, and irregular dates of actual and/or forecasted income over the seven years of the project.
As we would expect, as the returns come sooner on this project the return on it has gone up, when compared with our previous calculation.
If we entered dates that were exactly one year apart for each of the cashflows this would give an answer of 1.82%, the same answer as using the IRR function.
Again continuing with our previous example, lets we add more expected income and periods. Again as the returns come sooner the return on the project has increased when compared to have all of the cashflows spaced exactly one year apart.
WHAT TO DO IF YOUR CALCULATION RETURNS #num!
Microsoft Excel uses an iterative technique for calculating IRR AND XIRR starting from guess. If XIRR can’t find a result that works after 100 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.
#NUM! error can also appear if:
1. there isn’t at least one positive and at least one negative value in the list of cashflows
2. any of the specified dates precede the first specified date
3. the specified dates and values arrays have different lengths
XIRR can also return a #VALUE! error if it cannot not recognize any of the specified dates as dates (e.g. you have the value of 20 among your dates array)
This week, guest blogger Nick Williams discusses calculating the Internal Rate of Return for a regular cashflow. Nick is an Access tutor based in the U.K.
IRR – Internal Rate Of Return
IRR returns an interest rate. It is the interest rate that would need to be applied to all of the cashflows in the calculation to give an NPV of zero for the project (see above). In short it tells you the return on the money invested in a project and is often used as an investment decision tool. It is designed to work with a set of regularly spaced cashflows only.
IRR is the same as interest rate of savings or loan, however there is it applied to a very straight forward situation.
The syntax of the IRR function in Excel is IRR(values, [guess])
values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values for the project.
guess is an optional parameter. It is a number that you are guessing is close to the result of IRR. If this parameter is omitted, it is set to 0.1 (10%) by default.
Continuing with our example of above we can see the function in use below.
Intuitively we know that that is right. Above we discovered that the project had a negative NPV if we applied a 3% discount rate. Therefore we knew that the discount rate to get an NPV of zero had to be below 3%.
Again, as above, lets add two more years of income and see what happens to the result. We got a positive NPV using a 3% rate before so we know that the answer will be above 3% before we start.
Note: Microsoft Excel uses an iterative technique for calculating IRR starting from guess. If IRR can’t find a result that works after 20 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.