Robert C. Higgins
Chapter 7 – Discounted Cash Flow Techniques page 247
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.
1) An investment costing $50,000 promises an after tax cash flow of $18,000 per year for 6 years.
a. Find the investment's accounting rate of return and its payback period.
b. Find the investment's net present value at a 15 percent discount rate.
c. Find the investment's profitability index at a 15 percent discount rate.
d. Find the investment's 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...