ACCT 133 – Spreadsheet Accounting
Instructor Problem #2 (I102)
Using the =PMT function in Excel
Purpose: The purpose of this handout is to describe some practical applications of the =PMT function. This model will utilize this function.
Introduction to Excel Business Functions
The Excel program offers users a variety of 52 business functions. A list of them is attached for your future reference. (Utilize the Help menu for instructions on specific functions.)
About the =PMT function
One of the most practical features of the Excel program, or any other spreadsheet program, is its ability to quickly calculate loan payments under various and differing assumptions.
The =PMT (or Payment function) could be a very useful feature for you as you enter into more and more financial transactions as you get older. The =PMT function automatically calculates the periodic payment of a loan based on the loan amount, the interest rate, and the term or period of time over which the loan will be repaid.
The format for the =PMT function is =PMT(rate,#pmts,principal). All three of these components must be entered in this exact order. In addition, commas must separate the three components.
Remember that the interest rate and the # of payments should always be in the same time denomination. Interest rates are usually expressed in annual terms. This means that if you are making monthly payments, then both the rate and the # payments should be expressed in monthly terms.
Let’s say you are going to buy a car, and you need to borrow $12,000. The bank says they can finance you at 12% for a four-year car loan. How much would your monthly payments be?
In Excel, you could easily calculate this as follows:
=PMT(12%/12,4*12,12000)
where the first variable is the interest rate, expressed monthly. The second variable is the number of payments, expressed monthly. The third variable is the principal or the amount of the loan. The computer will calculate this in less than a second and should return an answer of
–316.01. This means that monthly payments of $316.01 would be required.
How much would you end up paying for the car over the life of the loan? This could easily be calculated by taking that monthly payment and multiplying by the # of months…in this case 48. In this example, we would be paying a total of $15,168.29 with no rounding of the payment (or $15,168.48 if we round the payment.) Since we borrowed $12,000, our financing costs (or interest) would be $3,168.29. (To calculate the total cost of the car, you would have to add back any type of down payment.)
In this problem, we will be calculating loan payments for different houses under a variety of loan options. The only difference in purchasing a house is that there are usually “points” involved. “Points” represent fees charged by the lender for processing your loan. One Point is equal to 1% of the loan balance (not the purchase price). So on a $50,000 loan, one point would be equivalent to $500. Two points would be equal to $1,000. With the prices of homes in California, and the high loans required to purchase them, points can really add up!
Mr. O.B. Servant is looking to purchase a home. He knows you have been studying accounting and he has asked you for your advice on buying a house. He has looked at many different houses and isn’t sure how much the monthly payment will be based on a number of alternatives.
The potential houses that he has looked at are as follows:
1. Two Bedroom with One Bath $300,000
2. Three Bedroom with One Bath $350,000
3. Three Bedroom with Two Baths $400,000
He has spoken to his bank and they have provided three potential options for financing the purchase of the house as follows:
· Option 1 10% down, one point, and fixed interest of 7.5%.
· Option 2 15% down, two points, and fixed interest of 7.0%
· Option 3 20% down, one point, and fixed interest of 7.0%
The term on all loans is 30 years. The points must be paid up front at the time of closing.
Prepare an analysis of these alternatives showing all the data, the resulting down payment, the loan fees, and the total due at closing. In addition, show the monthly payments for each house.
For a real eye opener, calculate the total amount that you would have paid for the house over the life of the loan. This should allow you to easily see how much the financing truly costs you. See the attached example to get you started. This is just an example. You can custom tailor it to fit your needs.
Remember to keep flexibility in mind. The user should be able to enter any variables they wish and have the answer section calculate for them automatically. This means that your answer section should only contain cell references.
Some helpful hints: The =PMT function returns payments as negative values. If you would like the amount shown as a positive number, use the =ABS (Absolute function) to change the presentation. In addition, since you are multiplying by percentages, you should also consider using the =ROUND function we discussed in the payroll problem. This will cause the computer to truncate numbers and will prevent rounding errors from coming up in your work. The attached analysis that I prepared uses these features.
Turn in the following:
A printout of the completed model showing all three loan options for house #1.
A printout of the completed model showing all three loan options for house #2
A printout of the completed model showing all three loan options for house #3
A printout of the complete formulas for one of the above utilizing the Ctrl plus ~ command