Microsoft Office Tutorials and References

In Depth Information

**Chapter 12: Discounting and Depreciation Formulas**

Figure 12-7:
The NPV function can accept multiple positive and negative cash flows.

Mismatched interest rate periods

In the previous examples, the discount rate conveniently matched the time periods used in the

cash flow. Often, you’ll be faced with a mismatch of rate and time periods. The most common

situation occurs when the desired rate of return is an annual effective rate and cash flows are

monthly or quarterly. In this case, you need to convert the discount rate to the appropriate

period.

See Chapter 11 for a discussion on interest rate conversion.

Figure 12-8 shows a rental of $12,000 paid quarterly in advance. It also shows an initial price of

$700,000 and a sale (after three years) for $900,000. Note that because rent is paid in advance,

the purchaser gets a cash adjustment to the price. However, at the end of three years (12

quarters), the same rule applies, and the rent payable for the next quarter is received by the new

owner. If you discount at 7% per annum effective, this shows an NPV of $166,099.72. The formula

in cell D22 is:

=NPV(C5,D8:D20)*(1+C5)

In some situations, determining the frequency of cash flows is simple. With rent, for instance, the

lease agreement spells out how often rent is paid. When the future cash flow is revenue from the

sale of a product, the figures are usually estimates. In those cases, determining whether to state

the cash flows monthly, quarterly, or annually is not so clear. Generally, you should use a

frequency that matches the accuracy of your data. That is, if you estimate sales on an annual basis,

don’t divide that number by 12 to arrive at a monthly estimate.