Analyzing and Charting Financial Data

Excel provides a wide range of financial functions related to loans and investments. One of these is the PMT function, which can be used to calculate the payment schedule required to completely repay a mortgage or other type of loan. Figure 4-1 describes the PMT function and some of the other financial functions often used to develop budgets and financial projections.

Before you can use the PMT function, you need to understand some of the concepts and definitions associated with loans. The cost of a loan to the borrower is largely based on three factors-the principal, the interest, and the time required to repay the loan. Principal is the amount of money being loaned. Interest is the amount added to the principal by the lender. You can think of interest as a kind of "user fee" because the borrower is paying for the right to use the lender's money for an interval of time. Generally, interest is expressed at an annual percentage rate, or APR. For example, an 8 percent APR means that the annual interest rate on the loan is 8 percent of the amount owed to the lender.

An annual interest rate is divided by the number of payments per year (often monthly or quarterly). So, if the 8 percent annual interest rate is paid monthly, the resulting monthly interest rate is 1/12 of 8 percent, which is about 0.67 percent per month. If payments are made quarterly, then the interest rate per quarter would be 1/4 of 8 percent, which is 2 percent per quarter.

The third factor in calculating the cost of a loan is the time required to repay the loan, which is specified as the number of payment periods. The number of payment periods is based on the length of the loan multiplied by the number of payments per year. For example, a 10-year loan that is paid monthly has 120 payment periods (that is, 10 years × 12 months per year). If that same 10-year loan is paid quarterly, it has 40 payment periods (that is, 10 years × 4 quarters per year).

4-2a using the PMT Function

To calculate the costs associated with a loan, such as the one that Bob and Carol need to start their winery, you must have the following information:

· The annual interest rate
· The number of payment periods per year
· The length of the loan in terms of the total number of payment periods
· The amount being borrowed
· When loan payments are due

The PMT function uses this information to calculate the payment required in each period to pay back the loan. The syntax of the PMT function is

PMT(rate, nper, pv [, fv=0] [, type=0])

where rate is the interest rate for each payment period, nper is the total number of payment periods required to repay the loan, and pv is the present value of the loan or the amount that needs to be borrowed. The PMT function has two optional arguments-fv and type. The fv argument is the future value of the loan. Because the intent with most loans is to repay them completely, the future value is equal to 0 by default. The type argument specifies when the interest is charged on the loan, either at the end of the payment period (type=0), which is the default, or at the beginning of the payment period (type=1).

For example, you can use the PMT function to calculate the monthly payments required to repay a car loan of $10,000 over a 5-year period at an annual interest rate of 9 percent. The rate or interest rate per period argument is equal to 9 percent divided by 12 monthly payments, which is 0.75 percent per month. The nper or total number of payments argument is equal to 12 × 5 (12 monthly payments over 5 years), which is 60. The pv or present value of the loan is 10,000. In this case, because the loan will be repaid completely and payments will be made at the end of the month, you can accept the default values for the fv and type arguments. The resulting PMT function

PMT(0.09/12, 5*12, 10000)

returns the value -207.58, or a monthly loan payment of $207.58. The PMT function results in a negative value because that value represents an expense to the borrower. Essentially, the loan is money you subtract from your funds to repay the loan.

Rather than entering the argument values directly in the PMT function, you should include the loan terms in worksheet cells that are referenced in the function. This makes it clear what values are being used in the loan calculation. It also makes it easier to perform a what-if analysis exploring other loan options.

Bob and Carol want to borrow $310,000 for their winery at an 8 percent annual interest rate. They plan to repay the loan in 10 years with monthly payments. You will enter these loan terms in the Overview worksheet.

To Enter the Loan Information in the Overview Worksheet:

1. Open the Levitt workbook and then save the workbook as Levitt Winery.

2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4.

3. Go to the Overview worksheet. The Overview worksheet provides a summary of Bob and Carol's business plan, including their loan request and business forecasts.

4. In cell C5, enter 310,000 as the loan amount.

5. In cell C6, enter 8% as the annual interest rate.

6. In cell C7, enter 12 as the number of payments per year. Twelve payments indicate monthly payments.

7. In cell C8, enter the formula =C6/C7 to calculate the interest rate per period. In this case, the 8 percent interest rate is divided by 12 payments per year, calculating the monthly interest rate of 0.67 percent.

8. In cell C9, enter 10 as the number of years in the loan.