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.
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).
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.