You've heard about the tobacco buyout... here's how it works.  10
equal payments...the first september 15, 2005 then 9 more each January
15 starting Jan. 15, 2006.  What would be the discount rate on a lump
sum payment made october 15, 2005 for the 9 payments starting Jan. 15,
2006.  I would like an excel spreadsheet where you can enter in the
lump sum % (say 72%)and get back the discount rate (say 7.13%).  I can
do an IRR formula but do not know how to take into account the 3 month
time span on the first payment.

Hi wlbsr!
I've written a spreadsheet for you, you can get it at the following link:

http://www.angelfire.com/alt/elmarto/googleanswers/516110-discountrate.xls

(right-click on the link and choose "Save Target As...")

It's interesting to note that not only the first payment comes at a
"funny" time, but all 9 payments do. The first payment comes 1/4 year
from now (if "now" is Oct 15); the second payment comes 1 1/4 years
from now; the third comes 2 1/4 years from now, and so on.

In order to get the solution, I simply used the IRR function to
calculate the 3-month discount rate. Then I annualized it using the
usual formula [(1+i)^n]-1. It should be quite clear from the
spreadsheet.

In order to change the parameters of this problem, I painted in yellow
the cells that contain them: you can change the lump-sum "rate" (81%
in your example in the comment) and the total amount due with the
non-lump-sum payment option (100,000 in your example in the comment).

If there's anything you don't understand from the spreadsheet or if
it's not what you were looking for, please don't hesitate to request
clarification, and I'll keep working on it.

Best wishes!
elmarto

---

Request for Answer Clarification by wlbsr-ga on 30 Apr 2005 15:42 PDT

this is exactly what i was thinking.  i had done the irr part but
needed a push on the quarterly frequency.  are you positive about
annualizing the quarterly discount rate... the annual discount rate
suprises me to be that high.  this is a real situation getting ready
to come up on about $10 bil. in the US  Most people facing this
situation need decide whether to take a lump sum payment.  some facing
this decision will be investing the money or paying off loans.  others
may be making the lump sum payment as an investment.  I may be doing
both so the DR is critical. Thanks for the answer.  Beyond the scope
of this question do you think that I need to do anymore work or go
with this ss.  I assume if i needed to fine tune the payment times
more (if something were to change) I cound go to monthly payments and
modify the annualized discount formula by changing the 4 to a 12.

---

Clarification of Answer by elmarto-ga on 30 Apr 2005 16:02 PDT

Hi wlbsr,
Thank you for the rating! I'm very glad you liked my answer.

Regarding the annualization of the quarterly rate, I'm positive that
the formula is correct. Unfortunately, I feel I don't have enough
information on the subject of the tobacoo buyout in order to give an
educated advice as to how these results apply to the real issue. Also,
please bear in mind that the lump-sum rate I included in the sheet is
just an example, so the actual DR remains unknown until the lump-sum
rate is defined.

Best regards,
elmarto

---

Clarification of Answer by elmarto-ga on 30 Apr 2005 16:10 PDT

I forgot to mention -- yes, you could certainly switch to monthly
payments and change the formula from 4 to 12 if something were to
change. The logic of the procedure would be exactly the same.

Best regards,
elmarto

exactly what I was looking for.

Wlsbr --

A 3-month net present value (NPV) factors is simply the cost of 3
months of money.  If your real annual interest rate is 12%,  you'd
simply be using 1/4 of the year's rate (4%).

I would be using NPV calculations here, simply because it's easier to
setup and understand.

Choice of the interest rate for compounding is key.  My guess is that
the number used will be the 5-year U.S. Treasury bill yield, available
in the Wall Street Journal.  A discount higher rate would reduce the
lump sum payment.

Finally, I'd be happy to create the spreadsheet for you, if you'd like
-- though you'll have to understand that changes in assumptions can
change lump sums slightly.

Best regards,

Omnivorous-GA

Wlbsr --

In the interim since the last comment, I've set up an NPV table for
you.  It allows you to multiply any of the 10 payments (first, plus
the 9 annual) by a factor to get it's value in a lump sum.  I haven't
read the tobacco buyout program, but I'm guessing that the first
payment is smaller (probably 1/4) of the annual payments -- we we have
to be careful in handling it.

ALSO NOTE: There's an implied discount of 1-month to that first
payment, with a timing different of one-month on it (Sept. 15 to Oct.
15).  That would simply be one month of the implied rates.

My table handles interest rates of 3.5% - 4.2%, bracketing the range
of a 5-year Treasury bill.  Let me know if that would be acceptable --
and if you want to try an example payout, such as:
$2,500 in October, $10,000 in years 1-9

Best regards,

Omnivorous-GA

thanks for your reply,  read the original question more carefully. 
Ther are 10 equal payments.  One on sept 15, 2005 and 9 more each Jan
15 starting in 2006.  There are going to be financial institutions
offering lump sum payments on the last 9 payments and they will make
this lump sum payment on oct 15, 2005.  I need a spreadsheet that
figures the interest rate from the percentage of the lump some
payment.  using the irr function and an 81% lump sum payment I get
4.44%.  For example 100,000 is 10 payments of 10,000.  I get the first
one anyway but then I want a lump sum payment in return for the next
9.  So an 81% lump sum payment would be 72,900 on Oct 15, 2005.  using
irr this would equal 4.44%.  This is probably close but because of the
1st payment is discounted only 3 months i am sure it is off a little.

The discount rate would be whatever someone chooses to pay you but i
am looking to convert a lump sum percentage (81%) into a discount rate
(4.44%) also handling the funny payment time of the lump some.