Part II: Working with Formulas and Functions
For an asset with a five-year useful life, the period for the first year goes from 0 to 1. For
the second year, the periods span 1–3. The third year spans periods 3–5. That continues
until the last year, which spans 9–10 (10 is double the five-year life). The starting period
portion of the formula evaluates like this:■ (^) If the year to compute is the acquisition year, make the starting period 0.
■ If the year to compute is the last year, make the starting period the useful life
times 2 and subtract 1.
■ For all other years, subtract the acquisition year from the year to compute, multiply
by 2, and subtract 1.
The ending period portion of the formula is similar to the starting period portion. For the
first year, it ends at period 1. For the last year, it ends at the useful life times 2. For the
middle years, it does the same calculation except that it adds one instead of subtracting.
By doubling the useful life, say from 7 periods to 14 periods for a 7-year asset, we can
introduce the half-year convention into a declining balance function like VDB.
Calculating present value
The time value of money (TVM) is an important concept in accounting and finance. The idea
is that a dollar today is worth less than the same dollar tomorrow. The difference in the
two values is the income that you can create with that dollar. The income may be interest
from a savings account or the return on an investment.
Excel provides several functions for dealing with TVM, such as the PV function for
calculating the present value. In its simplest form, PV discounts a future value amount by a
discount rate to arrive at the present value. If I promise to pay you $10,000 one year from
now, how much would you take today instead of waiting? Figure 15.19 shows how you would
calculate that amount:
=PV(C4,C3,0,-C2)
FIGURE 15.19
A present value calculation
The present value calculator in Figure 15.19 suggests that you would take $9,434 now
instead of $10,000 a year from now. If you took the $9,434 and were able to earn 6% over
the next year, you would have $10,000 at the end of the year.