NPV, IRR & Payback Calculator in Google Sheets

Photo by Rubaitul Azad on Unsplash

Key terms explained

• Net Present Value (NPV): The total value of a project's future cash flows expressed in today's money, minus the initial investment. A positive NPV suggests the project adds value.
• Internal Rate of Return (IRR): The discount rate at which NPV equals zero. If the IRR is higher than your required return (hurdle rate), the project may be worth pursuing.
• Discount rate: The percentage used to reduce future cash flows to their present value. It typically reflects the cost of capital or the minimum acceptable return.
• Cash flow: Money moving into or out of a project. Outflows (costs) are negative; inflows (revenue or savings) are positive.
• Payback period: The number of years it takes for cumulative cash inflows to recover the initial investment.
• Discounted payback period: Like payback period, but uses discounted cash flows so the time value of money is accounted for.
• XNPV / XIRR: Google Sheets functions that handle cash flows arriving on irregular dates, unlike NPV() and IRR() which assume equal time intervals.
• Hurdle rate: The minimum acceptable rate of return set by an organisation. Projects with IRR below this rate are typically rejected.
• Time value of money: The principle that a pound (or dollar) received today is worth more than the same amount received in the future, because today's money can be invested.

What this guide covers and what it excludes

This guide shows you how to build an investment appraisal model for a single project with up to ten annual cash flow periods, then publish it as an interactive web calculator using the GSheetPress add-on. It covers NPV, IRR, discounted payback, and XIRR for irregular dates.

Excluded: Portfolio comparison across multiple projects, tax shield calculations, inflation adjustments, Monte Carlo sensitivity analysis, and currency conversion. Discount rate selection depends on your organisation's cost of capital and risk profile — always confirm the rate with your finance team before using results to make decisions.

Prerequisites

• A Google account with Google Sheets access.
• The GSheetPress add-on installed from the Google Workspace Marketplace. Open any spreadsheet, go to Extensions → Add-ons → Get add-ons, and search for GS2Web SaaS.
• A GSheetPress account with an active Calculator entitlement. Sign up or check your plan at gsheetpress.com/admin/signup.html. The account email must match the Google account you use in Sheets.
• A dedicated sample workbook — do not use a file containing private or sensitive data.

Build the cash-flow model in a helper sheet

Before creating the calculator interface, build the underlying financial model. This keeps your formulas clean and makes it easy to wire them to the calculator's input cells later.

Step 1 — Create the workbook and sheets

1. Open Google Sheets and create a new blank spreadsheet. Name it Investment Appraisal – Sample.
2. Rename the default tab Model.
3. Add a second tab and name it Calculator. This is where GSheetPress will generate its template.

Step 2 — Lay out the Model sheet

Use the layout below. Column A holds labels, columns B through L hold data for Year 0 through Year 10. For this worked example we use five years of cash flows.

Step 3 — Enter the period headers and example cash flows

1. In B1 enter 0. In C1 enter 1. Continue to G1 which should read 5.
2. In B2 enter the initial investment as a negative number, for example -50000.
3. In C2:G2 enter the projected inflows. For this example use: 12000, 15000, 18000, 16000, 14000.

These values will later be replaced by references to the calculator's input cells (the G column of the Calculator sheet). For now, hard-coding them lets you verify the model before wiring it up.

Step 4 — Add the discount factor formula

The discount factor for year n at rate r is 1 divided by (1 + r) raised to the power of n. In Google Sheets that is written as =1/(1+rate)^n.

1. Decide where to store the discount rate. Use cell B8 in the Model sheet and label A8 as Discount Rate. Enter 0.10 in B8 (representing 10%). This cell will later reference the calculator input.
2. In B3 enter:

=1/(1+$B$8)^B1

3. Copy B3 across to C3:G3.

Step 5 — Add the discounted cash flow row

1. In B4 enter:

=B2*B3

2. Copy B4 across to C4:G4.

Step 6 — Add the cumulative discounted cash flow row

1. In B5 enter:

=B4

2. In C5 enter:

=B5+C4

3. Copy C5 across to D5:G5.

Step 7 — Calculate NPV, IRR, and payback

Place the summary results below the cash flow table. Use rows 10 onwards in the Model sheet.

NPV (B10). Google Sheets' NPV() function discounts future cash flows only — it does not include the Year 0 investment automatically. Add Year 0 outside the function:

=B2+NPV($B$8,C2:G2)

This adds the Year 0 value (negative investment) to the NPV of Years 1–5.

IRR (B11). IRR requires the full series including Year 0:

=IRR(B2:G2)

Discounted payback (B12). The discounted payback period is the first year in which the cumulative discounted cash flow turns positive. Use MATCH to find that year:

