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

Custom Search