Solve this problem attached using formulas or excel (steps must be shown) :

After consistently failing to win the lottery for several years, you notice the following article in theWall Street Journal. Here's your chance to buy a lottery ticket that is a guaranteed winner. But since it will apparently cost a bit over the original $1 ticket price, the winning ticket is considerably out of your price range. The investment company you work for is, however, interested in such opportunities. You decide to research a bit further and gather the interest rate data (on the next page) for the day before the ticket is auctioned. In your "first cut" at the analysis, you decide to ignore taxes. The winner will receive the first of the 16 payments almost immediately, with the rest stretched out over 15 years.

1- If you can purchase the ticket for $2.0 million, what annual rate of return will you get on your investment? What about if you win the bid at $1.5 million?

2- Given the current (July 1, 1992) prices and yields in the capital markets, what do you think is the approximate "fair market value" of the ticket?

3- If you factored taxes into this situation, how would it change your valuation of the ticket?

291-028 Valuation and Discounted Cash Flows Valuation and Discounted Cash Flows 291-028 3. After consistently failing to win the lottery for several years, you notice the Tables for Problem #3 are extracted from following article in the Wall Street Journal. Here's your chance to buy a lottery ticket that is a guaranteed winner. But since it will apparently cost a bit over the The Wall Street Journal, July 2, 1992 original $1 ticket price, the winning ticket is considerably out of your price range. The investment company you work for is, however, interested in such opportunities. You decide to research a bit further and gather the interest rate data (on the next page) for the day before the ticket is auctioned. In your "first TREASURY BONDS, NOTES & BILLS cut" at the analysis, you decide to ignore taxes. The winner will receive the first incpresentative Over-the-Counter quotations based on transactions of $1 of the 16 payments almost immediately, with the rest stretched out over 15 years. Treasury bond. note and bill quotes ate ase on mof internwer changes in 32nds. n-Treasury note. Treasury bill quotes in hundredths. vields are to maturing rity and based on me asked quote. ' aths, quoted on terms of If you can purchase the ticket for $2.0 million, what annual rate of return will you get on your week bills are boldfaced. For bonds callable prior to maturity, vicios and investment? What about if you win the bid at $1.5 million? maturity date for issues below par. -. When Source: Federal Reserve Bank of New York. U.s. Treasury strips as of 3 p.m. Eastern time, also based on transaction Given the current (July 1, 1992) prices and yields in the capital markets, what do you think is the cans 101 1/32. Net changes In 32nds. Yields ids calculated on the asked quote- approximate "fair market value" of the ticket? ci-stripped coupon Interest, bo- Treasury bond, stripped principal, no-Trea- computed to the earliest call date for issues quoted above par and to the MORTGAGE-BACKED SECURITIES Source: Bear, Stearns & Co. via Street Software Technology Inc. Representative Issues, quoted by Salomon Broth GOVT. BONDS & NOTES REMAINING If you factored taxes into this situation, how would it change your valuation of the ticket? ) (Pts.-3208) (32ds) CHANGE te Mo/ Yr Bid Asked Cha. vid. (Years) HELD' (Basis pis. 100: 11 100 30-YEAR The Wall Street Journal, June 30, 1992 GNMA 8.0% 29.6 FHLMC Gold 8.0% 10.5 101-14 29.3 100-30 FNMA 8.0% 100-24 88&8888 58 8583 Pricey, but Perhaps the Only Way GNMA 9.0% 106-09 FHLMC Gold 9.0% 105-14 FNMA 9.0% GNMA 10.0% 105-12- To Buy a Certain Lottery Winner FHLMC Gold 10.0% 109-02 + 2 FNMA 10.0% 107-22 unch unch unch 15-YEAR By ANDREA GERLIN in the auction, declines to speculate about GNMA 8.0% whether the minimum bid is a bargain or HLMC Gold 8.0% FNMA 8.0% 5.2 103-24 + 4 7 Staff Reporter of THE WALL STREET JOURNAL 103-13 + 4 7.18 $3 39 3393939383 NEW YORK - After the late Solomon how much the prize may fetch. "That's for 103-06 + 4 7.24 Keith bought a lottery ticket at the Chirag each individual to calculate, depending on on projections from Salomon's prepayment model, assuming interest Newsstand on Wall Street in 1987, his rates remain unchanged from current levels number came up not once but twice. what they think the future of interest rates Mr. Keith won a $5 million share of is going to be," he says. a larger jackpot but didn't live to enjoy Using the 15-year Treasury bond at much of it. Unfortunately, the 55-year-old recent yields as a benchmark, a Wall ank janitor died in an auto accident 15 Street financial analyst predicts that the months later, not long after collecting the successful bid is likely to hover around $2.1 second of 21 annual installments of $240,245 million. But if a bidder were to walk away with rights to the payments in exchange each. As a result, surrogate's court in lower for the minimum $1.3 million, the financial 38GP &8=8CP28828 Manhattan will be the scene of some analyst figures that the investor will have unusual bidding tomorrow morning when parked the money in the equivalent of a 17% investment. And while that yield *+++: Mr. Keith's $3.8 million taxable lottery would be high, the risk is low, because the proceeds - the remainder after he and his state isn't likely to default. heirs collected five payments -are offered In the past, living prize winners have in an estate auction. This time, the award will cost more sold their lottery futures in private auc- tions, but many states prohibit such trans- than the "dollar and a dream" advertised by New York state's lottery. A minimum actions. New York state allows them only with a judge's approval. A public estate bid of $1.3 million has already been set in this latest of gambles on interest rates. auction is even less common. The lucky bidder will buy into an Although the $240,245 annual payments may look attractive, would-be investors exclusive club of 800 grand prize winners. should bear in mind that the end payment Merely playing this game may require membership in an exclusive club, though: in 2007 would have a net present value of $68,281, discounted at 8.18% annually. Bidders must present a certified or bank check payable to the public administrator Charles Milo, an attorney involved of New York for $130,000