Monthly Compound Interest

[Guest]

Hello!

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

Of course this would all be graphed out on a nice line graph...

 

[Guest]

There is a "Loan Amortization" template on my computer that I presume was
loaded with Excel. Take a look at that for a start.

HTH,
Barb Reinhardt

 

[joeu2004]

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

I presume you mean that you have a savings account into which you
deposit $1486.40 per month.

As for "the" formula, it depends on what you want to compute:
interest per month, cumulative interest monthly or account balance
monthly.

In any case, I think you will need to set up a spreadsheet with at
least 600 rows (12*50), then use the Chart Wizard to construct a graph
of whatever values you want to graph. Here are some columns that you
might want:

A: Date
B: Interest earned (on previous balance)
C: Deposit
D: Balance
E: Cumulative interest

A2: any date (say, 6/1/2007)
A3: =date(year(A2),1+month(A2),day(A2))
Copy down through A601
Alternatively: Enter the first two monthly dates, then drag
the pair of cells down using the handle in the lower right.

B2: leave empty
B3: =round(D2*(1+9.45%/12), 2)
or
=round(D2*(1+rate(12,0,-1,1+9.45%)), 2)
Copy down through B601
Note: You need to decide whether 9.45% is the (simple) nominal
rate (use 9.45%/12) or the (compounded) annual effective rate (use the
RATE(...)).

C2: 1486.40
C3: =C2
Copy down through C601

D2: =C2
D3: =B3+C3+D2
Copy down through D601

E2: leave empty
E3: =B3+E2
Copy down through E601

 

[joeu2004]

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.