Investment returns from non-periodic cash flows

Description



This 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 Scheduler

Technical Stock Analysis