Spreadsheet programs like Microsoft Excel are ideal for use in calculating multiple financial variables, such as rate of payback or current value. Any variable in an equation can be determined as long as the value of the other variables is known. Use Excel to calculate the final value of an incremental term based on a permanent payment at the end of the first permanent period (interest payment), the rate of increase of cash payments per period, and the implied interest rate (the rate available on the same product), Is the necessary return rate for the investment. For example, a permanent term can start with a $1,000 interest payment at the end of the first year, with payments increasing at 1 percent annually, and with similar products with a 2 percent interest rate.
Enter the value of each variable and formula that increases life in Excel
Enter the amount paid permanently at the end of the first permanent period in the ‘B2’ box in Excel. For example, if the fix pays $1,000 at the end of the first year, type “1000” in the “B2” box. Label the adjacent cell ‘C2’ as ‘First Payment’.
Enter implied interest (the rate available for similar investments) for permanent cash payments into the ‘B3’ box. For example, if the implied interest rate for permanent payments is 3 percent annually, type ‘0.03’ in cell ‘B3’. Label the adjacent cell ‘C3’ as ‘Interest’.
Enter the annual growth rate of permanent cash payments into the ‘B4’ box. For example, if the payout is permanently increased at an annual rate of 2 percent, type ‘0.02’ in the ‘B4’ box. Label the adjacent cell ‘C4’ ‘Growth Rate’.
Enter the formula ‘= B2 / (B3-B4)’ in the ‘B5’ box. The formula is the amount paid annually at the end of the first permanent period divided by the difference between interest rates and growth rates. As a result, the final value of permanent increases in the period before the first payment. Label the adjacent cell ‘C5’ as ‘End Value’.
The perpetual growth formula does not work if the growth rate is greater than interest rates. This is reasonable because an investment cannot grow at a rate greater than permanent interest rates.