What is Perpetuity?
Perpetuity is a type of annuity that pays a constant stream of cash flows indefinitely. For example, a bond that pays a fixed coupon every year forever is a perpetuity. Perpetuity is also used to estimate the terminal value of a company’s cash flows in valuation analysis.
How to Calculate Perpetuity in Excel?
To calculate the present value of a perpetuity in Excel, we can use the following formula:
= C / r
Where:
- C is the cash flow per period
- r is the discount rate or interest rate
For example, if a bond pays $10 per year and the discount rate is 5%, the present value of the bond is:
= 10 / 0.05
= 200
This means that an investor would be willing to pay $200 for the bond that pays $10 per year forever.
How to Calculate Perpetuity with Growth in Excel?
Sometimes, the cash flow of a perpetuity may grow at a constant rate. For example, a company’s dividends may increase by 2% every year. To calculate the present value of a growing perpetuity in Excel, we can use the following formula:
= C / (r - g)
Where:
- C is the cash flow per period
- r is the discount rate or interest rate
- g is the growth rate
For example, if a company pays $10 in dividends per year and the dividends grow by 2% per year, the present value of the company is:
= 10 / (0.05 - 0.02)
= 333.33
This means that an investor would be willing to pay $333.33 for the company that pays $10 in dividends per year and grows by 2% per year.
Example
Let’s say we want to compare two investment options: a bond that pays $100 per year forever and a stock that pays $50 in dividends per year and grows by 3% per year. The discount rate for both investments is 10%. Which one has a higher present value?
To answer this question, we can use the Excel formulas for perpetuity and growing perpetuity. We can create a table like this:
| Investment | Cash Flow | Growth Rate | Discount Rate | Present Value |
|---|---|---|---|---|
| Bond | 100 | 0% | 10% | =C2/D2 |
| Stock | 50 | 3% | 10% | =C3/(D3-E3) |
The result is:
| Investment | Cash Flow | Growth Rate | Discount Rate | Present Value |
|---|---|---|---|---|
| Bond | 100 | 0% | 10% | 1000 |
| Stock | 50 | 3% | 10% | 1000 |
We can see that both investments have the same present value of $1000. This means that they are equally attractive to an investor.
Other Approaches
Another way to calculate the present value of a perpetuity in Excel is to use the PV function. The PV function returns the present value of an investment based on a series of future payments. The syntax of the PV function is:
=PV(rate, nper, pmt, [fv], [type])
Where:
- rate is the interest rate per period
- nper is the total number of payment periods
- pmt is the payment made each period
- fv is the future value of the investment (optional)
- type is 0 or 1, indicating when the payments are due (optional)
To use the PV function for a perpetuity, we can set the nper argument to a very large number, such as 9999, and the fv argument to 0. For example, to calculate the present value of the bond in the previous scenario, we can use:
=PV(0.1, 9999, 100, 0, 0)
The result is 1000, which is the same as using the C / r formula.
Similarly, to use the PV function for a growing perpetuity, we can adjust the pmt argument by dividing it by 1 + g, where g is the growth rate. For example, to calculate the present value of the stock in the previous scenario, we can use:
=PV(0.1, 9999, 50 / (1 + 0.03), 0, 0)
The result is 1000, which is the same as using the C / (r – g) formula.
