Tutorial 3 - Present Value of Uneven Cash Flows 7 9 Find the present value of an uneven cash flow stream invested for various compounding periods and at different discount rates. The Problem RST Corporation has the opportunity to purchase a CNC machine that promises cash flows of $500 in year 1, $300 in year 2, and $100 in year 3. The interest rate is 7%. What is the present value of the cash flows from this machine? 10 11 12 13 14 15 Uneven cash flow A series of cash flows in which the amount varies from one period to the next Hint: Unless told otherwise, assume that cash flows occur at the end of the period! 53 16 17 Finance Concept: PV - CF/(1+r) + CF/(1+r) + CF/(1+r)" 19 Present Value = Sum of the present values of the individual cash flows 20 PV = 500/(1+0.07) + 300/(1+0.07) + 100/(1+0.07) 21 PV =467.29 +262.03 +81.63 = $810.95 22 23 In Words: The value today of future cash flows discounted at seven percent is $810.95 24 25 Spreadsheet Solution: 28 27 Use formulas to find your answer by completing this PV Table: 28 29 Today Year 1 Year 2 Year 3 30 a Interest Rate 7% 7% 7% 31 b Time 0 1 2 3 32 c Cash File 500 100 33 d PV interest factor 34 e Present Value 35 36 1 First, we must calculate the PV Interest Factor for each cash flow in row d. PVIF = 1/(1+1) 37 In cell F33 enter the formula: = 1/(1+F30)^1 and copy to cells G33 and 33 90 M N . B C D E F G . K 37 In cell F33 enter the formula-1/(1+F30^1 and copy to cells G33 and H33 88 You must change the exponents on cells G33 and 33 139 140 2 Then we find the PV of each cash flow in row e. 141 In cell F34 enter the PV formula: CFPVIF F32 F33 and copy to cells G34 and 134 42 43 3 Lastly, we sum the PVs to get the PV of the annuity in cell E34 44 In cell E34 enter the formula: =sum(F34:34) 45 46 4 Your answer should be: The PV of the meven cash flows is $810.95 47 48 Note: You can change any of the inputs and Excel will automatically recompute. 49 50 51 52 Test your skills 53 54 What is the Present Value of the cash flow above if the discount rate is 192 55 Enter 01 for each interest rate and copy and paste your answer bere. 58 Answer $88620 57 58 59 Spreadsheet Solution with the Excel Wizard: 60 61 Today Year 1 Year 2 Year 3 62 a Interest Rate 0.07 - Time 0 1 2 3 64 c Cash Flow 300 65 d Present Value 66 67 500 100 1. Put the cursor on E65 and click the function wizard ().click financial, scroll down to NPV and click OK. is located on your standard menu bar 6B 69 70 71 NPV is the Net Prescil value because some of the cash flows may be negative NPV is also for cash Dows that are not constant ad CF 75 76 77 Using the NPV formula directly 78 79 Use the built-in Excel formula: NPV(rate, CFs) 80 81 82 Inputs: 83 rate 0.07 84 cash flows = 500 CF CF in year 1 85 300 CF in year 2 86 100 CF, CF in year 3 87 88 1. In cell H81 type: NPV 89 2. Then click on cell F83 or enter 0.07 and enter a comma 3. Then put your cursor on cell F84 (at the first CE of $500) and drag the cursor to F86 90 and enter the ending parenthesis ) and then click enter, 91 4. Your answer should be $810.95 92 93 Hint: This is similar to your calculator cash flow keys. 94 95 96 Using the Table and PV formulas: 97 98 Time CF PV 1. Type in your cash flows in cells D99 through D102. 99 0 2. Type in your discount rate in cell D104 100 1 3. In cell E99 type: PV($D$104,C99,0,-D99) 101 2 4. Copy to cells E 100E 102. 102 3 5. In cell E106 type: SUM(E99 E102) 103 6. You should get $810.95 as the PV SUM with the 104 rate inputs from the previous problem. 105 CF=0 106 PV SUM 107 108 100 oral Hint: This is similar to your calculator cash flow keys. Using the Table and PV formulas: CF PV Time 0 1 2 1. Type in your cash flows in cells D99 through D102. 2. Type in your discount rate in cell D104 3. In cel E99 type: =PV($D$104,C99.0,-D99) 4. Copy to cells E100E 102 5. In cell E106 type: =SUM(E99 E102) 6. You should get $810.95 as the PV SUM with the inputs from the previous problem CF 0 3 rate PV SUM 8 9 Test your skills 0 What is the present value of a cash flow of $1,000 today. $2,000 at the end of the next year (time=1). 1 $3,000 at the end of year 2, and $4,000 at the end of year 3, discounted at 5% (rates 2 13 14 Your answer should be $9081.20 15 PRINT your spreadsheets. 16 End of Spreadsheet 2. 17 18 19 20 21 122 123 124 125 128