Amortization schedule with fixed monthly payment and balloon excel

This example teaches you how to create a loan amortization schedule in Excel.

1. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. We use named ranges for the input cells.

2. Use the PPMT function to calculate the principal part of the payment. The second argument specifies the payment number.

3. Use the IPMT function to calculate the interest part of the payment. The second argument specifies the payment number.

4. Update the balance.

5. Select the range A7:E7 (first payment) and drag it down one row. Change the balance formula.

6. Select the range A8:E8 (second payment) and drag it down to row 30.

It takes 24 months to pay off this loan. See how the principal part increases and the interest part decreases with each payment.

Example 2: Pick the balloon payment amount and calculate the periodic payment:

Example 3: Pick any periodic payment amount:

Example 4: Pick your payments and see what you can borrow:

Balloon Amortization Schedule with Extra Payments

The calculator's support for extra payment is very flexible. First, you'll notice the calculator prompts you for "Extra Payments Start?" date. You can, therefore, schedule extra payments between the regular due dates if doing so is better for your cash flow.

As mentioned elsewhere, the calculator allows for a one-time extra payment or for multiple extra payments. The multiple extra payments can be for 2 or any number up until the loan is paid-in-full. (In that case, set the number of extra payments to "Unknown.")

When the extra payments are "off-schedule," the calculator prepares an expanded amortization schedule, showing the payment being applied 100% to the principal with interest accruing.

This is the correct way to apply the payment - something that other online calculators don't usually handle properly. That is if they even let you plan for extra payments between regular payments.

The Interest-Only Payment Method is a Special Case

Most frequently, the periodic payments get allocated to both principal and interest. Thus with each payment, the loan balance is being reduced.

But what if the borrower wants to pay even less per period?

If that's the case, the lender may agree to make the balloon loan one where the borrower pays only the interest due on each payment date. Paying only the interest each period reduces the payment amount even more for the borrower.

This calculator supports interest-only payments (select the option under "Amortization Method"). If you select it, however, the calculator works slightly differently.

• First, the balloon payment will always be equal to the loan amount. Therefore, it isn't possible to solve for the balloon payment.
• Or looked at in a different way, the user cannot provide a periodic payment amount. The calculator will always calculate the regular payment amount since it is the interest due.
• When introducing extra payments into the interest-only cash flow, the calculator's main window shows the amount of the first interest-only payment. But after each prepaid principal amount, the subsequent payments will be reduced since prepaying lowers the loan balance which, of course, reduces the interest due.

Given the above, if you select interest only, in almost all cases, to use the calculator, you'll want to set both of these inputs to 0.

Charts

As the day winds down, I go cross-eyed looking at columns of numbers. That's where cash flow charts come in handy. You can quickly learn the relationship between the principal, interest and optional extra payments.

This calculator creates 3 charts.

• The annual chart compares total interest and principal paid each year.
• The accumulated chart shows the amounts allocated to the principal and interest since the start of the loan.
• The pie chart clearly shows the relationship between total interest and principal with calculated percentages.

Bloggers, feel free to use these charts to make your point. Click for several export options.

Should I take out a balloon loan? There's Risk!

Balloon loans have their advantages. The borrower gets to borrow a large amount, for a short period, while making relatively small periodic payments.

However, the borrower should only consider this loan type if they are confident that they'll have the funds available or that they'll be able to refinance the loan in time to make the balloon payment when it comes due. Otherwise, the borrower will most certainly default on the terms of the loan, and they risk ruining their credit rating.

You can leave your comments and questions below.

How do I make a balloon payment amortization schedule in Excel?

How do you calculate amortization on a fixed monthly payment?

How do you calculate a balloon payment in Excel?

How do I create a loan amortization schedule with extra payments in Excel?