Remember Me?

#1
November 17th 06, 11:03 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 1
how do i create a loan with a balloon payment in Excel?

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

#2
November 18th 06, 12:16 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,118
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/

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post intern, aka: slave labor Excel Discussion (Misc queries) 1 July 24th 06 06:31 PM wirthless New Users to Excel 7 October 30th 05 03:57 PM MEGTOM New Users to Excel 5 October 27th 05 03:06 AM punkyh New Users to Excel 8 April 28th 05 07:56 PM -keevill- Excel Discussion (Misc queries) 1 February 8th 05 01:02 AM

All times are GMT +1. The time now is 06:45 AM.