Re: New User Quest. on Financial Formulas For Compount Interest, etc.
From: JE McGimpsey (jemcgimpsey_at_mvps.org)
Date: 02/28/04
- Next message: Ken Wright: "Re: multiple workbooks..."
- Previous message: Ken Wright: "Re: Excel Multiplication"
- In reply to: Robert11: "New User Quest. on Financial Formulas For Compount Interest, etc."
- Next in thread: Norman Harker: "Re: New User Quest. on Financial Formulas For Compount Interest, etc."
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 28 Feb 2004 08:02:04 -0700
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%
In article <4MGdnay5ePSCP93d4p2dnA@comcast.com>,
"Robert11" <rgsxrose@comcast.net> wrote:
> 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
>
- Next message: Ken Wright: "Re: multiple workbooks..."
- Previous message: Ken Wright: "Re: Excel Multiplication"
- In reply to: Robert11: "New User Quest. on Financial Formulas For Compount Interest, etc."
- Next in thread: Norman Harker: "Re: New User Quest. on Financial Formulas For Compount Interest, etc."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
