Nash - Scientific Computing with PCs (523165), страница 43
Текст из файла (страница 43)
A general method for solving a single nonlinear equation(one real root of a function) is easier to find (Kahaner, Moler and Nash S G, 1989, Chapter 7; Nash J C,1990d, Algorithm 18). An advantage is that we can work entirely in real arithmetic.14.4 Programs or PackagesWe now need to decide how to implement our solution method. If we decide to compute all the roots ofthe polynomial, we need a tool that can compute these. Since the Jenkins (1975) program is part of theACM Transactions on Mathematical Software collection available through NETLIB we could get this by14: EXAMPLE: INTERNAL RATE OF RETURN PROBLEM123electronic mail or a file transfer method.
We would then have to write a driver program, compile it andsupply the polynomial coefficients.MATLAB uses the companion matrix approach and has a function roots() built in. A useful adjunct to thisis the poly() function that lets us rebuild the polynomial from its roots as a check on the computations.We note that DERIVE will symbolically solve for real roots of some nonlinear equations.
It is designedto find exact or symbolic solutions, so we cannot be certain it will be of use. There are many choices ofgeneral rootfinder software. In addition to the references above, we can use codes from Press et al. (1986)and subsequent volumes. There are also rootfinders in a number of public software collections e.g.,NETLIB.Using spreadsheets we can easily plot NPV(r) for a reasonable range of discount rates r. Somespreadsheets now offer to find roots of functions (solve equations), though student versions on our shelfof Lotus, Quattro and Excel did not have this capability.
They have a built-in IRR and NPV functions thatwe do not think are worth using, as will become clear. It is possible to "program" in the macro languageof spreadsheets, and root finding could be carried out this way.Lotus allows other software to be attached to it. Of these "add-in" programs, What-If Solver (Nash J C,1991b) provides a capability to find roots of general nonlinear equations, so could be applied here.14.5 Some SolutionsOur software choice for solving rate-of-return problems would be a straightforward spreadsheet packagewithout special additions to compute roots of equations. That is, we forgo the mathematical satisfactionof finding the complete set of roots of the polynomial equation (14.2.2). Because the rate of return is usedas a guide to investment management, it is not important to find a very accurate approximation to anysingle root.
Instead, we suggest plotting the NPV against discount rate r and observing where zeros arelocated. It is easy to add to the table used for plotting and verify NPV for good "guesses" of r. However,we suggest that IRR and NPV functions built into many spreadsheets not be used for reasons we givebelow.Figure 14.5.1 shows the data and expressions for the straightforward problem defined in Figure 14.2.1a.Figure 14.5.2 shows the graph of NPV(r) versus r, the discount rate. Calculations were carried out withLotus 1-2-3; Quattro Pro gives equivalent results.
Readers should note that the @NPV() function withinspreadsheets does not give us NPV(r) as defined in Equation 14.2.1 without some modifications. If inthe spreadsheet our net revenues N(1) ... N(9) are stored in cells C11 to C19, with the initial net revenue(i.e., the investment) N(0) in C10, the correct expression for the NPV for time t=0 at a rate stored in cellB10 is+C10+@NPV(B11,C11..C19)Since the IRR is a built-in function of many spreadsheet packages, it is tempting to use this function.
InFigure 14.5.1 this works correctly. DO NOT trust such functions. They will not always work for problemswith multiple roots, such as that defined by the net revenues in Figure 14.2.1b. From the graph in Figure14.5.3 we see that there are solutions near 10%, 50% and -150% and these are confirmed by the worksheet.The @IRR function determines this latter root at -143.857% from starting guesses at 0, 0.5, and -1.5, eventhough the correct value is very close to -143%. It fails (gives ERR as the result) from several other trialstarting values. In an attempt to overcome the difficulty, we changed the sign of all net revenues.
Thisshould not change the position of the root, but it caused all our attempted @IRR calculations to give anERR result. The NPV at -143.857 as calculated by the @NPV function and by direct spreadsheet statementsare different. This is probably because the elements of the sum are quite large and digit cancellation isoccurring. Note @IRR does not find the root at 10% that is likely to be of interest.We can also program our own solution by rootfinding.
In 1980 we built a rate-of-return program in BASICusing character graphics to plot NPV versus r and the rootfinder of Algorithm 18 in Nash J C (1990d).124Copyright © 1984, 1994 J C & M M NashSCIENTIFIC COMPUTING WITH PCsNash Information Services Inc., 1975 Bel Air Drive, Ottawa, ON K2C 0X1 CanadaFigure 14.5.1Copy for:Dr. Dobb’s JournalData and expressions for a straightforward internal rate of return problem using a Lotus1-2-3 spreadsheet.IRR2a.WK1 -- Internal rate of return=7.827 %Time period:012345678Investments --> 600000 20000Revenues-->030025060000=========================================================Combined--> -600 300 250 600 -20000 455 666 777Contribution to -600 278 215 479 -14790 289 393 425NPV at r= 7.8275 == Summed NPV contributions =0.002455666777NPV (r from @IRR = 7.8275) --> -2E-14== check on calculated IRR using = -600 + NPV(t=1..t=8)We would not recommend programming this problem now unless it is to be embedded inside someheavily-used investment analysis package.Using the "all roots" formulation, we can quite in quite straightforward fashion acquire and install theJenkins (1975) program.
This took one of us about an hour to do, including getting the code from NETLIBby electronic mail, downloading it by telephone to our PC, writing and testing a driver program, and thenrunning our problem. A nuisance was finding the right values to use for the radix, number of digits,smallest and largest real numbers that the program needs. These are particular to compiler and computerused; we eventually spent over half a day investigating the possibilities, as illustrated in Figure 14.5.4.A further difficulty comes in deciding whether roots are admissible.
The roots() function of MATLAB gives(1+r) values at 1.10, 1.50 and -0.5 having small non-zero imaginary parts. Initial results from the Jenkinsprogram did not seem to match. Using a smaller "machine precision" value, though the arithmetic isunchanged, gave results in closer agreement to MATLAB. Using MATLAB, we reconstruct the polynomial,scale it appropriately, and find the deviation from the original sequence of cash flows. Such an exerciseshowed that the roots computed by MATLAB indeed gave us back the original problem. We tried the samereconstruction with results from the Jenkins code, varying the tolerance used to judge "small" numbers.In the case of the simple-root problem (data of Figure 14.2.1a) the deviations decreased as the tolerancewas decreased. For the multiple-root problem, however, Figure 14.5.4 shows that the solutions vary inquality as the tolerance for zero is made smaller.
For both problems, a very small tolerance caused afailure flag to be set. The behaviour of results using three different compilers (Lahey F77L 5.01a,Microsoft Professional Fortran 5, Watcom Fortran 9) were similar but far from identical.Figure 14.5.5a shows the very simple steps to use the SOLVE capability of the symbolic mathematicspackage DERIVE. We get just three reported roots (DERIVE makes no attempt to assess multiplicity). Inthis case we can SOLVE.
This did not give a solution for the more straightforward problem of Figure14.2.1a, though we note that a good approximation to roots can be found by plotting NPV(r), sinceDERIVE’s graphs have a user-controllable cross-hair for which the position is displayed as well as aZOOM facility that lets us locate a root as accurately as we like. Alternatively, as shown in Figure 14.5.5b,we can set up a Newton iteration in DERIVE, but must remember to set the arithmetic as approximateto get numerical answers. If we leave arithmetic as exact, the iteration fills memory as it takes a great dealof time to "compute" roots.14: EXAMPLE: INTERNAL RATE OF RETURN PROBLEM125Figure 14.5.2Graphical solution of the problem of Figure 14.5.1.Figure 14.5.3Graphical solution of the problem of Figure 14.2.1b.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