=MATCH(TRUE,G5:B5>=0,0)-1+(-INDEX(B5:G5,MATCH(TRUE,B5:G5>=0,0)-1)/INDEX(B4:G4,MATCH(TRUE,B5:G5>=0,0)))

This formula finds the last year with a negative cumulative balance, then interpolates to estimate the fraction of the following year needed. If the project never reaches payback, it returns an error rather than a misleading zero — which is intentional. Do not suppress it with IFERROR(...,0) or you will miss unrecoverable projects.

For the worked example (10% discount rate, cash flows as above) you should see approximately:

Handling irregular cash flow dates with XNPV and XIRR

If your project does not produce cash flows at neat twelve-month intervals — for example, a construction contract with milestone payments — use XNPV() and XIRR() instead.

These functions require an explicit date for each cash flow. Add a date row to your Model sheet:

1. In B9 through G9 enter the actual dates, formatted as dates (for example 01/01/2024, 15/04/2024, 30/09/2024 and so on).
2. Replace the NPV formula in B10 with:

=XNPV($B$8,B2:G2,B9:G9)

3. Replace the IRR formula in B11 with:

=XIRR(B2:G2,B9:G9)

Note that XNPV() includes Year 0 in its own discounting, so do not add B2 outside the function as you do with NPV(). For further detail on these functions see the Google Sheets XIRR function documentation.

Why IRR can be misleading

IRR assumes that interim cash flows are reinvested at the IRR itself, which is often unrealistic. It also fails when cash flows change sign more than once — for example, a project that has negative cash flows at the start, positive cash flows in the middle, and then large decommissioning costs at the end. In that case IRR() may return multiple valid solutions or an error.

Warning signs to watch for:

• The IRR result is #NUM! — there may be no real solution, or the function's iterative search failed.
• The IRR seems implausibly high (above 100%) — check that Year 0 is correctly entered as a negative value.
• Running IRR on a project with two sign changes — compare it with NPV at several discount rates to get a fuller picture.

When IRR is unreliable, rely on NPV as the primary decision metric.

Build the GSheetPress web calculator

Now that the model is working, create a calculator interface so colleagues can enter assumptions and see results without editing the spreadsheet. This section uses the GSheetPress Calculator feature. You will set up seven inputs: initial investment, five annual cash flows, and discount rate.

Create the calculator

1. Click on the Calculator tab in your workbook.
2. Go to Extensions → GS2Web SaaS → Calculator → New Calculator.
3. When prompted, enter a name such as Investment Appraisal.
4. When asked for the number of inputs, enter 7. Do not count the Calculate button.
5. When asked whether to include graphs, choose according to your preference. For this guide, choose No.
6. Click Create. GSheetPress generates the template headers in row 1 (A1:H1) and input rows 2 through 8 automatically. Row 9 becomes the button row, row 10 the merged Result Fields separator, row 11 the result heading row, and results begin at row 12.

The generated column headers in row 1 are: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules. Do not edit row 1.

Input row 2 — Initial Investment

• A2 — Label: Initial Investment (negative)
• B2 — Input Type: Number
• C2 — Required: Tick the checkbox to mark as required
• D2 — Data: Leave blank
• E2 — Default: -50000
• F2 — ToolTips: Enter the upfront cost as a negative number, e.g. -50000
• H2 — Rules: Leave blank

Input row 3 — Year 1 Cash Flow

• A3 — Label: Year 1 Cash Flow
• B3 — Input Type: Number
• C3 — Required: Tick the checkbox
• D3 — Data: Leave blank
• E3 — Default: 12000
• F3 — ToolTips: Expected net cash inflow in Year 1
• H3 — Rules: Leave blank

Input row 4 — Year 2 Cash Flow

• A4 — Label: Year 2 Cash Flow
• B4 — Input Type: Number
• C4 — Required: Tick the checkbox
• D4 — Data: Leave blank
• E4 — Default: 15000
• F4 — ToolTips: Expected net cash inflow in Year 2
• H4 — Rules: Leave blank

Input row 5 — Year 3 Cash Flow

• A5 — Label: Year 3 Cash Flow
• B5 — Input Type: Number
• C5 — Required: Tick the checkbox
• D5 — Data: Leave blank
• E5 — Default: 18000
• F5 — ToolTips: Expected net cash inflow in Year 3
• H5 — Rules: Leave blank

Input row 6 — Year 4 Cash Flow

• A6 — Label: Year 4 Cash Flow
• B6 — Input Type: Number
• C6 — Required: Tick the checkbox
• D6 — Data: Leave blank
• E6 — Default: 16000
• F6 — ToolTips: Expected net cash inflow in Year 4
• H6 — Rules: Leave blank

