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

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

0
rgsxrose (12)
2/28/2004 2:15:58 PM
excel 39879 articles. 2 followers. Follow

2 Replies
717 Views

Similar Articles

[PageSpeed] 0

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
>
0
jemcgimpsey (6723)
2/28/2004 3:02:04 PM
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
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Robert11" <rgsxrose@comcast.net> wrote in message
news:4MGdnay5ePSCP93d4p2dnA@comcast.com...
> 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
>


0
njharker (1646)
2/28/2004 3:36:29 PM
Reply:

Similar Artilces:

Must buy new computer...Ideas?
Hello to all! Finally my over 10-1/2 year old Dell desktop's extreme slowness has seriously gotten on my nerves and I'm now looking for a new one. (Surprisingly, it's working pretty well otherwise...) Maybe this isn't the right forum? But beside the usual things like RAM and HD GBs, are there things I should be sure to have...or be careful NOT to have on a desktop? After n ot looking into computers for over a decade I feel like a Martian in the Earth. lol! Also, is it possible to transfer to the new one ALL my stuff, including programs, etc.? Do you thin...

create folder to all users
i am searching for a way to create a folder called search and replay under the outbox folder. i do not wish to use a plublic folder. the search and replay folder will open up to a web page. i need to know if there is any way of creating this folder without going to each of the workstations. we are running exchange 2003 w/ mixed outlook versions (200,xp,2003). please offer me any ideas you might have. thank you for your help. You'll have to script a solution. Nothing native in Exchange will let you do this and deploy to existing users. Nue "murr" <martynaughton@gmail....

HQ users should be able to add ship-to records from the stores.
HQ users should be able to add ship-to records from the stores. As of RMS 1.3.???, HQ users can indeed create ship-to records at the stores. I'm told that RMS doesn't handle this properly. There are problems created when stores are messing around with global customers. I suspect that MS will 'fix' this by eliminating the ability of stores to add Ship-To records. I have a customer, ABC Fitness, who has one office, but several gyms. I repair the treadmills at their various gym locations. I have each service site set up as a Ship-To in RMS, within the ABC global customer ac...

Default properties for new folders
How does one set the default properties applied when creating new email folders? Currently, when I create a new folder, it is created with the AutoPreview turned on by default. I'd like to change that do that it is left off by default. It's something of a pain to create a new folder and then go in to customize it's properties. You can do this by editing the Messages view; View-> Arrange By-> Current View-> Define Views... You might want to define a new view yourself and apply that view on first use of that folder. -- Robert Sparnaaij [MVP-Outlook] www.howto-o...

New Email Messages
I just installed Outlook 2007 on my new PC and for some reason the new messages I received can only be received when I press the SEND/RECEIVE BUTTON. My Outlook at work receives these new mail messages as they are received. So how can I configure the system to make that happen? -- The Trail Hiker!! There are two settings within the accounts 1) Send on starting 2) Auto send/receive every xx minutes - set this for 10 minutes "hiker0531" <hiker0531@aol.com> wrote in message news:87259C7A-5C07-4846-BF61-DE3F98345D21@microsoft.com... >I just installed Outlook 2007 on my n...

User Calendar Vanished
Hello, Just a quick query regarding a user whos calendar contents seems to have vanished! for dates prior to June 05 This is not a big deal, but i thought a little odd.. any reason anyone might know of why this would happen? one thing i suggest was that some data archived off has been deleted.. do archives include calendar data ? thanks On Tue, 21 Feb 2006 01:44:28 -0800, "Chris" <Chris@discussions.microsoft.com> wrote: >Hello, > >Just a quick query regarding a user whos calendar contents seems to have >vanished! for dates prior to June 05 > >This is...

Unable to open "New" window to write email.
I am using Microsoft Outlook in Office 2003. It has been working great until today. When I click "New" to write a new email I get an error window : The messaging interface has returned an unknown error. If the problem persist, Restart Outlook. I have tried this several times, even restarted the computer to no avail. Anyone have a solution???? ...

