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:                         Search JabSto ::

Custom Search