ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   how do i create a loan with a balloon payment in Excel? (https://www.excelbanter.com/excel-worksheet-functions/119307-how-do-i-create-loan-balloon-payment-excel.html)

Rox A.

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/

Ron Coderre

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