Discounted Cash Flow Techniques Essay Example for Free

Discounted Cash Flow Techniques Essay A brief tutorial on Excel financial functions (problems to follow) You may find the following Excel, built-in financial functions helpful when analyzing the problems below. (To access these functions, select Insert, Functions, and choose Financial.) =PV(rate, nper, pmt, fv, type) returns the present value of a series of cash flows. =FV(rate, nper, pmt, pv, type) returns the future value of a series of cash flows. =PMT(rate, nper, pv, fv, type) calculates the periodic payment for a loan based on constant payments and a constant interest rate. =NPER(rate, pmt, pv, fv, type) returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. =NPV(rate, range) returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values). (Warning: By convention, NPV calculates the net present value one period before the first cash flow.) =IRR(range, guess) returns the internal rate of return for a series of cash flows. In these functions, rate = the discount, or interest rate. nper = number of periods. pmt = annual uniform payment. fv = future value, or future cash flow. type is a logical value allowing you to specify if cash flows occur at the end or the beginning of the period. A value of 1 indicates beginning of period, 0 or omitted indicates end of period. pv = present value. range = the cells on your spreadsheet containing the cash flows you want to analyze. For example, if the cash flows are in the first 10 rows of column A, the entry for range would be a1:a10. guess = your guess as to the internal rate of return. This helps the computer get started and may be left blank. An example Suppose you want to know the present value of $100 per year for 19 years and $500 at the end of the 19th year when the interest rate is 13 percent. Select a spreadsheet cell and enter =PV(0.13,19,100,500). Excel will return ($742.83). This is the amount one should be willing to pay today to receive the indicated stream of cash flows when the interest rate is 13 percent. Problems 1) An investment costing $50,000 promises an after tax cash flow of $18,000 per year for 6 years. a. Find the investments accounting rate of return and its payback period. b. Find the investments net present value at a 15 percent discount rate. c. Find the investments profitability index at a 15 percent discount rate. d. Find the investments internal rate of return. e. Assuming the required rate of return on the investment is 15 percent, which of the above figures of merit indicate the investment is attractive? Which indicate it is unattractive? 2) A $1,000 par value, 10 percent coupon bond matures in 20 years. If the price of the bond is $1,196.80, what is the yield to maturity on the bond? Assume interest is paid annually. 3) Ten years ago you invested $1,000 for 10 shares of Trublock common stock. You sold the shares recently for $2,000. While you owned the stock it paid $10.08 per share annual dividends. What was your rate of return on Trublock stock? 4) Having heard of your knowledge of present value techniques, you have been asked to testify as an expert witness in the following lawsuit. Several homeowners in a nearby community have organized to protest against alleged gouging on the part of a local lending institution. One resident presents his payment book as evidence. The resident has a 30-year, fixed rate loan at 6 percent interest for $200,000. He got the loan 10 years ago and has been making equal annual payments of $14,529.60 ever since. He observes that he has paid the lending company $145,296.00 yet his payment book indicates that the principal due on the loan has only declined by $33,345.40. He presents this as obvious proof of gouging on the part of the money changers. Do you agree? Why, why not? 5) In 1984, the city council of the town of Patterson agreed that their community badly in need of a modern hotel that would cost approximately $25 million. To finance construction members of the council organized the Patterson Hotel Corporation. Through strenuous promotion they raised $15 million by selling 15,000 shares of stock at $1,000 per share. They secured the other $10 million necessary to build the hotel as a loan provided by a local bank on a 10 year, 14 percent mortgage that called for uniform annual payments sufficient to pay interest and to extinguish the debt at the end of 10 years. Upon completion, the Patterson Hotel Corporation leased the hotel to a national company that operated a chain of hotels. The lease ran for 30 years and contained a clause permitting the lessee to purchase the hotel for $10 million at the end of the 30-year period. The lessee agreed to furnish the hotel and pay all taxes (including income taxes) and operating expenses, and was to meet the interest and repayment obligations on the mortgage during the first 10 years of the lease. During the last 20 years of the lease, the operating company agreed to make payments sufficient to permit annual dividends of $400 per share. No payments at all were to be made to the stockholders during the first 10 years. This was the most favorable operating contract that Patterson Hotel Company was able to negotiate. When stockholders, many of whom had bought stock under considerable pressure, learned that there was no prospect of dividends for 10 years, they were quite upset, and a number of them were anxious to sell their stock. Conrad Billings, a local businessmen in the original group that promoted the hotel, was reported to be buying stock from some of these disgruntled stockholders at $750 a share. Some locals were heard to comment that Conrad Billings was a clever old bastard who was taking advantage of his public-spirited neighbors. There were remarks regarding the fat dividends he would be receiving after the mortgage was paid off. One man was said to have declined Billings’ offer of $750 a share and to have commented publicly that nobody was going to get his stock unless they paid what it was worth. Make whatever assumptions and calculations you find necessary to estimate the fair value of the stock. Was $750 a share really too low a price? 6) You need four new tires for your car. You can buy cheap retread tires for $25 a piece and replace them every 20,000 miles or you can spend $40 per tire and replace them every 40,000 miles. If money has an opportunity cost of 10 percent to you, how many miles must you drive annually to warrant the more expensive tires?