A discount factor can be thought of as a conversion factor for time value of money calculations. The discount factor table below provides both the mathematical formulas and the Excel functions used to convert between present value (P), future worth (F), uniform gradient amount (G), and uniform series or annuity amount (A).

Advertisement

What is a Discount Factor?

Time value of money calculations are based on the principle that funds placed in a secure investment earn interest over time. The compounding principle states that if we have $P to invest now, the future value will increase to $F=$P*(1+i)n after n years, where i is the effective annual interest rate.

The discounting principle states that if we want to have $F in n years, we need to invest $P right now. So, discounting is basically just the inverse of compounding: $P=$F*(1+i)-n.

The discount formula can be written as P=F*(P/F,i%,n), where (P/F,i%,n) is the symbol used to define the discount factor. To convert the future value to the equivalent present value, you simply multiple the future value by the discount factor.

In the past, it was common to refer to a discount factor table to look up the number needed to perform a time value of money conversion. With the use of calculators and spreadsheets, the table lookup technique is practically obsolete.

Cash Flow Diagrams

A Cash Flow Diagram can help you visualize a series of receipts (positive values) and disbursements (negative values) at discrete periods in time. To be clear about the nomenclature used in the discount factor table, refer to the following cash flow diagrams for P, F, A, and G.

Present Value
Fig 1. Present Value (single payment cash flow at t=0)
Future Worth
Fig 2. Future Worth (single payment cash flow at t=n)
Uniform Series
Fig 3. Uniform Series Cash Flow (the same payment amount A from t=1 to t=n)
Gradient Series
Fig 4. Uniform Gradient Series Cash Flow (linearly increasing payment amount from G at t=2 to (n-1)G at t=n
Exponential Gradient Series
Fig 5. Exponential Gradient Series Cash Flow (g might be the inflation rate for example)

Discount Rate

The Discount Rate, i%, used in the discount factor formulas is the effective rate per period. It uses the same basis for the period (annual, monthly, etc.) as used for the number of periods, n. If only a nominal interest rate (rate per annum or rate per year) is known, you can calculate the discount rate using the following formula:

Simple Amortization Calculation Formula
where
r = nominal annual interest rate
k = number of compounding periods per year
p = number of periods per year corresponding to the basis for n

This formula for the effective rate per period is more general than the formula used in the Excel functions EFFECT and NOMINAL. The EFFECT and NOMINAL functions are only used for converting between the effective and nominal annual rates, where p=1.

Monthly Payment Periods (p=12)

If the compound period is also monthly, the discount rate for a monthly payment period (p=12) simplifies down to i = r / 12. To determine the discount rate for monthly periods with semi-annual compounding, set k=2 and p=12.

Daily Compounding (p=365 or p=360)

The above formula can be used to calculate an effective annual interest rate for daily compounding by setting p=1 and k to the number of banking days in the year (typically 365 or 360).

Discount Factor Table for Discrete Compounding

The following table lists discount factors used for conversions between common discrete cash flow series, present value, future worth, etc. The { } braces around the Excel formula indicate that the formula must be entered as an array function using Ctrl+Shift+Enter.

Example: To convert F to P, multiply F by the discount factor (P/F,i%,n). It might help to think of "P/F" as "P given F". This representation comes from the algebraic equivalence P=F*(P/F).

Nomenclature
iDiscount Rate (effective rate per period)
nNumber of Periods
PPresent Worth
FFuture Worth
AUniform Series Amount (or "Annuity")
GUniform Gradient Amount

ConvertSymbolDiscount Factor FormulaDiscount Factor Formula in Excel
P to F(F/P,i%,n)(1+i)n=FV(i,n,0,-1)
F to P(P/F,i%,n)(1+i)-n=PV(i,n,0,-1)
F to A(A/F,i%,n)i/((1+i)n-1)=PMT(i,n,0,-1)
P to A(A/P,i%,n)i*(1+i)n/((1+i)n-1)=PMT(i,n,-1)
A to F(F/A,i%,n)((1+i)n-1)/i=FV(i,n,-1)
A to P(P/A,i%,n)((1+i)n-1)/(i*(1+i)n)=PV(i,n,-1)
G to P(P/G,i%,n)((1+i)n-1)/(i2*(1+i)n)-n/(i*(1+i)n){=NPV(i,(ROW(INDIRECT("1:"&n))-1))}
G to F(F/G,i%,n)((1+i)n-1)/i2-(n/i){=(P/G,i%,n) * (F/P,i%,n)}
G to A(A/G,i%,n)(1/i)-n/((1+i)n-1){=(P/G,i%,n) * (A/P,i%,n)}
EG to P(P/EG,z-1,n)(zn-1)/(zn(z-1)), z=(1+i)/(1+g)=PV(z-1,n,-1)

The Excel formulas for (F/G,i%,n) and (A/G,i%,n) are based on the algebraic equivalence of F/G=(P/G)*(F/P) and A/G=(P/G)*(A/P). Replace the discount factor symbols (P/G,i%,n), (F/P,i%,n) and (A/P,i%,n) with the appropriate discount factor formula listed in the table.

Discount Factors for Continuous Compounding

Continuous compounding is not exactly the same as daily compounding. The exact discount factor formulas for continuous compounding are given in the table below (where n is the number of years and r is the nominal annual rate). Note that the discount factor for F to P is just the inverse (1/x) of the factor for P to F.

ConvertSymbolDiscount Factor Formula... in Excel
F to P(P/F,r%,n)e-r*n = 1/er*n=1/EXP(r*n)
P to F(F/P,r%,n)er*n=EXP(r*n)
F to A(A/F,r%,n)(er-1)/(er*n-1) =(EXP(r)-1)/(EXP(r*n)-1)
A to F(F/A,r%,n)(er*n-1)/(er-1) =(EXP(r*n)-1)/(EXP(r)-1)
P to A(A/P,r%,n)(er-1)/(1-e-r*n) =(EXP(r)-1)/(1-1/EXP(r*n))
A to P(P/A,r%,n)(1-e-r*n)/(er-1) =(1-1/EXP(r*n))/(EXP(r)-1)

See Also

Additional Resources

  • Time Value of Money at wikipedia.com.
  • Time Value Function Tutorial by Timothy R. Mayes at http://www.tvmcalcs.com/index.php/calculators/excel_tvm_functions/excel_tvm_functions_page1
  • Discount Mathematics at http://www.excelexchange.com/discount%20mathematics.htm.
Disclaimer: This article is meant for educational purposes only. You may want to consult with a qualified professional regarding financial decisions.

Cite This Article

To reference this article from your website or blog, please use something similar to the following citation:

- Wittwer, J.W., "Calculating Discount Factors in Excel," From Vertex42.com.

Follow Us On ...

FB  PIN  TWEET  LI  IG  YT 
Excel Tips and Templates Newsletter - Subscribe