**Excel** is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in **Excel**. In this article, I will illustrate **3** useful methods for how to calculate discount rate for **NPV** in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Download this workbook and practice while going through the article.

## Introduction to Net Present Value (NPV)

**Net Present Value (NPV)** is the value of all future cash transactions spanning the entire life of an investment after being discounted to the present.

The formula for **Net Present Value** is

**NPV = FV/(1+i)^n**

Where,**FV** = Future Value of a cash flow**i** = Discount Rate**n** = number of time periods

## 3 Easy Methods to Calculate Discount Rate for NPV in Excel

This is the dataset for todayâ€™s article. We have the **Future Value** (after 1 year)Â and the **Net Present Value** **(NPV)**. We will calculate the discount rate.

### 1. Calculate Discount Rate Manually Using NPV Formula

First, I will show how to calculate the discount rate manually. But before that, letâ€™s understand the scenario first.

Suppose you are going to invest **$8,000** ** today** in a certain business. The return will be

**$10,000**

**. Now you are going to calculate the rate of return i,e the discount rate.**

__after one year__Since you invest

**$8,000**today, this can be considered your

**NPV**. And the return after

**1**year (

**$10,000**) will be your

**Future Value**.

Now, letâ€™s calculate the discount rate.

**Steps:**

Go to **C8** and write down the following formula

`=(C4/C5)^(1/C6)-1`

- Now press
**ENTER**.**Excel**will calculate the discount rate.

That means your rate of return on this investment will be **25.00%**

### 2. Apply What-If-Analysis Feature to Calculate Discount Rate for NPV

We can also use **the What-If-Analysis feature** to calculate discount rate for **NPV**. In this method, we will set an **NPV** and **Excel** will calculate the discount rate.

**Steps:**

- Go to
**C5**and write down the following formula.

`=C4/(1+C8)^C6`

- Now press
**ENTER**.

**Excel**returned**$10,000**as**NPV**since__we have no interest rate__. You can**ignore this value**as we will set our own**NPV**and will calculate the discount rate.- Now, go to the
**Data**tab >>**Forecast**>>**What-If Analysis**>> select**Goal Seek**.

**Goal Seek**window will appear.__Considering the__, we will set**NPV $8000****C5**to**8000**by changing the discount rate**C8**. By doing so,**Excel**will calculate the discount rate required to have the**NPV**as**$8000**. Now click**OK**.

**Excel**will calculate the discount rate. Format the rate to**percentage**, your final output will be like this.

### 3. Use of RATE Function to Determine Discount Rate for Loan Payment

You can also use **the RATE function** to calculate the discount rate. This method is useful when you are dealing with a series of cash flows.

Before describing the method, let me explain the context first.

Suppose you have taken a loan of **$25,000** ** today** from a bank. Hence, you have to pay the loans. The condition is such that, you have to pay

**$10000**per year for the next

**4**years. In these circumstances, if you want to calculate the interest rate, you can use

**the RATE function**.

** **

**Steps:**

- Go to
**C9**and write down the formula.

`=RATE(C5,-C4,C6)`

**Explanation:**

- The first argument
**NPER**Â indicates the number of installments which is**4**. - The next one is
**PMT**which indicates the cash flow in each installment. Note that there is a negative sign (**â€“**) before**C4**.__Thatâ€™s because you are paying that amount. So this is a__.**cash outflow** - The next argument is
**PV**indicating the net present value.

- Now press
**ENTER**.**Excel**will calculate the**Interest Rate**.

This result means you are paying the loan at a **21.86%** interest rate.

## Things to Remember

- While using
**the RATE function**, remember that the payment amount (**PMT**) is negative.

## Conclusion

In this article, I have demonstrated **3** effective methods to show how to calculate discount rate for **NPV** in **Excel**. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.