Google Sheets is used for everything from personal budget planning to professionally prepared company spreadsheets. To that end, Google Sheets includes some advanced financial functions — including the ability to calculate Net Present Value (NPV).
Net Present Value is a concept that’s used to determine the profitability of things like capital expenditures and investments. With the NPV Google Sheets function, you calculate the value of a stream of payments.
The hope is that an NPV will go up over time. But you can get a negative NPV as well — which tells you that the investment probably isn’t a good choice.
The calculation for NPV is pretty complicated. But that’s one of the major advantages of a Google Sheet. You don’t need to concern yourself with the calculations for NPV. You just need to know how to use the Google Sheets NPV formula.
This Article Covers:
What is the NPV Formula in Google Sheets?
The primary function of the NPV formula is to compare the throughput of a loan or expenditure against what that same amount of money could have made by just earning interest in a bank account or other investments.
If you know how to use NPV function in Excel, you already know how to use NPV in Google Sheets. NPV spreadsheets are all designed similarly. In Google Sheets, the NPV formula looks like this:
=NPV(discount, cashflow_1, cashflow_2, …)
- discount is the amount of interest you could otherwise be making on that money
- Each cashflow is how much money is going in and out. You can enter each cashflow individually or select a range of cells.
How to Use NPV Google Sheets Function to Calculate the Cost of a Loan
Let’s say a family member needs you to loan them $1,000 and pay you back over three months — in payments of $200, $300, and $500.
Under cashflow, we have the initial amount of the loan (money going out). We have the payments (money coming in). And we have the discount rate.
With this very simple calculator, we can see that we’re losing money on this transaction. And it only makes sense that we are. We are lending out $1,000 and getting back $1,000. There’s an opportunity cost there, based on how much money we could be making elsewhere.
Most situations aren’t that clear-cut. Consider if our family member instead promised to pay us back in three payments of $400, totaling $1,200.
The situation looks a little better now. But it’s also a little clearer why these calculations are important. To the untrained eye, lending someone $1,000 and getting back $1,200 would net them $200. It would be a great investment.
But in reality, it’s closer to $150. Why the discrepancy? Because there’s opportunity cost; you could be making interest on that money.
While NPV calculations are usually used for business finances, they can also be useful for personal financial decisions. Note that I also discussed how to use Google Sheets Goal Seek to create a what-if analysis for your business.
Calculating the Value of Capital Expenses with NPV
Let’s now turn our eye to something (small) business-related. You’ve thought about it and decided that you want to start a business — a hot dog stand on the pier. It will cost $5,000 to set up the stand, but you’ve figured out that you should be able to ramp up your income pretty quickly after that.
Even figuring in your other expenses, you think you can make $1,000 the first month, $2,000 the second month, and $3,000 the third month.
Putting that all into the calculations tells you that you will have made $715.39 after three months.
Again, this goes under the assumption of a 2% discount. But it’s important. If you didn’t have the NPV there, you would assume that you’d have a full $1,000 of profit at the end of the three months. In theory you do, but when comparing the against if you had just left it in the bank at the discount rate you only have $715.39.
NPV provides a more realistic look at your cashflows with opportunity cost baked in. This is why it’s an essential component of any capital expenditure.
Things to Consider When Using The Spreadsheet Function for Calculating Net Present Value
Is your NPV spreadsheet function giving you an error? A formula NPV function can give you an error if you’re using incorrect data parameters. Two important things to remember are:
- We never put dates in when using NPV. NPV assumes that you’re using a static interval: daily, weekly, monthly, or annual. Your “discount” percentage should be set at the same interval as the transactions involved.
- Negative cash flows represent you putting money in, whereas positive cash flows represent money coming back to you. It’s important to remember that your initial investment is negative, not positive.
NPV is a very specific utility — but when it is needed, it’s useful.
What If Your Cash Flows Come at Different Intervals?
As we mentioned, NPV is used for consistent intervals. In our example, we have no dates set — it’s assumed that we made an investment and we are paid every month/week/year. But what if those intervals aren’t consistent?
Google Sheets has another function for this called XNPV which is used as so:
XNPV(discount, cashflow_amounts, cashflow_dates)
We can see here that we can use the XNPV function to perform similar calculations, we’ll just need to set dates:
To make the above calculations work we followed these steps:
- Select the cell for the result (F4)
- Type =XNPV(
- Select the cell with the discount rate (F3) and then add a comma
- Select the range for the cashflow_amounts which is cells B4:B7 in our example
- Select the range for the cashflow_dates which in our example is A4:A7
- Hit Enter
Our final formula looks like this:
=XNPV(F3,B4:B7,A4:A7)
These calculations will differ from an NPV formula because of the altered intervals. Over these altered intervals, the discount percentage will continue to accrue at a variable rate due to different lengths of time.
Starting to Use NPV: Free Template
If you’re interested in using the NPV function, you can start now with this NPV Template below.
[thrive_leads id=’60706′]
Once you’ve loaded the NPV template, make a copy of it. You can then edit the surrounding parameters for your situation, or look at the NPV formula to see how it’s used.
The NPV formula is only one of many Google Sheets formulas out there to calculate the cost of capital expenditures, loans, investments, and more. Most Excel formulas are either directly translated into Google Sheets or are extremely similar — making it very easy to use Google Sheets if you’re already familiar with Microsoft Excel. But even if you aren’t, if there’s an accounting or financial function you want to use, there’s likely a built-in algorithm.
It should be noted that it’s also always possible to put in the actual algorithms that accountants use to calculate NPV. The NPV Google Sheets function is shorthand; it makes it easier to perform these accounting functions. But because Google Sheets contains a robust calculation system and easy-to-use variables, Google Sheets can be used to calculate any formula — not just the formulas that it has built in.
Looking for more information? Read my guide on the NPER function in Google Sheets.