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