Understanding Loans, Interest, and Repayment

Chapter 16: Cash is King - Page 16.15

Understanding Loans, Interest, and Repayment

A business plan should handle loans, interest, and repayment following standard accounting convention. Amounts of new loans (after start-up) go into the Cash Flow table in the upper section as money received. Interest, which is an expense deductible against income, goes into the Profit and Loss statement. Principal repayments go into the Cash Flow table in the lower section, as spending.

Some people are confused by the concept of separating the payment into interest and principal. A common example, at least in the United States, is making payments on a mortgage. Most lending institutions clearly separate payments into interest and principal components. Even if you write a single check each month to repay the mortgage loan, the payment is divided into interest and principal.

Detailed Principal Payments Function

Your software is likely to have functions to calculate principal and interest payments from assumptions, so you can project payments over time without having to estimate each one. In the following illustration, the spreadsheet uses a built-in financial calculator to estimate the principal payments required for the sample case we've been using. (Note: For this illustration, we display the numbers in dollars, not thousands of dollars, as they are displayed in other illustrations in this chapter.)

Using the Principal Payments Function (PPMT)

Use the computer to calculate principal payments for the cash flow table. (This table is taken from Business Plan Pro®, and shown in dollars, not thousands of dollars).

In the sample, the company borrowed $400,000 in a 10-year loan at 8.5% per year several years ago. During the sample plan period, it is making regular payments of just under $5,000 per month. The interest portion of the payment is calculated automatically in the Profit and Loss table. The principal portion of the payment is calculated using the formula below, which you can copy into your own worksheet. The formula for the first month's principal payment is:

=-PPMT(long_term_interest_rate/12,column()+44,120,400000)

The formula starts with a negative sign ("-") because the cash flow row uses positive numbers for spending amounts, while many spreadsheet functions assume cash flow calculations use negatives for spending.
The first element of that formula (=PPMT) is a standard function call, used in Business Plan Pro® as well as in Microsoft Excel and compatible spreadsheet software.
The "long_term_interest_rate/12" is the annual interest rate from a general assumptions table, divided by 12 (because this is monthly interest). The range is named "long_term_interest_rate" using standard spreadsheet range naming.
The "column()+44" portion represents the 47th payment. "Column()" is a built-in number that equals 3 in the third column.
The 120 in the formula is the total number of payments (120 for a 10-year loan).
The 400000 at the end of the formula represents the original loan amount, $400,000.

You can use this special payment function to calculate your own principal payments, if you wish. Or you can get the number from loan papers or from your banker.

[Home] [Buy this Book] [Links] [About the Author] [About this Site] [Site Map] [Contact]