Investment Science: Net Present Value and Internal rate of return calculations in spreadsheet (OpenOffice)

In the previous posts, we have seen ways to calculate the Net Present Value and Internal rate of returns using Texas Instruments BA II Plus Financial Calculator, in this short post, we will see the way to compute NPV and IRR using the openoffice spreadsheet program.

Given a cash flow stream (-2, 1, 1, 1) at an yearly interest rate of 10%, we know that

Net Present Value (NPV) = -2 + 1 / (1.1) + 1 / (1.1 ^ 2) + 1 / (1.1 ^ 3) = 0.487

Internal rate of return (IRR) is computed from

-2 + 1 / (1 + IRR) + 1 / (1 + IRR) ^ 2 + 1 / (1 + IRR) ^ 3 = 0

Internal rate of return IRR = 23.38%

To do these computations, enter the cash flow streams in columns, let's assume that we entered the above cash flow stream (-2, 1, 1, 1) from rows B7 to B10, then

Net Present value (NPV) = NPV(0.1, B8:B10) + B7
Internal rate of return (IRR) = IRR(B7:B10)

Sample Spreadsheet: Calculating Net Present Value (NPV) and Internal rate of return (IRR)

Figure 1: Calculating NPV and IRR in openoffice spreadsheet program

Note: While calculating the Net Present Value, never include the initial cash flow (-2 in the above example) in the NPV function, but enter the subsequent cash flow streams which are to be discounted in the NPV function along with the interest rate and then add the initial cash flow to the result of the NPV function.