I need the answers or instructions on how to get the answers for all of problem three and the unfinished part of problem 4. This class is financial modeling, must use the formulas by tagging them to the individual rows/columns Ex: =$B$3+D16, this is random just an example.

PV Problem: PV Interest Rate Period FV $ $ FV Problem: PV Interest Rate Number of Period FV 27.47 =B5/(1+B3)^B4 5% 49 =2016-1967 300.00 $ 25,000.00 5% 10 $ 40,722.37 =B8*(1+B9)^B10 0 -1000 1 2 3 500 4 5 6 2000 0 1 100 100 2 100 100 3 100 100 4 100 100 5 100 100 6 100 100 Paid at the end of each period Paid at the beginning of each period A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 B C D E F THE PRESENT VALUE OF $100 IN 3 YEARS In this example we vary the discount rate r X, future payment n, time of future payment r, interest rate Present value, X/(1+r)n Discount rate 0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 12% 15% 18% 20% 22% 25% 30% 35% 40% 45% 50% $ 100 3 6% 83.96 =B2/(1+B4)^B3 Present value $ 100.00 =$B$2/(1+A8)^$B$3 $ 97.06 =$B$2/(1+A9)^$B$3 $ 94.23 $ 91.51 $ 88.90 $ 86.38 $ 83.96 $ 81.63 $ 79.38 $ 77.22 $ 71.18 $ 65.75 $ 60.86 $ 57.87 $ 55.07 $ 51.20 $ 45.52 $ 40.64 $ 36.44 $ 32.80 $ 29.63 Suppse we are to be paid $100 in three years. Us TVM principles, what is this amount worth today? 1. Introduce PV formula A5 2. Calculate PV at different interest rates Present Value of $100 to be Paid in 3 Yea 120 100 Present value 80 60 40 20 0 0% 5% 10% 15% 20% 25% 30% 35% 40% Discount rate G H I J 1 2 in three years. Using re to be paid $100 es, what is this amount worth today? 3 4 5 6 7 8 PV at different interest rates 9 10 11 ent Value of $100 12to be Paid in 3 Years when Discount Rate Varies 13 14 15 16 17 18 19 20 21 22 10% 15% 20%2325% 30% 35% 40% 45% 50% 24 Discount rate 25 26 27 28 K A 1 2 3 4 B D Annual payment r, interest rate 100 6% Year 1 2 3 4 5 Payment at end of year $ 100.00 $ 100.00 $ 100.00 $ 100.00 $ 100.00 6 7 8 9 10 11 12 Present value of all payments Summing the present values 13 Using Excel's PV function 14 Using Excel's NPV function 15 16 17 Present value of payment $ 94.34 =B6/(1+$B$3)^A6 $ 89.00 =B7/(1+$B$3)^A7 $ 83.96 =B8/(1+$B$3)^A8 $ 79.21 $ 74.73 $ 421.24 =SUM(C6:C10) $421.24 =PV(B3,5,-B2) $421.24 =NPV(B3,B6:B10) Ex2.PRESENT VALUE OF AN ANNUITY: FIVE ANNUAL PAYMENTS OF $100 EACH PAID AT THE BEG. OF EACH PERIOD 19 20 E Ex1.PRESENT VALUE OF AN ANNUITY: FIVE ANNUAL PAYMENTS OF $100 EACH PAID AT THE END OF EACH PERIOD 5 18 C Payment at beg. of Present value year of payment 100 100.00 =B21/(1+$B$3)^A21 100 94.34 100 89.00 100 83.96 100 79.21 Year 0 21 1 22 2 23 3 24 4 25 26 27 Present value of all payments Summing the present values 28 Using Excel's PV function 29 Using Excel's NPV function 30 31 32 446.51 =SUM(C21:C25) $446.51 =PV(B3,5,B21,,1) $446.51 =NPV(B3,B22:B25)+B21 1. PV is a function of cash flows o 2. Find different ways to reach PV 3. Use SUM to find PV 4. Use PV to find PV 5. Use NPV to find PV 6. What is NPV again? A 33 B Payment at end of Present value year of payment 100 94.34 =B35/(1+$B$3)^A35 100 89.00 100 83.96 100 79.21 600 448.35 Year 1 35 2 36 3 37 4 38 5 39 40 41 Present value of all payments Summing the present values 42 Using Excel's PV function 43 Using Excel's NPV function 44 45 46 Payment at end of Present value year of payment 100 94.34 =B49/(1+$B$3)^A49 300 267.00 200 167.92 500 396.05 100 74.73 Year 1 49 2 50 3 51 4 52 5 53 54 55 Present value of all payments Summing the present values 56 Using Excel's PV function 57 Using Excel's NPV function 58 59 60 1000.04 =SUM(C49:C53) cannot apply $1,000.04 =NPV(B3,B49:B53) Ex5.PRESENT VALUE OF A SINGLE CASHFLOW 62 63 64 65 66 794.87 =SUM(C35:C39) $794.87 =PV(B3,5,B35,B39-B35) $794.87 =NPV(B3,B35:B39) Ex4.PRESENT VALUE OF IRREGULAR CASHFLOW 48 61 D Ex3.PRESENT VALUE OF AN ANNUITY (FIVE ANNUAL PAYMENTS OF $100 EACH PAID AT THE END OF EACH PERIOD) and A LUMPSUM PAYMENT $500 AT THE END OF INVESTMENT HORIZON: 34 47 C Year 1 2 3 4 Payment at end of year Present value of payment 0 0.00 =B63/(1+$B$3)^A63 0 0.00 0 0.00 0 0.00 E A B 5 67 68 69 Present value of all payments Summing the present values 70 Using Excel's PV function 71 Using Excel's NPV function 72 73 74 C 100 D 74.73 74.73 ($74.73) =PV(B3,A67,,B67) $74.73 =NPV(B3,B63:B67) $94.34 $94.34 E F G H I J K L M N 1 2 3 4 5 1. PV is a function of cash flows of any project, time, and interest rate assumptions 2. Find6different ways to reach PV - when would you use one way vs. another? (when CF or INT changes per period) 3. Use 7SUM to find PV 4. Use 8PV to find PV 5. Use 9NPV to find PV 6. What 10is NPV again? 11 12 0 1 2 3 4 5 13 Time 100 100 100 100 100 14 Cashflow of Ex1 100 100 100 100 100 15 Cashflow of Ex2 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 F 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 G H I J K L M N F 67 68 69 70 71 72 73 74 G H I J K L M N THE FUTURE VALUE OF $100 IN 3 YEARS In this example we vary the discount rate r X, payment today n, time to maturity r, interest rate Future value,X*(1+r)^n Discount rate 0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 12% 15% 18% 20% 22% 25% 30% 35% 40% 45% 50% 100 3 10% $ 133.10 =B2*(1+B4)^B3 Future value 100.00 =$B$2*(1+A8)^$B$3 1. Introduce FV formula A5 103.03 =$B$2*(1+A9)^$B$3 2. Calculate FV at different interest rates 106.12 109.27 Future Value of $100 in 3 Years when Discount Rate V 112.49 400 115.76 350 119.10 300Value 122.50 Future 250 125.97 129.50 200 140.49 150 152.09 100 164.30 50 172.80 0 181.58 0% 5% 10% 15% 20% 25% 30% 35% 40% 45% 50% 195.31 219.70 246.04 274.40 304.86 337.50 Discount rate ferent interest rates 3 Years when Discount Rate Varies 5% 30% 35% 40% 45% 50% nt rate A 1 2 B 25 D E F Ex1. FUTURE VALUE WITH RGULAR ANNUAL DEPOSITS AT THE END OF EACH YEAR Interest 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 C Year 1 2 3 4 5 6 7 8 9 10 We are going to build a table showing capita for a 10 year annuity. At the end of each yea $100 into an account earning 6% compound 6% Account balance, beg. year 0.00 100.00 206.00 318.36 437.46 563.71 697.53 839.38 989.75 1,149.13 Future value Deposit at Interest Account end earned balance, of year during year end of year 100.00 0.00 100.00 100.00 6.00 206.00 100.00 12.36 318.36 100.00 19.10 437.46 100.00 26.25 563.71 100.00 33.82 697.53 100.00 41.85 839.38 100.00 50.36 989.75 100.00 59.38 1,149.13 100.00 68.95 1,318.08 $1,318.08 =FV(B2,10,-C4) GOALS: 1. define annuity: a series of equal periodic payments. 2. build table to arrive at future value of account 3. utilize FV to arrive at same answer 1. In D4 enter = $B$2*B4. Notice the dollari formula down to D13. In E4 enter =sum(B4: you the balance at the end of the year, inclu Drag down. 2. In B5, enter =E4. This is because the acco beginning of the year is the account balance previous year. Drag down to B13. 3. In C15 enter =FV(B2,A13,-C4). Note that t is positive, because it is a withdrawal, the op of previous deposit (-C4, negative number). for "Future Value" for an annuity. It should g result we reached manually in E13. Ex2. FUTURE VALUE WITH RGULAR ANNUAL DEPOSITS AT THE BEG. OF EACH YEAR 26 Interest 27 28 29 30 31 32 33 34 35 36 Year 1 2 3 4 5 6 7 8 9 6% Account balance, beg. of year 0.00 106.00 218.36 337.46 463.71 597.53 739.38 889.75 1,049.13 =(B28+C28)*$B$26 Deposit at Interest Account beg. earned balance, of year during year end of year 100.00 6.00 106.00 =SUM(B28:D28) 100.00 12.36 218.36 100.00 19.10 337.46 100.00 26.25 463.71 100.00 33.82 597.53 100.00 41.85 739.38 100.00 50.36 889.75 100.00 59.38 1,049.13 100.00 68.95 1,218.08 37 38 39 40 41 42 A 10 B 1,218.08 Future value C D 100.00 79.08 E 1,397.16 F $1,397.16 =FV(B26,A37,C37,,1) Ex3. FUTURE VALUE WITH RGULAR ANNUAL DEPOSITS AT THE END OF EACH YEAR AND A LUMPSUM DEPOSIT AT THE BEG. OF THE INVESTMENT HORIZON 43 Interest 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 Year 1 2 3 4 5 6 7 8 9 10 6% Account balance, beg. of year 500.00 630.00 767.80 913.87 1,068.70 1,232.82 1,406.79 1,591.20 1,786.67 1,993.87 Future value =B45*$B$43 Deposit at Interest Account end earned balance, of year during year end of year 100.00 30.00 630.00 =SUM(B45:D45) 100.00 37.80 767.80 100.00 46.07 913.87 100.00 54.83 1,068.70 100.00 64.12 1,232.82 100.00 73.97 1,406.79 100.00 84.41 1,591.20 100.00 95.47 1,786.67 100.00 107.20 1,993.87 100.00 119.63 2,213.50 ($2,213.50) =FV(B43,A54,C54,B45) Ex4. FUTURE VALUE WITH IRREGULAR ANNUAL DEPOSITS AT THE END OF EACH YEAR 60 Interest 61 62 63 64 65 66 67 68 69 Year 1 2 3 4 5 6 7 8 6% Account balance, beg. of year 0.00 100.00 306.00 624.36 1,061.82 1,625.53 2,323.06 3,162.45 =B62*$B$60 Deposit at Interest Account end earned balance, of year during year end of year 100.00 0.00 100.00 =SUM(B62:D62) 200.00 6.00 306.00 300.00 18.36 624.36 400.00 37.46 1,061.82 500.00 63.71 1,625.53 600.00 97.53 2,323.06 700.00 139.38 3,162.45 800.00 189.75 4,152.19 4. With unequal annual deposit, we cannot u future value directly. We can still build up tables to mannually calc We can also use NPV function to first calculat of a series of unequal cashflow, and then com value (value at time 0) to get the future value 70 71 72 73 74 75 76 77 A 9 10 B 4,152.19 5,301.32 Future value NPV FV of NPV C 900.00 1,000.00 D 249.13 318.08 E 5,301.32 6,619.40 =fv() cannot applied $3,696.24 =NPV(B60,C62:C71) $6,619.40 =FV(B60,A71,,C74) =C74*(1+B60)^10 Ex5. FUTURE VALUE OF A SINGLE CASHFLOW 78 79 Interest 80 81 82 83 84 85 86 87 88 89 90 91 92 F 4. With unequal annual deposit, we cannot u future value directly. We can still build up tables to mannually calc We can also use NPV function to first calculat of a series of unequal cashflow, and then com value (value at time 0) to get the future value Year 1 2 3 4 5 6 7 8 9 10 6% Account balance, beg. of year 100.00 106.00 112.36 119.10 126.25 133.82 141.85 150.36 159.38 168.95 Future value Deposit at Interest Account end earned balance, of year during year end of year 0.00 6.00 106.00 0.00 6.36 112.36 0.00 6.74 119.10 0.00 7.15 126.25 0.00 7.57 133.82 0.00 8.03 141.85 0.00 8.51 150.36 0.00 9.02 159.38 0.00 9.56 168.95 0.00 10.14 179.08 ($179.08) =FV(B79,A90,,B81) G H I J K L M N O 1 We are going to build a table showing capital appreciation r a 10 year annuity. At the end of each year we will deposit 2 earning 6% compounded annually. 100 into an account 3 In D4 enter = $B$2*B4. Notice the dollarization. Drag the 4 rmula down to D13. In E4 enter =sum(B4:D4). This gives ou the balance at5the end of the year, including interest. rag down. 6 7 8 In B5, enter =E4.9This is because the account balance at the eginning of the year 10 is the account balance at the end of the revious year. Drag down to B13. 11 12 13 14 In C15 enter =FV(B2,A13,-C4). Note that the final number positive, because 15it is a withdrawal, the opposite direction f previous deposit 16(-C4, negative number). The FV formula is r "Future Value" for an annuity. It should give you the same esult we reached17 manually in E13. 18 19 20 21 22 23 24 Year 1 2 3 4 5 6 7 8 9 10 Deposit at Future end value of of year Deposit 100 168.95 =L4*(1+$B$2)^(10-K4) 100 159.38 =L5*(1+$B$2)^(10-K5) 100 150.36 100 141.85 100 133.82 100 126.25 100 119.10 100 112.36 100 106.00 100 100.00 Summing the future value 1318.08 =SUM(M4:M13) 25 26 Year Deposit at beg. of year Future value of Deposit 27 28 29 30 31 32 33 34 35 36 0 1 2 3 4 5 6 7 8 9 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 179.08 =L27*(1+$B$26)^($K$37-K27) 168.95 159.38 150.36 141.85 133.82 126.25 119.10 112.36 106.00 G H I 37 38 39 40 41 J K 10 L Summing the future value M N O 1397.16 =SUM(M27:M36) 42 43 Year Deposit at end of year Future value of Deposit 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 0 1 2 3 4 5 6 7 8 9 10 500.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 Summing the future value 895.42 =L44*(1+$B$43)^($K$54-K44) 168.95 159.38 150.36 141.85 133.82 126.25 119.10 112.36 106.00 100.00 2213.50 =SUM(M44:M54) 59 60 Year Deposit at end of year Future value of Deposit 61 62 63 64 65 66 67 68 69 With unequal annual deposit, we cannot use FV to calculate ure value directly. e can still build up tables to mannually calculate future value. e can also use NPV function to first calculate the present value a series of unequal cashflow, and then compound this present ue (value at time 0) to get the future value (value at time 10). 1 2 3 4 5 6 7 8 9 100.00 200.00 300.00 400.00 500.00 600.00 700.00 800.00 900.00 168.95 =L61*(1+$B$60)^($K$70-K61) 318.77 451.09 567.41 669.11 757.49 833.71 898.88 954.00 G H I J With unequal annual 70 deposit, we cannot use FV to calculate ure value directly. 71 e can still build up tables to mannually calculate future value. 72 function to first calculate the present value e can also use NPV 73 cashflow, and then compound this present a series of unequal ue (value at time740) to get the future value (value at time 10). 75 76 77 K 10 L 1,000.00 Summing the future value M 1000.00 N O 6619.40 =SUM(M61:M70) 78 79 Year Deposit at end of year Future value of Deposit 80 81 82 83 84 85 86 87 88 89 90 91 92 0 1 2 3 4 5 6 7 8 9 10 100.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Summing the future value 179.08 =L80*(1+$B$79)^($K$90-K80) 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 179.08 =SUM(M80:M90) P 1 2 3 4*(1+$B$2)^(10-K4) 4 5*(1+$B$2)^(10-K5) 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 27*(1+$B$26)^($K$37-K27) 28 29 30 31 32 33 34 35 36 P 37 38 39 40 41 42 43 44 44*(1+$B$43)^($K$54-K44) 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 61*(1+$B$60)^($K$70-K61) 62 63 64 65 66 67 68 69 P 70 71 72 73 74 75 76 77 78 79 80 80*(1+$B$79)^($K$90-K80) 81 82 83 84 85 86 87 88 89 90 91 92 A B C D SAVING FOR COLLEGE 1 Here is a very real problem. We need to save for our child's expected college costs. Unlike those of us whose financial situation is Warren Buffett's, we have cash flow restrictions. We need to save/invest now so as to NOT have to borrow lots of money when o 2 reaches 18. Today is our child's 10th birthday and we make our first deposit today. We expect our investments to earn 8% over th 3 several years. The annual college cost is $20,000 for four years. 4 GOALS: 5 1. Understand why NPV must = 0. 6 2. Use NPV function. 7 3. If underfunded, "play" with annual investment to approach NPV = 0. 8 4. Use SOLVER to find the required deposit amount. 9 10 11 8.00% 12 Interest rate $6,227.78 13 Annual deposit $20,000.00 =B16+C16 14 Annual cost of college 15 Birthday 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 10 11 12 13 14 15 16 17 18 19 20 21 Account balance on Deposit or birthday, withdrawal before on each birthday/at deposit/withdrawal beginning of year $0.00 $6,726.00 $13,990.08 $21,835.28 $30,308.10 $39,458.75 $49,341.45 $60,014.76 $71,541.94 $55,665.29 $38,518.52 $20,000.00 NPV of all payments FV of all deposits&withdrawals $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 ($20,000.00) ($20,000.00) ($20,000.00) ($20,000.00) Account balance, after deposit/withdrawal, at the beg. of year $6,227.78 $12,953.77 $20,217.85 $28,063.06 $36,535.88 $45,686.52 $55,569.22 $66,242.54 $51,541.94 $35,665.29 $18,518.52 $0.00 $0.00 =C16+NPV(B12,C17:C27) $0.00 =C29*(1+B12)^12 5. We can use SOLVER to find an exact amount we need to invest. Go to the Data|Analysis tab, then click Solver. A box will open up. "Set Objective" is the cell you wish to "force" to some value or range. "Set" C29 (or E27) to "Value Of" "0" "by chaning" parameters B13 (how much we invest). Click solve. Voila. The annual deposit amount will change to get to an NPV of 0. If you cannot find Analysis under Data tab, go to File|Option|Add-ins, click "GO" on the bottom, and select Analysis Toolpak and Solver Add-ins. Then click OK. You should now be able to see Analysis under Data tab. E F 1 nlike those of us whose financial situation is similar to o NOT have to borrow lots of money when our child 2 e expect our investments to earn 8% over the next 3 4 5 6 7 8 9 10 11 12 13 14 =D16*(1+$B$12) 15 Account balance, End of year balance, with interest $6,726.00 16 $13,990.08 17 $21,835.28 18 $30,308.10 19 $39,458.75 20 $49,341.45 21 $60,014.76 22 $71,541.94 23 $55,665.29 24 $38,518.52 25 $20,000.00 26 $0.00 27 28 6+NPV(B12,C17:C27) 29 30 31 t. Go to the Data|Analysis tab, 32 you wish to "force" to some value 33much we invest). ameters B13 (how to an NPV of 0. 34 dd-ins, click "GO" 35on the bottom, You should now 36 be able to see 37 38 39 40 Year 1 2 3 4 5 6 7 8 9 10 11 12 G H I J K L 1. First assume annual deposit to be $4,000. In C16 enter = $B$13 and drag down to C23. In C24 enter = -$B$14 and drag down to C27. Why the negative of B14? 2. In E16 enter =D16*(1+$B$12). Drag down. In B17 enter = E16. Drag down. Finally, in D16 enter = B16 + C16. Drag down. 3. It looks like we're in trouble. In C29 calculate the NPV of our "project" by entering =C16+NPV(B12,C17:C27). In C30, calculate the FV of NPV by entering =C29*(1+B12)^12. What do you conclude? 4. As in real life, something's gotta give. Either we need to increase the interest rate (by taking on more risk) or increase the amount we invest each year. Because we entered formulas you can see what happens to the table and the NPV by changing B13 or B12 or both. For now, adjust B13 (saving more) to approach NPV = 0. Given 8% interest, you need to deposit about $6227.78 each year to reach zero NPV. Birthday Time Cashflows 10 0 $6,227.78 M N O P Q R S T 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 11 12 13 14 15 16 17 1 2 3 4 5 6 7 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 18 8 0 ($20,000.00) U V W X 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 19 20 21 9 10 11 1 2 3 ($20,000.00) ($20,000.00) ($20,000.00) 22 12 4 A C SAVING FOR COLLEGE Using PV and PMT functions 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 B Interest rate Linda's age today Age at starting college Years of college 8% 10 18 4 Annual cost of college 20,000 PV of college cost at 18th birthday Annual payment You don't need to build a table. Many fun Excel to provide you the answers quickly. GOALS: 1. Use PV (present value) and PMT (paym answers. 2. Use a compound function (a function in ($71,541.94) =PV(B2,B5,-B7,,1) $6,227.78 =PMT(B2,B4-B3,,B9,1) $6,227.78 =PMT(B2,B4-B3,,PV(B2,B5,-B7,,1),1) 1. What will be the present value (8 years in the future, at 18th birthday) of $80,000 college costs? In B9 enter =PV(B2,B5,-B7,,1). NOTE: PMT is a cash outflow (-), and "type" is a 0 for end of year or 1 (in our case) at the beginning of the year. In other words, we pay for a year of college at the beginning of each academic year. 2. How much do we need to invest each year to cover college costs? In B10 enter =PMT(B2,B4B3,,B9,1). NOTE: our "PV" in B9 is actually a future value (8 years from now) if we stands on 10th birthday, hence, B9 goes into the FV argument in the PMT formula. 4. We can use a compound function to arrive at our annual investment answer in one step (as opposed to first calculating PV and then PMT). In B11 enter =PMT(B2,B4-B3,,PV(B2,B5,-B7,,1),1). NOTE: By now you should see that the formula is prompting you for the correct cells...in other words you don't have to memorize formula, just let the formula box walk you through the problem! PMT function is also used a lot when calculating the payoff schedule for loans. P44, 2.6 D E F on't need to build a table. Many functions are provided with 1 answers quickly. to provide you the S: 2 PV (present value) and PMT (payment) functions to arrive at 3 ers. a compound function (a function inside a function) 4 5 6 7 8 $38,651.88 =PV(B2,B4-B3,,B9) 9 PV of college at 10 ($6,227.78) =PMT(B2,B4-B3,E9,,1) 10 ($6,227.78) =PMT(B2,B4-B3,-PV(B2,B4-B3,,PV(B2,B5,-B7,,1)),,1) 11 12 invest each year to 3. We can also first calculate the value of college cost on 10th 13 enter =PMT(B2,B4birhtday. In E9 enter =-PV(B2,B4-B3,,B9). Then calcualte annual B9 is actually a14 deposit we need. In E10, enter =PMT(B2,B4-B3,E9,,1). Now our ow) if we stands on "PV" in E9 is a present value if we stands on 10th birthday, hence, es into the FV 15 E9 goes into the PV argument in the PMT formula. 16 la. 17 18 19 20 21 22 23 24 25 26 27 28 G H I J K L M N 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Birthday Time Cashflows Break Down: Phase 1, Deposit 10 11 12 13 14 15 0 1 2 3 4 5 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 0 1 2 3 4 5 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 $6,227.78 Phase 2, Withdraw PV on 10th ($38,651.88) O P Q R S T U 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 16 17 21 22 6 7 8 9 10 11 $6,227.78 $6,227.78 ($20,000.00) ($20,000.00) ($20,000.00) ($20,000.00) 12 6 7 $6,227.78 $6,227.78 18 19 20 8 0 1 2 3 ($20,000.00) ($20,000.00) ($20,000.00) ($20,000.00) PV on 18th $71,541.94 4 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B C D USING NPV TO CHOOSE BETWEEN INVESTMENTS Discount rate Year 0 1 2 3 4 5 NPV 5% Investment A Investment B -800 -800 250 600 500 200 200 100 250 500 550 300 1750 1700 700.99 685.63 =NPV(B2,C6:C10)+C5 1. Investments are projects with projected (best guess) cash flows 2. Firms will choose projects with the highest NPV (or at least > 0) 3. Compare projects with NPV 4. Initial cash flow is negative because you're making an investment A B C 1 CALCULATING THE IRR WITH EXCEL 2 r, interest rate 3 Year 4 0 5 1 6 2 7 3 8 4 9 5 10 11 NPV 12 IRR 13 14 NPV if IRR Discount 15 rate 0% 16 1% 17 2% 18 3% 19 4% 20 5% 21 6% 22 7% 23 8% 24 9% 25 10% 26 11% 27 12% 28 13% 29 14% 30 15% 31 16% 32 8.0000% Payment -800 100 150 200 250 300 200 -32.11 =NPV(B2,B6:B10)+B5 6.6965% =IRR(B5:B10) 0.00 NPV 200.00 165.86 133.36 102.41 72.92 44.79 17.96 -7.65 -32.11 -55.48 -77.83 -99.21 -119.67 -139.26 -158.04 -176.03 -193.28 NPV and the Discount Rate 250 200 150 100 50 NPV 0 -50 0% -1% -100 -150 -200 -250 2% 1% 4% 3% 6% 5% Discount rate 8% 7% 10% 9% 12% 11% 14% 13% 15 D E F G 1 EXCEL E IRR WITH 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 NPV 17 and the Discount Rate 250 200 150 100 50 0 -50 0% 1% 100 150 200 250 2% 1% 18 19 1. Find NPV of project changing interest rates; profits are sensitive to int rates! 20 2. Define IRR; rate of return for a project if NPV = 0 21 3. Use IRR function 22 4. Show IRR = NPV = 0 discount rate 23 24 4% 6% 8% 10% 12% 14% 16% 25 3% 5% 7% 9% 11% 13% 15% 26 Discount rate 27 28 29 30 31 32 H HOMEWORK 2, Financial Modeling 3103, Fall 2017 DUE Monday 09/25/17 at 11:59 p.m. ***Late Submissions will be penalized one point per day late.*** Today's deposit Interest rate Number of Years Future value: Use future value formula: Use FV() function: $750.00 12% 18 $5,767.47 $5,767.47 Find the future value of $750 deposited today, earning 12% per year for 18 years. Please calculate the future value both by mannually entering the formula, as in "FV Intro", and by using FV() function, as in "FV Multiply Cash Flows". osited today, earning calculate the future the formula, as in tion, as in "FV Interest rate 6% Year 1 2 3 4 Gift at the end of each year $10,000.00 $10,000.00 $10,000.00 $10,000.00 Present value using SUM Present value using PV() $ $ $ $ Present value 9,433.96 8,899.96 8,396.19 7,920.94 $ 34,651.06 ($34,651.06) You will receive four annual gifts from your investment, each gift is $10,000 and will be received at the end of each year. 1. Calculate the present value of each annual gift. 2. Calculate the present value of all gifts by summing up the annual present values of each gift. 3. Calculate the present value of all gifts directly by using the PV function. ifts from your investment, be received at the end of each e of each annual gift. e of all gifts by summing up the h gift. e of all gifts directly by using Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 IRR Cashflows ($3,000.00) $100.00 1. You invest $3,000 in a project. You expect a net revenue of $100 for the first year, with net revenues to grow at 4% per year through year 15. Calculate and fill in the cash flows through year 15. 2. Format the cells to be currency with $ symbol and 2 decimal places. 3. Calculate the IRR of this project. Would you take this project? Why or why not? WRITE YOUR EXPLANATION HERE: net revenue o grow at 4% the cash bol and 2 take this ANATION SAVING FOR COLLEGE We need to save for our child's expected college costs. We have cash flow restrictions, so need to save/invest now so as to NOT hav money when she reaches 18. Each deposit happens at the day of each birthday (or you can treat it as the beginning of each year), s till 18th birthday when we start to pay for the college (no deposit at the 18th birthday, i.e. the final deposit is on 17th birthday). W investments to earn 8% over the next several years. The annual college cost is $20,000 for four years, at the beginning of each year 1. If we deposit $4,000 each year, how much money will be left in your accout or be owned to the bank at the 22th birthday, given a 10% interest rate your bank charges on your borrowing (i.e interest rate is 8% if your positive, and interest rate is 10% if your balance is negative). Answer: Interest rate earned on investment Interest rate charged on borrowing Annual deposit Annual cost of college Birthday 10 11 12 13 14 15 16 17 18 19 20 21 8% 10% 4,000.00 20,000.00 Account balance on birthday, before deposit/withdrawal Deposit or withdrawal on each birthday/at beginning of year 0.00 4,320.00 8,985.60 14,024.45 19,466.40 25,343.72 31,691.21 38,546.51 45,950.23 28,026.25 8,668.35 (12,464.82) 4,000.00 4,000.00 4,000.00 4,000.00 4,000.00 4,000.00 4,000.00 4,000.00 (20,000.00) (20,000.00) (20,000.00) (20,000.00) Account balance, after deposit/withdrawal, at the beg. of year 4,000.00 8,320.00 12,985.60 18,024.45 23,466.40 29,343.72 35,691.21 42,546.51 25,950.23 8,026.25 (11,331.65) (32,464.82) 2. If we want to be fully funded by our own deposit (final balance of our account just equal to 0), how much we put into the bank each year? Now we assume that interest rate is always 8% no matter wehther your bala positive or not. Answer: Interest rate earned on investment Years of deposit Annual deposit Years of college Annual cost of college 8% 8 4 20,000.00 Fill out the table and use Date|Solver to solve the problem Birthday Account balance on birthday, before deposit/withdrawal Deposit or withdrawal on each birthday/at beginning of year Account balance, after deposit/withdrawal, at the beg. of year 10 11 12 13 14 15 16 17 18 19 20 21 Solve the problem using PV() and PMT() PV of college cost at 18th birthday Annual deposit 3. Optional question. This question is comprehensive and complicated. Please try your best. If we want to b by our own deposit and we've already have $5,000 in our account on 10th birthday, how much money should the bank each year? Assume interest rate is always 8%. Interest rate earned on investment Years of deposit Initial Balance Annual deposit Years of college Annual cost of college 8% 8 5,000.00 4 -20,000.00 Fill out the table and use Data|Solver to solve the problem Birthday Account balance on birthday, before deposit/withdrawal 10 11 12 13 14 15 16 17 18 19 20 21 Solve the problem using PV and PMT Deposit or withdrawal on each birthday/at beginning of year Account balance, after deposit/withdrawal, at the beg. of year (1) One way is let PV of college costs on 10th birthday = PV of annual deposit on 10th birthday + Initial $5,000 asset PV of college cost on 18th birthday use PV() to calculate the present value of college cost on 18th birt birthday PV of college cost on 10th birthday use PV() and the present value of college cost on 18th birthday (yo on 10th birthday, i.e. what is the present value of all future cash ou Present Value of annual deposit needed on 10th birthday PV of college costs on 10th birthday = PV of annual deposit on 10 deposit we need on the 10th birthday, given that we've already kno known we have an inital 5,000 asset on 10th birthday Annual deposit use PMT() to calculate the annual deposit, given that we've calcula B76 (2) Another way is let PV of college cost on 18th birthday = FV of annual deposit on 18th birthday + FV of Initial $5,000 PV of college cost on 18th birthday use PV() to calculate the present value of college cost on 18th birt birthday FV of initial $5,000 on 18th birthday use FV() to calculate the future value of our initial 5,000 asset on 1 Future Value of annual deposit needed on 18th birthday PV of college cost on 18th birthday = FV of annual deposit on 18th value of annual deposit we need on the 18th birthday, given that w 18th birthday, and known the future value of our initial asset on 18 Annual deposit use PMT() to calculate the annual deposit, given that we've calcula cell,B81 (3) The third way just uses PMT() function. By entering the correct PV and FV argument for this function, we can quick PV of college cost on 18th birthday use PV() to calculate the present value of college cost on 18th birt birthday Annual deposit use PMT() to calculate the annual deposit, given that we've calcula cell,B85, and we know the value of our initial asset on 10th birthda in FV argument should be different with each other. The one in PV represent the value of what we are going to withdraw from out acc It is highly recommended that you first draw a timeline (as what we did in class), break down the question, and figure o The main idea of this question is to fully fund your future college cost, in another word, make the value of all your cash (your college costs or liabilities). Please remember that present value and future value are relatively speaking, depend then you should calculate the present value of your future college cost on 10th birthday, and the present value of your 10th birthday equal to the sum total of the present value of deposit on 10th birthday and your initial 5,000 assets at 10t present value of your future college cost on 18th birthday, and the future value of your annual deposit and inital 5,000 equal to the sum total of the future value of annual deposit and the future value of initial assets on 18th birthday. ave/invest now so as to NOT have to borrow lots of as the beginning of each year), since 10th birthday, deposit is on 17th birthday). We expect our rs, at the beginning of each year. e owned to the bank at the beginning of interest rate is 8% if your balance is Account balance, End of year balance, with interest 4,320.00 =IF(D14>=0,D14*(1+$B$9),D14*(1+$B$10)) 8,985.60 Hint: use if() function to take care of the different interest 14,024.45 rate given wheter your account balance at the beg. of each 19,466.40 year is positive or negative. I've already enter the formula 25,343.72 in the cell of the first year, but please make sure you understand why we use if() and how to solve this situation 31,691.21 by yourself. 38,546.51 45,950.23 28,026.25 8,668.35 (12,464.82) (35,711.30) ust equal to 0), how much money should o matter wehther your balance is Account balance, End of year balance, with interest your best. If we want to be fully funded y, how much money should we put into Account balance, End of year balance, with interest hday + Initial $5,000 asset alue of college cost on 18th birthday, i.e. discount all future cash outflows for college cost to the time point of 18th ollege cost on 18th birthday (you've calculated in the upper cell, B74) to calculate the present value of college cost sent value of all future cash outflows for college cost at the very beginning, the 10th birthday y = PV of annual deposit on 10th birthday + Initial $5,000 asset, therefore, what is the present value of annual ay, given that we've already known the present value of future cash outflows for college cost on 10th birthday, and t on 10th birthday deposit, given that we've calculated the present value of these deposit we need on 10th birthday in the upper cell, irthday + FV of Initial $5,000 asset on 18th birthday alue of college cost on 18th birthday, i.e. discount all future cash outflows for college cost to the time point of 18th ue of our initial 5,000 asset on 18th birthday, i.e. compound our initial assets to the time point of 18th birthday = FV of annual deposit on 18th birthday + FV of Initial $5,000 asset on 18th birthday, therefore, what is the future the 18th birthday, given that we've already known the present value of future cash outflows for college cost on value of our initial asset on 18th birthday deposit, given that we've calculated the future value of these deposit we need on 18th birthday in the upper r this function, we can quickly solve the problem. alue of college cost on 18th birthday, i.e. discount all future cash outflows for college cost to the time point of 18th deposit, given that we've calculated the future value of these deposit we need on 18th birthday in the upper our initial asset on 10th birthday.Notice that the sign of the cashflow in PV argument and the sign of the cashflow with each other. The one in PV represent the value of what we initially deposit into our account and the one in FV going to withdraw from out account. wn the question, and figure out how to solve this problem without a table. ake the value of all your cash inflows (your deposit or assets) equals to the value of all your cash outflows e relatively speaking, depending on the time point you're standing at. If you're standing at 10th birthday, nd the present value of your future annual deposit on 10th birthday, and let the present value of cost on our initial 5,000 assets at 10th birthday. If you're standing at 18th birthday, then you should calculate the nual deposit and inital 5,000 asset on 18th birthday, and let the present value of cost on 18th birthday ssets on 18th birthday