View Question
Q: discount value of future payments ( Answered ,   3 Comments )
 Question
 Subject: discount value of future payments Category: Business and Money > Finance Asked by: wlbsr-ga 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: elmarto-ga 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/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```
 wlbsr-ga rated this answer: `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.```