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:
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
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.
Search JabSto ::

Custom Search