I have a series of future cash flows, and a lump sum offer for them. Is
there a function I can use in Excel to calculate what the discount rate used
to generate that lump offer was? I can trial and error it using PV and
extrapolating a rate, but I was hoping for something that I could plug
Present Value, Future Value and Number of Periods into that would give me the
discount rate.
Well, now you have asked two questions with potentially different
answers.
A. If you simply have PV, FV and n(umber of periods), use the RATE()
function.
Some caveats:
1. RATE() returns the __periodic__ rate. If n is not a number of
years, you probably want to annualize the result. How you (should) do
that depends on the type of annual rate you want. Usually, the annual
compound rate is what you want. For example, if n is number of
months, the annualized rate can be computed by either of the following
equivalent methods, whichever you prefer:
=(1+rate(...))^12 - 1
=fv(rate(...), 12, 0, -1) - 1
2. Sometimes, RATE() returns an error if it is unable to derive the
periodic rate within the limits of its internal algorithm. In that
case, you need to enter the "guess" parameter (see the Help page).
Unfortunately, often you have no idea how to offer a "guess".
3. As an alternative to RATE(), in this simple case, you can compute
the __periodic__ rate with the following formula:
=(FV / PV)^(1/n) - 1
If n is a number of months and you plug that into the (second)
exponential annualization formula above, you will see that the
annualized rate can be computed by:
=(FV / PV)^(12 / n) - 1
B. However, if you have a "series of cash flows", as you stated
originally, you will need to use either the IRR() or XIRR() function,
whichever fits your data best. IRR() is used when cash flows (some
may be zero) occur with the same frequency, e.g. monthly. XIRR() is
used when cash flows occur with irregular frequency.
Some caveats:
1. IRR() returns a __periodic__ rate; ergo, you might want to
annualize it. XIRR() always returns an annual(ized) rate.
2. Be sure to use alternate signs (plus and minus) for inflows and
outflows. You must have at least one inflow and one outflow.
3. Like RATE(), sometimes IRR() and XIRR() need help by your providing
a "guess" parameter. In obscure cases, I have resorted to the trial-
and-error alternative that you described. (Although the Solver
feature sometimes works.)
