Microsoft Office Tutorials and References
In Depth Information
Creating formulas to calculate values
Two other functions you might use are the NOW and PMT functions. The NOW function
displays the time Excel updated the workbook’s formulas, so the value will change every
time the workbook recalculates. The proper form for this function is =NOW() . To update the
value to the current date and time, press the F9 key or display the Formulas tab and then, in
the Calculation group, click the Calculate Now button.
The PMT function is a bit more complex. It calculates payments due on a loan, assuming a
constant interest rate and constant payments. To perform its calculations, the PMT function
requires an interest rate, the number of payments, and the starting balance. The elements
to be entered into the function are called arguments and must be entered in a certain order.
That order is written as PMT(rate, nper, pv, fv, type) . The following table summarizes the
arguments in the PMT function.
The interest rate, to be divided by 12 for a loan with monthly
payments, by 4 for quarterly payments, and so on
The total number of payments for the loan
The amount loaned (pv is short for present value, or principal)
The amount to be left over at the end of the payment cycle (usually
left blank, which indicates 0)
0 or 1, indicating whether payments are made at the beginning or at
the end of the month (usually left blank, which indicates 0, or the end
of the month)
If Consolidated Messenger wanted to borrow $2,000,000 at a 6 percent interest rate and
pay the loan back over 24 months, you could use the PMT function to figure out the
monthly payments. In this case, the function would be written =PMT(6%/12, 24, 2000000) ,
which calculates a monthly payment of $88,641.22.
TIP Because the payment calculated by the PMT function represents money that lows out
of your bank account, the result is a negative number. If you want the result to be expressed
as a positive number, multiply the formula’s result by -1.
You can also use the names of any ranges you defined to supply values for a formula. For
example, if the named range NortheastLastDay refers to cells C4:I4, you can calculate the
average of cells C4:I4 with the formula =AVERAGE(NortheastLastDay) . With Excel, you can
add functions, named ranges, and table references to your formulas more efficiently by
using the Formula AutoComplete capability. Just as AutoComplete offers to ill in a cell’s
text value when Excel recognizes that the value you’re entering matches a previous entry,
Formula AutoComplete offers to help you ill in a function, named range, or table reference
while you create a formula.