 < Day Day Up > 

The topics discussed in the following section are, strictly speaking, part of the standard functioning of an Excel worksheet and not VBA programming. That being said, there are a number of financial calculations that you perform frequently in business settings, so they're included in this book. These functions are discussed in the context of VBA routines, but there will be enough examples of how to use the financial functions in your worksheets so that you'll learn how to use them as formulas, too.
One of the strengths of the Excel spreadsheet program is that you can summarize worksheet data in many different ways, but one of its weaknesses, at least in terms of relatively new users taking advantage of those features, is that you need to know they're there. Table 92 lists the mathematical operations (and a few other operations) that you can use to summarize the data in a worksheet.
Function  Description 

AVERAGE  Finds the arithmetic average (mean) of a data set 
COUNT  Counts the number of cells in a range 
COUNTA  Counts the number of nonblank cells in a range 
COUNTBLANK  Counts the number of blank cells in a range 
COUNTIF (range, criteria)  Counts the number of cells in a range that match a given criteria 
MAX  Finds the largest value in a range 
MEDIAN  Finds the median value or values (the value or the value pair closest to the average) of a range 
MIN  Finds the smallest value in a range 
MODE  Finds the most common value in a range 
STDEV  Finds the standard deviation of the values in a range 
SUM  Finds the arithmetic sum of the values in a range 
SUMIF(range, criteria)  Finds the arithmetic sum of the values in a range that meet a given criteria 
So now you know what the basic summary functions are, but how will your colleagues know which operations they can use? Simple: you tell them. You can list the available operations in a cell, a comment, or a text box that you place beside the data.
The following procedure is an example of how you might go about allowing your colleagues to identify which data to summarize and to select which summary operation to perform.
Note  Although this version of the procedure uses a Select Case statement with included code to perform each summary calculation, you could choose to create a function for each summary operation and call the function from within the corresponding Case statement. You might also choose to let the user type in the number of the summary operation they want to perform instead of the name. (To avoid spelling and capitalization errors, the values must match exactly.)
Public Sub Summarize() 
Note  You probably noticed how much work the With ActiveCell.CurrentRegion. Columns(intColNumber) statement saved in the Summarize procedure. If it weren't for the With statement, the range reference code would have wrapped around every line, making the code extremely hard to read. Always look for such opportunities to simplify your code. 
One of the most common financial calculations you'll be asked to perform is to determine the monthly payment on a loan. For that calculation you use the PMT function, which has the syntax:
PMT(rate, nper, pv, fv, type)
Table 93 describes the five arguments used with the PMT function.
Argument  Description 

rate  The interest rate, to be divided by 12 for a loan with monthly payments 
nper  The total number of payments for the loan 
pv  The amount loaned (pv is short for present value, or principal) 
fv  The amount to be left over at the end of the payment cycle (usually left blank, which indicates 0) 
type  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 you wanted to borrow $20,000 at an 8 percent interest rate and pay the loan back over 24 months, you could write the formula in a worksheet cell as =PMT(8%/12, 24, 20000), which results in a monthly payment of $904.55. In Excel VBA, you would once again use the Application.WorksheetFunction object to call the PMT function within a VBA function procedure. The sample procedure assumes the rate is in cell B2, the number of payments is in cell B3, the amount borrowed is in cell B4, the amount owed at the end of the loan (always 0) is in cell B5, and the time when a payment is made (leave blank to use the default value) is in cell B6.
Public Function MonthlyPayment(rate, nper, pv, fv, when) As Currency
With Application.WorksheetFunction
MonthlyPayment = .Pmt(rate / 12, nper, pv, fv, when)
End With
End Function
Public Sub Payment()
MsgBox (MonthlyPayment(Range("B2"), Range("B3"), Range("B4"), _
Range("B5"), Range("B6")))
End Sub
Warning  You might have noticed that in the MonthlyPayment function the type argument is replaced with whenit's because type is a reserved word in VBA and Excel generates an error when the compiler encounters it. 
There are also Excel worksheet functions that you can use to determine the amount of a payment devoted to interest and to the loan's principal. These calculations are important for tax reasons. For example, if the interest on your home loan is taxdeductible, it helps to know exactly how much of each monthly payment represents interest and how much pays down the principal. The IPMT worksheet function lets you calculate how much of a payment goes toward interest. The syntax of the IPMT function is similar to the PMT function's syntax, but there are some key differences.
IPMT(rate,per,nper,pv,fv,type)
The rate, pv, fv, and type arguments all mean the same as they do in the PMT function, but the per argument is new. The per argument represents the period for which you want to find the interest and must be somwhere between 1 and nper. For example, if you wanted to determine how much of each month's payment is devoted to interest, you could do so using the following procedure, which places the resulting value in the active cell:
Public Sub DetermineInterest()
Dim intRate, intPer, intNper As Integer
Dim curPv, curInterest As Currency
intRate = InputBox("What is the interest rate (as an integer)?")
intPer = InputBox("For which month do you want to find the interest?")
intNper = InputBox("How many payments will you make on the loan?")
curPv = InputBox("How much did you borrow?")
With Application.WorksheetFunction
curInterest = 1 *(.IPmt(intRate / 1200, intPer, intNper, curPv))
End With
ActiveCell.Value = curInterest
End Sub
Tip  This procedure multiplies curInterest by 1 to produce a positive result. It's true that your cash flow is negative, but most folks like to think of payments in positive numbers (if not positive terms). 
You can list how much each payment contributes to interest by adding a For…Next loop around the periodic interest calculation, which places the resulting values in a column starting with the active cell.
Public Sub DetermineAllInterest()
Dim intRate, intPer, intNper, intPayment As Integer
Dim curPv, curInterest As Currency
intRate = InputBox("What is the interest rate (integer number only)?")
intNper = InputBox("How many payments will you make on the loan?")
curPv = InputBox("How much did you borrow?")
For intPer = 1 To intNper
With Application.WorksheetFunction
curInterest = 1 * (.IPmt(intRate / 1200, intPer, intNper, curPv))
'Divide by 1200 to get a monthly percentage (12 months * 100 per cent)
End With
ActiveCell.Value = curInterest
ActiveCell.Offset(1, 0).Activate
Next intPer
End Sub
The complementary function of IPMT is PPMT, which determines the amount of a payment that is devoted to the loan's principal. The PPMT function's syntax is exactly the same as that of IPMT, but the result is the dollar amount of a payment that is applied to the principal.
Now that you've seen the functions you use to determine your payments, and what share of those payments go toward interest and principal, it's time to show you how to reverse engineer an interest rate from a known payment schedule. Yes, it's rare that you would need to figure out your interest rate for a home loan (because it's something few folks forget, and it'll be right there on the loan papers), but just in case you need to, here it is.
The syntax of the RATE function is similar to those of the PMT family of functions.
RATE(nper,pmt,pv,fv,type,guess)
Because there are some differences between RATE and the other PMT functions, Table 94 summarizes the RATE function's arguments for you.
Argument  Description 

