Free Excel templates

A free XIRR, NPV and IRR calculator in Excel, for irregular cash flows

Up to twenty dated cash flows, XIRR and XNPV, the periodic IRR and NPV for comparison, money multiple, profit, a running cash position, and the error Excel's NPV() function invites: three sheets, no macros.

The file reads eight irregular cash flows over five years: 1.0 million invested, a follow-on, a capital call, distributions and an exit, 750,000 of profit in all. Respecting the dates, the XIRR is 11.46% and the XNPV at 10% is 71,705. Treating each row as one year apart, as IRR() does, gives 8.21%, and NPV gives a negative value: both wrong for these dates. Applied to all the flows, NPV() also discounts the first one, which shifts the answer by a whole period.

Free online calculator, no sign-up: xirr calculator.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

XIRR_NPV_IRR_Calculator.xlsx (13 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with Financial Modeling from First Principles, which explains the method behind it, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Three sheets. Dates, amounts, labels and the discount rate are blue inputs.

Cash flows
Up to twenty dated cash flows with a label, years from the first date, and the discount rate.
Results
XIRR, XNPV, IRR, NPV computed right and computed the usual wrong way, total invested and returned, money multiple, profit, length, the XNPV at the XIRR, and the running cash position.
Checks
XNPV at the XIRR is zero, profit identity, the NPV() error identity and the worked case: all OK.

What the worked case returns

MeasureValue
XIRR11.46%
XNPV at 10%71,705
IRR treating rows as years8.21%
NPV, rows as years-112,086
Money multiple1.56x
Profit750,000
Length5.1 years

XIRR and IRR answer different questions. IRR assumes every cash flow is exactly one period after the last; XIRR uses the actual days between them. With irregular dates only XIRR is the rate of return, and the gap here is more than three points.

How to use it on your own cash flows

  1. Enter dates and amounts on Cash flows, oldest first, money in as negative.
  2. Set the discount rate.
  3. Read XIRR and XNPV on Results.
  4. Compare them with IRR and NPV to see how much the dates matter.

Questions people ask about it

Is this template really free?

Yes. The file costs nothing: you enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference. It uses Excel's own XIRR and XNPV functions on a range that grows with the number of flows you enter.

What is the difference between XIRR and IRR?

IRR assumes equal periods between cash flows. XIRR uses each flow's actual date, counting days over 365 from the first. For irregular flows, only XIRR is the true annual rate.

Why is NPV() wrong in Excel?

It discounts the first value by one full period. If your first cash flow is today, add it outside the function: first flow plus NPV(rate, the rest).

Go further

For building models from first principles, with the checks that keep these errors out, Financial Modeling from First Principles and its companion workbooks go further.

See the book and its free files →

Other free templates

This template goes with Financial Modeling from First Principles. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.