Calculating Future Value

[Tamera George]

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!

 

[joeu2004]

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 just am trying to learn how to do the calculations
myself.

Good for you!

Generally, the function to use is FV(rate,nper,pmt,pv). In your examples,
pmt is zero.

One key point is: pmt, pv and FV are signed amounts. Usually we use
positive for inflows and negative for outflows. But which is which depends
on your point of view: borrower or lender; investor or "bank". I usually
use negative pv so that FV is positive; then I chose the sign for pmt
relative to pv or FV.

Another key point is: rate, nper and pmt are __periodic__ amount based on
the compounding frequency. For example #3, the number of periods (months)
is 12*10.

Unfortunately, there are two schools of thought on how to convert an annual
rate to a periodic rate. You need to do it according to the method used by
the particular investment. Sometimes that is dictated by regional law.

For investments, the term "interest" usually refers to a simple rate,
whereas the term "yield" usually refers to a compounded rate. So, for
example #3, the monthly rate might be 8.9%/12 or (1+8.9%)^(1/12)-1,
depending on whether 8.9% is an "interest rate" or "yield".

The mathematical expression (1+8.9%)^(1/12)-1 "decompounds" (for want of a
better term) the effect of the compounded yield. Usually, it can be
calculated alternatively using RATE(n,0,-1,1+annualRate); for example,
RATE(12,0,-1,1+8.9%).

I say "usually" because the Excel RATE function sometimes returns an error
(#NUM or #DIV/0) if it is unable to "decompound" due to its iterative
algorithm. That should never happen when the second parameter is zero; but
I believe it does :-(. So I usually use the mathematical expression.

Putting that all together, your examples can be computed as follows,
assuming that the annual rates are simple "interest" rates:

1. =FV(6.25%/2,12*2,0,-5000)
2. =FV(7.63%/4,6*4,0,-5000)
3. =FV(8.9%/12,10*12,0,-5000)
4. =FV(10%/365,3*365,0,-5000)

Note for #4: Some people use 365.25 instead of 365. US law requires the
use of 365, although 366 can be used for leap years.

-----

5. 8 percent compounded continuously for two years

I saved this example for last because it is entirely different. It is
useful only in theoretical mathematics; for example, in the derivation of
Black-Scholes model.

The general formula is: pv*EXP(rate*nper).

Here, rate and nper can be annual amounts. Thus, nper might be a
non-integer. I have never seen a formula for continuous compounding with
periodic payments (pmt).

So for #5, the formula would be =5000*EXP(8%*2)

 

[joeu2004]

5. 8 percent compounded continuously for two years [....]
The general formula is: pv*EXP(rate*nper).
Here, rate and nper can be annual amounts.

So it might be better to use "term" instead of "nper".

"Nper" is usually means "n periods" or "number of periods". "Term" refers
to a number of years (or some unit period).

I might also note that rate and term really are for the same unit period,
not necessarily annual.

So if we are given an __monthly__ rate of 0.6% for two years, I would use
the formula =5000*EXP(0.6%*2*12).

On the other hand, if we are given an __annual__ rate of 8% for 18 monhts, I
would use the formula =5000*EXP(8%*18/12).

The point is: there should be no need to convert the rate to a periodic or
annual rate.

That avoids the natty question of how to do the conversion (simple v.
compounded; "interest rate" v. "yield").

 

[joeu2004]

5. 8 percent compounded continuously for two years [....]
The general formula is: pv*EXP(rate*nper).
Here, rate and nper can be annual amounts.

So it might be better to use "term" instead of "nper". [....]
I might also note that rate and term really are for
the same unit period, not necessarily annual.

Also note that here, pv is __not__ a signed cash flow. It should have the
same sign that you want for FV, which is usually positive.

As an exercise, compare the results of #5 as written with 8% compounded
daily for 2 years. That is:

=FV(8%/365,2*365,0,-5000)

=5000*EXP(8%*2)

Extra credit: for those terms (5000 at 8% for n years), at what point (that
is, for what n) is the difference $1 or more?

PS: I purposely omitted the issue about rounding. Usually, I explicitly
round any monetary formula to at least the smallest denomination. For
example, ROUND(FV(8%/365,2*365,0,-5000),2). This applies at least to any
real payment or reported amount. This guideline applies to all financial
calculations, not just future value formulas.

 

[joeu2004]

I suppose the interest rates 6.25, 7.63, 8.9, 10 and 8
are effective annual rates to be converted in effective
infra-annual rates as follows:
i2 = (1+0.0625)^(1/2)-1 FV = 5000(1+i2)^24
i4 = (1+0.0763)^(1/4)-1 FV = 5000(1+i4)^24
i12 = (1+0.089)^(1/12)-1 FV = 5000(1+i12)^120
i365 = (1+0.1)^(1/365)-1 FV = 5000(1+i365)^1095 [....]
These calculations may differ from the Excel's ones.
In fact Excel uses the standard US banking calculations
which take into account bank profits instead of
mathematical principles.

That assertion is patently incorrect. In fact, FV(i2,24,0,-5000) returns
exactly the same result as 5000*(1+i2)^24. The same is true for the other
three examples.

The standard Excel financial functions (PV, FV, NPER, PMT and RATE) do not
take any "standard US banking" practices into account, much less
calculations based on "bank profits instead of mathematical principles".

Users of those functions might, however. As I noted: "there are two
schools of thought on how to convert an annual rate to a periodic rate. You
need to do it according to the method used by the particular investment.
Sometimes that is dictated by regional law".

I cannot say the same with impunity about Excel bond functions (e.g. YIELD
and PRICE). They might or might not rely on some US-specific bond
conventions. Honestly, I don't know since I am not familiar with bond
conventions the world over.

In any case, I am quite sure that any such US-specific bond conventions are
not motivated by "bank profits instead of mathematical principles". More
likely, they were developed in pre-computer times in order to make
computations easier to do by hand or with a mechanical calculator using
predetermined tables.

 

[joeu2004]

They do!
In my country (Italy) you can negotiate a financial operation
with a bank at 5% annually compounded.
Then if you change your mind and ask for semi-annually compounded
operation you see that the rate is <2.5% as per i2 = (1+0.05)^(1/2)-1
I've been told that normally that's not the case in US.

But such regional policies and laws are __not__ built into the Excel
functions, as you asserted incorrectly.

They merely affect the __periodic__ rate that we must choose to input to the
Excel functions.

In other words, the __user__ of the function, not Excel, determines how the
annual rate is interpreted and converted to a periodic rate.

-----

As for your rant about US practices, it is mostly incorrect or incomplete.

For investments, which Tamera is asking about (not loans), US law states
that the effective compounded "annual percentage yield" (APY) __must__ be
disclosed. The simple "interest rate" __may__ also be disclosed. If only
one rate is disclosed, it must be the APY.

 

[Michael Marshall]

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 howto 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 atime 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 growor 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 neverending 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 compoundingof 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 isused 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 variablewould 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 withExcel'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