Microsoft Office Tutorials and References

In Depth Information

**13. Using Financial Functions**

13. Using Financial Functions

Although the bulk of Excel
’
s financial functions are for professional fin-

anciers and investors, a few functions are useful for anyone planning to use

a loan to purchase a car or house. The examples in this chapter represent

a small subset of the calculations possible with Excel
’
s financial func-

tions.

Excel 2013 adds two new financial functions:

•
PDURATION

PDURATION
—
Calculates how many periods it will take for an in-

vestment to reach a certain value given a specific interest rate.

•
RRI

RRI
—
Calculates the equivalent rate for the growth of an investment.

You can enter the beginning value, the value now, and how many years

have gone by, and the function will calculate the average annual in-

terest rate you earned.

Note

Eight other functions began using
“
new algorithms
”
starting in Excel

2010. This means that a worksheet in Excel 2007 might return different

answers from a worksheet in Excel 2013. These improved algorithms of-

ten affect only fringe cases of the functions. For normal usage, the

results are usually the same. However, if Excel 2013 returns a dif-

ferent result, it is more accurate than the Excel 2007 result. Here are

the functions affected:

CUMIPMT

CUMIPMT
—
Cumulative interest paid on a loan

CUMPRINC

CUMPRINC
—
Cumulative principal paid on a loan

IPMT

IPMT
—
Interest payment for an investment

IRR

IRR
—
Internal rate of return for a series of cash flows

PMT

PMT
—
Payment for a loan

PPMT

PPMT
—
Payment on principal for an investment

XIRR

XIRR
—
Internal rate of return for a schedule of cash flows