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.

## Download Practice Workbook

Download the practice workbook from here.

## 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**.

## 3 Quick Methods to Calculate Discount Rate in Excel

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.

**Read More: How to Use Dividend Discount Model Formula in Excel**

### 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.

**Read More:** **How to Calculate 10 Percent Discount in Excel (With Easy Steps)**

### 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.

**Read More:** **How to Calculate Discount Rate for NPV in Excel (3 Useful Methods)**

## Things to Keep in Mind

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

## 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. Follow our website **ExcelDemy** to get more articles like this.