amortization schedule / Mortgage payments

B

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.
 
B

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
 
B

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?
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top