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.
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.
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.
Another thing you can do is play around with the number of months in your calculations to see how it impacts the 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.
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.
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.