How to Calculate Discount Factor in Excel (6 Common Ways)

For economics and business purposes, it is very frequently needed to calculate the time value of money. The discount factor is one of the most important factors in calculating these. In this article, I will show you 6 examples to calculate discount factor in Excel.


Download Practice Workbook

You can download our practice book from here for free.


What Is Discount Factor?

The discount factor is a factor that calculates the present worth of future cash flows. The formula to calculate the discount factor is:

Discount Factor = [1+(i/n)]-n*t

Here,

i = Rate of interest

n = Number of compounding periods per year

t = Number of years


6 Common Ways to Calculate Discount Factor in Excel

Say, we have a dataset containing ‘Interest Rate’, ‘Number of Years’, and ‘Discount Factor’ columns. Interest rates and the number of years are given in our dataset. We have to calculate the discount factor. Go through the examples below to apply to your particular case.👇

Calculate Discount Factor Dataset


1. Calculate Daily Compounding Discount Factor

If you are given a compound rate per day, then you have to calculate the discount factor as daily compounding. Follow the steps given below to accomplish this. 👇

📌 Steps:

  • Firstly, click on the D5 cell where you want to put your formula. Subsequently, write the following formula.
=(1+$B$5/365)^(-365*C5)

Calculate Daily Compounding Discount Factor

Note:

  • Here, we have used 365 as n’s value, as it is the daily compounding discount factor calculation and there are 365 days in a year.
  • As our interest rate is fixed here, that’s why we made the interest rate cell absolute by putting dollar signs. You can do this by simply pressing the F4 key.
  • At this time, you have got the daily compounding discount factor for 1 year. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag Fill Handle to Copy Formula

Thus, we can calculate discount factors for any year in Excel. And, the result sheet will look like this. 👇

Calculated Discount Factors


2. Compute Discount Factor Compounding on Weekly Basis

If you are given a compound rate per week, then you have to calculate the discount factor as weekly compounding. Follow the steps given below to do this. 👇

📌 Steps:

  • Initially, click on the D5 cell where you want to put the required formula. Next, insert the following formula.
=(1+$B$5/52)^(-52*C5)

Calculate Weekly Compounding Discount Factor

Note:

  • Here, we have used 52 as n’s value, because we are calculating the weekly compounding discount factor here and there are 52 weeks in a year.
  • As our interest rate is fixed in our dataset, we made the interest rate cell absolute by putting dollar signs. You can do this by simply pressing the F4 key.
  • As a result, you will get the weekly compounding discount factor for 1 year. Following, place your cursor in the bottom right position of your cell.
  • At this time, the fill handle will appear. Now, drag the fill handle below to copy the formula below for all the cells.

Drag Fill Handle to Copy Formula

Finally, you can calculate discount factors for any year in Excel. For example, the result sheet will look like this. 👇

Calculated Discount Factors


3. Monthly Compounding Discount Factor

Now, if you are given a compound rate per month, then you have to calculate the discount factor as monthly compounding. Follow the steps given below to achieve this. 👇

📌 Steps:

  • At first, click on the D5 cell where you want to put your formula. Then, write the following formula.
=(1+$B$5/12)^(-12*C5)

Monthly Compounding Discount Factor

Note:

  • Here, we have used 12 as n’s value, because it is the monthly compounding discount factor calculation and there are 12 months in a year.
  • As our interest rate is fixed in the dataset, we have made the interest rate cell absolute by putting dollar signs. You can also do this by simply pressing the F4 key.
  • Now, you have got the monthly compounding discount factor for 1 year. Following, place your cursor in the bottom right position of your cell.
  • Subsequently, the fill handle will appear. Now, drag the fill handle below to copy the formula below for all the years.

Drag Fill Handle to Copy Formula

As a result, you can calculate discount factors for any year in Excel through this method. And, the result sheet will finally look like this. 👇

Calculated Discount Factors


4. Quarterly Compounding Discount Factor

Moreover, if you are given a compound rate per quarter, then you have to calculate the quarterly compounding discount factor. Go through the steps given below to accomplish this. 👇

📌 Steps:

  • First, click on the D5 cell where you want to put your required formula. Next, insert the following formula.
=(1+$B$5/4)^(-4*C5)

Calculate Quarterly Compounding Discount Factor

Note:

  • In the formula, we have used 4 as n’s value, as it is the quarterly compounding discount factor calculation and we know there are 4 quarters in a year.
  • As our interest rate is fixed here, that’s why we made the interest rate cell absolute here by putting dollar signs. You can do this by simply pressing the F4 key.
  • Consequently, you have got the quarterly compounding discount factor for 1 year. Now, place your cursor in the bottom right position of your cell.
  • As a result, the fill handle will appear. Lastly, drag the fill handle below to copy the formula for all the cells below.

Drag Fill Handle to Copy Formula

Finally, you can calculate discount factors using this method for any year in Excel. And, the result sheet is gonna look like this. 👇

Calculated Discount Factors


5. Calculate Half Yearly Compounding Discount Factor

Now, if you are given a compound rate as per half-year, then you have to calculate the discount factor as half-yearly compounding. Follow the steps given below to do this. 👇

📌 Steps:

  • At the very beginning, click on the D5 cell where you want to put your formula. Now, write the following formula here.
=(1+$B$5/2)^(-2*C5)

Calculate Half-Yearly Compounding Discount Factor

Note:

  • We have used 2 as n’s value in the formula because it is the half-yearly compounding discount factor calculation and there are 2 half years per year.
  • Now, as our interest rate is fixed here, we made the interest rate cell absolute by putting dollar signs. Another thing, you can do this by simply pressing the F4 key.
  • As a result, you have calculated the half-yearly compounding discount factor for 1 year. Next, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all the years.

Drag Fill Handle to Copy Formula

Consequently, you can calculate discount factors for any year in Excel using the formula. For example, the result sheet will look like this. 👇

Calculated Discount Factors


6. Calculate Discount Factor for Annuity

Besides, if you are given a compound rate annually, then you have to calculate the yearly compounding discount factor. Go through the steps given below to accomplish this. 👇

📌 Steps:

  • Initially, click on the D5 cell where you want to put your required formula. At this time, write the following formula.
=(1+$B$5/1)^(-1*C5)

Calculate Annually Compounding Discount Factor

Note:

  • In this formula, we have used 1 as the n’s value, because we are calculating the discount factor for annuity here.
  • As our interest rate is fixed here, we have made the interest rate cell absolute by putting dollar signs. You can do this by simply pressing the F4 key too.
  • Consequently, you will get the discount factor for annuity for 1 year. Next, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Now, drag the fill handle below to copy the formula for all cells below.

Drag Fill Handle to Copy Formula

As a result, we can calculate discount factors for any year in Excel using this formula. Consequently, the result sheet is gonna look like this. 👇

Calculated Discount Factors


Conclusion

So, in this article, I have shown you 6 practical examples to calculate discount factor in Excel. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo