amortization schedule / Mortgage payments

[Bmac]

I would like Excel to calculate the remaing balance based on the current
month. For example if I am making my 26th payment on my $200,000 mortgage,
and assuming i have made every payment on time and minimum payment only, what
is my balance as of this month.

 

[FSt1]

hi
see this site for a amortization template.
http://office.microsoft.com/en-us/templates/TC010197771033.aspx

regards
FSt1

 

[FSt1]

hi
here is another.
http://www.vertex42.com/ExcelTemplates/excel-amortization-spreadsheet.html

regards
FSt1

 

[Bernie Deitrick]

Let's say that you are paying 6% per year on a 30 year mortgage:

Enter this into a cell, and copy down to get a table
=PV(6%/12,361-ROW(A1),PMT(6%/12,360,200000))

or just this to get the Present value:
=PV(6%/12,361-26,PMT(6%/12,360,100000))

HTH,
Bernie
MS Excel MVP

 

[Bmac]

Thanks Bernie,

I am trying to create a sheet so that I can put an interest rate, loan
amount and term in and compare the variables against each other. When I put
the formula in and try to reference the rate and term to a particular cell
and then copy down, it doesn't maintain that same reference cell, it of
course copies down those cells too. How do I fix that?

 

[Bernie Deitrick]

When you select the cell and Excel puts in the address, press F4. That will convert, say, A1 to
$A$1. Subsequent presses will toggle to $A1, A$1, etc. The $ prevents the following row or column
address from incrementing when the cell is copied. (You can also type the cell address in that
way...)

HTH,
Bernie
MS Excel MVP