Lab 7: Excel: Annual Percentage Rate (APR), Loan ...

UNM CS-150L Lab 7

Fall 2008

Lab 7: Excel: Annual Percentage Rate (APR), Loan Amortization Schedules, the PMT( )

function, Freeze Pane.

Due: Sunday November 2 at midnight.

In this lab you will be making an Excel workbook with three worksheets: "PMT", "Amortization Schedule" and "Modified Amortization Schedule". The worksheets will compare various scenarios of an auto loan and various ways of calculating the amortization schedule. The information and calculations listed below must be included in your workbook. It is up to you to organize, format, color, and highlight the information in a professional looking, easy to read manner.

The Excel PMT(rate, nper, -pv) function calculates the periodic payment, P, on a loan by the following formula:

P

=

rate ? pv ? (1 + rate)nper (1 + rate)nper -1

Where rate is the periodic interest rate (APR divided by the number of periods per year), nper is

the total number of periods during the term of the loan, and pv is the original amount financed.

For example, consider the following scenario: A five year loan is created on an original principal of $5,000 at a fixed APR of 6.75% compounded monthly.

The values in rows 1 through 4 are given directly in the scenarios statement and are shown with a green background. The values of pv, rate, and nper needed in the Excel PMT function are shown with a blue background in column B. The periodic Interest Rate, rate, is the APR divided by the number of periods per year, in this case 12. Notice that the periodic interest rate is displayed with four decimal places. In finance, it is customary to display four decimal places of percentages less than one. For percentages greater than one, generally two decimal places are used (as in the APR of 6.75%).

The total number of periods of the loan, nper, is the number of periods per year times the number of years of the loan: 12 months per year ? 5 years = 60 months.

-1-

UNM CS-150L Lab 7

Fall 2008

When the Excel PMT(rate, nper, -pv) function is used to calculate payments on a loan, the original balance is entered as a negative number. In the above scenario, the Excel PMT function, PMT(E7,E8,-E1), gives a value of $98.42 for the amount periodic loan payment. This is the amount that if paid every period (every month in this case) for a total of 60 periods, will bring the loan balance to a value of zero.

The total amount of all of the payments of the loan is the amount of each payment ($98.42) times the number of payments (60). This total is $5,905.04. This is $905.04 more than the original principal of $5000. The $905.04 is the interest or finance charge of the loan.

This is basically all that a bank loan officer needs to understand. The bank loan officer asks the customer how much he or she wants to borrow, enters the loan period and loan rate into a spreadsheet, and tells the customer the periodic payment amount. If this amount is too large for the customer, then the loan officer can try extending the loan term. Typically automobile loans can be made for terms 3, 5 or 7 years and home mortgage loans can be made for 15, 20, 30 or 40 years. Generally, the interest rate is higher for longer term loans. If the longest term available results in too high a periodic payment, then the loan officer can try reducing the loan amount.

As future financial decision makers (not just bank loan officers), it is necessary for you to understand what is inside the Excel PMT(rate, nper, pv) function. For example, when the Federal Reserve announces it is going to raise interest rate paid on bank deposits by ? percent how should your bank respond by changing its rates on auto loans home loans, CDs, and other rates? The broad economic factors that need to be considered in making such a decision are beyond the scope of this course, however, one of the components of this decision, understanding what goes on inside the PMT function, is within this course's scope.

In this lab, you will: use the PMT function,

use the explicate form of the PMT function,

P

=

rate ? pv ? (1 + rate)nper (1 + rate)nper -1

,

use a loan amortization schedule to help understand how this equation works, and use a loan amortization schedule that allows for more flexibility than the PMT function. In

particular, the PMT function assumes that every payment is made for the same amount

and made exactly once per period. However, sometimes a borrower will make an extra

payment or pay a larger than required amount. On most loans, this extra reduces the

principal and will cause the loan to be paid off sooner. The PMT function has no ability

to deal with these extra payments, however, it is easy to see the effects of extra

payments with a loan amortization schedule.

-2-

UNM CS-150L Lab 7

Fall 2008

1. PMT Tab [35 points]: Choose a home price between $150,000 and $400,000. This will be the home value used in each of the scenarios in your workbook. Also, each loan scenario will assume that a 5.00% down payment is placed on the home mortgage.

In each scenario we will be considering only standard, fixed rate, mortgages with no closing costs, no insurance and no buy-down points. In each of the scenarios of this lab use following table to determine the APR of the loan. Wells Fargo offers different rates for many different home values between those shown in the table. For simplicity, use the $150,000 home value rates for all homes of less than $200,000. Use the $200,000 rates for all homes values of at least $200,000 and less than $250,000, etc.

