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

=FV(5.75%/12,5*12,–900,150000,0)

Figure 11-6:
Calculating the future value of payments and a lump sum.