Financial Functions in Excel

MS Excel has an excellent function set for performing financial calculations. This tutorial will consider a loan with monthly payments, a 30-years duration, an annual interest rate of 6%, a present value of $150,000 (amount borrowed) and a future value of 0 (a hope amount to be achieve when load is paid off).

In the scenario user pays monthly payments of 6%/12 = 0.5% for Rate and 30*12 = 360 for Nper (total number of periods). If the user makes an annual payments on the same loan, he uses 6% for Rate and 30 for Nper.

Pmt

Select cell A2 and insert the PMT function.

Note: The last two arguments are optional. For loans the Fv can be omitted. If Type is omitted, it is assumed that payments are due at the end of the period.

The monthly payment is equals to $899.33.

Tip: while working with financial functions in MS Excel, always put a question, “am I making a payment or am I receiving a payment? When a user takes a loan of $150,000, it is positive because he has received the amount and now he makes monthly payments of $899.33 which is negative because the user is paying it off.

Rate

A user can use the RATE function to calculate the interest rate, if Rate is the only unknown variable.

Nper

If a user makes a monthly payments of $899.33 on a 30-year loan, with an annual interest rate of 6%, it takes 360 months to pay off this loan. This is called the NPER function.

You have already knew this, but you can change the monthly payment now to see how this affects the total number of periods.

100.7154

Conclusion: if you make monthly payments of $1,899.33, it takes less than 101 months to pay off this loan.

If a user makes monthly payments of $899.33 on a 30-year loan, with an annual interest rate of 6%, how much can he borrow? You already know the answer. This function is called as the PV (Present Value) function

If a user makes monthly payments of $899.33 on a 30-year loan, with an annual interest rate of 6%, do he pays off this loan? Yes. This function is called as the FV (Future Value) function.

But, if he makes monthly payments of only $1,000.00, he still has debt after 20 years.