New User Quest. on Financial Formulas For Compount Interest, etc.

[Robert11]

Hello:

New user.

Not being a financial math guhru or statistician, I am a bit confused over
what Excel function to use.

Would be most appreciative for any help on the following two items, please:

a. to determine compound interest:

Am interested in just seeing the amount an initial investment would
grow to if the interest is compounded
for different periods throughout the 1 year

(e.g., if compounded daily, every 3 months, only
simple interest applied at end of the 1 yr., etc.)

b. am also interested in determining:

If a CD account has its interest earned applied once at the end of
the
maturity, which I guess would be considered as "simple interest," how does
one compute what a lower
rate of interest, but compounded e.g., 4 times a year, would be equivalent ?

What formula does one use ?

(e.g., what would 2.22 % simple interest be equivalent to compared to
interest compounded 4 times per year ?)

Much thanks,
Bob

 

[JE McGimpsey]

One way:

Check out the FV() function in Help

Assume

A1: initial investment (e.g., 1000)
A2: interest rate (e.g., 6%)
A3: length of investment (e.g., 20 years)
A4: number of compounding periods per year (e.g., 4, or quarterly
compounding)

then the amount A1 will grow to is

=FV(A2/A4, A3*A4, 0, -A1, 0)

where the second 0 is used if the interest payments are due at the end
of the period. If they were due at the beginning, then you would use 1.

The amount from the above example would be $3,290.66


Change A4 from 4 to 1 for "simple interest" (result: $3,207.14), or to
12 for monthly compounding (result: $3,310.20), to see the difference
compounding makes.

b. Based on the above example, the effective simple rate is calculated as

=(1+A2/A4)^A4-1

e.g., to get the same amount of interest/growth as a 6% rate compounded
quarterly, you'd need

=(1 + 6%/4)^4 - 1

or approximately 6.136%.

If you have the Analysis Toolpak Add-in (Tools/Add-ins...) loaded, you
can use the EFFECT() formula:

=EFFECT(6%,4) ==> 6.136%

Conversely, to calculate the nominal quaterly rate provided by a 2.22%
simple return:

=((2.22%+1)^(1/4) -1)*4 ==> 2.02%

or, using the ATP's NOMINAL() function:

=NOMINAL(2.22%,4) ==> 2.02%

 

[Norman Harker]

Hi Robert!

Problem a:

I assume that you want to use a constant "declared" rate for
comparison purposes and that you are using the nominal regime where
this rate is divided by the number of compounding periods per year.

Here the base formula is

=(1+Nom/Freq)^Freq
You could use the "sledgehammer" FV function
=FV(Nom/Freq,Freq,-1,0,0)

Problem b:

You have an annual effective rate of 2.22% To convert to the quarterly
effective equivalent use:

=(1+2.22%)^(1/4)-1

In terms of the nominal regime, multiply the result by 4 to get the
annual nominal compounded quarterly.

Interest conversions are done by using the equation:

(1+Noma/Freqa)^Freqa = (1+Nomb/Freqb)^Freqb

Where the frequency is 1, then the rate is the annual effective rate.

I've got an interest conversions Addin plus tutorial file if you'd
like it. Just email a request to the address below.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.