show formulas

fixed figures

referred to the table to answer the spreadsheet. interest rate is shown on this spreadsheet

You don't need to build a table. Many functions are provided with Excel to provide you the answers quickly. GOALS: 1. Use PV (present value) and PMT (payment) functions to arrive at answers 2. Use a compound function (a function inside a function) 1. What will be the present value (8 years in the future) of $80,000 in projected college costs? In B9 enter PV[B2,B5,-87.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. SAVING FOR COLLEGE Interest rate 3 Linda's age today 4 Age at starting college 5 Years of college 6 7 Annual cost of college 8 9 PV of college at 18 =P(B2,B5,-87.,1) 10 Annual payment =PMT(B2,B4-83, -89,1) 11 2. How much do we need to invest each 12 year to cover college costs? In 310 enter 13 =PMT(B2,84-83.-89.1). NOTE: Our PV 14 is actually a future value (8 years from 15 now], hence, it goes in the FV space in the PMT formula 16 17 18 19 SAVING FOR COLLEGE 20 Interest rate 21 Linda's age today 22 Age at starting college 23 Years of college 24 25 Annual cost of college 26 27 Annual payment -PMT(820,822-321..PV1820,823,B25,1),1) 28 29 30 31 32 33 3. 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 B27 enter PMT(B20,822 B21.,PV1820,823,825.11.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 formulae, just let the formula box walk you through the problemi You don't need to build a table. Many functions are provided with Excel to provide you the answers quickly. SAVING FOR COLLEGE Interest rate Linda's age today Age at starting college Years of college GOALS: 1. Use PV (present value) and PMT (payment) functions to arrive at answers. 2. Use a compound function (a function inside a function) Annual cost of college 1. What will be the present value (8 years in the future) of $80,000 in projected college costs? In B9 enter PV(B2,B5,-87,.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. PV of college at 18 -PV(B2,B5,-87.1) Annual payment PMT(82,B4-B3, -89,1) 1 2. How much do we need to invest each 2 year to cover college costs? In B10 enter 3 =PMT(B2,B4-B3-B9,1). NOTE: Our "PV 4 is actually a future value (8 years from S now), hence, it goes in the FV space in the PMT formula. 6 .7 18 19 SAVING FOR COLLEGE 20 Interest rate R1 Linda's age today 22 Age at starting college 23 Years of college 24 25 Annual cost of college 26 27 Annual payment -PMT(820,822-B21,,PV1820,823,825,1), 1) 28 29 30 31 32 33 linductione Collane nn tahle 3. 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 B27 enter PMT(820,822 B21,,PV(820,823,825,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 formulae, just let the formula box walk you through the problemi 2 SAVING FOR COLLEGE 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 similar to 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 she reaches 18. We expect our investments to earn 8% over the next several years. The annual college cost is $20,000 for four years. After that, it's her problem. 4 5 6 7 GOALS 8 1. Understand why NPV must=0 9 2. Use NPV function 10 3. If underfunded, play with annual investment to approach NPV = 0. 11 12 Interest rate 8% 13 Annual deposit 6.227.78 14 Annual cost of college 20,000.00 1. In C16 enter $8$13 and drag down to C23. In C24 enter-SB$14 and drag down to C27. Why the negative? 2. In B17 enter-E16. Drag down. In D16 enter-B16 +C16. Drag down. Finally, in E16 enter 016*(1+$B$12). Drag down. Birthday 10 11 12 13 14 15 16 17 In bank on birthday. Deposit or withdrawal before deposit withdrawal at beginning of year 0.00 6,227.78 6,726.00 6,227.78 13,990.08 6,227.78 21,835.28 6,227.78 30,308.10 6,227.78 39,458.75 6.227.78 49,341.45 6.227.78 60,014.76 6,227.78 71,541.94 -20,000.00 55,665.29 -20,000.00 38,518.52 -20,000.00 20,000.00 -20,000.00 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Total 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 End of year with interest 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 0.00 3. It looks like we're in trouble. In C29 calculate the NPV of our project by entering -C16+NPV(B12,017:C27). What do you conclude? 19 20 21 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 313 (saving more) to approach NPV = 0. NPV of all payments =C16+NPV(B12,C17:027) 5. We can use SOLVER to find an exact amount we need to invest. Click on C29. Go to the Data tab, then click Solver. A box will open up. Set Objective is the cell you wish to "force" to something else. Set to "O by chaning parameters B13 (how much we invest). Click solve. Voila. The annual deposit amount will change to get to an NPV of You don't need to build a table. Many functions are provided with Excel to provide you the answers quickly. GOALS: 1. Use PV (present value) and PMT (payment) functions to arrive at answers 2. Use a compound function (a function inside a function) 1. What will be the present value (8 years in the future) of $80,000 in projected college costs? In B9 enter PV[B2,B5,-87.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. SAVING FOR COLLEGE Interest rate 3 Linda's age today 4 Age at starting college 5 Years of college 6 7 Annual cost of college 8 9 PV of college at 18 =P(B2,B5,-87.,1) 10 Annual payment =PMT(B2,B4-83, -89,1) 11 2. How much do we need to invest each 12 year to cover college costs? In 310 enter 13 =PMT(B2,84-83.-89.1). NOTE: Our PV 14 is actually a future value (8 years from 15 now], hence, it goes in the FV space in the PMT formula 16 17 18 19 SAVING FOR COLLEGE 20 Interest rate 21 Linda's age today 22 Age at starting college 23 Years of college 24 25 Annual cost of college 26 27 Annual payment -PMT(820,822-321..PV1820,823,B25,1),1) 28 29 30 31 32 33 3. 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 B27 enter PMT(B20,822 B21.,PV1820,823,825.11.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 formulae, just let the formula box walk you through the problemi You don't need to build a table. Many functions are provided with Excel to provide you the answers quickly. SAVING FOR COLLEGE Interest rate Linda's age today Age at starting college Years of college GOALS: 1. Use PV (present value) and PMT (payment) functions to arrive at answers. 2. Use a compound function (a function inside a function) Annual cost of college 1. What will be the present value (8 years in the future) of $80,000 in projected college costs? In B9 enter PV(B2,B5,-87,.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. PV of college at 18 -PV(B2,B5,-87.1) Annual payment PMT(82,B4-B3, -89,1) 1 2. How much do we need to invest each 2 year to cover college costs? In B10 enter 3 =PMT(B2,B4-B3-B9,1). NOTE: Our "PV 4 is actually a future value (8 years from S now), hence, it goes in the FV space in the PMT formula. 6 .7 18 19 SAVING FOR COLLEGE 20 Interest rate R1 Linda's age today 22 Age at starting college 23 Years of college 24 25 Annual cost of college 26 27 Annual payment -PMT(820,822-B21,,PV1820,823,825,1), 1) 28 29 30 31 32 33 linductione Collane nn tahle 3. 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 B27 enter PMT(820,822 B21,,PV(820,823,825,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 formulae, just let the formula box walk you through the problemi 2 SAVING FOR COLLEGE 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 similar to 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 she reaches 18. We expect our investments to earn 8% over the next several years. The annual college cost is $20,000 for four years. After that, it's her problem. 4 5 6 7 GOALS 8 1. Understand why NPV must=0 9 2. Use NPV function 10 3. If underfunded, play with annual investment to approach NPV = 0. 11 12 Interest rate 8% 13 Annual deposit 6.227.78 14 Annual cost of college 20,000.00 1. In C16 enter $8$13 and drag down to C23. In C24 enter-SB$14 and drag down to C27. Why the negative? 2. In B17 enter-E16. Drag down. In D16 enter-B16 +C16. Drag down. Finally, in E16 enter 016*(1+$B$12). Drag down. Birthday 10 11 12 13 14 15 16 17 In bank on birthday. Deposit or withdrawal before deposit withdrawal at beginning of year 0.00 6,227.78 6,726.00 6,227.78 13,990.08 6,227.78 21,835.28 6,227.78 30,308.10 6,227.78 39,458.75 6.227.78 49,341.45 6.227.78 60,014.76 6,227.78 71,541.94 -20,000.00 55,665.29 -20,000.00 38,518.52 -20,000.00 20,000.00 -20,000.00 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Total 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 End of year with interest 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 0.00 3. It looks like we're in trouble. In C29 calculate the NPV of our project by entering -C16+NPV(B12,017:C27). What do you conclude? 19 20 21 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 313 (saving more) to approach NPV = 0. NPV of all payments =C16+NPV(B12,C17:027) 5. We can use SOLVER to find an exact amount we need to invest. Click on C29. Go to the Data tab, then click Solver. A box will open up. Set Objective is the cell you wish to "force" to something else. Set to "O by chaning parameters B13 (how much we invest). Click solve. Voila. The annual deposit amount will change to get to an NPV of