Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Numbers and financial data drives today's business world and Excel 2007: Financial Analysis can help decode this information. The proper understanding of these numbers, and the formulas behind them, can be the gateway to corporate and personal success. Microsoft MVP (Most Valuable Professional) Curt Frye teaches basic fluency in corporate finance, enabling users to see the meaning behind essential financial calculations. Curt explains how to review formulas to ensure they have the proper inputs, and shows how to interpret formula output. He also covers how to calculate leverage ratios and amortization and depreciation schedules, as well as forecast future growth. Exercise files accompany this course.
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.
There are currently no FAQs about Excel 2007: Financial Analysis.
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.