Prevent new comments on old posts in sharepoint blogs
How do I stop comments on certain posts in a SharePoint blog? I have removed permissions to the specific post to the lowest possible access and people can STILL post a comment. If I uncheck one more item in the list of permissions, the users can no longer see the post. Any suggestions? ...

A Short Video on Adding/Editing New Customers
If capturing customers at the POS is important to you, take a couple of minutes and watch this video. This application also allows for edit of Global customer information in HQ installations. http://www.retail-pos.com/ReverseLookup.wmv Contact us for detailed info and demo. sales(at)retail-pos.com www.retail-pos.com It would be nice if you could encode the videos with a more common codec, and not in wmv format. This requires the GTM3 codec (GoToMeeting). Windows Media Player 11 doesn't automatically download the codec, and I'm not going through the trouble to download and in...

New Location/New setup
Currently I'm downsizing our office, and moving to a new location. I'm running MS Server2003 as well as Exchange 2003. Once moved, I will no longer require the server (since I am setting up a home office). Are there currently any steps or procedures available to correctly shutdown one office...and basically setup a home office with no exchange server. Also I will be removing the current pc's from the Domain, and setting up a few in a workgroup. I would like to receive emails from the other employees for time being until I completely cancel this email account. I already have a...

How do I activate the new mail button
When I start Outlook, the "New", "forward", "reply" and "reply to all" buttons are not active which means I can not send mail. I have no problems receiving mail. Also, on web pages with email links, when I click on them, the Outlook application is not launched which is almost certainly related to this. I think I'm missing something very simple here! Are you in a mail enabled folder? Do you have an active email account installed? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert...

Style available all sheets new or old
I need a custom style that is available in all spreadsheets, new or old. I can set style and save it in book.xlt in xlstart, but that only works for new workbooks, and doesn't work with old spreadsheets when I call them up. I want to be able to select a group of cells, or column or row and apply a setting for negative numbers to have brackets. I am using Excel 2000. I even tried saving the style in personal.xls, but that doesn't work as it is hidden. Do I have to use a macro? And if so what would it be? Thanks One way: Public Sub MergeStyles() ActiveWorkbook....

Formula to return All Teams that.......
Greetings, I am trying to write a formula that will return the team name of ALL teams that had Kitchen duty the day before they have Garden duty (and various other combinations) Without using a macro!!!! For example using a small sample of the data A B C D E 1 2 Date Cleaning Garden Kitchen Day Off 3 12/05/2005 Team A Team B Team C Team D 4 13/05/2005 Team C Team D Team B Team A 5 14/05/2005 Team C Team B Team D Team A 6 15/05/2005 Team A Team D Team B Team C 7 16/05/2005 Team C Team D Team A Team B 8 17/05/2005 Team D Team A Team B Team C 9 1...

Sub Form Assigning New Values to Old Records
I am using a subform within a form to have users update certain fields in the record where the rest remiain locked. On the Main form when the list box (which is bound to a query) is used to select the record set to display in the subform it sometimes takes an old record and assigns a new value to the record. For example it changes the Period field from 9 to 10. Taking a record created in period 9 and assigning a period ten to it which is the selection I make in the list box. Addtionally, it looks like it is taking the first record in the related table to assign this new value to. ...

Why is excel 2007 slow when selecting chart data from the formula
Excel 2007 is very slow when selecting chart data from the formula bar. For example 8, X-Y scatter graph curves are generated from data base of approximately 2000 lines long x 2 columns. A convienient way to edit the cell references for each curve is to simply click onto a curve and the cell references will appear in the formula bar. With all earlier versions of excel including 2003, once the curve is clicked the cell reference appears in the formula bar almost instantaneously. However when the same method is used with excel 2007, it can take 10-20 seconds before the formular appears. ...

Setting up second virtual server/connector for smart host for only select users
I currently have one domain and SMTP virtual server defined to handle all mail including internet email. We have our own MX record. Occasionally I have one user who will need to send a mass mailing based on our customer database and this clogs the system. My thought was to define a second SMTP virtual server/connector that would route the email to a smart host (our service provider offers this service). If I could direct this specific user to use this service for the mass mailing, would it help the situation? If yes, how would I go about doing it - have the two SMTP server definitions coe...

