Financial Functions in Excel
To explain some of Excel’s most common financial functions, we will use a loan example. The loan has monthly payments, a 6% annual interest rate, a 20-year term, a present value of $150,000 (the amount borrowed), and a future value of $0 (the goal after repaying the loan).
We make payments every month, so we divide the annual interest rate (6%) by 12 to get a monthly rate of 0.5%. To get the total periods (240) multiply the number of years (20) by 12. If we are making annual payments on the same loan, we should use 6% as the interest rate and 20 as the number of periods.
PMT
Click into cell A2 and enter the PMT formula to perform the payment calculation.
Note: the last two arguments are optional. Since loans are paid off completely, the future value (FV) is zero. Therefore, FV is often not required, but it’s shown here for better understanding. If you don’t select a Type, the payment will be considered due at the end of the period.
Result: the monthly payment equals $1,074.65.
Tip: When using financial functions in Excel, always consider whether you are paying money (use a negative value) or receiving money (use a positive value). We took a loan of $150,000 (inflow) and are making monthly repayments of $1,074.65 (outflow). To explore more examples, go to our page on the PMT function.
RATE
If the interest rate is the only unknown variable, we can calculate it using the RATE function.
NPER
Or the NPER function. When you pay $1,074.65 every month on a 20-year loan with 6% annual interest, the total time to pay off the loan is 240 months.
We know this already. Now, let’s adjust the monthly payment to understand its impact on the number of periods.
Conclusion: By paying $2,074.65 each month, the loan will be fully paid in less than 90 months.
PV
Or the PV (Present Value) function. If you pay $1,074.65 each month for 20 years at 6% interest, what is the total amount you can borrow? You already know the answer.
FV
We conclude this chapter by introducing the FV (Future Value) function. If we pay $1,074.65 each month for 20 years at 6% interest, will we pay off the loan? Yes.
However, if we pay only $1,000.00 per month, the debt will not be fully paid off after 20 years.
1/11 Completed! Learn more about financial functions ➝
Next Chapter: Statistical Functions
Leave a Reply