Microsoft Office Tutorials and References

In Depth Information

**The Basic Excel Financial Functions**

The payments are monthly, so the other arguments are converted to months — the annual

interest rate is divided by 12, and the nper expressed in years is multiplied by 12. The current balance

of the mortgage is shown as a cash inflow in this example even though no cash is actually

flowing in. There was a cash inflow when you originally bought the house. That is, someone paid you

a large sum of money in exchange for a promise to pay it back, and you turned around and

bought a house with the money. Because the scope of your problem is from now until five years

from now, it doesn’t contemplate the time when the funds actually flowed in.

One way to think of it is that someone loaned you $150,000 right now to pay off your

mortgage, even though that didn’t really happen. The –$137,435.10 is the computed

outflow to pay that money back at the end of the five years.

Calculating payments

The PMT function computes payments required to get a certain balance (pv) down to zero, or

some other number (fv). Its syntax is

PMT(rate, nper, pv, fv, type)

Computing loan payments

When borrowing money, a key consideration is the periodic payment amount.

In this example, you want to buy a $32,000 car, and you need to compute how much your

monthly payments will be. You will make a down payment of $4,000, and the car dealership is

offering 2.1% financing for a four-year loan (see Figure 11-7).

=PMT(2.1%/12,4*12,28000,0,0)

The formula returns $608.69. So, if you can handle such a monthly outflow, you can get the

$28,000 that you borrowed down to zero in 48 payments.

Figure 11-7:
Calculating a loan payment.