Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
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/



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I create an Excel calendar that references Excel spreadsheet? intern, aka: slave labor Excel Discussion (Misc queries) 1 July 24th 06 06:31 PM
Create database in excel? wirthless New Users to Excel 7 October 30th 05 03:57 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Wrong answer after using the payment formula in excel punkyh New Users to Excel 8 April 28th 05 07:56 PM
create price list from excel sheet -keevill- Excel Discussion (Misc queries) 1 February 8th 05 01:02 AM


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

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017