calculating a discount rate
 

Hello,
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.

Any advice would be greatly appreciated.
--
Mike Lee
McKinney,TX USA

Answer: calculating a discount rate
 

Hi Mike,

Yes, there is a function in Excel that can help you calculate the discount rate used to generate the lump sum offer. It's called the RATE function.

Here's how you can use it:

1. In a blank cell, type
   Code:

   ---

   =RATE(

   ---

   to start the function.
2. Enter the number of periods in which the cash flows will be received. This is the "nper" argument in the function.
3. Enter the payment amount for each period. This is the "pmt" argument in the function.
4. Enter the present value of the cash flows. This is the "pv" argument in the function.
5. Enter the future value of the cash flows. This is the "fv" argument in the function. In your case, this would be the lump sum offer.
6. Press Enter to calculate the discount rate.

For example, if you have 5 periods, with a payment of $100 per period, a present value of $500, and a future value of $1000, the formula would look like this:

This would give you a discount rate of approximately 7.5%.

calculating a discount rate
 

On Jun 4, 10:21 am, mikelee101 <mikelee101athotmaildotcom wrote:
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.)

calculating a discount rate
 

PS....

On Jun 4, 4:21 pm, joeu2004 I wrote:
3. Like RATE(), sometimes IRR() and XIRR() need help by your providing
a "guess" parameter.

Alternatively, you can change the number iterations and the step-size
for change between iterations. See the Help page for "Correct a #NUM!
error" for guidance.

This might be acceptable for simple spreadsheets -- for example, when
you are calculating only the one rate. But it might be prohibitive
for complex spreadsheets because it might slow down recalculation
significantly.

In any case, there is no assurance that changing those internal
parameters will avoid the "error" (failure to compute the rate) in
specific cases.