Which table will refresh by adding new Account / Contact
Hi, I am new to CRM and trying to update CRM Accounts and Contacts from our Sales Management system. I am using Micorosft Bulk Import utility. I am in preparation of CVS files but not sure what columns do I need in CVS file to complete one Account/Contact record. Could anybody please help. I am .NET expert developer, please let me guide If I could develop CRM by using .NET not CVS Regards Hi Adnan, Microsoft do provide CRM SDK for CRM enhancment using .net. You can download from following url http://www.microsoft.com/downloads/details.aspx?familyid=9C178B68-3A06-4898-BC83-BD14B74308C5...

Adding new pages
Any thing I do on new pages happens simultaneously on any previous page. Can someone help me please? Can you be more specific? What stencils are you using and what are you trying? John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visio/common_questions.htm "Joanie" <anonymous@discussions.microsoft.com> wrote in message news:4C3642E4-4F2B-4A65-8C00-E9041CE74A37@microsoft.com... > Any thing I do on new pages happens simultaneously on any previ...

New User has admin rights
I just created a new user and assigned her the role of Sales Manager. This role is working correctly for other users with it assigned, but she has admin rights? This is the only role she has assigned.... Any ideas? TIA Metcalf ...

Publisher Users, a program suggestion for you
Publisher Folks, For years now, when trying to find a Publisher file of which I forgot the file name, would attempt to find the file using a phrase with Windows XP Search. It would always fail. While over on the Windows XP news group one of the folks suggested using Agent Ransack, a file finding program. It does a great job finding Publisher files when providing a word or phrase contained within the file. If you have a need...... http://www.mythicsoft.com/default.aspx -- Don - Publisher 2000� Vancouver, USA ...

New trend, new business, new year a new beginning
New trend, at start of new year. "If you want things to work different, you must try a different...whatever..." Does that seem obvious? Brighten up with this "reading" and attack the money issue with more gusto! I am finishing my year 2005 with some regrets and some joy. For some the year passed has not been "tip top" in their view. But I believe it has just been very well as the page I shall bring you that has this statement which I believe wholeheartedly: "THINGS ARE UNFOLDING AS IT SHOULD" (paraphrased) I invite you to read the whole "poem&quo...

New messages comes up in Deleted folder
Sometimes when I receive new messages the go to the Deleted folder instead of the Inbox. The messages are from random people...not just one sender. What is the problem? Thanks Check your message rules/filters. "key word" in new mail subject line of body is probably being tagged and filtered to deleted items folder. Regards C. >-----Original Message----- >Sometimes when I receive new messages the go to the >Deleted folder instead of the Inbox. The messages are >from random people...not just one sender. What is the >problem? Thanks >. > ...

user mode & kernel mode thread synchronization
What is ther difference between user mode & kernel mode thread synchronization ? when to use mutexes & when to use critical sections ?? Thanks in advance. MK wrote: > What is ther difference between user mode & kernel mode thread > synchronization ? > when to use mutexes & when to use critical sections ?? > > Thanks in advance. > All things kernel mode are off limits to an application program and off topic here. Mutexes and critical sections are for synchronizing threads in user mode (i.e. in application programs). They both perform the same synchron...

Continuous Formulas
I am working with dates, and I am wondering if there is a standard formula that I can enter. What I am looking to do is have 4 horizontal rows take one formula and one block on the horizontal line to take another. (The four I want to add 90 days to the date and the one I want to add 270). Please help!!! -- Buttaflye --They say home is where the heart is; then I wonder, where is my heart?-- Buttaflye, Your question is a bit (or more) confusing. Could you post an example of what you have, and what you want? HTH, Bernie MS Excel MVP "Kulakula" <Kulakula@discussions.micr...

Creating new row in other table
Dear All, Please teach me, how to make a new row/list in other table by entering number in other table. Sample, In the table A and in the field "Details", I put the number 2 and it automatically creates two rows in table B. Can this be done in MS Access? Thank you for your help. Regards, Maulwy Why? What business need are you attempting to solve with this approach? It's generally considered not very good database design to be creating 'empty' rows in a second table. Why do you want empty rows? And if the number you enter in TableA is a 'limit&...