Loan Schedule with Balloon Payment

[R0bert Neville]

I am looking for an Excel worksheet example of a loan schedule with a
balloon payment at the end. My internet search has not found much on
the subject; and generally returns results about traditional loan
payment schedules. I want to create a worksheet rather than use a loan
calculator found on the web as well.

This request related to new automobile financing incentive plans
revolving around loans with balloon payment at the end. My mother is
in the market for a new car and she recently saw an AD with this
incentive option. An Excel solution will help me show the real costs
associated with the loan and change numbers as when the dealer changes
the options (apparently they never have the left from the
advertisement). I already created a traditional loan payment schedule
in Excel; so a balloon payment should not be far off. But I not
entirely familiar with the math portion behind these balloon payments.
Please let know how to create such a worksheet or the math behind
them.

 

[Dana DeLouis]

Microsoft's Template gallery had this, but I'm not sure if it's helpful.

Balloon loan calculator
http://office.microsoft.com/en-us/templates/TC010525031033.aspx

 

[R0bert Neville]

Microsoft's Template gallery had this, but I'm not sure if it's helpful.

Balloon loan calculator
http://office.microsoft.com/en-us/templates/TC010525031033.aspx

Thanks Dana.

The template works.

Does anyone know the math portion? The information will help me
customize the template.

 

[Lewis Clark]

You can use the IPMT and PPMT functions to find the interest and principle
payments during each payment period, and the PMT function to find the total
principle and interest payment.

With a balloon loan, you usually just make interest payments each month.
Then when the time for the balloon payment arrives you are paying back all
of the still-outstanding principle.

What sort of customizing are you looking to do?

 

[Robert Neville]

You can use the IPMT and PPMT functions to find the interest and principle
payments during each payment period, and the PMT function to find the total
principle and interest payment.

With a balloon loan, you usually just make interest payments each month.
Then when the time for the balloon payment arrives you are paying back all
of the still-outstanding principle.

What sort of customizing are you looking to do?

My concern in a balloon loan calculator relates to an automobile
purchase. Customizing the worksheet means allowing one to find the
interest from terms, pmt, balloon payment; finding the pmt from terms,
interest, balloon payment; finding the balloon payment from term,
interest, and pmt. In addition, I need to customize it to include all
other automobile purchase fees.

I am just looking for a few references on the math portion as I work
to reassemble the worksheet. But your suggestions on functions does
lead me in the right direction.

 

[Robert Neville]

You can use the IPMT and PPMT functions to find the interest and principle
payments during each payment period, and the PMT function to find the total
principle and interest payment.

With a balloon loan, you usually just make interest payments each month.
Then when the time for the balloon payment arrives you are paying back all
of the still-outstanding principle.

What sort of customizing are you looking to do?

Here's another excellent template site.
http://www.vertex42.com/ExcelTemplates/balloon-loan-calculator.html

After reviewing these Excel spreadsheets, they seem straight-forward.
I still need to customize the template for my application, buying a
car for mom.

Basically, the template should help me analyze the dealer's offer; and
their subsequent counter-offer. The incentive plan is a balloon
payment plan: $269 a month, 48 months until the balloon, $10,500
payment at the end. They make no mention of the total purchase price,
amortization term, or interest rate. What questions do I need to ask
the dealer to develop a viable Balloon Payment Calculator?

I have to develop worksheets to figure out the purchase price,
interest rate, and payments. In case the dealer only gives me a subset
of the information.