Microsoft Office Tutorials and References

In Depth Information

**Limitations of Excel’s Financial Functions**

In this example, you get a loan to start a business. You can afford to pay $7,000 per month, and

you negotiate a deal with the bank to defer the first payment for 12 months. If the bank quotes an

8% rate on a ten-year loan, this formula will tell you how much you can borrow (see Figure 11-16):

=PV(8%/12,12,0,–PV(0.08/12,10*12,–7000))

Figure 11-16:
Calculating the present value of regular payments with a deferred start is a two-step process.

First, calculate the present value, which is $576,950. This value is used as the future value

argument of the outer function. The outer function further discounts this amount over the year

deferral period, and results in $532,733. In other words, if you borrow $532,733 now, the amount will

increase to $576,950 in one year with no payments, and it will reduce to zero in ten years with a

$7,000 monthly payment.

Valuing a series of variable payments

This example calculates the present value when the payments change over time. Assume that

you want to buy your way out of a property lease, and you need to know how much it’s worth.

There are nine years left on the lease, and the payment schedule is

h
Years 1–3: $5,000/month

h
Years 4– 6: $6,500/month

h
Years 7– 9: $8,500/month

The following formula calculates the value of the lease assuming a 10% discount rate:

=PV(10%/12,36,–5000)+

PV(10%/12,3*12,0,–PV(10%/12,36,–6500))+

PV(10%/12,6*12,0,–PV(10%/12,36,–8500))

The result of $449,305 is calculated in three steps: