
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).
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: