CHAPTER 8 FINANCIAL CALCULATOR Would you like to easily solve personal finance and real estate problems that make some business tycoons frown in confusion? With this book and EZ-$-PLAN software it's a snap. You simply decide which of several classes of problems you're interested in, call-up the spreadsheet and the corresponding template for the class of problem to be solved, enter the data and the spreadsheet displays the result. Classes of problems discussed in this chapter include: compound interest and annuity types. COMPOUND INTEREST Compound interest problems involve four parameters: (1) Present value of a deposit or investment (pv), (2) Interest rate of the investment per compounding period in percent (i), (3) Number of compounding periods during which investment appreciates (n) and (4) Future value of deposit or investment (fv). We assume that we have either a single deposit or that the problem can be broken down into a series of single deposits. EZ-$-PLAN'S powerful COMPINT2 program allows you to solve for any of the four parameters if you know the other three. Let us consider several examples of financial problems involving compound interest. Future Value of Investment Suppose $1000 is deposited in a savings bank at 6% interest compounded annually. What will be the value of the account after ten years? This information defines three of the parameters: present value (pv)=$1000, number of compounding periods (n)=10 and periodic interest rate (i)=6%. We will first identify the sequence of steps to solve the problem for the future value (fv) and then discuss the process. The following steps solve for the future value (fv) of the deposit, see table 5.1: (1) Load the spreadsheet AS-EASY-AS: boot-up your PC; change directory: Enter C:CD\ASEASY Ret.; Enter ASEASY/P/CTR Ret. (2) Load COMPINT2 file: Enter /FR Ret.; move cursor to COMPINT2 file and Ret. (3) Move the cursor to the location of the input data parameters (i.e., C7:C10), enter the data given for the three parameters: pv=1,000, n=10 and i=6 and enter fv=0 for the parameter to be determined. The result for the future value, fv=1,790.85, is found in cell C18. For step (1) a description of how to load the AS-EASY-AS spreadsheet into random access memory if found in the Quick Start 33 Chapter Eight Table 8.1. Future Value A C E G 2 Compound Interest (COMPINT2) 3 Section 1: Input Data 4 ----------------------------------------------------------------- 5 PARAMETERS VALUE VALUE VALUE 6 ----------------------------------------------------------------- 7 Present value (pv) 1,000 1,000 1,000 8 Future value (fv) 0 0 0 9 Number of compounding periods (n) 10 10 10 10 Periodic interest rate in percent (i) 6 7 8 11 ----------------------------------------------------------------- 12 13 Section 2: Output Data 14 ----------------------------------------------------------------- 15 PARAMETERS VALUE VALUE VALUE 16 ----------------------------------------------------------------- 17 Present Value (pv) 1,000 1,000 1,000 18 Future Value (fv) 1,790.85 1,967.15 2,158.92 19 Number of compounding periods (n) 10 10 10 20 Periodic interest rate in percent (i) 6.000 7.000 8.000 Appendix. Step (2) loads the COMPINT2 file from hard disk to RAM within AS-EASY-AS. In step (3) move the cursor to cell locations C7:C10 and enter pv=1000 in cell C7, fv=0 in C8, n=10 in C9 and i=6 in cell C10. The COMPINT2 program solves for the parameter for which a zero is entered, future value in this example. In the output section, i.e., C17:C20, the input values are repeated and the future value result, fv = 1,790.85, appears in cell C18. Table 8.1 shows the format of the COMPINT2 template, and columns A and C refer to this example. Section 1, A3:G10, shows the input data fields and Section 2, A13:G20, shows the output data fields. Three separate problems can be run simultaneously in columns C, E and G. You enter values for three parameters in the input data field and a zero for the parameter to be determined. The answer along with repeated input values are found in the output data field. Note that all input data should be positive numbers. Range of Compound Interest The range feature of COMPINT2 will determine how the future value of an investment varies with the interest rate. As is shown in table 8.1, copy the input data from column C rows 7, 8 and 9 to columns E and G and enter interest rates of 7% and 8% in cells E10 and G10. Cells C18, E18 and G18 show that the future value increases from $1,790, to $1,967 and $2,158 as the interest rate increases from 6%, to 7% and 8%. The present value (pv) or the number of compounding periods (n) could likewise be varied to see the effect on the future value. 34 Chapter Eight Use of Future Value to Pick the Best Terms Suppose two banks offered annual interest rates and compounding periods as follows: * Bank 1: 6.3% annual interest rate, 6 compounding periods per year. * Bank 2: 6.0% annual interest rate, 12 compounding periods per year. Which bank offers the best deal? One has a higher rate, the other offers more frequent compounding. The question can be answered with the application of the program COMPINT2. Calculate the future value after one year of $1000 deposited in each bank as follows: (1) Determine interest rate per compounding period for each bank: Bank 1: 1.05% (= 6.3% per year/6 compounding periods per year). Bank 2: 0.50% (= 6% per year/12 compounding periods per year). (2) Load AS-EASY-AS and COMPINT2 as described in the earlier example. (3) Enter input values for banks 1 and 2 into COMPINT2 as shown in table 5.2 in cells C7, C9, C10, E7, E9 and E10. Enter 0 (zero) in cells C8 and E8 so that COMPINT2 will calculate the future value (fv). The future values of the investments in banks 1 and 2 appear in cells C18 and E18. An account in banks 1 and 2 will grow to $1064 and $1061 respectively. Bank 1 offers the better deal. Table 8.2. Future Value A C E 2 Compound Interest (COMPINT2) 3 Section 1: Input Data 4 ---------------------------------------------------------- 5 PARAMETERS VALUE VALUE 6 ---------------------------------------------------------- 7 Present value (pv) 1,000 1,000 8 Future value (fv) 0 0 9 Number of compounding periods (n) 6 12 10 Periodic interest rate in percent (i) 1.050 .500 11 ---------------------------------------------------------- 12 13 Section 2: Output Data 14 ---------------------------------------------------------- 15 PARAMETERS VALUE VALUE 16 ---------------------------------------------------------- 17 Present value (pv) 1,000 1,000 18 Future value (fv) 1064.68 1061.68 19 Number of compounding periods (n) 6 12 20 Periodic interest rate in percent (i) 1.050 .500 35 Chapter Eight Interest Rates Next let us show how the very same program can be used to solve for a compound interest rate if the present value (pv), future value (fv), and number of compounding periods (n) are known. A second method of comparison to determine which of the above banks gave the largest interest rate is to convert their respective terms to an effective annual rate (EAR). The effective annual rate is the annualized interest rate offered. Proceed as follows: load AS-EASY-AS and COMPINT2. Table 8.3 shows the input data in region A3:E10 and the output data in region A13:E20. We enter the present and future values for each bank (1000 and 1064, 1000 and 1061 respectively) in cells C7:C8 and E7:E8 and set n=1 in cells C9 and E9 for a single annual compounding period. We next set i=0 in cells C10 and E10 to solve for the interest rate corresponding to the other 3 parameters. Cells C20 and E20 of table 8.3 show the result; the EAR for banks 1 and 2 are 6.4% and 6.1%, respectively. Table 8.3. Interest Rate A C E 2 Compound Interest (COMPINT2) 3 Section 1: Input Data 4 ---------------------------------------------------------- 5 PARAMETERS VALUE VALUE 6 ---------------------------------------------------------- 7 Present value (pv) 1,000 1,000 8 Future value (fv) 1,064 1,061 9 Number of compounding periods (n) 1 1 10 Periodic interest rate in percent (i) 0 0 11 ---------------------------------------------------------- 12 13 Section 2: Output Data 14 ---------------------------------------------------------- 15 PARAMETERS VALUE VALUE 16 ---------------------------------------------------------- 17 Present value (pv) 1,000 1,000 18 Future value (fv) 1,064 1,061 19 Number of compounding periods (n) 1 1 20 Periodic interest rate (i) 6.400 6.100 Present Value Hope Planner's rich uncle would like to contribute to his two nieces' college education funds. Suppose that the two girls will be ready to enter college in ten years, and that Uncle Harry would like to pay for the first two years expenses, which he estimates will cost a total of $80,000. How much would he have to deposit today in a fund assuming a compound annual yield of 8%, 9%, and 10%? We calculate the present value of the investment with the following sequence. (1) Load AS-EASY-AS and COMPINT2. (2) Table 8.4 shows the input data entered in columns C7:C10, 36 Chapter Eight E7:E10 and G7:G10. Input data for the future value and number of compounding periods are entered in rows 8 and 9. Interest rates of 8%, 9% and 10% are entered in cells C10, E10 and G10. We set the present value (pv)=0 in cells C7, E7 and G7 so that COMPINT2 will compute the present value. Row 17 of table 8.4 shows the results. Uncle Harry would need to deposit now between $30,843 and $37,055 depending on whether the yield of the fund was 10% or 8%, respectively. Table 8.4. Present Value A C E G 2 Compound Interest (COMPINT2) 3 Section 1: Input Data 4 ----------------------------------------------------------------- 5 PARAMETERS VALUE VALUE VALUE 6 ----------------------------------------------------------------- 7 Present value (pv) 0 0 0 8 Future value (fv) 80,000 80,000 80,000 9 Number of compounding periods (n) 10 10 10 10 Periodic interest rate in percent (i) 8 9 10 11 ----------------------------------------------------------------- 12 13 Section 2: Output Data 14 ----------------------------------------------------------------- 15 PARAMETERS VALUE VALUE VALUE 16 ----------------------------------------------------------------- 17 Present value (pv) 37,055.48 33,792.86 30,843.46 18 Future value (fv) 80,000 80,000 80,000 19 Number of compounding periods (n) 10 10 10 20 Periodic interest rate in percent 8.000 9.000 10.000 Number of Compounding Periods Suppose the Planners had a nest egg of $25,000 and wanted to know how long it would take it to grow to $80,000. They assume that their investments will grow at an annual rate between 8% and 10%. We calculate the number of compounding periods as follows: (1) Load AS-EASY-AS and COMPINT2. (2) Enter pv=25,000, fv=80,000, i=8, 9 and 10 and n=0. The input set-up is given in table 8.5, A3:G10. Load the three sets of known data into the input cells and a 0 (zero) for the number of compounding periods, (n), the variable to be determined. COMPINT2 automatically solves for the number of compounding periods. Row 19 gives the Planners their result. It will take between 12.2 and 15.1 years for their portfolio to grow to $80,000 if they make no further deposits and achieve the assumed rate of return on investment. The Planners could reach their goals much faster if they made regular deposits into their funds. This creates an annuity problem which we will discuss in the next section. 37 Chapter Eight Table 8.5. Number of Compounding Periods A C E G 2 Compound Interest (COMPINT2) 3 Section 1: Input Data 4 ----------------------------------------------------------------- 5 PARAMETERS VALUE VALUE VALUE 6 ----------------------------------------------------------------- 7 Present value (pv) 25,000 25,000 25,000 8 Future value (fv) 80,000 80,000 80,000 9 Number of compounding periods (n) 0 0 0 10 Periodic interest rate in percent (i) 8 9 10 11 ----------------------------------------------------------------- 12 13 Section 2: Output Data 14 ----------------------------------------------------------------- 15 PARAMETERS VALUE VALUE VALUE 16 ----------------------------------------------------------------- 17 Present value (pv) 25,000 25,000 25,000 18 Future value (fv) 80,000 80,000 80,000 19 Number of compounding periods (n) 15.11 13.50 12.20 20 Periodic interest rate in percent (i) 8.000 9.000 10.000 ANNUITIES Annuities are popular financial devices often used in home mortgage and pension payout situations. EZ-$-PLAN helps you solve annuity problems quickly. An annuity is a series of equal deposits or payments made at regular intervals over a period of time. Two different kinds of annuities are in common use: the ordinary annuity and the annuity due. In an ordinary annuity, payments are made at the end of each payment period. Most loans are examples of ordinary annuities. In an annuity due, payments are made at the beginning of the payment period. A lease is usually an annuity due. Five parameters are involved in annuities: (1) Number of payment periods (n), (2) Interest rate (%) per payment period (i), (3) Amount of periodic payment (pt), (4) Present value of annuity (pv) and (5) Future value of annuity (fv). Four spreadsheet files are provided to solve annuity problems as follows: Annuity Type (Payment) Value Known File Name Ordinary (end) Present ANNUITP Ordinary (end) Future ANNUITF Due (beginning) Present ANNDUEP Due (beginning) Future ANNDUEF 38 Chapter Eight To solve an annuity problem, first determine whether the problem at hand involves an ordinary annuity (payment at end of period) or an annuity due (payment at beginning of period). Next determine if the problem involves present or future value and note the program file name in the above table. Next, load the AS-EASY-AS spreadsheet, load the selected program file and enter the input parameters. A zero is entered for the parameter to be determined, the same as in the interest program COMPINT2. Let's try several examples. Present Value of Annuity Due (Not included in SHAREWARE version). Payment for Annuity Due (Not included in SHAREWARE version) Number of Compounding Periods, Ordinary Annuity Suppose that a used car dealer offered to sell you a used car with terms as follows: no down payment, 12% annual interest and $200 payment per month. You are considering three different cars on his lot with prices of $6,000, $7,000 and 8,000. How many months would you have to pay the $200 per month? (1) Since the payments are made at the end of each monthly period, this is an ordinary annuity problem. Since the problem involves the present value, load AS-EASY-AS and the spreadsheet file ANNUITP. (2) Enter the values of the known and desired parameters in the input data filed (see table 8.8 rows 7, 8, 9 and 10). The periodic interest rate (i)=1 (=12%/12 payments per year). Cells C19, E19 and G19 contain the desired result. The number of monthly payments ranges from 35.8 to 51.3 for cars with prices from $6,000 to $8,000. Table 8.8. Ordinary Annuity/Present Value, A C E G 2 Determine Number of Compounding Periods (ANNUITP) 3 Section 1: Input Data 4 ----------------------------------------------------------------- 5 PARAMETERS VALUE VALUE VALUE 6 ----------------------------------------------------------------- 7 Present value (pv) 6,000 7,000 8,000 8 Number of compounding periods (n) 0 0 0 9 Periodic interest rate (i) 1.000 1.000 1.000 10 Payment (pt) 200.00 200.00 200.00 11 ----------------------------------------------------------------- 12 Section 2: Output Data 13 14 ----------------------------------------------------------------- 15 PARAMETERS VALUE VALUE VALUE 16 ----------------------------------------------------------------- 17 18 Present value (pv) 6,000.00 7,000.00 8,000.00 19 Number of compounding periods (n) 35.8 43.3 51.3 20 Periodic interest rate in percent (i) 1.000 1.000 1.000 21 Payment (pt) 200.00 200.00 200.00 39 Chapter Eight Table 8.9. Ordinary Annuity/Present Value A C E G 2 Determine Periodic Interest Rate (ANNUITP) 3 Section 1: Input Data 4 ----------------------------------------------------------------- 5 PARAMETERS VALUE VALUE VALUE 6 ----------------------------------------------------------------- 7 Present Value (pv) 6,000 5,500 5,000 8 Number of compounding periods (n) 36 36 36 9 Periodic interest rate in percent (i) 0 0 0 10 Payment (pt) 200.00 200.00 200.00 11 ----------------------------------------------------------------- 12 Section 2: Output Data 13 14 ----------------------------------------------------------------- 15 PARAMETERS VALUE VALUE VALUE 16 ----------------------------------------------------------------- 17 18 Present value (pv) 6,000.00 5,500.00 5,000.00 19 Number of compounding periods (n) 36.000 36.000 36.000 20 Periodic interest rate in percent (i) 1.021 1.535 2.121 21 Payment (pt) 200.00 200.00 200.00 22 23 Annual interest rate in percent 12.246 18.420 25.452 Periodic Interest Rate, Ordinary Annuity Suppose that the same used car dealer offers the following terms: no down payment; prices on the three cars in question of $5,000, $5,500 and $6,000; and 36 equal payments of $200 at the end of each month. What periodic interest rates correspond to the above terms? The periodic interest rate is determined as follows. (1) Since the payments are at the end of each period, this is an ordinary annuity problem. Since the problem involves a present value, load AS-EASY-AS and the spreadsheet file ANNUITP. (2) Enter the values of the known and desired parameters in the input data field (see table 8.9 rows 7, 8, 9 and 10). Cells C20, E20 and G20 show the resulting periodic (monthly) interest rate. The product of these values by 12 yields the annual interest rate, found in cells C23, E23 and G23 of table 8.9. The interest rate on the loan ranges from 12.2% for the $6000 car and 25.5% for the $5000 car. Note that interest rate can be directly calculated by inserting 0's in the interest rate row for only the ordinary annuity for the present value case (ANNUITP). For the other three files, if the interest rate is the desired parameter, the correct value must be calculated by a successive approximation approach. For example, successively guess the interest rate value to bring the other parameters close to their known values. This will result in a suitable solution. NET PRESENT VALUE (Not included in SHAREWARE version).