Nper  The total number of payment periods in an annuity. 
Pmt  The payment made each period. This value can't change over the life of the annuity. If pmt is omitted, you must include the fv argument. 
Pv  The present value of the annuitythe total amount that a series of future payments is worth now. 
Fv  The future value, or a cash balance that you want to attain after the last payment is made. If fv is omitted, it's assumed to be 0. (The future value of a loan, for example, is 0.) 
Type  The number 0 or 1, indicating when payments are due. (0 is the default, which means payments are due at the end of the month.) 
Guess  Your guess for what the rate will be. If you omit guess, it's assumed to be 10 percent. If RATE doesn't converge, try different values for Guess. RATE usually converges if Guess is between 0 and 1. 
So, if you wanted to figure out the interest rate on a $150,000 home loan that you were paying back at $1,186.19 a month over 15 years, you would use the following formula to determine the annual percentage rate of the loan:
=RATE(180,1186.19,150000)*12
It's important to enter the payment (the second parameter) as a negative number. It might make it easier to remember this requirement if you think of the payment as money that's leaving your bank account.
The PV, or present value, function returns the present value of an investment, which is financespeak for the total amount of money that a series of equalvalue future payments is worth now. When you borrow money, the loan amount is the present value to the lender. For example, if you wanted to calculate the present value of a $150,000 loan with 5 percent interest to be paid back monthly over 15 years, you would use the following formula:
PV(rate,nper,pmt,fv,type)
In the preceding formula, once again, rate is the interest rate per period (usually an annual rate divided by 12), nper is the total number of payment periods (in this case, 12 * 15, or 180), pmt is the payment made each period (1186.19 in this case), fv is the future value, or a cash balance you want to attain after the last payment is made (the future value of a loan is 0), and type indicates if a payment is due at the end of the month (0, the default, means you pay at the end of the month, and 1 means you pay at the beginning of the month). So, for the loan described above, you would create this formula:
=PV(5%/12, 180, 1186.19)
The preceding formula generates a present value of $150,000, the total amount of the loan. The sum total of the payments is actually $213,514.20, but the present value of the loan is lower because, when the loan is made, the interest hasn't had time to compound. After 10 years of payments, for example, a homeowner would have paid $142,342.80 but would still be facing another 60 payments!
A function that's related to PV is NPV, which calculates the net present value of an investment. The primary difference between present value and net present value is that net present value assumes the value of investment decreases over time due to inflation or another discount rate. That assumption is reflected in the NPV function's syntax.
NPV(rate, value1, value2, value3…)
Another difference between present value and net present value calculations is that the values in an NPV formula can vary, but in a PV calculation the payments must be constant over the life of the annuity. For example, you might consider making a $5,000 investment in a security that would pay you $1,000 after the first year, $1,500 after the second year, $2,000 after the third year, and $2,500 after the fourth year. Assuming 4 percent inflation, you would create the following formula to evaluate the investment:
=NPV(4%, 5000, 1000, 1500, 2000, 2500)
The preceding formula generates a result of $1,214.78. There's no magical threshold where the net present value of an investment makes it worthwhile; you need to compare the return of several investments to determine whether and how to invest your money.
For more information on using Excel to perform indepth financial analysis, see Data Analysis and Business Modeling with Microsoft Excel, by Wayne L. Winston (Microsoft Press, 2004).
 < Day Day Up > 
