Microsoft Office Tutorials and References

In Depth Information

**Chapter 12: Discounting and Depreciation Formulas**

Calculating future value

The data for this example is shown in Figure 12-10. The NPV calculation is performed by the

formula in cell B15:

=NPV(B3,B7:B13)+B6

The future value is calculated using the following formula (in cell B17):

=(NPV(B3,B7:B13)+B6)*(1+B3)^7

Figure 12-10:
Calculating FV using the NPV function.

The result is also computed in column D, in which formulas calculate a running balance of the

interest. Interest is calculated using the interest rate multiplied by the previous monthâ€™s balance.

The running balance is the sum of the previous balance, interest, and the current monthâ€™s cash

flow.

It is important to properly sign the cash flows. Then, if the running balance for the previous

month is negative, the interest will be negative. Signing the flows properly and using addition is

preferable to using the signs in the formulas for interest and balance.

Smoothing payments

Chapter 11 covers the use of the PMT function to calculate payments equivalent to a given

present value. Similarly, you can use the NPV function, nested in a PMT function, to calculate an

equivalent single-level payment to a series of changing payments.

This is a typical problem where you require a time-weighted average single payment to replace a

series of varying payments. An example is an agreement in which a schedule of rising rental

payments is replaced by a single-level payment amount. In the example shown in Figure 12-11, the