1 / 27

Using Financial Functions in Excel or a TI-83 to Solve TVM Problems

Note: This PowerPoint presentation is under construction. Currently, this only shows how to do this in Excel and we intend to make improvements in that presentation. Then we will add slides to explain how to use the TI-83 to do the same.

lavey
Download Presentation

Using Financial Functions in Excel or a TI-83 to Solve TVM Problems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Note: This PowerPoint presentation is under construction. Currently, this only shows how to do this in Excel and we intend to make improvements in that presentation. Then we will add slides to explain how to use the TI-83 to do the same. Using Financial Functions in Excel or a TI-83 to Solve TVM Problems This explains how to use the Excel Finite Functions to solve Time Value of Money Problems related to annuities and bonds (Press <F5> to run Slide Show)

  2. How to use the annuity formula to determine the PV of an ordinary annuity. If we determined this using the annuity formula, we would have determined this as shown below: Problem: Consider an annuity where you are paid $100 at the end of each year for ten years. Assuming that we discount at a rate of 12%, compounded monthly, determine the present value of this annuity.

  3. How to use Excel Spreadsheet to determine the PV of an ordinary annuity To determine this in Excel, click on the paste function symbol. Problem: Consider an annuity where you are paid $100 at the end of each year for ten years. Assuming that we discount at a rate of 12%, compounded monthly, determine the present value of this annuity.

  4. Next, click to select the Financial category. Then click on the PV function Finally, click on the OK button. This will open the Insert Function Window.

  5. First, type in the rate per compound period. Second, type in the number of payments. Third, type in the amount of each payment. Either leave FV blank or type in 0 since there is no balloon-type of payment at the end of the annuity. Either leave type blank or type in 1 since this is an ordinary annuity as opposed to an annuity due. If you then click on OK, Excel will put the result and the formula in the current cell in the Excel spreadsheet. This is the function. This shows the result in the current cell. Excel Shows the answer as a negative number since that is the cash outlay you would incur now in order to be able to buy the annuity. This then brings up the PV function window. 1% 120 100

  6. Step 1: Enter the interest rate. Step 2: Enter the number of payment periods (Nper). Step 3: Enter the amount of the annuity payment. Answer in positive terms We do not have a FV at this point so we leave FV blank or enter a 0. We will do the example again, except we will put in cell references instead of numbers. Also, we will put the payment as a negative number instead of positive. This will make the PV a positive number.

  7. The screen shown here is where we need to begin. This is where we input our variables to solve for the PV of an Annuity. 1. Press the “2nd” button 2. Press the “x-1” button • Answer: • The number of payments made (N). 2. The corresponding interest rate (I%). 3. The amount of the payment being made (PMT). Things to note: • Be sure to enter your interest rate as a whole number. • Make sure PV= and FV= are set at 0. • Make sure P/Y= and C/Y= are set at 1. • Select the proper Annuity. For this problem, END should be highlighted. To arrive at this screen: This opens the FINANCE functions. To enter the above screen, we choose: 1:TVM Solver… by highlighting it and pressing enter. We can now input our variables. Question: To solve for the PV of an Annuity, what variables do we need?

  8. 1. Press the “2nd” button 2. Press the “x-1” button This is how it would look on your TI-83 screen. After you have entered your N, I%, and PMT: This will bring you back to the FINANCE functions menu. 3. On the menu, select: 4: tvm_PV and press enter. 4. Press enter once more to get the PV of an annuity. SHORTCUT: If all your defaults are set (N=0, I%=0, PV=0, PMT=0, FV=0, P/Y=1, C/Y=1, PMT: END) Then you can skip the TVM Solver… steps and go right into the tvm_PV function. When you get tvm_PV on your screen you can enter the N, I% and PMT in parentheses and press enter to get the same answer. Example: tvm_PV(120, 1, 100)

  9. How to use the annuity formula to determine the FV of an ordinary annuity. Problem: Consider an annuity where you are paid $100 at the end of each year for ten years. Assuming that we discount at a rate of 12%, compounded monthly, determine the future value of this annuity. If we determined this using the annuity formula, we would have determined this as shown below:

  10. To determine this in Excel, click on the paste function symbol. How to use Excel Spreadsheet to determine the FV of an ordinary annuity Problem: Consider an annuity where you are paid $100 at the end of each year for ten years. Assuming that we discount at a rate of 12%, compounded monthly, determine the future value of this annuity.

  11. Select the Financial Category Select the FV function Click OK This will open the Insert Function Window This will open the FV function window.

  12. Determining Future Value Step 1: Enter the interest rate. Step 2: Enter the number of payment periods (Nper). Step 3: Enter the amount of the annuity payment. Answer We do not have a PV at this point so we leave PV blank or enter a 0. Same drill as PV...

  13. Determining Payment in PV Problem Select the Financial Category Highlight the PMT function Click OK

  14. Determining Payment in PV Problem Enter the interest rate Enter the number of periods We calculated the PV in our 1st example. If you are not given a PV then you will have to calculate like in the 1st example because PV is required to find the payment. Answer

  15. Determining Payment in FV Problem

  16. Determining # Payments in FV Problem

  17. Determining # Payments in PV Problem

  18. Determining # Payments in FV Problem

  19. Determining Rate in PV Problem

  20. Determining Rate in PV Problem To get the stated annual rate, you would need to multiply the 1% by the number of compound periods per year:

  21. Determining Rate in FV Problem

  22. PV(annuity Due) Problem: Consider an annuity where you are paid $100 at the beginning of each year for ten years. Assuming that we discount at a rate of 12%, compounded monthly, determine the present value of this annuity.

  23. PV(bond) Problem: Consider a $1000 bond with a 6% coupon rate, paid semiannually that matures 14 years from now. Assuming that similar bonds now pay 8% interest, compounded semiannually, determine what should be the value of this bond today. = 499.89 + 333.48 = $833.37

  24. PV(bond)

  25. Yield to Maturity 4%*2 coupon periods per year = 8%

  26. Determine # periods in Bond Problem

  27. Internal Rate of Return (IRR)

More Related