

Subject:
discount value of future payments
Category: Business and Money > Finance Asked by: wlbsrga List Price: $20.00 
Posted:
30 Apr 2005 01:05 PDT
Expires: 30 May 2005 01:05 PDT Question ID: 516110 
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. 

Subject:
Re: discount value of future payments
Answered By: elmartoga on 30 Apr 2005 13:23 PDT Rated: 
Hi wlbsr! I've written a spreadsheet for you, you can get it at the following link: http://www.angelfire.com/alt/elmarto/googleanswers/516110discountrate.xls (rightclick 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 3month 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 lumpsum "rate" (81% in your example in the comment) and the total amount due with the nonlumpsum 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  
 
 

wlbsrga
rated this answer:
exactly what I was looking for. 

Subject:
Re: discount value of future payments
From: omnivorousga on 30 Apr 2005 07:44 PDT 
Wlsbr  A 3month 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 5year 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, OmnivorousGA 
Subject:
Re: discount value of future payments
From: omnivorousga on 30 Apr 2005 08:41 PDT 
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 1month to that first payment, with a timing different of onemonth 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 5year 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 19 Best regards, OmnivorousGA 
Subject:
Re: discount value of future payments
From: wlbsrga on 30 Apr 2005 12:26 PDT 
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. 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 