View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre Ron Coderre is offline
external usenet poster
 
Posts: 2,118
Default how do i create a loan with a balloon payment in Excel?

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


"Rox A." wrote:

I would like to create a loan that has the option of a balloon payment for
the 12th payment/