Balloon payment has its pros and cons. The lump sum at the end of every payment can be a determining factor in whether you want to take a loan or buy a particular car or not. So, calculating balloon payments beforehand is a must before going into such transactions. Microsoft Excel offers some handy functions which you can use to calculate the balloon payment very easily on your own. In this tutorial, we are going to see how you can do that and also how you can calculate monthly payment if you already have a balloon payment fixed using Excel.
Overview of Balloon Payment
The balloon payment is a type of payment that does not fully amortize over a due transaction period. So it has to be paid as a sum either at the end or in the middle of the period. In other words, it is the large payment due at the end of the balloon loan. These types of loans can be any type of amortized loan. The earlier payments of these loans are structured in such a way that the amount is way smaller in comparison and a large payment is generally due at the end of the period.
The main advantage of using balloon payment is to lower the burden throughout the early period of transactions. This is particularly helpful if you are sure to come up with a large amount of money after some period. At the same time, the large payment at the end can also be detrimental. It can be a burden in the end if you don’t have enough cash to pay- maybe because you didn’t come up with the money or for other reasons. In those cases, other forms of arrangements are usually done to pay up the money.
Mathematically, The formula for balloon payment can be summed up to be the following.
Here,
CP = Constant Payment,
BP = Balloon Payment,
N = Number of Payments,
r = Discount/Interest Rate.
How to Calculate Balloon Payment in Excel: 2 Easy Methods
Excel has two built-in functions that you can use to calculate a balloon payment. Keep in mind, that these functions return different results. They also take different variables. But depending on the situation, both of them can be defined as the balloon payment of a transaction. As different parameters are used to calculate both functions, different datasets are used in each section for the demonstration.
1. Using FV Function
The first function we are going to use to calculate the balloon payment is the FV function. This function is used to calculate the future value of a payment. The function can take several arguments. Of them, three are primary- interest rate, number of periods, and the amount paid in each period. It can also take two optional arguments- present value and 0 or 1 depending on when the payment is due.
From the required arguments, it is safe to conclude that we can use this function to calculate balloon payment if we have a predetermined amount of payment at each transaction.
Follow these steps to see how we can do that.
Steps:
- First of all, enter the parameters in your Excel sheet. This includes the principal amount, interest, duration, and monthly payment.
- Now select a cell to store the balloon payment value. We are selecting cell C9 for this.
- Next, select cell C9.
- Then write down the following formula in it.
=FV(C5/12,C6*12,C7,-C4)
- After that, press Enter on your keyboard.
This is the amount you have to pay after the ten years if you continue to pay eight hundred dollars each month.
Read More: How to Calculate Monthly Mortgage Payment in Excel
2. Utilizing PV Function
There is another built-in function in Excel called the PV function which we can also use to our advantage to calculate a balloon payment. The function is used to calculate the present value in a transaction. Just like the previous function, it can take three primary arguments- interest rate, number of transactions, and the amount paid at each transaction. It can also take two optional arguments- the future value and 0 or 1 depending on when the payment is due in each period.
Use this function if you have a period already determined after when you are going to pay the balloon payment. This way, you can calculate the amount you have to pay at each period and what you have to pay in the end as the balloon payment. The amount to be paid is calculated with the help of the PMT function which we demonstrate within the steps.
Follow these steps to see how you can calculate a balloon payment with the help of these functions.
Steps:
- First, enter the parameters in your Excel spreadsheet.
- Second, select a cell to store monthly payments in the spreadsheet. For example, we are selecting cell C9 for this.
- Now select the cell and write down the following formula in it.
=PMT(C5/12,C6*12,C4)
- After that, press Enter.
- Next, select a cell to store the balloon payment value. For that, we are selecting cell C10.
- Now select the cell and write down the following formula.
=PV(C5/12,(C6-C7)*12,-C9)
- Finally, press Enter.
This way, you can calculate a balloon payment in Excel, depending on your parameters.
💬 Note
Both the monthly payment and the balloon payment values here are cash outflows. So the result is negative as we can see from the figure of the result.
Read More: How to Calculate Monthly Payment with APR in Excel
How to Calculate Monthly Payment from Balloon Payment in Excel
With all being said, let’s look over another handy method that can come in handy for many situations. Let’s assume we have a balloon payment already fixed. This can happen when you know when you will pay the exact amount. Then you can calculate the monthly payment from the balloon payment considering the interests very easily in Microsoft Excel. In this section, we are going to show you how to do that.
For this, we are going to use the PMT function. This function takes three primary arguments- interest rate, number of periods, and the principal value. It can also take two optional arguments- a face value and 0 or 1 depending on the time of payment. If we consider the balloon payment as the face value of this function, the function will give us the transaction required at each period.
Follow these steps to see how we can calculate the monthly payment from a balloon payment in Excel.
Steps:
- First of all, enter the parameters on your Excel spreadsheet. Make sure to put the balloon payment in a negative value.
- Now select a cell to store the monthly payment value. Here, we have selected cell C9 for the demonstration.
- After that, select the cell again and write down the following formula.
=PMT(C5/12,C6*12,C4,C7)
- Finally, press Enter.
This is how you can calculate the monthly payment from a balloon payment in Excel.
Read More: How to Calculate Loan Payment in Excel
💬 Things to Remember
- Use the FV function to calculate the balloon value if you have the amount you will pay at each transaction already fixed.
- Meanwhile, use the PV function to calculate balloon payments if you have a period already fixed after which you want to pay in bulk.
- Interest rate and loan duration should be in the same time units. So divide/multiply these values to match each other.
Download Practice Workbook
You can download the workbook used for the demonstration from the link below.
Conclusion
These were the methods you can follow to calculate the balloon payment in Excel. Hopefully, you are now confident with the usage of these functions to calculate balloon payments and monthly payments. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
Related Articles
- How to Calculate Monthly Payment on a Loan in Excel
- How to Calculate Down Payment in Excel Using VLOOKUP
- How to Calculate a Lease Payment in Excel
- How to Calculate Coupon Payment in Excel
- How to Calculate Auto Loan Payment in Excel
- How to Calculate Car Payment in Excel
<< Go Back to Calculate Payment in Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I was following along with your tutorial and getting different results from those shown. In particular, I was using 6% as the interest rate. I downloaded the example file and found that the interest rate was displaying 6% as a rounded number, but was calculating based on an interest rate of 5.85%.
Hi Tom R,
Thanks for your comment. The numeric values are not a big issue. Our main focus is to demonstrate the procedure so that you can understand it and implement it in your regular life. Moreover, we also recommend our users download our Excel workbook first to overcome such misunderstandings.
However, we are providing an updated image of that part for your convenience.