How to Calculate Discounted Cash Flows using Excel

This guide will walk you through the concepts, formulas, and Excel techniques for calculating discounted cash flows, with special focus on annuities, perpetuities, and delayed payments. Also READ: Understanding Annuities, Perpetuities, and Their Variations

The time value of money is one of the most fundamental concepts in finance, stating that a dollar today is worth more than a dollar in the future. This principle forms the foundation for discounted cash flow analysis, which is essential for valuing investments, loans, and various financial instruments.

Discounted cash flow calculations help answer critical questions like:

• What is the present value of future payments?
• How much should I invest today to reach a future goal?
• What is the fair value of a bond, loan, or investment?

Fundamental Concepts

Before diving into specific calculations, let's establish the key variables used throughout this guide:

• PV: Present Value - the current value of future cash flows
• FV: Future Value - the value of a present amount at some point in the future
• PMT: Payment - the periodic cash flow in an annuity
• r: Discount Rate - the interest rate or required rate of return (expressed as a decimal)
• n: Number of Periods - the total number of time periods
• g: Growth Rate - the rate at which payments grow over time (for growing annuities/perpetuities)
• t: Time - specific time period

The fundamental formula for discounting a future cash flow is:

PV = FV / (1 + r)^n

Where PV is the present value, FV is the future value, r is the discount rate per period, and n is the number of periods.

Calculating the Present Value of a Single Cash Flow 

Before addressing multiple cash flows, it's important to understand how to discount a single payment.

Manual Calculation

The formula to calculate the present value of a single future cash flow is:

PV = FV / (1 + r)^n

In Excel

In Excel, you can use either a formula or the built-in PV function:

• Formula approach: =FV/(1+r)^n
• Function approach: =PV(r, n, 0, -FV)
  • Note: In Excel's PV function, you input 0 for the PMT parameter since we're dealing with a single future payment, not periodic payments.

Example: To find the present value of $10,000 received 5 years from now with a 7% discount rate:

• Manual formula: =10000/(1+0.07)^5 = $7,129.86
• Excel function: =PV(0.07, 5, 0, -10000) = $7,129.86

Annuities 

An annuity is a series of equal payments made at regular intervals. There are two main types of annuities:

Ordinary Annuities 

In an ordinary annuity (also called annuity in arrears), payments occur at the end of each period.

Formula

The present value of an ordinary annuity is:

PV = PMT × [(1 - (1 + r)^(-n)) / r]

In Excel

You can calculate the present value of an ordinary annuity using:

• Formula approach: =PMT*((1-(1+r)^(-n))/r)
• Function approach: =PV(r, n, -PMT, 0, 0)

Example: To find the present value of $1,000 paid at the end of each year for 10 years with a 5% discount rate:

• Manual formula: =1000*((1-(1+0.05)^(-10))/0.05) = $7,721.73
• Excel function: =PV(0.05, 10, -1000) = $7,721.73

Annuities Due

In an annuity due, payments occur at the beginning of each period.

Formula

The present value of an annuity due is:

PV = PMT × [(1 - (1 + r)^(-n)) / r] × (1 + r)

In Excel

You can calculate the present value of an annuity due using:

• Formula approach: =PMT*((1-(1+r)^(-n))/r)*(1+r)
• Function approach: =PV(r, n, -PMT, 0, 1)
  • Note: The final parameter "1" indicates payments at the beginning of periods

Example: To find the present value of $1,000 paid at the beginning of each year for 10 years with a 5% discount rate:

• Manual formula: =1000*((1-(1+0.05)^(-10))/0.05)*(1+0.05) = $8,107.82
• Excel function: =PV(0.05, 10, -1000, 0, 1) = $8,107.82

Perpetuities

A perpetuity is an annuity that continues forever—an infinite stream of equal payments made at regular intervals.

Standard Perpetuities

Formula

The present value of a perpetuity is remarkably simple:

PV = PMT / r

In Excel

Since Excel doesn't have a dedicated function for perpetuities, we use the formula directly:

=PMT/r

Example: To find the present value of a perpetuity of $500 per year with a 6% discount rate:

=500/0.06 = $8,333.33

Growing Perpetuities

A growing perpetuity is a perpetuity where payments grow at a constant rate.

Formula

The present value of a growing perpetuity is:

PV = PMT / (r - g)

Where g is the growth rate (which must be less than r).

In Excel

Again, we use the formula directly:

=PMT/(r-g)

Example: To find the present value of $500 per year growing at 2% annually, with a 6% discount rate:

=500/(0.06-0.02) = $12,500.00

Delayed Payments

Sometimes cash flows don't start immediately, but after a certain period, these are called delayed payments.

Delayed Annuities 

A delayed annuity (or deferred annuity) is an annuity where payments start after a waiting period.

Formula

The present value of an ordinary annuity delayed by d periods is:

PV = PMT × [(1 - (1 + r)^(-n)) / r] × (1 / (1 + r)^d)

In Excel

You can calculate this as:

• Two-step approach:
  1. Calculate the PV as if it started immediately: =PV(r, n, -PMT)
  2. Discount that value for the delay: =Step1/(1+r)^d

