B10 fr A B E F G 1 Annuity 1 2 3 Future Value 2 3 Inputs 4 Payment $ 80.00 5 Discount Rate/Period 6% 6 Number of Periods 5 7 8 Annuity Future Value using a Time Line 9 Period 0 10 Cash Flows 11 Future Value of Each Cash Flow 12 Present Value 13 14 Annuity Future Value using the Formula 15 Future Value $ 450.97 16 17 Annuity Future Value using the FV Function 18 Future Value (S450.97) 19 20 $450.97 Single-FV Annuity-FV Single-PV Annuity-PV Loan + An annuity pays $80.00 each period for 5 periods. For these cash flows, the appropriate discount rate/period is 6.0%. What is the period 5 future value? Use worksheet Annuity-FV". 1. This problem is like the Single Cash Flow, except this time, you will have a cash flow cach period except for Period 0. 2. Select the Annuity-FV worksheet. 3. First , input the given data. Putting the data in the cells B4, B5, & B6 allows us to make changes later when circumstances change. 1. The Payment is $80.00 so type that in cell B4. 2. The Discount Rate/Period is 6.0% so type 0.06 in cell B5. Note that you should change the cell type to percentage. 3. The number of periods is 5 so input that in cell B6. 4. Next, work on the Future Value Using a Timeline. 1. Type 0 in cell B9 2. Type 1 in cell C9 3. Select cells B9 and 09. 4. Click on the filled in box in the right corner of C9 and drag across to cell G9. In cell D9, you will see 2. In cell E9, you will see 3. In cell F9, you will see 4. In cell G9, you will see 5. Alternatively, you can type those values into the correct cells. 5. Next, input the cash flow into each period. Annuity means that you have the cash flow in all periods except for Period 0. 1. Note that you use an absolute cell reference since you are copying this formula. 6. Next, create the Future Value of Each Cash Flow formula in cell Bi. 1. Select cell B11 2. You will need to copy this formula to cells CIT-GII, so you need to know if a value will change or not. If you need it to change, then, you use a relative cell reference. If it should not change, then you use an absolute cell reference. Note that this is the same formula that you used in the Single Cash Flow. When you create a Future Value for each cash flow, it is the same no matter when you have cash flow. 3. Formula for Future Value of Each Cash Flow 1. Text formula 1. Equals (Current Cash Flow)*(1+(Discount Rate/Period)) ((Number of Periods)-(Current Period)) 1. (Cash Flow) in a timeline always means the Current Cash Flow. The formula can be written 2. (Period) in a timeline always means the Current Period. The formula can be written either way. 2. (Current Cash Flow) is the cash flow in this period (which changes), so use cell B10. (Discount Rate/Period) is in cell B5, but this should not change as you copy the formula. Therefore, use SB$5. (Number of Periods) is in cell B6, but once again as you copy the formula, it shouldn't change. Therefore, use $B$6. (Current Period) is in cell B9, but this should change as you copy the formula. 2. Type -B10*(1+SB$5)^(SBS6-39) 3. Press Return/Enter 7. Copy cell B11 to cells C11 to GII. 8. The final answer involves summing the cells B11 through G11. This answer should be in cell G12 (the cell with the yellow background) 1. Click the Autosum (S) button on the Home tab, 2. Ensure that cells B10 through G12 are in S. 3. The answer is $450.97 9. Click the floppy disk icon in the navigation bar to save the file. 5. Create the Annuity Future Value formula in cell B15. 1. Select cell B15 1. You will not copy this formula, so you can use either relative or absolute cell reference. 2. Text formula either way 1. Equals (Payment) * (((1 + (Discount Rate/Period)) (Number of Periods)) - 1) / (Discount Rate/Period) 1. (Payment) is the single cash flow, so use cell B4. (Discount Rate/Period) is in cell BS. (Number of Periods) is in cell B6, so use B6. 2. Type -B4*