On Sunday, November 4, 2012 9:34:38 AM UTC-5, Tamera George wrote:
I am trying to calculate the following
Find the future value of an investment of $5,000 made today for the following
1,) 6.25 percent compounded semi-annually for twelve years
2.) 7.63 percent compounded quarterly for six years
3.) 8.9 percent compounded monthly for ten years
4.) 10 percent compounded daily for three years
5.) 8 percent compounded continuously for two years
I don't really want you to do the calculations for me, I need to know how to do the calculations to find these various types of answers using Excel. If you do work the problem please change the numbers so that I can follow what you are doing but am not getting the answer from you on this specific problem. I just am trying to learn how to do the calculations myself.
Thanks for the help!
The TVM functions in Excel namely RATE, NPER, PMT, PV and FV are based on a time value of money equation that can be written as a sum of three components. This could be a sum of discounted future value, discounted periodic payment and undiscounted present value. The sum may also be rewritten as compounded present value, compounded periodic payment and the uncompounded future value. Both these TVM equations help us to solve for various time value of money variables such as
1) present value of a single investment
2) present value of a series of periodic returns
3) future value of a single investment
4) future value of a series of periodic returns
5) interest rate for a single investment
6) interest rate for a series of periodic returns
7) periodic payment required of an investment to reach a future value
8) periodic payment required of an investment to reach a present value
9) the number of periods of a single investment
10) the number of periods of a series of periodic returns
But the two TVM equations used as the basis for the 5 TVM functions in Excel is very basic and does not help solve more complex financial problems. For example, using TVM functions in Excel one is not able to solve for RATE, NPER, PMT, PV and FV for series of periodic payments that have a GRADIENT meaning the periodic payment is not in constant amount. The payment may grow or shrink by a rate or it may increase or decrease by a constant amount. Other things that are required in TVM math would be the possibility of never ending periodic payment for which there is no provision in TVM equation for infinite number of periods. One would desire to be able to find present value of never ending stream of payments and the interest rate on such a continuous stream of payments.
The NPER variable in Excel TVM functions stands for number of periods and that is all it is; there is no way to tell Excel what a particular period may be (Year, Quarter, Month, Week, Day or whatever else)
The compounding of interest can not be specified as you have seen there is no way to tell Excel TVM functions about infinite or continuous compounding of interest.
Before I go on to show you finding the future value of $5,000 for the various time periods and compounding periods you would be glad to know that there is TADXL add-in
http://tadxl.com that has extended the 5 TVM function of Excel to address all of the possibilities detailed in the last few paragraphs that are missing in Excel.
To do this a new TVM function is introduced that is called GRADIENT that is used to specify the growth rate by which a periodic payment grows or shrinks and a constant amount by which the periodic payment increases or decreases.
With the 6 TVM functions in TADXL you would be able to tell Excel that a period means a YEAR by giving a value of 1 to the variable called PERIOD. If the period is a day you can give a value of 1/365 to such a variable.
You can also tell Excel that interest compounding is ANNUAL by giving a value of 1 to variable called COMPOUNDING, a value of 1/12 for such a variable would tell Excel that compounding of interest is monthly and finally a value of 0 would mean infinite compounding of interest.
And how about telling Excel that you have a never ending series of payment by giving NPER a value of INF (short for infinity) and then being able to find the present value of such payments and calculate the interest rate required for such infinite stream of payments.
In the text that follows, you will be shown the use of Excel FV function
http://tadxl.com/excel_fv_function.html called tadFV and it's comparison with Excel's own FV function to calculate future value of a single investment for different time periods and different interest compounding periods. The results will be identical in all cases.
RATE: 6.25%
GRADIENT: 0%
TAXRATE: 0%
NPER: 12
PMT: 0
PV: $(5,000.00)
TYPE: 0
GTYPE: 0
COMPOUNDING: 6/12
PERIOD: 1
DISTRIBUTION: 1
=FV( 6.25%/2, 12*2, 0, -5000 )
$10,464.18
=tadFV( 6.25%/2, , , 12*2, 0, -5000 )
$10,464.18
=tadFV( 6.25%, 0%, 0%, 12, 0, -5000, 0, 0, 6/12, 1, 1 )
$10,464.18
RATE: 7.63%
GRADIENT: 0%
TAXRATE: 0%
NPER: 6
PMT: 0
PV: $(5,000.00)
TYPE: 0
GTYPE: 0
COMPOUNDING: 3/12
PERIOD: 1
DISTRIBUTION: 1
Excel FV:
=FV( 7.63%/4, 6*4, 0, -5000 )
$7,868.96
tadFV:
=tadFV( 7.63%/4, , , 6*4, 0, -5000 )
$7,868.96
tadFV:
=tadFV( 7.63%, 0%, 0%, 6, 0, -5000, 0, 0, 3/12, 1, 1 )
$7,868.96
RATE: 8.90%
GRADIENT: 0%
TAXRATE: 0%
NPER: 10
PMT: 0
PV: $(5,000.00)
TYPE: 0
GTYPE: 0
COMPOUNDING: 1/12
PERIOD: 1
DISTRIBUTION: 1
Excel FV:
=FV( 8.9%/12, 10*12, 0, -5000 )
$12,135.73
tadFV:
=tadFV( 8.9%/12, , , 10*12, 0, -5000 )
$12,135.73
tadFV:
=tadFV( 8.9%, 0%, 0%, 10, 0, -5000, 0, 0, 1/12, 1, 1 )
$12,135.73
RATE: 10%
GRADIENT: 0%
TAXRATE: 0%
NPER: 3
PMT: 0
PV: $(5,000.00)
TYPE: 0
GTYPE: 0
COMPOUNDING: 1/365
PERIOD: 1
DISTRIBUTION: 1
Excel FV:
=FV( 10%/365, 3*365, 0, -5000 )
$6,749.02
tadFV:
=tadFV( 10%/365, , , 3*365, 0, -5000 )
$6,749.02
tadFV:
=tadFV( 10%, 0%, 0%, 3, 0, -5000, 0, 0, 1/365, 1, 1 )
$6,749.02
RATE: 8%
GRADIENT: 0%
TAXRATE: 0%
NPER: 2
PMT: 0
PV: $(5,000.00)
TYPE: 0
GTYPE: 0
COMPOUNDING: 0
PERIOD: 1
DISTRIBUTION: 1
Excel FV:
=5000*EXP( 8%*2 )
$5,867.55
tadFV:
=tadFV( 8%, 0%, 0%, 2, 0, -5000 ,0 , 0, 0, 1, 1 )
$5,867.55