Calculating the time value of money is a very common task in economics and business. One of the most crucial components in determining these is the discount rate. This article will guide you to calculate the discount rate in Excel with some easy and quick methods.

## What Is Discount Rate?

The factor used to return future cash flows to the present day is referred to as a ‘**discount rate**’. It also refers to the interest rate charged to commercial banks and other financial organizations for short-term loans they obtain from the **Federal Reserve Bank**.

## How to Calculate Discount Rate in Excel: 3 Quick Methods

Here, we are going to learn **3** simple and quick methods to calculate the **discount rate** in Excel. We have used some excellent examples with explanations for this purpose. So, without further delay, let’s get started.

### 1. Calculate Discount Rate for Non Compounding Interest in Excel

This method will show you **3** ways to calculate the **discount rate** for **non compounding** interest. Here, **non compounding or simple** interest is computed using a loan or deposit’s principle as the base. Contrarily, **compounding interest** is based on both the principal amount and the interest that is accrued on it each period. Let’s see the procedures below.

#### 1.1 Apply Simple Formula

In this method, we are going to apply a simple formula for calculating the **discount rate** for **non compounding** interest. Here, you can determine the percentage discount if you know the **original price** and the **discounted price**. Assuming, we have a dataset (**C4:D5**) in Excel that contains the **Original Price** and the **Discounted Price** of a product. Now, we need to calculate the **Percentage Discount**. The steps to do so are below.

**Steps:**

- To calculate the
**Percentage Discount,**first, you need to divide the**Discounted Price**by the**Original Price**. - In our case, we typed the formula below in cell
**D5**to do so:

`=C5/B5`

In this formula, cells **C5** and **B5** indicate the **Discounted Price** and the **Original Price** respectively.

- After pressing
**Enter**, we will get the output. - Secondly, we need to subtract the output from
**1**. For this, type the formula in cell**D5**:

`=1-(C5/B5)`

- Therefore, press the
**Enter**key to get the result.

- At this time, we need to change the result in
**percentage**For this, select the cell (**D5**) > go to the**Home**tab > go to the**Number**group > select**Percent Style**(**%**) symbol.

- In this way, we will find the desired
**Percentage Discount**.

**Note:**This

**Percentage Discount**indicates that you are not paying

**20%**if you are still making a payment of

**80%**.

#### 1.2 Use ABS Formula

Suppose we have a dataset (**B4:D8**) in Excel that contains some **Original Price** and **Sales Price** of a **Product**. Therefore, we need to calculate the **Discount Rate**. Here, we are going to use **the ABS function** in Excel to do so. The steps are below.

**Steps:**

- In the beginning, to calculate the
**Discount Rate**, we need to select the first blank cell (**D5**) of the**Discount Rate**column and type the formula:

`=(C5-B5)/ABS(B5)`

Here, cells **C5** and **B5** denote the **Sales Price** and the **Original Price** respectively.

- Now, press
**Enter**and get the result.

- At this moment, to fill this formula into the desired range, drag the
**fill handle**.

- Thus, you will get all the
**Discount Rate**values.

- If you want the
**Discount Rate**values in**percentage**form, you need to follow the steps the same as the previous method:

Select the range of cells (**D5:D8**) > **Home** tab > **Number** group > **%** symbol.

- In the end, we can see the final result just like the screenshot below.

#### 1.3 Insert Mathematical Formula

Let’s say, we have a dataset (**B4:D7**) in Excel that contains the **Future Cash Flow**, **Present Value** and **Number of Years**. Here, we need to find out the value of the **Discount Rate**. In this method, we will use a **mathematical formula** to do this. The steps are below.

**Steps:**

- To calculate the
**Discount Rate**, in the first place, select the blank cell below the heading**Discount Rate**and type the formula:

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

In the formula, cells **C5**, **C6** and **C7** refer to **Future Cash Flow**, **Present Value** and **Number of Years** successively.

- After pressing the
**Enter**button, you will get the value of the**Discount Rate**. - To get the
**Discount Rate**in**percentage**format follow the steps:

Select the cell of the **Discount Rate** value > **Home** tab > **Number** group > **%** symbol.

