Investment returns from non-periodic cash flows
DescriptionThis template can be used to calculate the internal rate of return (IRR) and net present value (NPV) of an investment that generates non-periodic cash flows. It works in Excel 2007 or later.
Download
.xlsx file (13 KB)Motivation
Projects often generate cash returns at non-periodic intervals. Estimates of returns from a project investment change as the project design and commissioning progress. This template helps in calculating the IRR and NPV for such cases. It is "dynamic", meaning the formulae get recaclulated when new data is added.How to use
Enter the cash flows and dates in columns A and B respectively. The rate of return used for estimation of the NPV is entered in the cell G5. If new entries are added, the results are updated automatically.
How it works
In versions of Excel prior to 2007, IRR and NPV could only be
calculated for periodic cash flows. In Excel 2007, two new formulae
"XIRR" and "XNPV" were introduced for calculation of IRR and NPV for
non-periodic cash flows. These formulae are used in this template.
The calulation is made dynamic by using Named Formulae "DATES" and
"VALUES". These use the OFFSET function to get the entries in the
columns A and B.
Excel uses an iterative technique for calculating XIRR. If the
iteration fails, the formula returns the #NUM error. An initial
guess can be used to assist the iteration. In this template, an
initial guess is not provided.
Also See:
Batch SchedulerTechnical Stock Analysis