Wells Fargo Advertised Home Mortgage Rates with a 5% down payment for homes in New Mexico on Oct 24,

2008

Home Value

15-Year Fixed APR

30-Year 40-Year Fixed

Fixed APR

APR

$ 150,000

6.784%

7.205%

8.139%

$ 200,000

6.752%

7.196%

8.105%

$ 250,000

6.710%

7.170%

8.084%

$ 300,000

6.694%

7.158%

8.071%

$ 400,000

6.665%

7.140%

8.053%

Figure 3: Wells Fargo Home Mortgage Rates

You must create a table with a column for each of the following scenarios: 9 15 year loan with monthly payments 9 15 year loan with semimonthly payments 9 15 year loan with biweekly payments 9 30 year loan with monthly payments 9 30 year loan with semimonthly payments 9 30 year loan with biweekly payments 9 40 year loan with monthly payments 9 40 year loan with semimonthly payments 9 40 year loan with biweekly payments

With the exception of the loan amount in row 3, wherever equations are required to be used, they must be written so that the left most equation cell in each row can be filled right to correctly calculate all the values in the row. The table must have a row for each of the following:

a. [1 point] Home value. This is the constant you choose between $150,000 and $400,000. This number is fixed for all tabs of your workbook. Since all scenarios use this same number, it should only be on one cell of the row.

b. [1 point] Down Payment Percentage. This is a constant 5%. This number is fixed for all tabs of your workbook. Since all scenarios use this same number, it should only be on one cell of the row.

c. [1 point] Loan Amount. This is the home value minus the down payment (not the down payment percentage). This number is fixed for all tabs of your workbook. Since all scenarios use this same number, it should only be on one cell of the row. -3-

UNM CS-150L Lab 7

Fall 2008

d. [1 point] Term (in years): Enter this as a constant as defined by each scenario.

e. [3 points] Annual Percentage Rate (APR): Enter this as a constant as defined by each scenario. Obtain the appropriate value from the Wells Fargo Home mortgage rates table given above. These values must be formatted as percentages with three decimal places.

f. [2 points] Number of Payments per Year: Enter this as a constant as defined by each scenario.

g. [5 points] Periodic Interest Rate: Calculate this by dividing the APR by the number of payments per year. These numbers should be formatted as percentages with four decimal places.

h. [4 points] Total Number of Payments: Calculate this.

i. [5 points] Periodic Payment PMT (the amount paid each period): Calculate this using the Excel built-in PMT() function.

j. [5 points] Periodic Payment Explicit: Calculate this using just basic arithmetic operations (no built-in functions). The explicit formula for PMT is the equation given on the first page of this lab. Note: The result from this calculation should match, to the penny, the result of using the built-in PMT function.

k. [3 points] Total of Payments: This is the sum of all the payments made over the term of the loan. Calculate this value. It is the periodic payment times the number of payments.

l. [2 points] Finance Charge: This is the total interest paid over the term of the loan. Calculate this by finding the difference between the total amount paid and the original amount financed.

m. [2 points] Simple Interest: Simple Interest = Principal * PeriodicRate * NumberOfPeriods

Use this equation to calculate the simple interest for each scenario. This will be different than the interest that is actually paid. The PMT function generates a periodic payment that is greater than the periodic interest. If the periodic payment was not greater than the periodic interest, then every period, the balance would increase. Therefore, every period the interest accrued would also increase since there would now be interest on both the original principal and on the unpaid interest. It is illegal to issue this type of loan as the loan would never be paid off. However, since the periodic payments are greater than the periodic interest, part of each payment reduces the principal. As the loan is paid off, the principal is reduced. As the principal is reduced, the interest accrued each period becomes smaller. Thus, a greater part of the payment goes towards the principal. The wondrous PMT function takes all of this into account. The equation for simple interest does not take this into account.

-4-

UNM CS-150L Lab 7

Fall 2008

Figure 4 shows an example of how you may choose to setup your worksheet. You may copy this format or create your own. You cannot copy the numbers because they are based on a 1.2 million dollar house. You are not allowed to finance a house worth more than 400 thousand. You can check the correctness of your equations by trying the 1.2 million value and see if all of the other numbers come out correct.

Figure 4: Possible layout for the PMT spreadsheet. Note: this layout only shows four scenarios. You need to include all nine

-5-

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download