How to calculate IRR or NPV in a spreadsheet

Lay out the cash flows in a column and use the functions made for it. In Grid by Zaweba, NPV and IRR work on cash flows that arrive at regular intervals, while XNPV and XIRR take a matching column of dates for flows that come at uneven times. MIRR gives a modified rate of return that accounts for borrowing and reinvestment rates.

The two ideas in brief

Net present value asks what a series of future payments is worth today, given a rate of return you could earn elsewhere. Money received in five years is worth less than the same money now, so each future amount is discounted. If the total, minus what you invest, is positive, the project beats your required rate.

Internal rate of return turns the question round. It's the rate at which the net present value comes out at exactly zero. If that rate is higher than the return you need, the investment looks worthwhile.

Setting out the cash flows

Put each period's cash flow in a column, in order. Money going out, such as the initial investment, is negative. Money coming in is positive. For the dated versions, put the date of each flow in the column beside it.

Keep the discount rate in its own labelled cell. You'll want to try different rates, and a formula pointing at one cell makes that easy.

NPV and XNPV

NPV takes a rate and a range of later cash flows. Its one trap catches nearly everyone: it treats the first value in the range as arriving at the end of the first period. So if your initial investment happens today, leave it out of the NPV range and add it separately. The usual pattern is NPV of the rate over the later flows, plus the initial cash flow.

XNPV takes a rate, the cash flow column and the date column, and discounts each flow by the actual time from the first date. It includes the first flow as happening on the first date, so there's no separate addition, and it handles irregular timing without any fuss.

IRR, XIRR and MIRR

IRR takes the whole column of flows, including the initial negative one, and returns the rate per period. If the periods are years, it's an annual rate. If they're months, convert before comparing.

XIRR takes the flows and their dates and returns an annual rate directly. It's the right choice for real investments where money goes in and comes out on odd dates, such as a portfolio with irregular top-ups.

Both search for an answer by trial, and a guess argument can help if they struggle. Flows that change sign more than once can have more than one possible rate, which is a property of the maths rather than the software. MIRR avoids that ambiguity by taking a finance rate for the negative flows and a reinvestment rate for the positive ones.

Checking and comparing

A quick check: NPV at the rate IRR returns should come out at zero, or extremely close. If it doesn't, the flows probably aren't laid out the way the functions expect.

For comparing projects, put them side by side with the same rate cell feeding all of them. A small table of NPVs at several discount rates shows how sensitive each project is, and a line chart of that table makes the comparison easy to see.

These functions sit in Grid by Zaweba's Financial family alongside loan, depreciation and bond functions, and they follow the same conventions as other spreadsheets, so figures agree when you share an .xlsx with an accountant or colleague.