mortgage ammortization schedule

[jIM]

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

 

[JLatham]

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/excel/HA010346401033.aspx
and a template is available from this page:
http://office.microsoft.com/en-us/templates/TC011415371033.aspx

 

[jIM]

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/excel/HA010346401033.aspx
and a template is available from this page:http://office.microsoft.com/en-us/templates/TC011415371033.aspx












- 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

 

[jIM]

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?

 

[JLatham]

Has the template been any help in tracking down your problem in your
workbook? Or do you still need assistance with it?

You said it wasn't updating automatically, have you checked Tools | Options
| [Calculation] tab to make sure it is still set to Automatic calculating?
If you recently opened a workbook with it set to manual, it could have gotten
toggled.

If you think there might be something I could help with but would need the
workbook to dig into, feel free to email as attachment to (remove spaces)
HelpFrom @ jlathamsite. com

 

[joeu2004]

D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C
$2/12,B6,D$2*12,C6)

I am not sure I posted my previous response correctly. To be brief,
your primary error is using C6 (remaining balance) instead of $B$2
(loan amount) in ISPMT().

(In your design, $B$2 contains the negative loan amount.)

However, I believe that even with that correction, ISPMT() computes
the wrong amount. I don't know why, off-hand.

But the better solution is to avoid ISPMT() altogether. Why use a
sledgehammer to kill ant? The period interest can be computed simply
by:

=C6*$C$2/12

where $C$2 is the annual interest rate in your design.

 

[jIM]

Has the template been any help in tracking down your problem in your
workbook? Or do you still need assistance with it?

You said it wasn't updating automatically, have you checked Tools | Options
| [Calculation] tab to make sure it is still set to Automatic calculating?
If you recently opened a workbook with it set to manual, it could have gotten
toggled.

If you think there might be something I could help with but would need the
workbook to dig into, feel free to email as attachment to (remove spaces)
HelpFrom @ jlathamsite. com

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?- Hide quoted text -

- Show quoted text -

The worksheet used "named references" instead of cell IDs in
equations. After about 90 minutes of work and self check, I have 2
working sheets which I am using for our 1st and 2nd mortgages.

thank you for offer of assistance.

 

[JLatham]

You're welcome, and glad you've got it all working now.

Has the template been any help in tracking down your problem in your
workbook? Or do you still need assistance with it?

You said it wasn't updating automatically, have you checked Tools | Options
| [Calculation] tab to make sure it is still set to Automatic calculating?
If you recently opened a workbook with it set to manual, it could have gotten
toggled.

If you think there might be something I could help with but would need the
workbook to dig into, feel free to email as attachment to (remove spaces)
HelpFrom @ jlathamsite. com

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/excel/HA010346401033.aspx
and a template is available from this page:http://office.microsoft.com/en-us/templates/TC011415371033.aspx

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

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?- Hide quoted text -

- Show quoted text -

The worksheet used "named references" instead of cell IDs in
equations. After about 90 minutes of work and self check, I have 2
working sheets which I am using for our 1st and 2nd mortgages.

thank you for offer of assistance.