Manipulating Numbers

 < 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.

Performing Summary Calculations

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 9-2 lists the mathematical operations (and a few other operations) that you can use to summarize the data in a worksheet.

Table 9-2: The Most Common Summary Calculations You'll Perform in Excel

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 non-blank 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()
Dim intColNumber As Integer
Dim strOperation, strCriteria As String
Dim sngResult As Single

MsgBox ("Select a cell within the table you want to summarize." _
& "Type the number, not the letter, representing the column of the " _
& "cells you want to summarize.")

intColNumber = InputBox("Which column do you want to summarize?")
strOperation = InputBox("Which summary operation do you want to perform?" _
& " The options are Sum, SumIF, Max, Min, Count, CountA, CountBlank, " _
& "CountIF, Average, Mode, StDev. (Type them exactly as they appear.)")
With ActiveCell.CurrentRegion.Columns(intColNumber)
Select Case strOperation
Case "Sum"
sngResult = Application.WorksheetFunction.Sum(.Cells)
MsgBox ("The sum of the column is " & sngResult & ".")
Case "SumIF"
strCriteria = InputBox("Type a criteria for the method by " _
& "typing a number alone or preceded by one of the operators " _
& ">, <, or =.")
sngResult = Application.WorksheetFunction.SumIf(.Cells, strCriteria)
MsgBox ("The sum of the values is " & sngResult & ".")
Case "Max"
sngResult = Application.WorksheetFunction.Max(Cells)
MsgBox ("The maximum value in the column is " & sngResult & ".")
Case "Min"
sngResult = Application.WorksheetFunction.Min(.Cells)
MsgBox ("The minimum value in the column is " & sngResult & ".")
Case "Count"
sngResult = Application.WorksheetFunction.Count(.Cells)
MsgBox ("The number of cells is " & sngResult & ".")
Case "CountA"
sngResult = Application.WorksheetFunction.CountA(Cells)
MsgBox ("The number of non-blank cells is " & sngResult & ".")
Case "CountBlank"
sngResult = Application.WorksheetFunction.CountBlank(.Cells)
MsgBox ("The number of blank cells is " & sngResult & ".")
Case "CountIF"
strCriteria = InputBox("Type a criteria for the method by " _
& "typing a number alone or preceded by one of the operators " _
& ">, <, or =.")
sngResult = Application.WorksheetFunction.CountIf(.Cells, strCriteria)
MsgBox ("The number of cells that meet the criteria is " & sngResult & ".")
Case "Average"
sngResult = Application.WorksheetFunction.Average(.Cells)
MsgBox ("The average of the values is " & sngResult & ".")
Case "Mode"
sngResult = Application.WorksheetFunction.Mode(.Cells)
MsgBox ("The mode of the values is " & sngResult & ".")
Case "StDev"
sngResult = Application.WorksheetFunction.StDev(.Cells)
MsgBox ("The standard deviation of the values is " & _
sngResult & ".")
Case Else
MsgBox ("Unrecognized operation; please try again.")
End Select
End With
End Sub

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.

Performing Financial Calculations

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 9-3 describes the five arguments used with the PMT function.

Table 9-3: An In-Depth Look at the PMT Function's Arguments

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 when-it'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 tax-deductible, 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 9-4 summarizes the RATE function's arguments for you.

Table 9-4: The Rate Function's Arguments

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 annuity-the 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.

Determining the Present Value of an Investment

The PV, or present value, function returns the present value of an investment, which is finance-speak for the total amount of money that a series of equal-value 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!

Determining the Net Present Value of an Investment

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 in-depth financial analysis, see Data Analysis and Business Modeling with Microsoft Excel, by Wayne L. Winston (Microsoft Press, 2004).



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net