Please fill out attached spreadsheet.
Time Value of Money Chapter 4/11/10 Chapter 4. The Time Value of Money The worksheet shown below performs most of the calculations required for Chapter 4, and it was used to create many of the chapter's tables and figures. We pasted in a few dialog boxes for specific Excel functions and features; they are shown off to the right of where they were used. However, in general we encourage students to become familiar with our Excel Tutorial and to refer to it if they encounter something they don't understand. It is also useful to learn how Excel models can be used to create tables and graphs that can then be copied into Word documents, which is the way we prepared the text manuscript for submission to the publisher. That procedure is used often in business (and in business courses) to prepare reports. Although answers to the Self-Test questions within the chapter are generally quite easy and can be worked with a calculator, we also solved them with Excel as a check and also to provide some information on the solutions for students who might have questions. The tabs at the lower part of this screen take you to the solutions for self-tests in the various sections of the chapter. Even students who are not familiar with Excel should still be able to see the solution setup and then work the problem with a calculator. While we did not create the model specifically for use in lectures, we like to use it in our lectures if we are in a classroom where a projector is attached to a computer. We scroll through the model and lecture on points and questions as they come up. This is most useful if students have some familiarity with Excel, but that is not really necessary because everything the model does can also be done with a financial calculator. FUTURE VALUES (Section 4.2) A dollar in hand today is worth more than a dollar to be received in the future because, if you had it now, you could invest it, earn interest, and end up with more than one dollar in the future. The process of going to future values (FVs) from present values (PVs) is called compounding. To illustrate, refer to our 3-year time line and assume that you plan to deposit $100 in a bank that pays a guaranteed 5% interest each year. How much would you have at the end of Year 3? See Columns o the right for a picture of the filled in dialog box for the FV function. Figure 4-1. Alternative Procedures for Calculating Future Values INPUTS: Investment = CF0 = PV =-$100.00 Interest rate = I =5.00%Notice that in the following dialog box we entered the variables as cell No. of periods = N = 3references. We could have used the numbers themselves, but using cell references makes the model more useful, as we demonstrate later. Setup of the problem as aPeriods: 0 5%123 You can see our Excel Tutorial for a thorough discussion of how to Time Line||||enter and use formulas. Cash Flow:-$10000FV = ?=FV(C40,C41,0,C39) 1. Step-by-Step: Multiply$100 by (1 + I)$100$105.00$110.25$115.76 2. Formula: FVN = PV(1+I)N FV3 = $100(1.05)3 =$115.76 35-$100.00$0 3. Financial Calculator:NI/YRPVPMTFV $115.76 4. Excel Spreadsheet: FV Function:FVN = =FV(I,N,0,PV) Fixed inputs:FVN = =FV(0.05,3,0,-100) =$115.76 Cell references:FVN = =FV(C40,C41,0,C39) =$115.76 In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no periodic cash flows, and then the PV. The data can be entered as fixed numbers or, better yet, as cell references. The Compounding Process: A Graphic View Figure 4-2 (just below) shows how a $1 investment grows over time at different interest rates. The curves were created by solving for FV at different values for N and I. The graph shows, simultaneously, the effects of time and interest rates. The data table used to create this figure is shown to the right. For instruction on data tables and graphing, refer to our Excel Tutorial, Tab 4. Data Used to Create Text Figure 4-2. Future Value of $1 Periods (N)Interest Rate (I) 115.7625-20%0%5%10%20% 0$100.00$100.00$100.00$100.00$100.00 1$80.00$100.00$105.00$110.00$120.00 2$64.00$100.00$110.25$121.00$144.00 3$51.20$100.00$115.76$133.10$172.80 4$40.96$100.00$121.55$146.41$207.36 5$32.77$100.00$127.63$161.05$248.83 6$26.21$100.00$134.01$177.16$298.60 7$20.97$100.00$140.71$194.87$358.32 8$16.78$100.00$147.75$214.36$429.98 9$13.42$100.00$155.13$235.79$515.98 10$10.74$100.00$162.89$259.37$619.17 PRESENT VALUES (Section 4.3) Mathematically, the present value is the opposite of the future value. Instead of compounding a present value forward to find the FV, you discount the FV back to find the PV. Thus, if you know the PV, you can compound to find the FV, while if you know the FV, you can discount to find the PV. To illustrate, refer to the time line on Row 70 below and assume that $115.76 is due in 3 years. If a bank pays a guaranteed 5% interest rate each year, how much must you deposit now to have $115.76 in 3 years? The amount of the required deposit is the PV of $115.76 due in 3 years when the discount rate is 5%, and it can be found by any one of four methods. Figure 4-3. Alternative Procedures for Calculating Present Values INPUTS: Future payment = CFN = FV =$115.76 Interest rate = I =5.00% No. of periods = N = 3 Problem as a Time LinePeriods:0123=PV(C111,C112,0,C110) |||| Cash Flow Time Line:PV = ?$115.76 1. Step-by-Step:$100.00$105.00$110.25$115.76 2. Formula: PVN = FV/(1+I)N PV = $115.76/(1.05)3 =$100.00 35$0$115.76 3. Financial Calculator:NI/YRPVPMTFV -$100.00 4. Excel Spreadsheet: PV Function:PV = =PV(I,N,0,FV) Fixed inputs:PV = =PV(0.05,3,0,115.76) =-$100.00 Cell references:PV = =PV(C111,C112,0,C110) =-$100.00 In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no periodic cash flows, and then the FV. The data can be entered as fixed numbers or, better yet, as cell references. The Discounting Process: A Graphic View Figure 4-4 shows how the present value of $1 due in the future declines as either the interest rate or the time until receipt increases. The Data Table to the right provides the data used to draw the figure. At 0%, the PV of $1 always remains at $1, but at higher rates the value at the end of N years is lower the higher the rate, and at a given rate, the value declines the larger the value of N. Data for Figure 4-4. Present Value of $1 Periods (N)Interest Rate (I) 0.86380%5%10%20% 0$1.0000$1.0000$1.0000$1.0000 5$1.0000$0.7835$0.6209$0.4019 10$1.0000$0.6139$0.3855$0.1615 15$1.0000$0.4810$0.2394$0.0649 20$1.0000$0.3769$0.1486$0.0261 25$1.0000$0.2953$0.0923$0.0105 30$1.0000$0.2314$0.0573$0.0042 35$1.0000$0.1813$0.0356$0.0017 40$1.0000$0.1420$0.0221$0.0007 45$1.0000$0.1113$0.0137$0.0003 50$1.0000$0.0872$0.0085$0.0001 FINDING THE INTEREST RATE (Section 4.4) Previously, we solved the basic equation to find FV and PV. However, we could just as easily solve for I or N. For example, suppose we know that a given bond has a cost of $100 and that it will return $150 after 10 years. Thus, we know PV, FV, and N, and we want to find the rate of return we would earn if we bought the bond.=RATE(C173,0,C171,C172) INPUTS: Present value (PV)-$100.00 Future value (FV)$150.00 No. of years (N)10 OUTPUT: Interest rate (I) = RATE(N,0,PV,FV) Interest rate (I)4.14% FINDING THE NUMBER OF YEARS (Section 4.5) Sometimes we need to know how long it will take to accumulate a given sum of money, given our beginning funds and the rate we will earn on those funds. For example, suppose we believe that we could retire comfortably if we had $1 million, and we want to find how long it will take us to reach that goal, assuming that we now have $500,000 invested at 4.5%.=NPER(C196,0,C194,C195) INPUTS: Present value (PV)-$500,000 Future value (FV)$1,000,000 Interest rate (I)4.50% OUTPUT: No. of years (N)=NPER(I,0,PV,FV) No. of years (N)15.7473 FUTURE VALUE OF AN ORDINARY ANNUITY (Section 4.7) An ordinary annuity has regular, periodic payments that occur at the end of each period. Methods for solving the future value of an ordinary annuity are shown below. Figure 4-5. Summary: Future Value of an Ordinary Annuity INPUTS: Payment amount = PMT =-$100.00 Interest rate = I =5.00% No. of periods = N = 3 1. Step-by-Step:0123 |||| -$100-$100-$100 $100.00 Multiply each payment by$105.00 (1+I)N-t and sum these FVs to$110.25 find FVAN:$315.25 2. Formula: FVAN = =$315.25 3. Financial Calculator:35$0-$100.00 NI/YRPVPMTFV $315.25 4. Excel Spreadsheet: FV Function:FVAN = =FV(I,N,PMT,PV) Fixed inputs:FVAN = =FV(0.05,3,-100,0) =$315.25