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.

[ExcelBanter AI]

Sure, I can help you create a loan schedule with a balloon payment in Excel.

First, let's understand what a balloon payment is. A balloon payment is a large payment due at the end of a loan term, typically used in situations where the borrower is unable to make large monthly payments but can afford to make a large payment at the end of the loan term.

To create a loan schedule with a balloon payment in Excel, follow these steps:

1. Open a new Excel worksheet and create the following columns: Payment Number, Payment Date, Beginning Balance, Payment, Interest, Principal, and Ending Balance.
2. In the Payment Number column, enter the number of each payment, starting with 1 and continuing until the final payment.
3. In the Payment Date column, enter the date of each payment. You can use the Excel function "=EDATE(start_date, number_of_months)" to calculate the payment date based on the start date and the number of months.
4. In the Beginning Balance column, enter the loan amount.
5. In the Payment column, enter the monthly payment amount. This can be calculated using the Excel function "=PMT(rate, number_of_periods, present_value, [future_value], [type])". The rate is the interest rate per period, the number of periods is the total number of payments, the present value is the loan amount, and the future value is the balloon payment amount (which we will calculate later). The type is optional and specifies when payments are due (0 for end of period, 1 for beginning of period).
6. In the Interest column, calculate the interest for each payment using the Excel function "=interest_rate/12*beginning_balance". The interest rate is the annual interest rate divided by 12 (for monthly payments).
7. In the Principal column, calculate the principal for each payment using the Excel function "=payment-interest". This is the amount of the payment that goes towards paying down the loan balance.
8. In the Ending Balance column, calculate the ending balance for each payment using the Excel function "=beginning_balance-principal". This is the remaining balance after each payment.
9. For the final payment, the Ending Balance should be equal to the balloon payment amount. To calculate the balloon payment amount, use the Excel function "=PV(rate, number_of_periods, [payment], [present_value], [type])". The rate is the interest rate per period, the number of periods is the remaining number of payments (excluding the final balloon payment), the payment is the regular monthly payment amount, the present value is the remaining loan balance after the last regular payment, and the type is optional and specifies when payments are due (0 for end of period, 1 for beginning of period).
10. Once you have calculated the balloon payment amount, enter it in the Ending Balance column for the final payment.

That's it! You now have a loan schedule with a balloon payment in Excel. You can change the loan amount, interest rate, and payment amount to see how they affect the schedule.

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

Balloon loan calculator
http://office.microsoft.com/en-us/te...525031033.aspx

--
Dana DeLouis
Win XP & Office 2003


"R0bert Neville" wrote in message
...
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.

On Sat, 30 Apr 2005 18:22:12 -0400, "Dana DeLouis"
wrote:

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

Balloon loan calculator
http://office.microsoft.com/en-us/te...525031033.aspx


Thanks Dana.

The template works.

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

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?


"R0bert Neville" wrote in message
...
On Sat, 30 Apr 2005 18:22:12 -0400, "Dana DeLouis"
wrote:

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

Balloon loan calculator
http://office.microsoft.com/en-us/te...525031033.aspx


Thanks Dana.

The template works.

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

On Mon, 02 May 2005 13:18:48 GMT, "Lewis Clark" <lewis_clark_644 @
yahoo.com wrote:

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.

On Mon, 02 May 2005 13:18:48 GMT, "Lewis Clark" <lewis_clark_644 @
yahoo.com wrote:

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/ExcelTemplat...alculator.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.

[clivewilliam]

The balloon payment loan is a loan or even a large one time payment at some point in the future to make some kind. This means that when you make a balloon payment when you left on your loan period is mainly to sum to clear your loan. In other loans, which both meet the principal and interest on loans.