Figuring Out Required Rate of Return
Problem
You want to figure out the required rate of return for some given present value to grow to a desired future value.
Solution
Use the RATE function.
Discussion
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.
See Also
Take a look at the other recipes in this chapter to learn about other calculations of the time value of money.