Microsoft Office Tutorials and References

In Depth Information

**Limitations of Excel’s Financial Functions**

If you know you paid $56.41 in interest last year on a $1,000 loan, you can compute the nominal

interest with the following formula:

=NOMINAL(56.41/1000,12)

This calculation results in a 5.5% APR compounded monthly.

Limitations of Excel’s Financial Functions

Excel’s primary financial functions (PV, FV, PMT, RATE, NPER, CUMIPMT, and CUMPRINC) are

very useful, but they have two common limitations:

h
They can handle only one level of interest rate.

h
They can handle only one level of payment.

For example, the NPER function cannot handle the variations in payments that arise with credit

card calculations. In such calculations, the monthly payment is based upon a reducing

outstanding balance and may also be subject to a minimum amount rule.

The common solution to the problem of varying payments is to create a cash flow schedule and

use other financial functions that can handle multiple payments and rates. Examples of the

process appear in the next two chapters. Briefly, the functions involved are

h
FVSCHEDULE:
Calculates a future value when the interest rate is variable

h
IRR:
Calculates a rate of return from a varying level of cash flow received at regular intervals

h
NPV:
Calculates the sum of the present values of a varying level of cash flow received at

regular intervals

h
MIRR:
A modified IRR that considers cash flows that are reinvested

h
XIRR:
Calculates a single rate from irregular cash flows

h
XNPV:
Calculates the net present value of irregular cash flows

In a situation that involves only slight variations, you can combine and nest Excel’s financial

functions.

The examples in this section can be found in the file named
extending basic

functions.xlsx
on the companion CD-ROM.

Deferred start to a series of regular payments

In some cases, a series of cash flows may have a deferred start. You can calculate the PV of a

regular series of cash flows with a deferred start by nesting PV functions.