How to Calculate Terminal Value as a Growing Perpetuity in Excel

Spreadsheet programs like Microsoft Excel are ideal for use in calculating many financial variables, such as the rate of return or the present value. Any variable in an equation can be determined as long as the value of the other variables are known. Use Excel to calculate the terminal value of a growing perpetuity based on the perpetuity payment at the end of the first perpetuity period (the interest payment), the growth rate of the cash payments per period, and the implied interest rate (the rate available on similar products), which is the rate of return required for the investment. For example, a perpetuity could start with an interest payment of $1,000 at the end of the first year, with the payment growing at 1 percent annually and with similar products having a 2 percent interest rate.

Input the Value of Each Variable and the Growing Perpetuity Formula in Excel

Step 1

Input the amount the perpetuity pays at the end of the first perpetuity period into cell 'B2' in Excel. For instance, if the perpetuity pays $1,000 at the end of the first year, enter '1000' into cell 'B2'. Label the adjacent cell 'C2' as 'First Payment'.

Step 2

Input the implied interest rate (the rate available on similar investments) on the perpetuity's cash payments into cell 'B3'. For instance, if the implied interest rate on the perpetuity's payments is 3 percent annually, input '0.03' into cell 'B3'. Label the adjacent cell 'C3' as 'Interest Rate'.

Step 3

Input the annual growth rate of the perpetuity's cash payments into cell 'B4'. For instance, if the perpetuity's payment grows at a rate of 2% percent annually, input '0.02' into cell 'B4'. Label the adjacent cell 'C4' as 'Growth Rate'.

Step 4

Enter the formula '=B2/(B3-B4)' in cell 'B5'. The formula is the annual payment at the end of the first perpetuity period divided by the difference between the interest rate and the growth rate. The result is the terminal value of the growing perpetuity in the time period prior to the first payment. Label the adjacent cell 'C5' as 'Terminal Value'.

Warning

The growing perpetuity formula does not work if the growth rate is greater than the interest rate. This is logical because an investment cannot grow at a rate greater than the interest rate in perpetuity.

resources