Thread: Calculating a Mortgage Payoff

Hello,

I am working with an Excel list of contacts for our company.

Each row has the persons first name, last name, original mortgage amount, origination
date, loan term (months or years) interest rate, etc...

I am trying to calculate the mortgage "pay off" for each of these records. That is, I am trying to figure out the principle balance due on the loan as of today.

I understand that I can do a loan amoritization using an Excel function.

Is there anyway of telling excel that I need the Principle balance on a particular month?

For example. Let's say that the borrower orginated a loan on 1/1/2008 for $100,000
at 5% for 30 years. I know that as of today, the borrower is approximately 39 months along into this loan. Is there a method for formula that I can use to calculate the Principle payoff as of today for this person? Am I able to tap into and use an Amortization function? I need to do this calculation for each record.

Thank you for your help.

Mike

This is my first post on this forum, and you have probably already moved on or found the solution to this problem, but I figured out a solution:

In the next available cell on row 2, put "=FV(F2/12,(NOW()-D2)/(365.25/12),PMT(F2/12,E2*12,C2),C2,0)"

I'm assuming here that rows C-F are original amount, original date, loan length (years), and interest (APY).

I also forgot to add, we're making the huge assumption that these people are on schedule with their payments. You're better off knowing their remaining principal and going from there.