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
ThePMT 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.
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 theRATE 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.
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 thePV 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 theFV
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.
