How can I figure monthly interest earned In Microsoft Excel or a website?

I would prefer to know how i need to enter the info in Microsoft Excel so that it can figure the information for me. Here is the scenario: I begin with $200. Each month I add $400 and 5% interest is paid on the total amount. I continue to do this for 10 years. Here is how I have been figuring it with a pen and paper:





Balance plus $400 times interest equals new balance


($200 + $400) 1.05= $630


($630 + $400) 1.05= $1081.50


($1081.50 + $400) 1.05= $1555.58





Can also be done like this:





($200 + $400) x .05= $30 then $30 + $600= $630





I hope This wasnt too confusing to figure out. Thanks for all your help

How can I figure monthly interest earned In Microsoft Excel or a website?
Two ways depending if you need to know the value at each increment:





The long way is to enter the formula you are writing on paper and then pull the formula down until you reach 10 years. If you're not familar with excel, it's hard to explain how to set it up without showing you.





Basically, the cell formula would look like this:


cell a1: =(200 + 400)*1.05


cell a2: =(a1 + 400)*1.05


Now, copy cell a2 and pull it down until you reach the period for 10 yrs.





Or you could use the future value function (FV). If you type in =FV( then excel will prompt you for the rest of the information for the formula. This is the easiest way and can be done in one cell. So, your formula would be =FV(5%, 120, -400, -200, 1).
Reply:The answer is $77,970.54





Your formulas are incorrect, because you mix units between monthly payments and annual interest rates.





Assuming you'll get monthly compounding on your 5% rate, then your formula should look like this...


Month #1: ($200 + $400)*(1+(.05/12))


Month #2: [($200 + $400)*(1+(.05/12)) + $400] * (1+(.05/12))





If you receive annual compounding, then...





Year #1: ($200 + 12*$400)*(1.05)


Year #2: [($200 + 12*$400)*(1.05)] + 12*$400] * (1.05)





Note that in the first example, each formula is for a month, and in the second example, each formula is for a year. It all depends upon how your interest is compounded, monthly or annually.





As you can see, this gets pretty cumbersome as you move out 10 years....





...So, let me introduce you to my little friend... called the FV function. This is an Excel worksheet function. You can read about it by looking up "FV function" in help.





The FV function returns the future value of an investment based on periodic, constant payments and a constant interest rate.





The syntax for the function is...


=FV(rate,nper,pmt,pv,type)





To calculate your example, you would enter the following formula.





=FV(5%/12,10*12,400,200)





The trick to using the FV function is to make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper





So for your example,





Rate: I am assuming your stated 5% interest rate was an annual interest rate, that is, 5% per year. Since your payments are monthly, you will need to divide this rate by the 12 months in a year, hence, "rate=5%/12".





nper: You want ten years, but receive monthly payments. So the number of periods is 10 years multiplied by 12 months in a year, or 120 payments.





pmt: This is from your prompt, "every month I add $400."





pv: This is from your prompt, "I begin with $200."





Put it all together, and you'll get $77,970.54





If you found my explaination confusing, then please go to the reference I listed below on the Microsoft web site.





http://office.microsoft.com/en-us/excel/...
Reply:In Excel you would use the future value function. The future value function works like this '=FV(rate,nper,pmt,pv,[type])'.





Rate = interest rate per period (5%/12 in your case)


Nper = number of periods (12 months x 10 years, 120 periods)


Pmt = periodic deposit (400)


Pv = initial deposit (200)


Type = optional (0 if deposit made at beginning of period, 1 if deposit made at the end of period)





For your problem, you would type this in Excel:


=FV(5%/12,120,-400,-200)


And the solution is $62,442.31





You can also use cell references if you want to change the values.

hide song