About this lesson
Perpetuities in Google Sheets: level and growing, and how they become a terminal value
In Google Sheets, a level perpetuity is worth the yearly cash flow divided by the discount rate: =B1/B2. A growing perpetuity divides next year's cash flow by the rate less the growth rate: =B1*(1+B3)/(B2-B3). That is the terminal value at the end of a forecast. Discount it to today with =B8/(1+B2)^B4.
Using Excel? Do this lesson in Excel.
=B1/B2What you'll practise
Value a diagnostics division beyond the forecast: a level perpetuity, next year's cash flow, a growing perpetuity, then bring that terminal value back to today.
4 steps · 40 points · the cells outlined in gold below
- perpetuities
- growing perpetuities
- terminal value
- discounting
The data
| A | B | |
|---|---|---|
| 1 | Year 5 cash flow ($m) | 18.4 |
| 2 | Discount rate | 9.50% |
| 3 | Long-term growth | 2.50% |
| 4 | Years to the end of year 5 | 5 |
| 5 | ||
| 6 | Level perpetuity ($m) | |
| 7 | Year 6 cash flow ($m) | |
| 8 | Growing perpetuity ($m) | |
| 9 | Terminal value today ($m) |