Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Just like when you determine payments for a fully amortized loan, you can use the PMT or Payment function to determine payments for a partially amortized loan. If you want the lump sum or balloon payment to be due at the end of the loan's term, you can put the balloon payment in the PMT functions, fv or future value argument, and then build the formula normally. So in this example, we have a loan of $30,000,000 with an annual interest rate of 7%, a term of 12 years, and a Balloon Payment at the End of $10,000,000.
So you will be paying out $20,000,000. That's 30,000,000 minus 10,000,000 over the 12 years, and then paying the $10,000,000 in one lump sum at the end. So to create the formula, I have =PMT and our five arguments are the rate, the number of payments, the present value of the loan, which is the principal, the future value, which is the balloon payment at the end, and the type which is whether payments are due at the end of a period or at the beginning. In this case, the type is zero, so we'll be able to leave it out.
So we type in the rate, which is in cell B5 and we divide it by 12, which is the number of payments per year. The number of payments is in cell B7, and we multiply by 12, again for each month for a monthly payment. Present value is the principal that's in cell B3. Future value is in cell B9, and that is $10,000,000. Now note that because the balloon payment is a negative cash flow to you it's expressed as a negative number.
We will close the parenthesis, because we don't have to put in the type argument, hit Return, and you can see the Monthly Payment on this loan is $264,010 per month, with a $10,000,000 payment due at the end of the loan's term. Loans with large balloon payments are extremely risky for the borrower, because external factors such as economic downturns, labor actions, and natural disasters can prevent them from earning the revenue required to make the balloon payment when it comes due.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98565 Viewers
80 Video lessons · 141543 Viewers
59 Video lessons · 59913 Viewers
52 Video lessons · 73091 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.