Formula for loan repayment with a future value

[Guest]

Hi,
Can anybody help me I need the formula to calculate repayments
Loan Amt: $20,000
Rate: 8,6%
Term 36 mths
Residual or Balloon Amt: $4,000.
Repayments: ????
I have used the PMT function but I don't know how to include a residual Pyt (end pyt)

 

[Norman Harker]

Hi Kez!

Use:
=PMT(8.6%/12,36,20000,-4000,0)
Returns: -534.488981131203

I've assumed that your rate is 8.6% per annum nominal compounded monthly.



--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi,
Can anybody help me I need the formula to calculate repayments
Loan Amt: $20,000
Rate: 8,6%
Term 36 mths
Residual or Balloon Amt: $4,000.
Repayments: ????
I have used the PMT function but I don't know how to include a residual

Pyt (end pyt)

 

[Peter atherton]

-----Original Message-----
Hi,
Can anybody help me I need the formula to calculate repayments
Loan Amt: $20,000
Rate: 8,6%
Term 36 mths
Residual or Balloon Amt: $4,000.
Repayments: ????
I have used the PMT function but I don't know how to

include a residual Pyt (end pyt)

.

Kes

=PMT(0.086/12,36,20000,4000,1) results in -724.87

The 4000 is the FV of the loan, sort of endowment mortgate
in the UK.

If the 4K is omitted the result is -627.78

Regards
Peter

 

[Norman Harker]

Hi Peter!

I think that you are mis-understanding the term "Balloon"

It's an interest only bearing part of a loan. The borrower borrows the 20000
and makes "normal" principal and interest payments on 16000 with interest
only being paid on the 4000 balloon element. The balloon element is repaid
at the end of the loan (or, refinanced),

We could calculate as per my suggestion:
=PMT(8.6%/12,36,20000,-4000,0)
Returns: -534.488981131203

Or we could calculate using:
=PMT(8.6%/12,36,16000,0,0)-(8.6%/12)*4000
Returns: -534.488981131203

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.