MORTGAGE CALCULATOR in Excel

How to calculate mortgage rates in Excel? How to make in Excel simple Mortgage Calculator?

---

Mortgage calculators are used to help a current or potential real estate owner determine how much they can afford to borrow on a piece of real estate. Mortgage calculators can also be used to compare the costs, interest rates, payment schedules, or help determine the change in the length of the mortgage loan by making added principal payments (read more on wikipedia).

http://en.wikipedia.org/wiki/Mortgage_calculator

---

To calculate the payments of a mortgage in Excel, you can use a simple formula PMT which calculates the mortgage rate  based on constant payments and a constant periodic interest rate.

PMT(rate, nper, pv, fv, type)

Rate -  is the interest rate for the loan.
Nper -  is the total number of payments for the loan.
Pv -  is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv - is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type - is the number 0 (zero) or 1 and indicates when payments are due.

You can used to PPMT function - Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

PPMT(rate, per, nper, pv, [fv], [type])

Rate - Required. The interest rate per period.
Per - Required. Specifies the period and must be in the range 1 to nper.
Nper - Required. The total number of payment periods in an annuity.
Pv - Required. The present value — the total amount that a series of future payments is worth now.
Fv - Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type - Optional. The number 0 or 1 and indicates when payments are due.


Download example XLS file: