To illustrate the most popular Excel financial functions, we will consider a loan with monthly payments, interest rate 6% per year, the term of this loan is 6 years, the present value (Pv) is $ 150000 (loan amount) and future value (Fv) will be equal to $0 (this is the amount that we hope to receive after all payments). We pay monthly, so in the column Rate calculate the monthly rate 6%/12=0,5%, and in the column nper calculate the total number of payment periods 20*12=240.
If payments on the same loan are made 1 once a year, then in the column Rate you need to use the value 6%, and in the column nper – value 20.
PLT
Select a cell A2 and insert the function PLT (PMT).
Explanation: The last two arguments of the function PLT (PMT) are optional. Meaning Fv may be omitted for loans (the future value of the loan is assumed to be $0, but in this example the value Fv used for clarity). If the argument Type is not specified, it is considered that payments are made at the end of the period.
Result: Monthly payment is $ 1074.65.
Tip: When working with financial functions in Excel, always ask yourself the question: am I paying (negative payment value) or am I being paid (positive payment value)? We borrow $150000 (positive, we borrow this amount) and we make monthly payments of $1074.65 (negative, we repay this amount).
RATE
If the unknown value is the loan rate (Rate), then it can be calculated using the function RATE (RATE).
KPER
Function KPER (NPER) is similar to the previous ones, it helps to calculate the number of periods for payments. If we make monthly payments of $ 1074.65 on a loan with a term of 20 years with interest rate 6% per year, we need 240 months to pay off the loan in full.
We know this without formulas, but we can change the monthly payment and see how this affects the number of payment periods.
Conclusion: If we make a monthly payment of $2074.65, we will pay off the loan in less than 90 months.
PS
Function PS (PV) calculates the present value of a loan. If we want to pay monthly $ 1074.65 according to taken on 20 years loan with an annual rate 6%What size of loan should be? You already know the answer.
BS
Finally, consider the function BS (FV) to calculate future value. If we pay monthly $ 1074.65 according to taken on 20 years loan with an annual rate 6%Will the loan be paid in full? Yes!
But if we lower the monthly payment to $ 1000then after 20 years we will still be in debt.
