Office For Seniors For Dummies
Book image
Explore Book Buy On Amazon
Excel 2019 financial functions are some of the most useful tools for home and small business worksheets because they’re all about the money: borrowing it, lending it, and monitoring it. Here’s the basic set of financial functions in Excel 2019:
  • PV: Calculates the present value or principal amount. In a loan, it’s the amount you’re borrowing; in a savings account, it’s the initial deposit.
  • FV: The future value. This is the principal plus the interest paid or received.
  • PMT: The payment to be made per period. For example, for a mortgage, it’s the monthly payment; in a savings account, it’s the amount you save each period. A period can be any time period, but it’s usually a month.
  • RATE: The interest rate to be charged per period (for a loan), or the percentage of amortization or depreciation per period.
  • NPER: The number of periods. For a loan, it’s the total number of payments to be made, or the points in time when interest is earned if you’re tracking savings or amortization.
These financial functions are related. Each is an argument in the others; if you’re missing one piece of information, you can use all the pieces you do know to find the missing one. For example, if you know the loan amount, the rate, and the number of years, you can determine the payment.

Take a look at the PMT function as an example. The syntax for the PMT function is as follows, with the optional parts in italics: 

PMT(RATE, NPER, PV, <em>FV, Type</em>)

The Type argument specifies when the payment is made: 1 for the beginning of the period or 0 at the end of the period. It’s not required.

So, for example, say the rate is 0.833% per month (that’s 10% per year), for 60 months, and the amount borrowed is $25,000. The Excel formula looks like this: 
=PMT(.00833,60,25000)
Enter that into a worksheet cell, and you’ll find that the monthly payment will be $531.13. You could also enter those values into cells, and then refer to the cells in the function arguments, like this (assuming you entered them into B1, B2, and B3): 
=PMT(B1,B2,B3)
Here is the syntax for each of the above-listed functions. As you can see, they’re all intertwined with one another: 
FV(RATE, NPER, PMT, <em>PV, Type</em>)
PMT(RATE, NPER, PV, <em>FV, Type</em>)
RATE(NPER, PMT, PV, <em>FV, Type</em>)
NPER(RATE, PMT, PV, <em>FV, Type</em>)
Here are some common ways to use Excel functions in everyday life:

If I borrow $10,000 for 3 years at 7% yearly interest, what will my monthly payments be?

Use the PMT function. Divide the interest rate by 12 because the rate is yearly, but the payment is monthly. Here is the syntax: 

=PMT(0.07/12,36,10000)
If I borrow $10,000 at 7% interest and I can afford to make payments of $300 per month, how many months will it take to pay off the loan?

Use the NPER function. Again, divide the interest rate by 12 to convert from yearly to monthly. Here’s the syntax: 

=NPER(0.07/12,300,10000)
If I deposit $10,000 into a bank account that pays 2% interest per year, compounded weekly, how much money will I have after 5 years?

You want the future value of the account, or FV. The period is weekly, so the interest rate should be divided by 52, and the length of the loan is 260 periods (52 weeks multiplied by 5 years). The PMT amount is 0 because no additional deposits will be made after the initial deposit. The initial deposit is expressed as a negative number because the calculation is from the perspective of the interest earner, not the interest payer. Here is the syntax: 

=FV(0.02/52,260,0,-10000)
As a final example, let’s check out a mortgage spreadsheet that was started at an earlier time and fill in some numbers. Each field is annotated to explain what each one contains.

Excel 2019 mortgage calculation

It’s all pretty self-explanatory, except the PMT function in cell B11: 

=PMT(B9/12,B10*12,B6)
The PMT function requires these three arguments:
  • Rate (RATE): For the rate, you’ll use B9/12 because you want to calculate a monthly payment, and the amount in B9 is per year. The interest rate per month is of the interest rate per year.
  • Number of periods (NPER): The number in B10 is years, and there are 12 periods (months) in each year — so multiply B10 by 12.
  • Present value (PV): For the present value, refer to B6, which contains the amount to be financed.
This example is especially useful because it shows how a function can be modified to fit a situation. In this case years had to be converted (which is how most people think about loans) to months (which is how Excel calculates loans).

One more thing: did you notice that the payment is negative in cell B11? To make it a positive number, you could enclose the function in an absolute value function: ABS. Then it would have looked like this: 

=ABS(PMT(B9/12,B10*12,B6))
When one function is nested inside another, there is only one equal sign, at the very beginning.

For more information, find out other ways you can analyze financial data using Excel.

About This Article

This article is from the book:

About the book author:

Faithe Wempen is an adjunct instructor at Indiana University Purdue University at Indianapolis (IUPUI). She is the author of over 150 books on computer hardware and software, and an online technology courseware developer whose courses have educated over a quarter of a million people.

This article can be found in the category: