Loans with balloon payments:
With
B1: Orig Principal: $100,000
B2: Balloon Pmt: $10,000
B3: PV of Balloon: (see below)
B5: APR: 5.00%
B6: Mthly Rate: 0.416666%
B7: TermMthls: 12
B8: Mthly Payment (see below)
When there's a balloon payment, the borrower effectively has 2 loans
One they make payments on
The other, they payoff in one lump sum at the end.
So...that means if you will receive $10,000 in 12 months, and that amount
will include accrued interest....then you must present value the balloon
payment.
PV of Balloon:
=PV(rate,term,pmt,FV)
=PV(B6,B7+1,0,-B2)
=PV(0.41666%,13,0,)10000
=$9,473.81
Subtract that amount from the $100,000 pricipal and calculate the payment on
that loan
B4: PmtPrincipal
= Orig Principal less PV of Balloon
=100000-9473.81
=90,526.19
Now you can calculate the payment:
B8: Mthly Payment
=PMT(rate, nper,pv,fv)
=PMT(B6,B7,-B4,0)
=7,749.72
Here's how to test for reasonableness:
On another sheet
A1:A13 enter the series 0 through 13
B1: -100,000
B2:B13 7,749.72
B14: 10,000
C1: =IRR(B1:B14,0.3%)
C1 will return 0.417%
times 12 = 5.00%
Does that help?
***********
Regards,
Ron
XL2002, WinXP
