Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:

Code:
=RATE(5,-100,500,-1000)
This would give you a discount rate of approximately 7.5%.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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.)

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating pensions rate Pasty Excel Worksheet Functions 4 May 25th 07 04:15 PM
Calculating Time * Rate Patty Excel Discussion (Misc queries) 3 January 26th 07 02:27 AM
Solving for discount rate in present value calculation [email protected] Excel Worksheet Functions 0 June 5th 06 05:07 PM
Calculating rate based on age ranges HRLADY Excel Worksheet Functions 0 May 24th 06 10:08 PM
Discount rate - what to use? tiger1969 Excel Worksheet Functions 1 September 7th 05 12:37 AM


All times are GMT +1. The time now is 08:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"