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.

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

regards
FSt1

"Bmac" wrote:

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.

hi
here is another.
http://www.vertex42.com/ExcelTemplat...readsheet.html

regards
FSt1

"Bmac" wrote:

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.

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" wrote in message
...
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.

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" wrote:

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" wrote in message
...
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.

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


"Bmac" wrote in message
...
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" wrote:

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" wrote in message
...
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.