You want to figure out the required rate of return for some given present value to grow to a desired future value.
Use the RATE function.
Let's say you have $5,000 now and want to know what rate of return would be required for that sum to grow to $15,000 in 10 years if interest is compounded annually. The RATE function can figure this out for you.
The syntax for RATE is =RATE(nper, pmt, pv, fv, type, guess), where nper is the number of payment periods, pmt is the payment per period, pv is the present value, fv is the future value, type is an indicator of the payment type (0 for payments at the end of each pay period or 1 for payments at the beginning of each pay period), and guess is an initial guess at the required interest rate.
For our example, the formula =RATE(10, 0, -5000, 15000, 0, 0.1) returns a required interest rate of 11.61%. Notice that I entered the present value as a negative value, indicating an out payment. The signs for pv and fv must be opposite (fv can be 0); otherwise, RATE returns an error.
Take a look at the other recipes in this chapter to learn about other calculations of the time value of money.
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Time Series Analysis
Curve Fitting and Regression
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations