Revisited - Balloon Loan help

[Robert Neville]

I am looking for some additional information on balloon loans; and
would like to create a spreadsheet to help me analyze offers relating
to an automobile purchase.

I found this excellent template linked below.
http://www.vertex42.com/ExcelTemplates/balloon-loan-calculator.html

After reviewing this Excel spreadsheet, it seems straight-forward,
yet I still need help understanding the math portion of balloon loans.

Basically, the template should help me analyze the dealer's offer; and
their subsequent counter-offer. The dealer's offers 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 been looking at the PMT, IPMT, PPMT, and PV formulas. How
different is the math portion of balloon loans to a regular loan? (I
am less interested in the pros and cons about the product.)

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.

 

[Barb Reinhardt]

If the dealer doesn't give you the information you need, I'd go to another
dealer.

 

[Bill Sharpe]

What's the car's sticker price? Does the manufacturer offer a rebate?
Assuming a zero percent interest rate on the loan, you'd be paying
$22,412 total for the vehicle.
Best thing to do is ask the dealer what interest rate he is using and go
from there. If he won't give you that information, assume 8%(or
whatever) interest, calculate the total interest for 48 months, subtract
it from $22,412, and offer him the remainder as a cash purchase price.
You could also calculate the time value of your balloon payment and
factor that in.

Bill

I am looking for some additional information on balloon loans; and
would like to create a spreadsheet to help me analyze offers relating
to an automobile purchase.

I found this excellent template linked below.
http://www.vertex42.com/ExcelTemplates/balloon-loan-calculator.html

After reviewing this Excel spreadsheet, it seems straight-forward,
yet I still need help understanding the math portion of balloon loans.

Basically, the template should help me analyze the dealer's offer; and
their subsequent counter-offer. The dealer's offers 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 been looking at the PMT, IPMT, PPMT, and PV formulas. How
different is the math portion of balloon loans to a regular loan? (I
am less interested in the pros and cons about the product.)

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.