LOAN CALCULATOR in Excel

How to calculate loan rates in Excel?   How to calculate a monthly payment in Excel?

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

 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: