amortization schedule / Mortgage payments

  • Thread starter Thread starter Bmac
  • Start date Start date
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.
 
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
 
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?
 
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
 
Back
Top