Input row 7 — Year 5 Cash Flow

• A7 — Label: Year 5 Cash Flow
• B7 — Input Type: Number
• C7 — Required: Tick the checkbox
• D7 — Data: Leave blank
• E7 — Default: 14000
• F7 — ToolTips: Expected net cash inflow in Year 5
• H7 — Rules: Leave blank

Input row 8 — Discount Rate

• A8 — Label: Discount Rate (decimal)
• B8 — Input Type: Number
• C8 — Required: Tick the checkbox
• D8 — Data: Leave blank
• E8 — Default: 0.10
• F8 — ToolTips: Enter as a decimal, e.g. 0.10 for 10%. Do not enter a percentage symbol.
• H8 — Rules: Leave blank

Button row 9 (generated — do not edit the type or data)

Row 9 is the generated Calculate button. Confirm it reads:

• A9 — Label: Calculate (you may rename this label if you wish)
• B9 — Input Type: Button
• D9 — Data: main
• E9: Reset (this is the generated reset label — do not change it to Calculate)

Understanding E (Default) and G (Inputs) columns

Column E holds the default values displayed when a user first opens the calculator. Column G (the Inputs column) holds the values submitted by the user at runtime — the web form overwrites G cells when someone clicks Calculate. Never store result formulas in column G. To test your formulas before publishing, copy E2:E8 and paste into G2:G8 using Paste special → Values only, so the formulas in the Model sheet can read live values from G.

Wire the Model sheet to the Calculator inputs

Now update the Model sheet so it reads from the Calculator sheet's G column instead of hard-coded values.

1. Click on the Model tab.
2. Click cell B2 (Year 0 cash flow — initial investment). Select the existing value and replace it with:

=Calculator!G2

3. Click cell C2 (Year 1 cash flow). Replace with:

=Calculator!G3

4. Click cell D2 (Year 2). Replace with:

=Calculator!G4

5. Click cell E2 (Year 3). Replace with:

=Calculator!G5

6. Click cell F2 (Year 4). Replace with:

=Calculator!G6

7. Click cell G2 (Year 5). Replace with:

=Calculator!G7

8. Click cell B8 (discount rate). Replace with:

=Calculator!G8

The Model sheet now recalculates automatically whenever values in Calculator!G2:G8 change.

Add result rows to the Calculator sheet

Result rows start at row 12 (after the separator in row 10 and heading row in row 11). Click the Calculator tab and fill in the following result rows. Column A holds the result label; column B holds the formula.

Row 11 is the result heading row generated by GSheetPress — leave it as generated.

Result row 12 — NPV

1. Click cell A12 and type Net Present Value.
2. Click cell B12. Type the following formula, beginning with the equals sign, then press Enter:

=Model!B10

Result row 13 — IRR

1. Click cell A13 and type Internal Rate of Return.
2. Click cell B13 and enter:

=TEXT(Model!B11,"0.00%")

Using TEXT() formats the IRR as a readable percentage in the published result. If you prefer a raw decimal, use =Model!B11 instead.

Result row 14 — Discounted Payback

1. Click cell A14 and type Discounted Payback (years).
2. Click cell B14 and enter:

=Model!B12

If the project never pays back within the modelled period, this cell will display an error. That is intentional — it signals an unrecoverable project. You may add a wrapper such as =IFERROR(Model!B12,"Does not pay back within 5 years") so the web user sees a plain-language message instead of a formula error.

Test before publishing

1. In the Calculator sheet, select E2:E8.
2. Copy (Ctrl+C or Cmd+C).
3. Click cell G2.
4. Go to Edit → Paste special → Values only.
5. Switch to the Model sheet and confirm B10, B11, and B12 show the expected results (approximately £10,214, 19.0%, and 3.9 years for the default inputs).

Alternative scenario check

To verify the model responds correctly, manually change G8 in the Calculator sheet to 0.20 (20% discount rate). The NPV in Model!B10 should become negative (approximately -£2,100), confirming the project is not viable at a 20% hurdle rate. The IRR stays unchanged at around 19% because IRR does not depend on the assumed discount rate. Revert G8 to 0.10 after testing.

Troubleshooting

NPV is unexpectedly large or small

• Check that Year 0 is entered as a negative value. A positive Year 0 means NPV() treats the initial investment as an inflow, inflating the result.
• Confirm the discount rate in Calculator!G8 is a decimal (0.10), not a percentage (10). Entering 10 instead of 0.10 produces a discount factor near zero, making future cash flows almost worthless.
• Verify the formula in Model!B10 adds B2 outside the NPV() call: =B2+NPV($B$8,C2:G2). If you accidentally include B2 inside NPV(), it discounts Year 0 as if it were a Year 1 cash flow.