Example: To find the present value of $1,000 paid annually for 10 years, but starting 5 years from now, with a 5% discount rate:

1. First, calculate the PV as if starting immediately: =PV(0.05, 10, -1000) = $7,721.73
2. Then discount for the 5-year delay: =7721.73/(1+0.05)^5 = $6,047.77

Combined in one formula: =PV(0.05, 10, -1000)/(1+0.05)^5 = $6,047.77

Delayed Perpetuities

A delayed perpetuity is a perpetuity that starts after a waiting period.

Formula

The present value of a perpetuity delayed by d periods is:

PV = (PMT / r) × (1 / (1 + r)^d)

In Excel

You can calculate this as:

=(PMT/r)/(1+r)^d

Example: To find the present value of a perpetuity of $500 per year starting 3 years from now, with a 6% discount rate:

=(500/0.06)/(1+0.06)^3 = $7,000.74

For a growing delayed perpetuity (growing at rate g, delayed by d periods):

PV = [PMT / (r - g)] × (1 / (1 + r)^d)

In Excel: =(PMT/(r-g))/(1+r)^d

Comprehensive Excel Functions 

Excel offers several financial functions that simplify discounted cash flow calculations:

Key parameters:

• rate: Interest/discount rate per period
• nper: Total number of payment periods
• pmt: Payment made each period
• pv: Present value
• fv: Future value
• type: When payments are due (0 = end of period, 1 = beginning of period)

Practical Excel Examples

Let's work through some complete Excel examples to demonstrate these concepts.

Example 1: Retirement Planning

Suppose you want to know how much to save each month to reach $1 million in 30 years, assuming an annual interest rate of 7%.

Step 1: Convert the annual rate to monthly. Monthly rate = 7% / 12 = 0.583%

Step 2: Calculate the required monthly payment. Excel formula: =PMT(0.07/12, 30*12, 0, 1000000) Result: -$887.63 (the negative indicates payment outflow)

Example 2: Mortgage Calculation

Calculate the present value of a 30-year mortgage with monthly payments of $1,500 at an annual interest rate of 4%.

Step 1: Convert the annual rate to monthly. Monthly rate = 4% / 12 = 0.333%

Step 2: Calculate present value (loan amount) Excel formula: =PV(0.04/12, 30*12, -1500) Result: $314,294.17

Example 3: Bond Valuation

A bond pays a $50 semiannual coupon for 10 years and has a $1,000 face value. If the market interest rate is 6% annually, what is the bond's fair value?

Step 1: Convert the annual rate to semiannual. Semiannual rate = 6% / 2 = 3%

Step 2: Calculate the present value of the coupons (annuity) and the face value (single payment). Excel formula: =PV(0.06/2, 10*2, -50, -1000) Result: $911.37

Common Pitfalls and Troubleshooting 

When working with discounted cash flows in Excel, watch out for these common issues:

1. Sign conventions: Excel's financial functions assume cash outflows are negative and inflows are positive. Mixing these up will give you the wrong answer.

2. Period matching: Ensure your discount rate matches the payment frequency. If payments are monthly, convert annual rates to monthly.

3. Begin/end of period confusion: Specify correctly whether payments occur at the beginning (type=1) or end (type=0) of periods.

4. Growing perpetuity limitations: Remember that for a growing perpetuity, the growth rate (g) must be less than the discount rate (r), or the calculation will yield a negative or undefined value.

5. Rounding errors: For complex calculations, use cell references rather than rounded intermediate values to maintain precision.

Advanced Applications 

Uneven Cash Flows

For irregular cash flows, you can use the NPV function:

=NPV(rate, value1, value2, ...)

For irregular intervals, use XNPV:

=XNPV(rate, values, dates)

Sensitivity Analysis Using Data Tables

You can create a sensitivity table to see how present values change with different discount rates:

1. Set up a range of discount rates in a column
2. Set up your PV formula in the adjacent cell to the first rate
3. Select the range including the formula and rates
4. Go to Data > What-If Analysis > Data Table
5. Set the "Column input cell" to your discount rate cell
6. Click OK

Calculating Internal Rate of Return (IRR)

To find the discount rate that makes the present value of cash flows equal to zero:

=IRR(values, [guess])

For irregular intervals:

=XIRR(values, dates, [guess])

Summary 

Discounted cash flow analysis is a powerful tool for financial decision-making. In this guide, we've covered:

• The time value of money and its importance
• Formulas for discounting single cash flows
• Ordinary annuities and annuities due
• Standard and growing perpetuities
• Delayed annuities and perpetuities
• Excel functions and practical examples
• Common pitfalls and advanced applications

By mastering these concepts and Excel techniques, you'll be well-equipped to perform financial analysis, investment valuation, and personal financial planning. Remember that while the mathematics may seem complex at first, with practice, these calculations become intuitive tools for making informed financial decisions.

For most common financial calculations, Excel's built-in financial functions provide a convenient and reliable way to perform even the most complex discounted cash flow analysis.