Excelexpl
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Excelexpl as PDF for free.
More details
- Words: 1,315
- Pages: 27
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)
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
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)
Related Documents
Excelexpl
December 2019 5More Documents from "hillmann"
Excelexpl
December 2019 5
Financing International Project
December 2019 15
A2008!1!1744073.chapter0 Introduction
April 2020 7
Orm6003 Foundations Of Leadership Week 3
April 2020 8
Mba Prep Summer Tech
December 2019 8