If an amount PV is invested and earns interest at an annual rate r compounded (reinvested) m times per year for a period of t years, then the future value (FV) is given by the formula FV = PV(1 + r/m)^(mt) We can use this formula together with some algebra to solve for PV, t, and r. (See the next sheet for use of built-in formulas.) FV = PV(1 + r/m)^(mt). PV = FV/(1 + r/m)^(mt). t = LN(FV/PV)/(m*LN(1+r/m)) r = m*((FV/PV)^(1/(m*t)) - 1) Formula for FV Formula for PV Formula for t Formula for r See the next worksheet for built-in Excel formulas.
As with the tutorial for simple interest, we can now set up a little "Time value of Money" (TVM) calculator that gives us any one of these quantities when the others are known. To set it up, let us use the data from Example 1 in the text: FV = ? Unknown PV = 2000 Given r = 4.75% Given m=4 Given t=6 Given Set it up to compute the future value (FV) by using the formula for FV here. (You did it right if you get a value of $2,570.00 ). Entered Calculated 4.75% 6 $2,000.00 4 We will not compute a formula to calculate m.
1.
Rate Years Present Value Future Value Periods/yr
r t PV FV m
Given values go here. Note: Format the PV and FV cells as currency, the r cells as percentage, and the t and m cells as "general". 2. Now fill in the remaining three formulas in the "Calculated" column. Let us now use the calculator to do Example 3 from the text: 3. Example You have $5000 to invest at 6% compounded monthly. How long will it take for your investment to grow to $6000? Solution We are given PV = $5000, FV = $6000, r = 6%, and m = 12. Enter these values in the "Entered" Column and read off the number of years from the "Calculated" column. That is the answer! Zero-Coupon Bonds 4. Look at Example 2 from the text: The Megabucks Corporation is issuing 10-year zero-coupon bonds. How much would you pay for bonds with a maturity value of $10,000 if you wish to get a return of 6.5% compounded annually.
Solution The maturity value is the future value, so FV = $10,000. So we enter t = 10, r = 6.5%, and m = 1 in the "Entered" column, and read off the present value (PV). Now go to the "Excel Formulas" sheet to see how to set up a "time value of money" calculator using Excel formulas.
per year for a
use of built-in formulas.)
worksheet for built-in Excel formulas.
mpute a formula to calculate m.
d m cells as "general".
"Calculated" column.
Excel formulas.
x 2 2 2 1 y t s x 0 0 0 1 0
y 1 0 0 0 0
s 0 0 1 0 0
t 0 1 0 0 0
u -1 2 -1 0 -1
2 p
v -2 3 -1 1 0
p 0 0 0 0 1
Ans 10 15 0 25 60
-1 1 -1 1
v
c
f
g
Using Excel Formulas To Make a TVM Calculator. Take a look at the setup below. ($5,000 invested for a period of time to yield $10,000). There are some important differences: 1. The present value (PV) is negative. The convention in Excel and other math of finance technologies is the following: Amounts you (the investor) pay are negative. Since you are investing $5,000, it is entered as negative. Amounts you receive (the future value in this case) are positive. 2. The formulas in the "Calculated" column are different. Entered Calculated 6.50% 6.99% 10 10.7502134 This will be used in Section 5.3. -$5,000.00 $10,000.00 4 -$5,247.80 $9,527.79
Rate Years Payment Present Value Future Value Periods/yr
r t PMT PV FV m
Do not change these! Explanation of the Formulas The formulas in the "Calculated" columns are based on the following version of the compound interest formula: FV = PV*(1+i)^n n = the number of interest periods; n = m*t i = the interest per period; i = r/m The formats of the Excel formuas we used are as follows: FV(i,n,PMT,PV) PV(i,n,PMT,FV) NPER(i,PMT,PV,FV) RATE(n,PMT,PV,FV) Formula for FV. Note that i = r/m and n = m*t. Formula for PV. Note that i = r/m and n = m*t. Formula for n. To get t we must divide it by m. Note that i = r/m. Formula for i. To get r, we must multiply it by m. Note that n = m*t.
In the TVM calculator above, we have used m*t for n and r/m for i. For example, the values entered above (unless you have changed them!) tell us that: * If $5,000 is invested for 10 years at 6.50% compounded quarterly, then FV = $9,527.79 * If you wish to realize $10,000 on a 10 year investment at 6.50% compounded quarterly, then PV = $5,247.80 * If you invest $5,000 and wish to realize $10,000 on a 10 year investment compounded quarterly, your investment must pay 6.99% * If you invest $5,000 and wish to realize $10,000 on an investment yeilding 6.50% compounded quarterly, you must wait approximately 10.75 years.
technologies is the following: ered as negative.
ed in Section 5.3.
FV = $9,527.79 nded quarterly,
nt compounded quarterly,