CODEWITHSUNDEEP
excelexcel-2007excel-formula
dwyane
dwyane

Reputation: 327

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.

Upvotes: 1

Views: 6852

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 1

Related Questions