Calculating Loan Payments (3)

In most situations you omit both of these optional arguments.

Let’s create a simple loan calculator using the PMT function. Start with a blank worksheet and then follow these steps:

1. Put the labels Amount of loan, Annual rate, Term in years, and Monthly payment in cells B2 through B5, in order.
2. Format cells C2 and C5 as currency with two decimal places.
3. Format cell C3 as percentage with two decimal places.
4. Enter the following formula in cell C5: =PMT(C3/12,C4*12,C2).

Note how in this formula we have done two things to make the result come out right:

• Divided the annual interest rate (in cell C3) by 12 to get the rate per month.

• Multiplied the yearly term (in cell C4) by 12 to get the term in months.

When you first enter the formula the cell displays #DIV/0!, which is Excel’s way of telling you an error occurred in the cell. An error occurs because the input cells are blank and the function cannot calculate. Go ahead and enter data in the three input cells and you’ll see a result displayed, as shown in Figure 3.1. Note that the payment amount is a negative value. This is in keeping with the way Excel handles cash flow, as was explained in Chapter 2, “Using Excel to Work with Money.” Because a loan payment is money going out, it is expressed as a negative value.

Taken From : Manage Your Money and Investments with Microsoft Excel

Related posts:

1. Calculating Loan Payments (2) The PMT function uses the following syntax: PMT(rate, nper, prin,...
2. Calculating Principal Payments (2) The PPMT function uses the following syntax; you’ll note that...
3. Calculating Loan Payments Few people can make it though life without taking out...
4. Calculating Interest Rate (2) The other three arguments are optional: fv is the future...
5. Calculating Principal Payments When you make a payment on a loan, each payment...

Related posts brought to you by Yet Another Related Posts Plugin.