Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I create an Excel calendar that references Excel spreadsheet? | Excel Discussion (Misc queries) | |||
Create database in excel? | New Users to Excel | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Wrong answer after using the payment formula in excel | New Users to Excel | |||
create price list from excel sheet | Excel Discussion (Misc queries) |