Financial Functions

How to use Financial functions in Excel

In this tutorial I will show you how to use some very useful financial functions in Excel by walking you through an example about a mortgage.

PMT Function

The PMT function calculates the monthly payments for a loan based on constant payments and a constant interest rate.

As an example let’s day we have a loan with monthly payments, an annual interest rate of 8%, a 30-year duration, a present value of $200,000, and a future value of 0 (amount of debt remaining after making all repayments).

We make monthly payments so we put a rate of 0.66% (this is what you get when you divide 8% by 12 months). Subsequently, we set 360 for nper (30 years * 12 months) which is the total number of periods.

Using the PMT function in Excel
Using the PMT function in Excel
Example of a completed PMT function in Excel
Example of a completed PMT function in Excel

In this case you would have to pay $1,456.39 dollars per month to repay your loan in 30 years.

Note that for loans FV can be omitted (FV of a loan equals 0). If Type is left out Excel assumes that payments will be due at the end of each period.

RATE Function

Using the same example, it might be the case that instead of not knowing the monthly payment, you have that information but you don’t know how much the rate is. In this case, you can use the RATE function to calculate the interest rate.

Using the RATE function in Excel
Using the RATE function in Excel

NPER Function

In a similar fashion, when you don’t know the number of periods but you do have all the other information, you should use the NPER function to calculate the number of periods.

Using the NPER function in Excel
Using the NPER function in Excel

Another thing you can do is play around with the number of months in your calculations to see how it impacts the monthly payments.

Find the number of repayment periods necessary with different monthly payments
Find the number of repayment periods necessary with different monthly payments

In this case if you increase the monthly repayments to $3,000 dollars per month, you will only need 88 periods to pay back your loan.

PV Function

And you might have understood the pattern already, but if you know all the other data while you don’t know the loan amount, you can use the PV function to find out how much money is borrowed given you know the monthly payments, the interest rate, and number of periods.

Using the PV function in Excel
Using the PV function in Excel

FV Function

Use the FV function to determine if you will successfully pay off your debt or will have outstanding debt given you make certain monthly payments, on a loan with an interest rate.

In the first example it shows that by paying $1456.39 you will pay off your loan in 30 years, but if you only make monthly payments of $1400 as is shown in the second example, you’ll be left with a debt of $82689.88 at the end of the period.

Using the FV function in Excel
Using the FV function in Excel
Find the amount of debt remaining after finishing all repayment periods with different monthly payments
Find the amount of debt remaining after finishing all repayment periods with different monthly payments

As this might be a quite complex issue I’m sure you might have some questions. If you do, just let me know below and I’ll answer you personally.