Document Sample

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). Formula for FV See the next worksheet for built-in Excel formulas. PV = FV/(1 + r/m)^(mt). Formula for PV t = LN(FV/PV)/(m*LN(1+r/m)) Formula for t r = m*((FV/PV)^(1/(m*t)) - 1) Formula for r 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. 1. (You did it right if you get a value of $2,570.00 ). Entered Calculated Rate r 4.75% Years t 6 Present Value PV $2,000.00 Future Value FV Periods/yr m 4 We will not compute a formula to calculate 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 y s t u 2 y 0 1 0 0 -1 2 t 0 0 0 1 2 2 s 0 0 1 0 -1 1 x 1 0 0 0 0 2 p 0 0 0 0 -1 v p Ans -2 0 10 3 0 15 -1 0 0 1 0 25 0 1 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 Rate r 6.50% 6.99% Years t 10 10.7502134 Payment PMT This will be used in Section 5.3. Present Value PV -$5,000.00 -$5,247.80 Future Value FV $10,000.00 $9,527.79 Periods/yr m 4 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) Formula for FV. Note that i = r/m and n = m*t. PV(i,n,PMT,FV) Formula for PV. Note that i = r/m and n = m*t. NPER(i,PMT,PV,FV) Formula for n. To get t we must divide it by m. Note that i = r/m. RATE(n,PMT,PV,FV) 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,

DOCUMENT INFO

Shared By:

Categories:

Stats:

views: | 20073 |

posted: | 9/15/2008 |

language: | English |

pages: | 43 |

Description:
This is an example on how to calculate compound interest in excel. This document is useful in calculating compound interest in excel.

OTHER DOCS BY Richard_Cataman

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.