Please explain or show how you calculated each cell and include formulas for a thumbs up. Thank you so much!
Formulas View Data Review Help Home Insert Draw Page Layout Enable PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. 3 X fo Find the present value of an uneven cash flow stream invested for various compounding per F H G U K E A C D B Present Value of Uneven Cash Flows 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? Uneven cash flow: A series of cash flows in which the amount varies from one period to the next. 3 1 2 3 4 15 Hint: Unless told otherwise, assume that cash flows occur at the end of the period! 16 17 Finance Concept: PV = CF,/(1+r) + CF,/(1+r) + CF/(1+r)' 10 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: 26 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 Flow 500 300 100 33 d PV interest factor 34 e Present Value 35 36 1 First we must calculate the PV Interest Factor for each cash Dow in rowd PVIE = LED Interest PV PVCF FVCF MS Ready I Type here to search O BE Home Insert Draw Page Layout Formulas Data View Review Help Enable Ed OTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. fx Find the present value of an uneven cash flow stream invested for various compounding peric K A B D E F G H 1 First, we must calculate the PV Interest Factor for each cash flow in row d . PVIF = 1/(1+r) In cell F33 enter the formula: = 1/(1+F30) 1 and copy to cells G33 and H33 You must change the exponents on cells G33 and H33. 2 Then, we find the PV of each cash flow in row e. In cell F34 enter the PV formula: CFPVIF F32 F33 and copy to cells G34 and H34. 3 Lastly, we sum the PVs to get the PV of the annuity in cell E34. In cell E34 enter the formula: sum(F34:34) 4 Your answer should be: The PV of the uneven cash flows is $810.95. Note: You can change any of the inputs and Excel will automatically recompute. Test your skills: What is the Present Value of the cash flow above if the discount rate 1%? Enter .01 for each interest rate and copy and paste your answer here. Answer: $886.20 Spreadsheet Solution with the Excel Wizard: Year 1 Year 2 Year 3 Today 0.07 0 a Interest Rate b Time C Cash Flow d Present Value 1 500 2 300 3 100 5 -6 07 1. Put the cursor on E65 and click the function wizard (3). Click financial. scroll down to NPV and click OK. is located on your standard menu bar 68 69 Interest Me lua l an alu hay Lua Haa n hael It was a cao la. PV PVCF FVCF MS Rady Type here to search O ile Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful_files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. fx Find the present value of an uneven cash flow stream invested for various compound B C D F H K 7 1. Put the cursor on E65 and click the function wizard (). click financial, 8 scroll down to NPV. and click OK. O is located on your standard menu bar. 9 0 NPV is the Net Present value because some of the cash flows may be negative. 1 NPV is also for cash flows that are not constant. 2. The dialog box will appear. Enter E62 or.07 for the Rate and the range of 2 cells containing the cash flows: F64:H64, for value 1. Click OK. 73 74 Note: You can also enter each cash flow in a separate value box by pointing and clicking 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
