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/ |
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/ |
All times are GMT +1. The time now is 09:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com