Excel - Number of Period (NPER) & Payment Amount (PMT)

As I promised, I will write more about Excel in our daily/monthly financial planning. Sometimes it is quite tedious to calculate how much payment/period of saving/installment you need to clear a loan or accumulate a certain value. Or even how much do you need to pay or save in order to repay your debt in 5 years time.

First of all, to get how long does you takes to clear your student loan. Let say the loan is $40,000 and the rate is 4.75%. Installment per month is $500. Below is the formula for the NPER.

NPER( interest_rate, payment, PV, FV, Type )

interest_rate = the interest rate for the investment/loan.
payment = amount of the payment for each period.
PV = the present value of the payments.
FV (optional) = the future value that you'd like the investment to be after all payments have been made. (For loan case, is $0 because you need to clear your loan)
type (optional) same with the type for Future Value which has been discussed earlier.

We type =NPER(4.75%/12,500,-40,000,0,1). For analysis you might want to identify how many payment does you need to do to clear off your debt, so you the coordinate formula which I discussed on Future Value.

Together with some calculations (Num of Payment x Installment - Total Value of Loan), you even can figure out how much does you lose as interest to bank for paying certain amount of installment. (**Note that the interest I took is a constant interest but it is not a constant interest for actual case).

For second case is more relevant to car loan but I still use student loan as an example because it is more close to us. And most of the case of unit trust investment also can use this function to calculate how much you need to pay per installment for specific returns. The formula as below:

PMT( interest_rate, number_payments, PV, FV, Type )

interest_rate = the interest rate for the loan/investment.
number_payments = the number of payments for the loan/investment.
PV = the present value or principal of the loan.
FV (optional) = future value or the loan amount outstanding after all payments have been made.
type (optional) ditto as above.

The above figure is the examples I did. The left hand side is student loan repayment plan. If you wish to settle your loan in 5 years time. You need to pay $747.32 per month. The function formula is =PMT(4.75%/12,year*12,-40,000,0,1). Year here is a constant column, inconstant row variable so you need to use $Xnn.

The right hand side is an investment with constant 12.5% return annually. The payment mode is quarterly (4 times per year) so the function should looks like this =PMT(12.5%/4,year*4,0,-100,000,0). After looking at the example, I am sure you can do it on Excel too.

Happy calculating!