Loan Calculator with PMT function
You have graduated from college and want to purchase a new car. In order to keepsome control over your finances, you decide to build a loan calculator in Excel. The calculator will contain the following information:
-- Auto purchase price
-- Amount of down payment you will make
-- Total amount of the loan you will need
-- Annual Loan interest rate
-- Total number of loan payments
-- Amount of monthly payment.
You will use the Formula Builder and the PMT function to build your calculator. Open theattached Excel worksheet. To see the Formula Builder go to View > Formula builder. When the word "argument" appears, that refers to information that is passed from the spreadsheet to the function so the function can do its job.
The goal of this exercise is to use Excel for "what if" calculations. Once we have our calculator built, we can plug in different numbers to see how each one would affect our monthly payment. For example, we could change the purchase price, the loan interest, the down payment, or the term of the loan. Using an optional argument we can also see how it will affect our payment if we pay the bank at the beginning or end of the month.
We will use the PMT function to build our payment calculator. The PMT function makes two assumptions: a loan has a constant interest rate and equal payments throughout its life. Based on these assumptions it returns the payment for any period you specify. Typically this payment is monthly, but it could also be quarterly, annually, bi-monthly, twice monthly, or any other oddball period you might think up.
When using PMT function, employ cell references whenever possible. For example, In the Loan Amount cell, by using cell references to construct a simple formula, you help the spreadsheet to automatically update itself whenever you change either the purchase price or down payment amount changes.
In the PMT function there are three required and two optional arguments. Let's take a look at each of these:
rate: specifies the annual interest rate in per cent. The rate can be fractional. For example, 4.25%. The interest rate is dependent on how your loan is repaid. If your loan is paid monthly, then formula rate would be the rate/12. If it is paid quarterly, then the value would be rate/4.
nper: refers to the number of payment periods. For example, if your car loan is to be paid monthly for three years, "nper" would be 36.
pv: is the present value of your loan. It represents how much you borrow. In order for your payment to be a positive number, you should put a "-" sign in front of pv. This is because each time you make a payment, the loan value moves closer to "0".
fv: an optional argument. If you are paying off a loan completely, then use the fv default value of "0". However, say you want to save up money to pay cash for your car in four years, you could enter the expected price of your car as the fv value. the PMT function would then tell you how much you would have to save each year to have enough funds for that cash payment.
type: another optional argument. The default value (0) indicates that your payment will be made at the end of each period. However, if you want to save a bit of money and make your payment at the beginning of each period, then put "1" in as value in the type field.
The PMT function also allows you to see how much total interest you pay for your loan. To obtain this figure multiply nper times the value returned by the PMT function. Then subtract the loan amount from the result of this multiplication. Enter the total interest paid for your loan in cell B9.
A couple hints: 1. Place your PMT function in the Monthly Payment cell B7; 2. If your payment comes to be a negative number, then put a minus sign in front of the function. That is because Excel regards a loan as putting you in negative financial territory, with the goal being to get back to "0"; 3. Please format your spreadsheet to provide graphic interest and improve legibility. 4. You can insert the PMT function in your cell by highlighting cell B7 and double-clicking on the function in Formula Builder. 4. Since a function is a formula, it always begins with an "=".
After formatting your worksheet, place your name in cell B11.
You can use any amount you want for the price of your desired car. One point of this exercise is to help you find what type of car you can afford given the amount you want to spend each month. The calculator also provides you with ways of modifying the terms of your purchase. For instance, how much does it lower your payment if you shop around and find a lower interest rate? How much could you lower the payment by having a bigger down payment? What happens if you extend the term of the loan.
Please e-mail your completed file to me as an attachment.