IRR returns #NUM!

• Check that the cash flow series in B2:G2 contains at least one sign change (one positive and one negative value). IRR cannot converge if all values share the same sign.
• If there are two sign changes (for example, negative at start, positive in the middle, negative at end), IRR may find multiple solutions. Use NPV at several discount rates as the primary measure instead.
• Try adding an optional guess argument: =IRR(B2:G2,0.1). The guess helps the iterative search start in a sensible range.

Discounted payback formula returns an error

• An error from the payback formula means the cumulative discounted cash flow never reaches zero within the modelled period. This is correct behaviour, not a bug. Add the IFERROR wrapper described above to show a user-friendly message.
• If you see a #VALUE! error, check that the discount factor row (B3:G3) is not returning errors — this usually means B8 (discount rate) is blank or contains text.

Calculator!G cells are blank after pasting defaults

• Ensure you used Paste special → Values only. A regular paste copies the formula from E cells, which may reference other cells and produce unexpected results in G.
• If G cells are still blank after a web submission, check that the calculator was published and that the form submission completed successfully before reading G.

Model sheet shows wrong values after publishing

• Confirm all Model sheet references point to Calculator!G cells, not Calculator!E cells. E holds defaults; G holds submitted values.
• Check that no formula in the Model sheet references a column G cell on the same Model sheet — that is a different column and will return a blank or error.

For further help with the add-on, visit the GSheetPress support page. For guidance on building other financial tools, see the GSheetPress Calculator feature overview and the Table feature guide.

Frequently asked questions

What is the difference between NPV() and XNPV() in Google Sheets?

NPV() assumes that every cash flow occurs at equal intervals (typically one year apart) and does not include the Year 0 investment — you must add it manually outside the function. XNPV() accepts an explicit date for each cash flow, making it suitable for projects where payments arrive on specific calendar dates rather than neat annual intervals. XNPV() discounts every cash flow relative to the first date in the series, so Year 0 is included inside the function and you do not add it separately.

Can I use this model for projects longer than five years?

Yes. Extend the cash flow rows in the Model sheet across as many columns as you need (up to column Z or beyond). Add matching input rows in the Calculator sheet — remembering to adjust every row number and G cell reference accordingly. If you add more inputs in the Calculator sheet you must create the calculator again with the correct input count, as the number of input rows is set at creation time. The payback and NPV formulas will also need their ranges extended to cover the additional periods.

Why does IRR give a higher result than I expected?

A common cause is an incorrect Year 0 value. If the initial investment is entered as a positive number instead of a negative one, IRR interprets the series as having an inflow at the start and treats the subsequent payments as costs. This can produce an artificially high or negative IRR. Double-check that B2 (or Calculator!G2) contains a negative number representing the upfront outlay. Also confirm that the cash flow series represents net values — if revenue figures are entered without subtracting operating costs, IRR will be overstated.

Publish and test your web calculator

1. Before publishing, share the workbook so the add-on can reach it. Click Share in the top-right corner of Sheets, go to General access, choose Anyone with the link, and set the permission to Editor. Click Done. Important: this setting means anyone who obtains the spreadsheet link can edit it directly. Use only the dedicated sample workbook — never a file containing real financial data or private information.
2. Click on the Calculator tab (the one containing the GSheetPress template).
3. Go to Extensions → GS2Web SaaS → Calculator → Publish Calculator.
4. Wait for the confirmation message indicating the calculator has been published successfully.
5. Go to Extensions → GS2Web SaaS → Calculator → Calculator Link. This opens a sheet listing your published calculators.
6. Find the row where the Sheet Name matches Investment Appraisal (the name you gave at creation).
7. Open the URL in column C (Link), not column B (Edit Link). Column C is the public-facing calculator. If the sheet is empty or the link is not yet shown, click Get Links to refresh the list.
8. In the public calculator, enter the default values: Initial Investment -50000, Year 1–5 cash flows as 12000, 15000, 18000, 16000, 14000, and Discount Rate 0.10. Click Calculate.
9. Confirm the results match: NPV approximately £10,214, IRR approximately 19.0%, Discounted Payback approximately 3.9 years. If results differ, return to the Model sheet and confirm that all G cell references are correct and that G2:G8 received the submitted values.
10. To embed the calculator on a website, copy the iframe code from column D of the Calculator Link sheet and paste it into an HTML block on your page. Republishing the same calculator later (for example, after adding a sixth year) keeps the same public link — you do not need to update the embed code.