Excel Payment Formula

Can someone explain to me the PMT formula开发者_如何学运维 in excel?

=-PMT ( 1) rate 2) nper 3) pv 4) [FV] 5) [Type] )

The above is the rough idea of what the pmt formula is. But am unsure about the ballooning payment and how to go about using this formula in excel.

Use the optional fv (future value) argument to record the balloon payment.

Loan = $200,000
Interest = 4.5%
Balloon = 120,000 after 10 years
Payments made monthly

=PMT(.045/12,10*12,-200000,120000)

Results in a payment of $1,279.11. After 120 payments, the loan balance will be $120,000. Note that the pv and fv argument signs must be opposite.

If your balloon payment isn't set but the term is

Loan = $200,000
Interest = 4.5%
Balloon = balance at 10 years
Monthly payment based on 30 year amortization

=PMT(.045/12,30*12,-200000)
=FV(0.045/12,10*12,1013.37,-200000)

The PMT function returns $1,013.37. This would be your payment to get the loan balance to zero after 30 years (360 payments). The FV function returns $160,178.96. This is the loan balance after 120 payments (10 years) of $1,013.37.