PII: S1057-0810(00)00045-7 Cash flow: a quick and easy way to learn personal finance Ronald R. Crabb* Finance and Business Law Departments, University of Wisconsin-Whitewater, 5002 Carlson Hall, Whitewater, WI 53190-1797, USA Abstract A cash flow spreadsheet methodology simplifies solving a number of common personal finance problems. At schools where a basic finance course is not a prerequisite for the personal financial planning course, this methodology makes learning personal finance skills and concepts quick and easy. It reinforces time value of money mathematics when students have had a prerequisite finance course. Creating the cash flow spreadsheets, editing them for errors, and testing the finished spreadsheets for logical consistency imprints in minds of most students the skills and concepts you are trying to teach. © 1999 Elsevier Science Inc. All rights reserved. JEL classification:C6; C63 Keywords:Goal Seek; Solver; Cash Flow Spreadsheet 1. Introduction Students understand cash flow. They work hard in the summer, storing up cash for the school year. Many of them live at home during the summer to avoid paying rent and to avoid buying food. When the school year starts, they begin to hemorrhage cash. Many of them go to the financial aid office to borrow $2,500 at a zero rate of interest until six months after they graduate. A few of those borrowers do not really need the cash, but they do understand that it makes sense to borrow money if the U.S. Government is paying the interest while they attend school and also for the first six months after they graduate. Students understand cash flow. * Tel.: 11-262-472-1326; fax:11-262-472-4863. E-mail address:crabbr@uwwvax.uww.edu (R.R. Crabb). Financial Services Review 8 (1999) 269–282 1057-0810/99/$ – see front matter © 1999 Elsevier Science Inc. All rights reserved. PII: S1057-0810(00)00045-7 Hence, the easiest way to teach students how personal finance math works is to show them the cash flows that underlie the financial transactions that they currently are (or will be) entering into. Students borrow money to go to college. They begin to experiment with credit cards. They will be buying houses. They will be saving for retirement. How much should they save? How big will the mortgage payment be? How expensive are credit cards? How big will the Stafford Loan payment be six months after graduation? 2. The student loan Consider a student who takes out a Stafford Loan. The student borrows money by signing a note for $2500, but the student only receives $2400. A $100 origination fee, 4% of the loan amount, covers up-front expenses and provides insurance to repay the loan if the student should die before the loan has been repaid. Assume that the interest rate on the loan is 7.5%, the current Stafford Loan rate, during the first two years of the repayment period, and then jumps to 8.25%, the maximum Stanford Loan rate, during the remaining eight years of the repayment period. To minimize the size of the tables in this article, assume that payments are made on a quarterly basis; in reality, payments would be made monthly. If the student borrows $10,000 over a four-year period, how large will her or his payment be when she or he begins to repay the loan? The cash flow is easy to set up on a spreadsheet: there are only a few simple equations. See Table 1 for the results of those equations. The next few paragraphs detail the classroom procedure that I use to create a Stafford Loan Excel 97 cash flow spreadsheet. This is a mental exercise, not a mechanical one. The learning and understanding does not come from simply typing a few equations and looking at the finished cash flow picture. I require my Table 1 An Excel spreadsheet for a student loana A B C D E 1 Time period Loan amount (BOQ) Interest expense Loan payment Loan amount (EOQ) 2 1 $10,000.00 $187.50 $300.00 $9,887.50 3 2 $ 9,887.50 $185.39 $300.00 $9,772.89 4 3 $ 9,772.89 $183.24 $300.00 $9,656.13 5 4 $ 9,656.13 $181.05 $300.00 $9,537.18 6 4 $ 9,537.18 $178.82 $300.00 $9,416.01 7 6 $ 9,416.01 $176.55 $300.00 $9,292.56 8 7 $ 9,292.56 $174.24 $300.00 $9,166.79 9 8 $ 9,166.79 $171.88 $300.00 $9,038.67 10 9 $ 9,038.67 $186.42 $300.00 $8,925.09 11 10 $ 8,925.09 $184.08 $300.00 $8,809.17 40 39 $ 4,385.68 $ 90.45 $300.00 $4,176.13 41 40 $ 4,176.13 $ 86.13 $300.00 $3,962.27 a BOQ, Beginning of quarter; EOQ, End of quarter. 270 R.R. Crabb / Financial Services Review 8 (1999) 269–282 students to think about what formula or number belongs in each cell. Beginning with a blank Excel spreadsheet, I type the labels shown in Table 1 and the 1 shown in cell A2. I then ask my students “How much did the student borrow?” Usually about half of the class says simultaneously “$10,000,” and I type a 10000 into cell B2. I then ask for a formula to compute the interest expense. Almost always, some student says “50.075*B2,” and I type that formula into cell C2. Rather than saying “It’s wrong.” I say: “It’s almost right. How does the equation need to be modified to compute thequarterly interest expense?” Given the hint, the same student, or a different student, says “50.075/4*B2,” and I edit the formula in cell C2. After explaining that cell D2 needs a number which is bigger than the interest expense shown in cell C2, else the ending loan balance will be bigger than the beginning loan balance, I enter a $300.00 into cell D2. The $300.00 in cell D2 is an arbitrary entry. It is not the loan payment amount; it will need to be adjusted (by Goal Seek in an Excel 97 environment or Back Solver in a Lotus 1–2-3 environment) when the spreadsheet equations underlying the cash flow are finished. I now ask for a volunteer to give me an equation to compute the loan balance at the end of the first quarter. I am indifferent as to whether the equation volunteered is correct or incorrect. If correct, I go on to row two. If incorrect, I say: “It’s almost right. How does the equation need to be modified to compute the ending loan balance?” After the formula 5B21C2-D2 is entered into cell E2, I move to the next row. I ask my students for a formula to compute the time period, and, after getting a correct answer or after editing an incorrect answer, enter5A211 into cell A3. I ask my students for a formula for the beginning loan balance at the beginning of the second quarter, and, after getting a correct answer or editing an incorrect answer, enter5E2 into cell B3. I click on cell C2, and I ask my students if the formula in cell C2 is appropriate for cell C3. After getting the answer “Yes.” I copy the C2 formula into cell C3. In cell D3 I type5D2. I click on cell E2, and I ask my students if the formula in cell E2 is appropriate for cell E3. After getting the answer “Yes.” I copy the cell E2 formula into cell E3. I then click on cells A3 through E3, and copy the formulae down through row 10. I click on cell C10, and ask my students what change needs to made to this formula. After a student replies that the 0.075 needs to be changed to 0.0825 because the interest rate changes from 7.5% to 8.25% at the beginning of the third year of the loan, I edit cell C10 to50.0825/ 4*B10. I click on cells A10 through E10, and copy the formulae down through row 41. Finally, I click on cells B2 through E41, and format to dollars by clicking the $ icon on the Toolbar. The process of requiring your students to think about what equation or number is going into each cell forces them to become active learners. Since the balance of the loan after 40 payments is not zero, I ask someone in the class to volunteer a new number for the loan payment amount. When a student says “$350.00,” I type 350 in cell D2, and the loan balance is now $906.37. After entering 375 into cell D2, the loan balance is ($621.59). A 365 entry results in a loan balance of ($10.41). It doesn’t take too long to get the loan balance close to zero; however, the closest a trial-and-error solution can come is minus one cent. The inability to get an exact zero answer allows for a discussion of how loans are actually amortized in the real world. To amortize his/her loan, the Stafford Loan borrower makes 39 equal payments of $364.83 and one final payment of $364.82. While a trial-and-error solution is certainly possible,1 it is easier and quicker to set the cursor on cell E41, go up to the Toolbar and click on Tools, and then click on Goal Seek. See Fig. 1. 271R.R. Crabb / Financial Services Review 8 (1999) 269–282 The Goal Seek tool requires three input values to solve a problem: a target cell (cell E41, the ending loan balance), a value for the target cell, and the cell address that you want to change to cause the target cell to take on the desired value. In theTo value: box, enter a zero. In the By changing cell: box, enter cell D2. Click on the OK button, and the Goal Seek algorithm will iterate towards a solution. The Goal Seek algorithm will try different values for the loan payment amount to find the loan payment amount that causes the loan balance, after 40 loan payments, to compute to a zero value. See Table 2. The cash flow Goal Seek methodology enhances learning because the student can literally see (on the monitor or on a printed hard copy) exactly how the cash flows from him/herself to the lender (or how a retirement fund is accumulated and liquidated). The student can see the interest expense (or earnings); the student can see the loan balance declining to zero over the life of the loan (or the retirement fund balance maximizing at retirement before declining to zero at the end of the retirement period). The process of writing the equations, targeting the ending loan balance to zero, and using first trial-and-error and then Goal Seek allows most students to quickly conceptualize what, for many, have been difficult concepts to comprehend. This is actually much easier to do in real time in a classroom than it is to explain in words. Working live in a classroom with a computer overhead display and a blank Excel spread- sheet, this problem can be set up and solved in about three minutes. If you have access to a computer lab classroom, then your students can work with you. Early in the semester the time will increase to about ten minutes because your students with weak spreadsheet skills will Fig. 1. Setting up Goal Seek to solve for the loan payment. 272 R.R. Crabb / Financial Services Review 8 (1999) 269–282 make typos, formulae errors, and copy-down errors. Over the course of the semester most of your students will become proficient spreadsheet users as they master personal finance problem solving skills and learn personal finance concepts. Whether you work with your Fig. 2. The Constant Dollar spreadsheet and Solver parameters. Table 2 The Goal Seek solution to the student loan problem A B C D E F G 1 Time period Loan amount (BOQ) Interest expense Loan payment Loan amount (EOQ) PVIFs 2 1 $10,000.00 $187.50 $364.83 $9,822.67 0.9816 35 34 $ 2,355.51 $ 48.58 $364.83 $2,039.27 0.5069 36 35 $ 2,039.27 $ 42.06 $364.83 $1,716.50 0.4967 37 36 $ 1,716.50 $ 35.40 $364.83 $1,387.07 0.4866 38 37 $ 1,387.07 $ 28.61 $364.83 $1,050.85 0.4768 39 38 $ 1,050.85 $ 21.67 $364.83 $ 707.69 0.4672 40 39 $ 707.69 $ 14.60 $364.83 $ 357.46 0.4577 41 40 $ 357.46 $ 7.37 $364.83 $ 2 0.4485 42 41 PVIFA is 5 27.4100 43 42 Payment is 5 $364.83 273R.R. Crabb / Financial Services Review 8 (1999) 269–282 students and they get instant feedback in a lab or delayed feedback when working on their own after class, most students find the cash flow Goal Seek methodology easy to understand. A number of my brighter students have told me that “This is like cheating. We’re not using the interest factors we learned in basic finance.” While the methodology is so simple that it may seem like cheating, it is just a different way of solving the loan amortization problem. The simplicity of the cash flow methodology coupled with the process of writing the cash flow equations, targeting the ending loan balance to zero, solving the problem with Goal Seek, and examining the spreadsheet solution for logical consistency (no equations errors et al.) imprints the amortization process in the minds of most students. The real issue is understanding, not methodology, not machinery. From an instructional standpoint, the important questions are: 1. Do students understand the loan amortization process; 2. Can students determine the size of a loan payment; and 3. Can these concepts quickly and easily become integral parts of the student’s knowledge base? For most students, the cash flow Goal Seek methodology enables students to answer “Yes.” to the above three questions. The cash flow Goal Seek methodology has four major advantages over the two traditional approaches (using a financial calculator or using PVIFA tables and a simple calculator) to finding loan payments amounts. First, the entire process of creating the cash flow spreadsheet allows students to visualize and understand exactly how the loan amortization process works. Second, the process of checking the spreadsheet for errors forces students to think about the relationships that must exist in a properly created amortization table. Is the loan balance continuously decreasing? Is the loan balance at the beginning of one time period equal to the ending loan balance of the prior period? Is the loan payment amount constant (except for the last payment if using answers computed to the nearest penny vis-a`-vis using 15 digit Goal Seek answers)? Does the interest expense continuously decrease as the loan payments are made? Is the final loan balance equal to zero? Does the sum of the loan payments, minus the sum of the interest expense, compute to the initial loan amount? Third, financial calculators or simple calculators and PVIFA tables do little to foster a conceptual understanding of the loan amortization process. Input some numbers; an answer appears on the display screen. The purely mechanical process of computing an answer using a hand-held calculator does not foster an understanding of the loan amortization process. Fourth, not only does the financial calculator not provide any insight as to how the loan amortization process works, the “black box” answer is, for most real world loan problems, not checkable. It simply is not feasible to create amortization tables using hand calculators and paper and pencil for most real world loans that have from 60 to 360 monthly payments. The cash flow methodology allows students to simultaneously find the solution to the loan problem and to see exactly how the loan balance decreases to zero over the 40 quarter time period. Students need to look at the cash flow columns to make sure that an equation error has not gotten into the cash flow algorithm. When students are examining their spreadsheets for errors, they are thinking; when they find errors, they are learning; after they have fixed 274 R.R. Crabb / Financial Services Review 8 (1999) 269–282 their errors, they have learned exactly how the amortization process works. In a learning context, this methodology creates an active learning situation. Students become comfortable with loan payment computation and loan amortization. Depending upon your needs and your students’ backgrounds and their needs (e.g., have they already had a basic finance course? Is this their first course in finance? Will they be taking more courses in finance using this course as a prerequisite?), it is easy to integrate the basic finance course into your financial planning course or vice versa. With the addition of a single column to the spreadsheet shown in Table 2, you can show your students the traditional PVIFA approach to solving this problem while also showing them a new way to compute PVIFA factors. 3. The PVIFA approach for a student loan In cell G2 of the spreadsheet shown in Table 2 enter51/(110.075/4). In cell G3 enter 5G2/(110.075/4). Click on cell G3, and copy down through row 10. Edit cell G10 to be 5G9/(110.0825/4) to reflect the change in the interest rate. Click on cell G10 and copy down through row 41. Set the cursor in cell G42. Go up to the Toolbar and double click the summation sign to sum up cells G2 through G41. The resulting sum, 27.41, is the present value annuity factor (PVIFA) for this loan problem. In cell G43 divide $10,000 by that annuity factor. The quotient, $364.83, computes the exact same payment as determined via the cash flow methodology. Students who have had a basic finance class learn a new way to compute annuity factors, and their memories of how to solve loan amortization problems are refreshed; students who have not had a finance class can see how annuity factors are built and used.2 I reinforce student learning by using the cash flow Goal Seek methodology to solve credit card and mortgage loan problems. Following that, students have a reasonably good understanding of the loan amortization process and are ready to move to the next topic: retirement funding. 4. Cash flow retirement planning Perhaps some of the nastiest mathematics that students can encounter in a financial planning course is the math that underlies retirement planning. Rich Fortin (1997), the author of “Retirement Planning Mathematics” (hereinafter referred to as RPM), used 17 equations in the body of his article and another 11 equations in the Appendix to show the present value/future value equations which need to be solved to determine how much to save for retirement. He demonstrated that the retirement savings approach which people might be most likely to use did not have a closed form solution. Like loan payment computation, a cash flow methodology greatly simplifies retirement planning mathematics. To save time and space, and to allow for a comparison between a standard present value/future value approach to retirement mathematics and a cash flow approach, this article will assume the same case facts as the RPM example. Those facts follow. Assume an age 25 student has just graduated from college. She expects to earn $25,000 in her first year of employment. She expects to work for 30 years, and she expects to be retired for 25 years. She 275R.R. Crabb / Financial Services Review 8 (1999) 269–282 expects an inflation rate of 3.2% in both her salary and in prices, and she wants a constant dollar annuity equal to one half of her final salary. She assumes that she will earn an 8% nominal interest rate on her investments, and she makes all of her computations on an end-of-the-year basis. She considers three approaches to funding her retirement. She could save a constant dollar amount every year, she could save a constant percentage of her salary each year, or she could save some initial percentage of her salary, and then increase that percentage each year.3 Because the nature of retirement planning is highly “What if?” based (What if she actually earns 12%? What if she only earns 4%?), an alternate Excel Tool, Solver, is used in place of Goal Seek. Solver uses a more complex trial and error algorithm; as a result, Solver takes more computer time to get an answer the first time it is used. However, this one-time cost is outweighed when testing multiple interest rates because Solver remembers the target cell, remembers the zero value for that target cell, and remembers the cell that needs to be changed to cause the target cell to compute to a zero value. Once the Solver algorithm has been defined for a particular problem, it allows the student to quickly do “What if?” analysis. Create a cash flow spreadsheet that shows the accumulation and liquidation of a retirement fund under the Constant Dollar approach. Once the formulae are written (see the Appendix for the equation details), bring up Solver to compute the amount of money that needs to be saved on an annual basis. Like Goal Seek, Solver requires that you specify three parameters: the target cell, the value for the target cell, and the cell whose value you want to change to make the target cell compute to the value you want that cell to have. In this case the target cell is the retirement fund balance at the end of the retirement period. Hence, have the cursor in cell E56 when you bring up Solver. In Fig. 2, the second line of the Solver box shows three possible options for the target cell:Max, Min, Value of . Set the pointer in the little circle to the left of the wordsValue of, and click the mouse button. We want the target cell, E56, to compute to zero, and, since the Solver default value is zero, this parameter is now correctly specified. Enter an I2 into theBy Changing Cell box. Click the Solve button in the Solver box, then click the OK button when the “Solver has found a solution” box appears on the monitor. The solution to this problem is shown in Table 3. The resulting spreadsheet allows your students to see what happens to the retirement fund of the hypothetical age 25 student as she accumulates capital for her retirement and then liquidates that capital during her retirement. As a byproduct of solving the retirement funding problem, the size of the retirement gap4 shows on the spreadsheet in cell E31, the year in which our hypothetical student retires. Again, this is much easier to do in real time in a classroom than it is to explain in words. From start to finish on a blank Excel spreadsheet, solving the Constant Dollar problem takes about ten minutes. As before, the student needs to examine his/her spreadsheet for logical consistency. Is the retirement fund at the beginning of one time period equal to the to retirement fund at the end of the previous period? Do the interest earnings increase continuously through the 30 year accumulation period, and continue to increase during the liquidation period, although at a slower rate, until the annuity payments become larger than the interest earned? Is the salary of the hypothetical student increasing continuously from when she begins work until when she retires? Does the sum of the interest earned over the entire accumulation and liquidation 276 R.R. Crabb / Financial Services Review 8 (1999) 269–282 period plus the sum of the dollars invested during the accumulation period equal the sum of the retirement annuity payments paid out during the liquidation period? Is the ending retirement fund at the end of the liquidation period equal to zero? The process of examining the spreadsheet for logical or mathematical errors, and fixing those errors, again creates an active learning environment that helps students conceptualize the retirement funding process. Modifying the Table 3 spreadsheet to solve the Constant Percentage problem and to solve the Constant Growth of an Initial Percentage problem is straightforward. Copy the entire spreadsheet shown in Table 3 and paste it into two blank spreadsheets. On each sheet, edit a few cells, copy down the new formulae, and bring up Solver to solve the problem. In about five minutes you can solve both problems. See the Appendix for the equation modifications needed to create these two spreadsheets. Before going to the Appendix, however, try to modify the equations yourself to see if you can create these spreadsheets. Irrespective of how our hypothetical student chooses to fund her retirement, she needs $469,526 in her retirement fund when she retires, assuming an 8% nominal interest rate. All types of sensitivity analysis are now possible for all three approaches. The simplest, changing the nominal interest rate, takes about 20 seconds per each interest rate. Type in the new rate, bring up Solver, and push the Solve button. Table 4, in the three columns that begin with Year one, shows the required fund at retirement (which is also the retirement gap) for interest rates from 4% through 12%. What if the student choosesnot to begin saving for her retirement in her first year of employment? What if she puts off saving for retirement until her sixth year of employment? On each spreadsheet, zero out (that is, type in a zero) cell E6, the Retirement Fund at the end Table 3 The Constant Dollar solution A B C D E F G H I 1 Year Retirement Fund (beginning of the year) Interest earned Dollars invested (or) dollars paid out Retirement Fund (end of the year) Nominal investment interest rate Salary (& price) inflation rate End-of- year salary Constant Dollar payment amount 2 1 $ 2 $ 2 $ 4,145 $ 4,145 8.0% 3.2% $25,800 $4,145 3 2 $ 4,145 $ 332 $ 4,145 $ 8,621 $26,626 6 5 $ 18,677 $ 1,494 $ 4,145 $ 24,315 $29,264 11 10 $ 51,757 $ 4,141 $ 4,145 $ 60,043 $34,256 16 15 $100,364 $ 8,029 $ 4,145 $112,538 $40,099 21 20 $171,783 $13,743 $ 4,145 $189,670 $46,939 26 25 $276,720 $22,138 $ 4,145 $303,003 $54,946 31 30 $430,908 $34,473 $ 4,145 $469,526 Retirement Gap $64,318 32 1 $469,526 $37,562 $(33,188) $473,900 33 2 $473,900 $37,912 $(34,250) $477,562 36 5 $482,378 $38,590 $(37,644) $483,323 41 10 $474,470 $37,958 $(44,065) $468,362 46 15 $422,886 $33,831 $(51,582) $405,135 51 20 $300,310 $24,025 $(60,380) $263,955 56 25 $ 65,444 $ 5,236 $(70,679) $ (0) 277R.R. Crabb / Financial Services Review 8 (1999) 269–282 of year five. On the Constant Growth of an Initial Percentage spreadsheet, one additional edit needs to be made. Since the initial percentage saved by our hypothetical student was assumed to be 5%, cell J7 needs to redefined as 5%. Bring up Solver, and push the OK button. While numbers will appear on the monitor in accumulation years 1 through 5, Solver disregards these numbers because of the $0 entry into cell E6. Table 4, in the three columns beginning with the words Year six, shows the effect of waiting until the end of six years before beginning to save for retirement for all three approaches. A more interesting “What if?” analysis, feasibility analysis, can be done on the Constant Growth spreadsheet. Column J shows the percentage of salary saved each year. This column lists what percentage of her salary the hypothetical student must save each year, given the nominal rate of return. If the student chooses to invest conservatively at 4%, by the time she gets to age 65, she needs to save 113% of her salary. This is not feasible. Even if she chooses to invest a bit more aggressively, at 6%, she still needs to save 63% of her salary in her last year of employment. Again, this is not feasible. Sensitivity and feasibility analyses allow students to quickly grasp important retirement concepts. Again, the real issue is understanding, not methodology, not machinery. From an instruc- tional standpoint, the important questions are 1. Do students know how to compute the savings amount required to fund a person’s retirement; 2. Do they understand the accumulation and liquidation process inherent in retirement funding; 3. Can they compute the size of the retirement gap; 4. Do they understand the need to begin saving for their retirements early in their careers; 5. Do they understand the need to invest aggressively when they are young (or, alternately stated, understand the long-run cost of investing conservatively when they get old); and, Table 4 Sensitivity analysis and funding amounts (in dollars or percentages) Nominal rate Real rate Retirement Fund(gap) Year one Cash Flow Constant Dollar Year six Cash Flow Constant Dollar Year one Cash Flow Constant Percentage Year six Cash Flow Constant Percentage Year one Cash Flow Constant growtha Year six Cash Flow Constant Growthb 4% 0.78% $728,311 $12,986 $17,488 33.7% 41.2% 11.4% 15.1% 5% 1.74% $647,123 $ 9,740 $13,559 25.8% 32.5% 10.3% 13.9% 6% 2.71% $578,313 $ 7,315 $10,541 19.8% 25.6% 9.1% 12.7% 7% 3.68% $519,700 $ 5,502 $ 8,217 15.2% 20.2% 7.9% 11.4% 8% 4.65% $469,526 $ 4,145 $ 6,423 11.7% 16.0% 6.5% 10.1% 9% 5.62% $426,364 $ 3,128 $ 5,034 9.0% 12.7% 4.9% 8.6% 10% 6.59% $389,056 $ 2,365 $ 3,956 6.9% 10.1% 3.0% 7.0% 11% 7.56% $356,653 $ 1,792 $ 3,117 5.3% 8.1% 0.6% 5.1% 12% 8.53% $328,379 $ 1,361 $ 2,463 4.1% 6.5% 22.4% 3.0% a Assumes savings 5% of her end-of-year One salary. b Assumes savings 5% of her end-of-year Six salary. 278 R.R. Crabb / Financial Services Review 8 (1999) 269–282 6. Can these concepts quickly and easily become integral parts of the student’s knowledge base? For most students, the cash flow Solver methodology produces “Yes” answers to most of the above questions. 5. Evaluating alternative investments Consider an individual investor who needs to choose between two possible investments. One costs $10,000, and the investor expects that s/he should double her/his investment in nine years. The other investment also costs $10,000, and the investor expects to triple her/his investment in 14 years. Which investment offers the investor the highest rate of return? The cash flow methodology spreadsheet solution to this problem is shown in Table 5. The $10,000 investment is set up to grow an arbitrary interest rate. Use Goal Seek to target the EOY (End Of Year) Table 5 Cash flow investment spreadsheet, IRR computationa A B C D E F G H I J K 1 8.0% 8.2% 2 Year Investment (BOY) Implicit interest Investment (EOY) Year Investment (BOY) Implicit interest Investment (EOY) ($10,000) 3 1 $10,000 $ 801 $10,801 1 $10,000 $ 816 $10,816 $0 4 2 $10,801 $ 865 $11,665 2 $10,816 $ 883 $11,699 $0 5 3 $11,665 $ 934 $12,599 3 $11,699 $ 955 $12,654 $0 6 4 $12,599 $1,009 $13,608 4 $12,654 $1,033 $13,687 $0 7 5 $13,608 $1,089 $14,697 5 $13,687 $1,117 $14,805 $0 8 6 $14,697 $1,177 $15,874 6 $14,805 $1,209 $16,013 $0 9 7 $15,874 $1,271 $17,145 7 $16,013 $1,307 $17,321 $0 10 8 $17,145 $1,373 $18,517 8 $17,321 $1,414 $18,734 $0 11 9 $18,517 $1,483 $20,000 9 $18,734 $1,529 $20,264 $0 12 10 $20,264 $1,654 $21,918 $0 13 11 $21,918 $1,789 $23,707 $0 14 12 $23,707 $1,935 $25,643 $0 15 13 $25,643 $2,093 $27,736 $0 16 8.5% 14 $27,736 $2,264 $30,000 $30,000 17 Year Investment (BOY) Implicit Interest Investment (EOY) IRR 5 8.2% 18 1 $10,000 $ 849 $10,849 19 2 $10,849 $ 921 $11,769 20 3 $11,769 $ 999 $12,768 21 4 $12,768 $1,083 $13,851 22 5 $13,851 $1,175 $15,027 23 6 $15,027 $1,275 $16,302 24 7 $16,302 $1,383 $17,685 25 8 $17,685 $1,501 $19,186 26 8.5 $19,186 $ 814 $20,000 a Target cells and variable cells are bold. BOY, Beginning of year; EOY, End of year. 279R.R. Crabb / Financial Services Review 8 (1999) 269–282 investment value to $20,000 at the end of the 9th year for the first investment and $30,000 at the end of the 14th year for the second investment. That is, in theTo Valuebox, type 20000 or 30000. The variable cell is the interest rate. The 14 year investment offers the highest expected rate of return, 8.2%, versus 8.0% for the nine year investment. Like many investment choices facing individual investors, this solution is quite sensitive to small changes in time. If the nine year investment horizon were just one half a year less, that is, the investor expected to double her/his money in eight and one-half years rather than nine years, then that investment would yield 8.5%, and it would be preferred over the 14 year investment. Students owning financial calculators can input $10,000 as the present value, $20,000 (or $30,000) as the future value, nine (or 14) as the time period, and ask the calculator to compute the interest rate that solves each problem. If your students have had a prerequisite finance course, then you can solve this investment problem using the IRR function, again integrating the finance course into your personal finance course. Set up the string of numbers shown in column K, cells 2 through 16, and type 5IRR(K2:K16) in cell K17. After formatting cell K17 to one decimal point, the IRR for the 14 year investment is 8.2%. Using an FVIF table, your students would drop down the period column to 14, go horizontally across the table looking for a 3 in the 14th period row. Your students would find a 2.937 in the 8% column and a 3.342 in the 9% column, and could interpolate 8.2% as the answer. 6. Conclusions There is no need for a prerequisite basic finance course for personal financial planning when using this methodology. By appealing directly to life experiences which students have had or will have, this methodology engages students’ interest in learning. The information is structured such that it can be readily grasped. Avoiding present and future value math (and later showing how that math works), providing visual numerical pictures of the entire process, and requiring students to write, edit, and fix the equations underlying the amorti- zation process allows those with little experience to simultaneously learn financial planning math skills and financial planning concepts. Many personal finance problems involve cash flow, and any problem that involves cash flow is solvable by this methodology. While simplifying the math makes a cash flow methodology both easy-to-use and useful, especially for the retirement problem that does not have a closed form solution, perhaps its greatest benefit lies in its requiring students to understand exactly what is happening when they take out loans, plan their retirements, and choose between alternative investments. Notes 1. To do this by trial and error, set cell F25E41, making it possible to see the final loan balance and the loan payment simultaneously. Since a $300 loan payment results in a positive loan balance, try a number larger than $300. Whether you type $350 or 280 R.R. Crabb / Financial Services Review 8 (1999) 269–282 $400 or $500 or even $1000 does not matter. A visual trial-and-error process is actually rather efficient. It usually takes less than a minute to find a loan payment amount that will get the ending loan balance to1/- a few cents. It has instructional value to demonstrate that the Goal Seek algorithm is nothing more than a trial-and- error algorithm. It is not a “black box”; there is nothing mysterious going on inside of the computer as Goal Seek iterates to a value of 364.829755293873 for the loan payment amount. 2. Because many students do not really understand the annuity concept when they enter a financial planning class, either because they have not had a basic finance class or the concept failed to gel when they took a basic finance class, another useful exercise at this point in the classroom is to show them what the annuity factor 27.41 means. In cell J2, enter5G42. Enter50.075/4*J2 into cell K2. Enter 1 into cell L2. Enter 5J21K2-L2 into cell M2. Enter5M2 into cell J3. Copy-down the formula from K2 into cell K3. Set cell L35L2. Copy-down the formula from cell M2 into cell M3. Click on cells J3 through M3. Copy down through row 10. Edit the interest formula in cell K10 to 50.0825/4*J10. Click on cells J10 through M10, and copy their formulae down through row 41. In cell M41, the student will see a $0.00. You invest $27.41 in the bank. The bank pays you interest of 7.5% annually (compounded quarterly) for two years, then 8.25% annually (compounded quarterly) for eight years. At the end of each quarter, you withdraw one dollar. At the end of 40 quarters, your bank account has a zero balance. The present value of a $1.00, payable at the end of each quarter for 40 quarters, given the above interest rates, is $27.41. By looking at how the cash flows through a bank over a ten-year period, your students can see exactly how annuities work. Note that you could put any number into cell J2, target cell M41 to a zero value, and then use Goal Seek to find the initial sum of money, $27.41, needed to provide a dollar per quarter for 40 quarters. 3. These three approaches (Constant Dollar, Constant Percentage, and Constant Growth of an Initial Percentage) are the three approaches that the major mutual fund providers put on their Web sites for use by their customers to answer the question “How much should I save to provide for my retirement?” The RPM article explored the mathe- matics underlying those three approaches. 4. When our hypothetical student retires at age 55, to provide a constant dollar annuity equal to one-half of her final salary for 25 years, she needs $469,526, assuming an 8% nominal interest rate. In the same sense that $27.41 was the amount of money needed to provide a $1.00 per quarter for 40 quarters, $469,526 is the amount of money needed to provide an initial annuity of $33,188 in her first year of retirement and $70,679 in her last year of retirement. By assumption, she has not yet accumulated any funds for retirement when she begins working. Since $469,526 (her future retirement fund) minus $0 (her current retirement fund) is equal to $469,526, the cash flow methodology computes the retirement gap as a byproduct of solving the retire- ment funding problem. Alternately, $469,526 is the present value of the 25 retirement annuity payments, computed on an ordinary annuity basis. 281R.R. Crabb / Financial Services Review 8 (1999) 269–282 Acknowledgment The author wishes to thank two anonymous reviewers. Without their input, this paper would not exist. His thanks are also extended to his secretary and his wife for reading all drafts of this paper, checking cell addresses for accuracy and grammar for consistency. Appendix A.1. Equation instructions for the constant dollar spreadsheet shown in Fig. 2. After entering the labels in cells A1 through I1, a 1 is entered in cell A2, a 0 in cell B2, 5$F$2*B2 in cell C2,5$I$2 in cell D2,5SUM(B2:D2) in cell E2, 8% in cell F2, 3.2% in cell G2, 525000*(11$G$2) in cell H2, and $2400 in cell I2. As before (the loan and mortgage cash flow spreadsheets), some cells in row 3 are different from those in row 2. Cell A3 is 5A211; cell B3 5 E2. Click on cells C2, D2, and E2, and copy their formulae into cells C3, D3, and E3. Cell H3 is5H2*(11$G$2). Click on cells A3 through H3, and copy down their formulae through row 56. Because she stops working at age 55, clear the contents of cells H32:H56. Enter a 1 incell A32 to define the first year of retirement. In D32, enter5 20.5*H31*(11$G$2). In cell D33, enter5 D32*(11$G$2); then copy this formula down through cell D56. Format to dollars without any cents. A.2. Equation editing instructions for the Constant Percentage Spreadsheet To edit the Constant Percentage spreadsheet, change the label in cell I1 to “Constant Percentage.” Reformat cell I2 to a percentage format by typing an arbitrary 10.00% in cell I2. Change the formula in cell D2 to5H2*$I$2. Click on cell D2, and copy this formula down through row 31 (or working year 30). Bring up Solver with E56 as the target cell, target cell E56 to zero, type in I2 as the variable cell, and then click on the Solve button. A.3. Equation editing instructions for the Constant Growth in an Initial Percent Spreadsheet To edit the Constant Growth of an Initial Percentage spreadsheet, change the label in cell I1 to “Constant Growth Rate.” Reformat cell I2 to a percentage format by typing an arbitrary 10.00% in cell I2. Type the label “Percent of Salary Saved” in J1 and type 5.00% in J2. Cell D2 5H2*J2. Click on cell D2, and copy down through cell D31. Cell J35J2*(11$I$2). Click on cell J3, format to percent with two decimals, and then copy down through cell J31. Bring up Solver with E56 as the target cell, target cell E56 to zero, type in I2 as the variable cell, and then click on the Solve button. Reference Fortin, R. (1997). Retirement planning mathematics.Journal of Financial Education, 23,73–80. 282 R.R. Crabb / Financial Services Review 8 (1999) 269–282