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" <(E-Mail Removed)> wrote in message
news:5C34199E-B751-4A35-A8BA-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:5C34199E-B751-4A35-A8BA-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:A1E454B9-4C42-4111-BD1E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:5C34199E-B751-4A35-A8BA-(E-Mail Removed)...
>> >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.
>>
>>
>>