Calculate Compound Interest in Excel - Excel by Richard_Cataman

VIEWS: 20,073 PAGES: 43

More Info
									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?
               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.
              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
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,

To top