On Feb 16, 11:00 am, "jIM" wrote:
On Feb 16, 10:27 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:





I'm going to ask what may be a silly question, but have you seen the free
Excel loan amortization analysis and schedule templates available simply as a
download from Microsoft:
'sales pitch' and explanation page:http://office.microsoft.com/en-us/ex...346401033.aspx
and a template is available from this page:http://office.microsoft.com/en-us/te...415371033.aspx

"jIM" wrote:
I am trying to use Excel to calculate my mortgage ammortization
schedule.

Example:

cell
B2 mortage amount (-$60,000) negative number used so other numbers
come out positive.
C2 interest rate (7.6%)
D2 term (30 years)
PMT (calculated from above 3) $423.64. This matches the bank quote.

A6-A365 are the dates (start April 2007 and end Mar 2037)
B6-B365 are the "periods" 1-360

C6-C365 is the balance remaining on loan. This is column which does
not "check"
C6 is =-B2 (mortage amount)
C7=C6-E6
C8=C7-E7, etc...

D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C
$2/12,B6,D$2*12,C6)
E6-E365 is principal paid (for that period). Formula is E6=B$4-D6;
E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest
paid for that period.

This series of payments "pays off" loan in 249 months (periods),
according to column C. It should not be zero balance until period
360.

I have a similar spreadsheet which allows for additional principal
payments, but I need to get basic sheet right before concentrating on
other issues. I can e-mail a working spreadsheet for anyone
interested in taking a look.

Thank You.

jIM- Hide quoted text -

- Show quoted text -

The one you pointed me to I had figured out, there was a seperate one
for an ammortization schedule, but it does not show formulas, and when
I added extra principal payments, the sheet did not recalculate.

The online sheet shows forumulas. When copied to an existing sheet,
it does not copy forumulas. What gives?

jIM- Hide quoted text -

- Show quoted text -

I had two sessions of excel running, it would not copy with formulas
to a different session. Closed second session, opened existing
workbook in current session, and copy worked with formulas.

The templates "fixed" the problem, but they are way too automated (for
copying and if-then analysis).

I like doing a payment schedule, then highlighting in yellow the row
where the payoff occurs. Then doing another analysis and highlighting
that payoff row in yellow, comparing the two different schedules.

The sheet allows me to do ONE analysis. Once I copy the whole
structure I need to backtrack and find mistakes (the copied cells did
not update when I changed the payment structure).

for example, is it better to make two $625 payments in November and
December of each year, or one $1250 payment in December. I know the
two payments are better (based on rules of compounding), but I want to
see the exact difference (in months).

If payment was $2500, what is the return on paying the additional
$1250 each year?