Excel has a wide range of financial functions that can be used to calculate a variety of financial concepts, such as present value, future value, interest rates, and depreciation. These functions can be used to analyze financial data, make financial projections, and track financial performance.

Introduction

Here are some of the most commonly used financial functions in Excel:

• PV (Present Value): The PV function calculates the present value of a future sum of money. This is useful for calculating the current value of an investment, loan, or other financial instrument.
• FV (Future Value): The FV function calculates the future value of a present sum of money. This is useful for calculating how much money you will have in the future if you invest a certain amount of money today.
• IRR (Internal Rate of Return): The IRR function calculates the internal rate of return on an investment. This is the rate of return that an investment would need to generate in order to break even.
• NPV (Net Present Value): The NPV function calculates the net present value of a series of cash flows. This is useful for comparing different investment options.
• PMT (Payment): The PMT function calculates the periodic payment amount for a loan or annuity. This is useful for calculating how much you will need to pay each month on a mortgage or car loan.
• RATE (Interest Rate): The RATE function calculates the interest rate on a loan or annuity. This is useful for calculating how much interest you will pay on a loan or how much interest you will earn on an investment.

To illustrate Excel’s most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that’s what you hope to achieve when you pay off a loan).

We make monthly payments, so we use 6%/12 = 0.5% for RATE and 20*12 = 240 for NPER (total number of periods). If we make annual payments on the same loan, we use 6% for RATE and 20 for NPER.

Financial Function: PMT

Select cell A2 and insert the PMT function.

Note: the last two arguments are optional. For loans, FV can be omitted (the future value of a loan equals 0, however, it’s included here for clarification). If Type is omitted, it is assumed that payments are due at the end of the period.

Result. The monthly payment equals $1,074.65.

Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)? We pay off a loan of $150,000 (positive, we received that amount) and we make monthly payments of $1,074.65 (negative, we pay). Visit our page about the PMT function for many more examples.

RATE

If Rate is the only unknown variable, we can use the RATE function to calculate the interest rate.

NPER

Or the NPER function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, it takes 240 months to pay off this loan.

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

Conclusion: if we make monthly payments of $2,074.65, it takes less than 90 months to pay off this loan.

PV

Or the PV (Present Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, how much can we borrow? You already know the answer.

FV

And we finish this chapter with the FV (Future Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, do we pay off this loan? Yes.

But, if we make monthly payments of only $1,000.00, we still have debt after 20 years.

Conclusion

These are just a few of the many financial functions available in Excel. For more information on these functions, please refer to the Excel Help documentation.

Here are some examples of how financial functions can be used in Excel:

• A financial analyst can use the PV function to calculate the present value of a company’s future cash flows. This information can be used to determine the company’s value and to make investment decisions.
• A business owner can use the FV function to calculate how much money they will have in the future if they invest a certain amount of money today. This information can be used to make financial projections and to plan for retirement.
• A homebuyer can use the PMT function to calculate how much they will need to pay each month on a mortgage. This information can be used to compare different mortgage options and to make sure that they can afford the monthly payments.

Financial functions can be a powerful tool for financial analysis, financial planning, and financial decision-making. By understanding how these functions work, you can use them to make better financial decisions.