- We can see the final output in the screenshot below.

### 2. Determine Discount Rate in Excel for Compounding Interest

Let’s look at another illustration to show how **compounding** affects the **discount rate**. Assuming, we have a dataset (**B4:D8**) in Excel that contains the values of **Future Cash Flow**, **Present Value**, **Number of Years** and **Number of Compounding Per Year**. Here, we need to calculate the **Discount Rate**. The steps to do so are below.

**Steps:**

- To determine the
**Discount Rate**, firstly, select the cell where you want to keep the value of the**Discount Rate**and type the formula:

`=C8*((C5/C6)^(1/(C8*C7))-1)`

In this formula, cells **C5**, **C6**, **C7** and **C8** indicate the **Future Cash Flow**, **Present Value**, **Number of Years** and **Number of Compounding Per Year** respectively.

- After that, press the
**Enter**button to find the result.

- Now, to get the result in
**percentage**format, follow the steps just like the previous methods:

Select the cell containing the **Discount Rate** value > **Home** tab > **Number** group > **%** symbol.

- Lastly, we will see the final result the same as the screenshot below.

### 3. Calculate Discount Rate for NPV in Excel

The value of all future cash flows, both positive and negative, discounted to the present, is known as **net present value** (**NPV**). In this method, we are going to learn **2** ways to calculate the **Discount Rate** for **NPV**.

#### 3.1 Use Excel What-If-Analysis Feature

To determine the **Discount Rate for NPV**, we may also use the Excel** What-If-Analysis** feature. This approach involves setting an **NPV** and letting Excel determine the **Discount Rate**. Assuming, we have a dataset (**B4:C9**) in Excel containing the values of **Future Value**, **NPV** and **Number of Years**. Now, to calculate the **Discount Rate** using **the What-If-Analysis feature** in Excel, follow the steps below.

**Steps:**

- To input the
**NPV,**first, select cell**C6**and type the formula below:

`=C5/(1+C9)^C7`

- Then, press
**Enter**.

- Due to the absence of an
**interest rate**, Excel computed**$9,000**as**NPV**. You can disregard this number because we will determine our own**NPV**and**Discount Rate**. - Next, select cell
**C9**> go to the**Data**tab >**Forecast**>**What-If Analysis**dropdown menu >**Goal Seek**.

- In turn, a
**Goal Seek**window will pop up. - Therefore, we will set
**C6**to**7000**based on the**NPV**of**$7000**by modifying the**Discount Rate****C9**. Accordingly, Excel will compute the required**Discount Rate**to achieve an**NPV**of**$7000**. - After that, click
**OK**.

- At this moment, another window named
**Goal Seek Status**will appear. - Again, click the
**OK**button.

- In this way, we will get the desired
**Discount Rate**.

- Besides, to get the result in
**percentage**format:

Select cell **C9** > **Home** tab > **Number** group > **%** symbol.

- Finally, we will see the final result just like the picture below.

#### 3.2 Apply Excel RATE Function

You can also compute the **discount rate** using **the RATE function** in Excel. However, this strategy is beneficial when dealing with a sequence of cash flows.

Let’s say you got a loan from a bank today for **$30,000**. As a result, you must repay the loans. The terms are that you must pay **$12000** per year for the following **5** years. In these cases, you can use the **RATE** function to calculate the **Discount Rate**.

**Steps:**

- To calculate the
**Discount Rate**, firstly, select the blank cell and type the formula:

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

**Note:**

- The first argument,
**nper**, states that there will be**5**installments. - The following one is
**pmt**, which displays the cash flow for every installment. A**minus sign**(**–**) is there before**C5**as you can see. That is because you are paying that amount. - The net present value, or
**pv**, is the following argument.

- Lastly, press the
**Enter**key and Excel will return the result accordingly.

- According to this outcome, you are paying a
**28.65%****discount rate**on the loan.

## Things to Keep in Mind

Keep in mind that the payment (**pmt**) should be **negative **when using the **RATE** function.

**Download Practice Workbook**

Download the practice workbook from here.

## Conclusion

I hope the above methods will be helpful for you to calculate the discount rate in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.

**<< Go Back to Discount | Formula List | Learn Excel**