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:

## No comments:

Post a Comment