Guide to NPV Google Sheets formula

Have you ever needed to calculate the Net Present Value (NPV) of a series of cash flows? If so, you've probably used the NPV function in Excel. But what if you're not using Excel? Or what if you need to do more advanced calculations, like adjusting for a different discount rate or a particular period?

In that case, the NPV Google Sheets formula might be worth checking out.

Net present value (NPV) explained

What is NPV?

Net present value (NPV) is the present value of cash flows at your projection's required rate of return compared to the value of your initial investment. Using the time value of money, this method evaluates the value of a project, a business, or an asset. It reflects variable value cash flows.

Why is NPV important?

You can use NPV to determine how much a project is actually worth. Then they factor in what it would cost to provide that same amount of money now (expenses and capital costs adjusted for inflation). It also factors in revenue.

If the project cannot return a positive NPV, perhaps you should consider investing in another.

The benefits of calculating NPV are as follows:

1. It provides a clear, concise way to evaluate an investment.
2. It translates all future cash flows and discounts into today's dollars.
3. It provides a comprehensive view of profitability.
4. It gives information about whether saving money is worthwhile depending on where interest rates fall.

NPV function in Google Sheets

How does it work?

The NPV function in Google Sheets calculates the NPV of an investment or series of investments over time.

This tutorial will show you how to use the NPV function in Google Sheets. We'll also provide a few examples to see how it works. Let's get started!

The Formula

= NPV(discount, cashflow_1,cashflow_2 ...)

Time of cash flow: The time at which a particular cash flow is expected to occur.

Discount rate: Rate of return that could be earned on investment with similar risk.

Net cash flow: The difference between the total cash flows in and out for a project.

Let's understand it with the help of an example.

Imagine that you are going to invest in a project for your business. This is a 4-year investment project and would give cash flows of $10M, $20M, and $30M at the end of the first three years, respectively, and is sold for $100M at the end of the year4. I

f your company requires a 10% annual return rate from this project and has to invest $120M for it, should you do that?

You need to calculate NPV for this project based on all the future cash flows, and then compare it to the money you have to invest in to see if this is worthwhile.

In this case, the first future cash flow is the $10M cash flow in at the end of first year, and what this cash flow values now is $10M divided by 1+annual return rate, which is also the discount rate.

Then you need to calculate net present value of all the cash flow and add them together, and the formula is:

NPV of the investment = $10M/(1+10%)+ $20M/(1+10%)² + $30M/ (1+10%)³ +$100M/ (1+10%)⁴ =$116.46

Now you get the present value of this project, and it is lower than the value of the initial investment ($120M). Therefore, you should not invest in it as you cannot get the return you want.

What are some advantages and disadvantages of using NPV?

Advantages:

1. The formula takes into account the time value of money. Money is worth more today than it will be in the future.
2. It uses discount rates, which reflect the true opportunity cost of capital for a project.
3. Provides a clear-cut decision criterion - If NPV is positive, a project should be accepted. If NPV is negative, a project should be rejected.
4. It is not affected by the methods used to finance a project.

Disadvantages:

1. NPV assumes that cash flows can be accurately predicted, which is often not the case.
2. It assumes that projects are independent of one another when in reality, they may be interdependent.
3. It assumes that the discount rate used is the actual opportunity cost of capital. However, this may not always be accurate.
4. It does not consider other factors that may affect the decision to accept or reject a project, such as strategic considerations.

What are some real-world examples of NPV?

You can make data-driven decisions on investing by using the NPV function in google sheets to see whether an investment is profitable. It is also used to compare investments and choose the most profitable one.

NPV is a key tool in financial decision-making. It can also be used to estimate the value of a project where future cash flows are expected, and upfront investment is necessary.

For example - Capital Budgeting

A company may be considering building a new warehouse in addition to keeping the one that it has been using.

In this case, information of financial cash flows in and out would need to be estimated carefully over a period after which a discount rate will be chosen. To find out how much you can expect from each option based on different discount rates and predictions, consider using NPV as your go-to formula!

Things to remember when calculating NPV

The NPV function takes the cash flow in relative order. Ensure that the order is correct.

You must use a consistent time period for cash flows (minutes, hours, weeks, months, years). Use whichever is convenient, but make sure to remain consistent.

Let's say your investment pays off after five years. Then Event 1 should be before Event 5 on any given investment statement. If noted incorrectly, this can result in misleadingly high or low numbers. A negative cash flow represents expenses being paid out. A positive value means income being earned.

The investment calculation must be based on periodic cash flows. For monthly cash flows, we use a monthly discount rate.

‍