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):
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:
The result of $449,305 is calculated in three steps: