Excelexpl

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 to run Slide Show)

How to use the annuity formula to determine the PV of an ordinary annuity. RATE= NPER= PMT= PV= FV=

1% 120 $100 ? 0

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.

If we determined this using the annuity formula, we would have determined this as shown below:

C 1   PV(ordinary annuity) = 1 − n  r  (1 + r)  12% r= = 1% = .01 12 100  1  n = 12 *10 = 120

= 10,000( .697 ) = $6970.05 1 − 120  .01  1.01 

How to use Excel Spreadsheet to determine the PV of an ordinary annuity RATE= NPER= PMT= PV= FV=

1% 120 $100 ? 0

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.

To determine this in Excel, click on the paste function symbol.

This will open the Insert Function Window.

Next, click to select the Financial category.

Then click on the PV function

Finally, click on the OK button.

This then brings up the PV function window.

1% 120 100

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

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.

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.

The screen shown here is where we need to begin. This is where we input To our arrive variables to solve for the PV of an Annuity. at this screen:

1. Press the “2nd” button 2. Press the “x-1” button 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? Answer: 2. The number of payments made (N). 2. The corresponding interest rate (I%). 3. The amount of the payment being made (PMT).

Things to note: 1. 2. 3. 4.

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.

After you have entered your N, I%, and PMT: 1. Press the “2nd” button 2. Press the “x-1” button 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) This is how it would look on your TI-83 screen.

How to use the annuity formula to determine the FV of an ordinary annuity. RATE= NPER= PMT= PV= FV=

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.

1% 120 $100 ? 0

If we determined this using the annuity formula, we would have determined this as shown below:

FV(ordinary annuity) = 12% r= = 1% = .01 12 n = 12 *10 = 120

[

]

[

]

C (1 + r ) n − 1 r

100 (1 + .01)120 − 1 = 10,000( 2.30 ) = $23,003.87 .01

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. To determine this in Excel, click on the paste function symbol.

This will open the Insert Function Window

Select the Financial Category Select the FV function

Click OK This will open the FV function window.

Determining Future Value Same drill as PV...

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.

Determining Payment in PV Problem

Select the Financial Category

Highlight the PMT function

Click OK

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

Determining Payment in FV Problem

Determining # Payments in FV Problem

Determining # Payments in PV Problem

Determining # Payments in FV Problem

Determining Rate in PV Problem

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:

1% *12 = 12%

Determining Rate in FV Problem

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.

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.

C 1  F  PV (bond ) = 1 − + r  (1 + r ) n  (1 + r ) n

30  1  1000  1 − + PV (bond ) = 28  .04  (1.04)  (1.04) 28 = 499.89 + 333.48 = $833.37

PV(bond)

Yield to Maturity

4%*2 coupon periods per year = 8%

Determine # periods in Bond Problem

Internal Rate of Return (IRR)