Microsoft Office Tutorials and References
In Depth Information
The Basic Excel Financial Functions
Rounding of financial formulas
When you’re working with financial formulas, the issue of rounding is almost certain to arise.
Excel offers several relevant functions, including ROUND, ROUNDUP, and ROUNDDOWN.
To help prevent cumulative errors, round only the final calculated value. In other words, avoid
rounding any intermediate, nonreported calculations.
In general, financial calculations rarely display more than two decimal places, and they often
display only full dollar values. For intermediate calculations, this means that you format to the
nearest cent (or dollar) and thus retain the fully accurate figure for subsequent calculations.
In some cases, calculations will be based on approximated data or data based upon subjective
opinions or adjusted data (such as rental values). A common professional practice is to report
rounded figures to avoid misleading readers. For example, you may have a rental value of
$43.55 per square foot, based on an average of recent transactions. If this rate is applied to an
area of 1,537 square feet, the calculated rental value is $66,936.35. However, the rental rate is
actually an approximation (it may actually be between $42 and $45). To avoid giving an
impression of extreme accuracy, you may want to round the calculated rental value to the nearest $100
or even nearest $1,000.
One problem of the accuracy allowed by modern technology is a danger of being seduced by
the precision of point estimates.
In this example, you are going to make monthly payments of $900 against your $150,000
mortgage. If your mortgage interest rate is 5.75%, this formula computes how much you will still owe
on your house in 5 years (see Figure 11-6):
Figure 11-6: Calculating the future value of payments and a lump sum.