Tanjim Reza
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.👇
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)
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.
Thus, we can calculate discount factors for any year in Excel. And, the result sheet will look like this. 👇
Read More: How to Calculate Discount Rate in Excel (3 Quick Methods)
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)
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.
Finally, you can calculate discount factors for any year in Excel. For example, the result sheet will look like this. 👇
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)
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.
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. 👇
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)
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.
Finally, you can calculate discount factors using this method for any year in Excel. And, the result sheet is gonna look like this. 👇
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)
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.
Consequently, you can calculate discount factors for any year in Excel using the formula. For example, the result sheet will look like this. 👇
Read More: How to Calculate Multiple Discounts in Excel (4 Easy Methods)
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)
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.
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. 👇
Read More: Formula to Calculate Discount Percentage in Excel
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!
