TX,
Some suggestions;
Move the Totals columns to the top of the worksheet (we
will work on the formula later)
Move the Graph to the top right of the interest column (L1)
You really do not need the graph it will only show a
straight line downward.
Your formulas are basically sound but they need some
Absolute cell references.
The following changes may not make sense at first but they
will when we are finished.
Change the formulas in E10 to = C10-I10 and in G10 to =PMT
(C7/12, C5*12, -C3) and in I10 to =PPMT(C7/12, 1, C5*12, -
C3). These numbers should be a positive number not a
negative.
Next in row 11 add to A11 the formula A10+1 in E11 change
to C11-E11
In G11 =PMT($C$7/12,$C$5*12,-$C$3)
In I11 =PPMT($C$7/12,A11,$C$5*12,-$C$3)
Now highlight Cells A11 through K11 and copy down to row
70 for five years or further if going beyond five years.
If you wish to have a loan calculate to 30 years copy the
formulas in A11 through K11 down to row 370. This will
copy the formulas down without change cell references.
Next we will do some conditional formatting to hide rows
that are not needed in a calculation.
Select all cells from A11 to K70 (or K370). Go to the
Menu toolbars Format/Conditional Formatting and
change 'Cell Value is' to 'Formula is' enter the formula
=ISERROR($E10). Click on Format, Font Tab, Color and
select white. Click OK and OK again.
Now when you enter the number of years for a loan only
those months will show in the amortization.
For the totals on top of the page you would only need the
Total payback and the interest paid. If you are familiar
with range names, name the Interest column 'Interest' then
in let's say cell F3 enter =SUM(Interest). If you are not
familiar with range names then enter. =SUM(K10:K370). In
H3 enter =C3-F3
Should work fine, let me know.
LOL
Charlie O'Neill
