View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Monthly Compound Interest

On May 6, 12:21 am, FRUSTRATED
wrote:
Need to graph out a monthly compound interest problem. Does anyone know the
formula to use for excel in computing this and graphing it?
APR: 9.45%
Per month: $1486.40
For 50 years


My previous response assumed that you wanted a monthly plot. (And you
probably would want 601 rows for the spreadsheet design that I
offered.)

If you want an annual plot, the formulas are a little more interesting
and educational. You only need 51 rows. The dates are the annual
anniversary. The other formulas might be:

Balance:
D2: 1486.40
D3: =fv(r, 12, -1486.40, -D2)
where "r" is 9.45%/12 or RATE(12,0,-1,1+9.45%) depending on
what type of interest rate you have.

Interest for the previous 12 months:
B3: =D3 - D2 - 12*1486.40

Cumulative interest:
E3: =B3+E2

There might be an off-by-one error, depending on your assumption about
when the deposit is made. I believe the above is consistent with the
schedule of deposits and interest payments that I used in my previous
posting.