in

How to calculate the end value is a permanent added value in Excel

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.

How to Calculate Terminal Value as a Growing Perpetuity in
How to Calculate Terminal Value as a Growing Perpetuity in Excel

Enter the value of each variable and formula that increases life in Excel


Step 1
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’.

Step 2
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’.

Step 3
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’.

Step 4
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’.

Alert
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.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

GIPHY App Key not set. Please check settings

Loading…

0
Difference Between Traditional and Contributory IRA

The difference between a traditional IRA and a contributing IRA

What Is a Signature Code on a Credit Card

What is the Signature Code on a Credit Card?