In Excel, we can calculate the payment for a loan by applying function PMT function. This function will calculate the constant periodic payment that must be paid off investment, with a condition of constant interest rate.
Calculate Payment For A Loan
=PMT( rate, nper, pv )
rate is the constant interest rate of the period
nper is the number of periods that the investment/loan is paid
pv stands for a Present value of the investment/loan
An investment or a loan usually has four main components: the total amount of investment, the interest rate, the number of periods (term), and the payment per period. PMT function will calculate the payment per period when the other three components are available.
Figure 1. Example of using PMT function to calculate the monthly payment of investment
As an example in Figure 1, we can calculate the monthly payment by input
Rate – Since the interest rate in the formula is counted by month, for interest rate, we will have to find the monthly rate by divided 7.5% to 12 months.
Nper- the total period should be in months (60 months)
PV – the total investment amount is $10,000. The reason for using the minor (-) before B3 is to make this value negative. Since an investment is a money owed.
- Make sure that interest rate and a number of periods should be in the same units. (usually monthly basis)
- Negative or possible present value is only a display. We highly recommend putting a minor in front of PV for investment and loan only.