11.5.3 FV The FV function calculates the future value of a constant annuity. You can include an additional cash flow that will occur now (time 0). By setting the annuity amount equal to zero you can use this function to calculate the future value of a cash flow that occurs now. The syntax of the FV function is: FV(rate,nper,pmt,pv,type) Pv and type are optional arguments and if omitted, they are assumed to be zeros. Example: You plan to invest $1,000 every year for 10 years starting a year from now. If you expect to earn a return of 8% per year, how much money will you accumulate at the end of 10 years? Enter the formula =FV(0.08,10,-1000, 0,0) to get the answer $14,486.56. What if you can make an additional investment of $2,000 now? Enter the formula =FV(0.08,10,-1000,-2000,0) to get the answer $18,804.41. Example: You have a $10,000 loan at 8% annual interest rate. After you make 10 annual payments of $1,000 starting today, what will be the remaining balance on the loan at the end of 10 years? Enter the formula =FV(0.08,10,-1000, 10000,1) to get the answer, - $5943.76. The negative sign indicates that you will have to make a payment of this amount to pay off the loan. Note that this is not the remaining balance immediately after you make the tenth payment at the beginning of the tenth year. Rather, it is the remaining balance a year later at the end of the tenth year, that is, 10 years from now. 11.5.4 PMT The PMT function calculates the constant annuity payments you will have to make to pay off a loan or to accumulate a certain amount of money. The syntax of the PMT function is: PMT(rate,nper,pv,fv,type) Fv and type are optional arguments, and if omitted they are assumed to be zeros. Example: You have a loan of $10,000 that carries an annual interest rate of 8%. If you want to pay it off in 10 equal annual instalments starting a year from now, what will be the annual payment? Enter the formula =PMT(0.08,10,10000, 0,0) to get the answer, - $1,490.29. What if the lender agrees to let you make a final payment of $2,000 at the end of 10 years to reduce your annual payments? Enter the formula =PMT(0.08,10,10000,- 2000,0) to get the answer -$1,352.24. Example: You want to accumulate $10,000 in 10 years by investing in equal annual instalments earning returns at the rate of 8% per year. If you start investing a year from now, how much will you have to invest annually? Enter the formula =PMT(0.08,10,0,10000,0) to get the answer -$690.29. If you can invest an additional $2,000 now, how would that reduce the annual investment requirement? Enter the formula =PMT(0.08,10,-2000,10000,0) to get the answer -$392.24. 251 CU IDOL SELF LEARNING MATERIAL (SLM)
11.5.5 NPER The NPER function calculates how many constant annuity payments you will have to make to pay off a loan or to accumulate a certain sum of money. The syntax of the NPER function is: NPER(rate,pmt,pv,fv,type) Fv and type are optional arguments and if omitted, they are assumed to be zeros Example: You have a $10,000 loan at an interest rate of 8% per year and you can afford to make annual payments of $1000 starting a year from now. How many payments will you have to make to pay off the loan? Enter the formula =NPER(0.08,-1000,10000,0,0) to get the answer 20.91. This means that you will have to make 20 full payments and then a final payment smaller than the $1,000. If you think you will be able to pay an additional $4,000 at the end of the period, how would that reduce the number of payments required? Enter the formula =NPER(0.08,-1000,10000,-4000,0) to get the answer 15.9, which should be interpreted the same as the previous answer. (Note that if you enter an annual payment of less than the annual interest payment, you will not get an answer because at that rate the loan will never be paid off.) Example: You can save $500 per year starting a year from now. If you can earn 8% per year interest on your investment and want to accumulate $10,000, how many years will it take? Enter the formula =NPER(0.08,-500,0,10000,0) to get the answer 12.4. If you can invest an additional $2,000 today, how will it reduce the number of years it will take to reach your goal? Enter the formula =NPER(0.08,-500,-2000,10000,0) to get the answer 8.8. 11.5.6 RATE The RATE function calculates the periodic interest rate on a loan that can be paid off with a certain number of equal periodic payments. Alternately, it calculates the return you will have to earn in order to accumulate a certain amount of money by making a number of equal periodic investments. The syntax of the RATE function is: RATE(nper,pmt,pv,fv,type,guess) Fv, type, and guess are optional arguments. If fv and type are omitted, they are assumed to be zeros. If guess is omitted, it is assumed to be 10%. Guess is your guess of what the rate would be. If RATE does not converge, try different values for guess. RATE usually converges if guess is between 0% and 100%. Example: You have a $10,000 loan that can be paid off with 10 annual payments of $1,200 each if you make the first payment one year from now. What is the interest rate on the loan? Enter the formula =RATE(10,-1200,10000,0,0) to get the answer 3.46% per 252 CU IDOL SELF LEARNING MATERIAL (SLM)
year. What if the loan involves a $2,000 additional final payment? Enter the formula =RATE(10,-1200,10000,-2000,0) to get the answer 5.90%. Example: You want to accumulate $20,000 by making 10 annual investments of $1,000 each. You will make the first payment one year from now. What rate of return would you need to earn? Enter the formula =RATE(10,-1000, 0,20000,0) to get the answer 14.69%. What if you can make an additional investment of $2,000 today? Enter the formula =RATE(10,-1000,-2000,20000,0) to get the answer 9.06%. 11.5.7 IPMT The IPMT function calculates how much of the equal periodic payment you make on a loan in a particular period will be applied towards interest. The syntax of the IPMT function is: IPMT(rate,per,nper,pv,fv,type) Per is the period number for which the interest amount is to be calculated. Fv and type are optional arguments and if omitted, they are assumed to be zeros. Example: You have a $10,000 loan at 8% interest that you will pay off in 10 equal annual instalments starting a year from now. How much of the third payment will be applied to interest? To calculate it, enter the formula =IPMT(0.08, 3,10,10000,0,0). The answer is - $685.13. If you will pay off the loan with 10 equal payments starting a year from now and a payment of $2,000 at the end of the tenth year, then you will enter the formula =IPMT(0.08,3,10,10000,-2000,0). The answer is -$708.11; it is this amount of the third payment that will be applied to interest. (Note that in these cases Excel internally calculates the equal periodic payments first. For example, they are $1,490.29 and $1,352.24, respectively.) 11.5.8 PPMT The PPMT function calculates how much of the equal periodic payment you make on a loan in a particular period will be applied towards the loan principal. The syntax of the PPMT function is: PPMT(rate,per,nper,pv,fv,type) Per is the period number for which the principal amount is to be calculated. Fv and type are optional arguments and if omitted, they are assumed to be zeros. Example: For the example under IPMT, to calculate the principal part of the third payment, enter the formula =PPMT(0.08,3,10,10000,0,0) when there is no lump sum payment at the end and the formula =PPMT(0.08,3,10,10000,-2000,0) in the second case. The answers are -$805.16 and -$644.13, respectively. Note that in both cases the interest plus principal payments add up to the equal periodic payments, as they should. 253 CU IDOL SELF LEARNING MATERIAL (SLM)
11.5.9 CUMIPMT The CUMIPMT function calculates the cumulative interest payment you will make on a loan over a number of consecutive periods if you are repaying the loan with equal periodic payments. The syntax of the CUMIPMT function is CUMIPMT(rate,nper,pv,start_period,end_period,type) Start_period and end_period specify the consecutive periods for which the cumulative principal payment is to be calculated. Note that unlike in the other functions, here type is not optional. Example: You have a $10,000 loan at 8% interest that you will pay off in 10 equal annual instalments starting a year from now. How much cumulative interest payment will you make as parts of the third to sixth payments? To find the answer, enter the formula =CUMIPMT(0.08,10,10000,3,6,0). The answer of -$2,333.04 says that for your third through sixth payments, s$2,333.04 will be applied towards interest. (Note that here again Excel internally calculates the equal periodic payment first. In this example it is $1,490.29.) 11.5.10 CUMPRINC The CUMPRINC function calculates the cumulative principal payment you will make on a loan over a number of consecutive periods if you are repaying the loan with equal periodic payments. The syntax of the CUMPRINC function is: CUMPRINC(rate,nper,pv,start_period,end_period,type) Start_period and end_period specify the consecutive periods for which the cumulative principal payment is to be calculated. Note that unlike in some of the other functions, here type is not optional. Example: You have a $10,000 loan at 8% interest that you will pay off in 10 equal annual instalments starting a year from now. How much cumulative principal payment will you make as parts of the third to sixth payments? To find the answer, enter the formula =CUMIPMT(0.08,10,10000,3,6,0). The answer of -$3628.14 says that from your third through sixth payments, $3,628.14 will be applied towards principal. (Note that here again Excel internally calculates the equal periodic payment first. In this example it is $1,490.29. The third to sixth payments would add up to $5,961.18, which equals the sum of the part applied to the principal as calculated here and the part applied to interest as we calculated in the example for CUMIPMT.) 11.6 OTHER TIME VALUE OF MONEY FUNCTIONS In addition to the functions that apply specifically to annuities, Excel offers several other time value of money functions. Among these, you will probably use the NPV and IRR functions often and the others infrequently. 254 CU IDOL SELF LEARNING MATERIAL (SLM)
11.6.1 NPV The NPV function calculates the net present value of a series of cash flows equally spaced in time with the cash flows taking place at the end of the periods. This means that the first cash flow is assumed to take place one period from now or, equivalently, the net present value is calculated as of one period before the first cash flow. The syntax of the NPV function is: NPV (rate, value1, value2,) Rate is the discount rate per period. Value1, value2, and so on, are up to 29 arguments representing the cash flows in order (earliest first). They must be equally spaced in time. You can specify both cash inflows and cash outflows by using appropriate signs. You can enter the cash flows as a mix of numbers, individual cell references (that is, cell names or cell addresses), and arrays (ranges). If cash flow in any period is zero, you have to enter it as zero or indicate it with an extra comma. For example, the formula =NPV (0.1, 1000, A3, -B5,0, C5:C10) specifies a discount rate of 10% and has zero cash flows at the end of period 2 and also at the end of period 5. In the NPV function, you cannot directly specify cash flows at the beginning of the periods. You can get the answer for this, however, by first calculating the net present value using the NPV function and then calculating the future value of the answer for one period. As discussed earlier, you can also use the PV function to calculate the net present value for some special types of cash flows. 11.6.2 IRR The IRR function calculates the internal rate of return for a series of cash flows. The cash flows must be equally spaced in time and in order (the first cash flow first, the second cash flow second, etc.), but do not have to be equal. Also, they must include at least one positive and one negative cash flow. The syntax of the IRR function is: IRR (values, guess) Values is an array or range containing the cash flows. You can use this function as =IRR({-100,110}) where the braces are necessary to convert the cash flows into an array or as =IRR(G6:G7), where G6:G7 is a range (array) containing the cash flows Guess is an optional initial guess you provide, but you may need to provide it only if you expect the answer to be unusually large (positive or negative). If you omit it, Excel starts with a guess of 10%. 255 CU IDOL SELF LEARNING MATERIAL (SLM)
Excel tries to find an answer accurate to within 0.00001 percent using an iterative technique. If it cannot do so within 20 tries, it returns #NUM!. At that point, or if the answer is far from what you expected, try a different initial guess. Remember that the answer will be in the unit of time used for the cash flows—that is, if you use monthly cash flows, the internal rate of return will be a monthly rate. 11.6.3 MIRR The MIRR function calculates the modified internal rate of return for a series of cash flows. The cash flows must be equally spaced in time and in order (the first cash flow first, the second cash flow second, etc.), but do not have to be equal. Also, they must include at least one positive and one negative cash flow. The syntax of the MIRR function is: MIRR(values,finance_rate,investment_rate) Values is an array or range containing the cash flows. You can specify values as {- 100,110,35} where the braces are necessary to convert the cash flows into an array or as (G6:G8), where G6:G8 is a range (array) containing the cash flows. Finance_rate is the interest rate you pay on the money used in the cash flows. Investment_rate is the interest rate you receive on the cash flows as you reinvest them. If you want to use this function, first make sure you understand the concept of MIRR. 11.6.4 XNPV The XNPV function calculates the net present value of a series of cash flows that are not equally spaced in time. The syntax of the XNPV function is: XNPV(rate,values,dates) Rate is the annual discount rate to apply to the cash flows. (Here you cannot use the rate for any other measure of period.) Values represent the series of cash flows and the dates represent the corresponding dates. The safest way to use this function is to create a table of the dates and cash flows (for example, with the dates in A4:A10 and the cash flows in B4:B10) and use the ranges in a formula as =XNPV(0.1,B4:B10,A4:A10). The XNPV is calculated as of the first date in the arguments, which also has to be the earliest date. If there is no cash flow on the first date, you still have to enter it with a cash flow of zero so that Excel knows to what date you want everything discounted back. The other cash flows can be in any order because Excel does discounting based on the number of days between the date of a cash flow and the first date. (To see exactly how Excel converts the annual discount rate to the appropriate discount rate for each cash flow, look in the description of the XNPV function in Help.) 256 CU IDOL SELF LEARNING MATERIAL (SLM)
11.6.5 XIRR The XIRR function calculates the internal rate of return for a series of cash flows that are not necessarily periodic. (For periodic cash flows, it is easier to use the IRR function.) The syntax of the XIRR function is: XIRR(values,dates,guess) Values represent the series of cash flows and the dates represent the corresponding dates. The safest way to use this function is to create a table of the dates and cash flows (for example, with the dates in A4:A10 and the cash flows in B4:B10) and use the ranges in a formula as =XIRR(B4:B10,A4:A10).The cash flows must include at least one positive and one negative cash flow. Guess is an optional initial guess you provide, but you may need to provide it only if you expect the answer to be unusually large (positive or negative). If you omit it, Excel starts with a guess of 10%. Excel tries to find an answer accurate to within 0.000001 percent using an iterative technique. If it cannot do so within 100 tries, it returns #NUM!. At that point, or if the answer is far from what you expected, try a different initial guess. Note that the answer you get will be an (effective) annual rate of return. 11.6.6 FVSCHEDULE FVSCHEDULE calculates the future value of a cash flow today by applying a series of different interest rates for the intervening periods. You can use this function for variable or adjustable rate loans or investments. The syntax of the FVSCHEDULE function is: FVSCHEDULE(principal,schedule) Principal is the cash flow today and schedule is an array of the interest rates to apply. You can use this function as =FVSCHEDULE(100,{0.1,0.05}) where the braces are necessary to convert the interest rates into an array or as =FVSCHEDULE(100,G6:G7), where G6:G7 is a range (array) containing the interest rates. Note that here the interest rates do not have to be for periods of equal length because Excel simply compounds the rates as given. One interest rate may be for a three-month period, a second can be for a two-year period, and so forth, as long as together they cover the entire period. Chances are you will find it easier to write your own formulas to do this type of calculation and, therefore, will rarely use this function. 11.7 BOND FUNCTIONS PRICE Returns the price per $100 face value of a bond that pays periodic interest. This is the primary function for pricing regular bonds. The syntax is: 257 CU IDOL SELF LEARNING MATERIAL (SLM)
PRICE(settlement,maturity,rate,yield,redemption,frequency,basis) Settlement is the bond’s settlement data, maturity is the maturity date, rate is the annual coupon rate, yield is the yield to maturity, redemption is the redemption value per $100 of face value, frequency is the number of coupon payments per year, and basis is an indicator of the day counting convention to use. Basis is optional. If it is omitted, Excel assumes the 30/360-day convention. You can enter the settlement and maturity dates either as text strings with quotation marks (for example, “5/1/1999”) or as serial numbers representing the dates using Excel’s convention (that is, serial date values). You can also simply enter the dates in cells and use the cell references as arguments. 11.7.1 Yield Returns the yield to maturity for a bond that pays periodic interest. The syntax is: YIELD(settlement,maturity,rate,pr,redemption,frequency,basis) Settlement is the bond’s settlement data, maturity is the maturity date, rate is the annual coupon rate, pr is the price per $100 of face value, redemption is the redemption value per $100 of face value, frequency is the number of coupon payments per year, and basis is an indicator of the day-counting convention to use. Basis is optional. If it is omitted, Excel assumes the 30/360-day convention. You can enter the settlement and maturity dates either as text strings with quotation marks (for example, “5/1/1999”) or as serial numbers representing the dates using Excel’s convention (that is, serial date values). You can also simply enter the dates in cells and use the cell references as arguments. 11.7.2 Duration Returns the Macauley duration of a bond. The syntax is: DURATION(settlement,maturity,coupon,yield,frequency,basis) Settlement is the bond’s settlement data, maturity is the maturity date, coupon is the annual coupon rate, yield is the yield to maturity, frequency is the number of coupon payments per year, and basis is an indicator of the day-counting convention to use. Basis is optional. If it is omitted, Excel assumes the 30/360-day convention. You can enter the settlement and maturity dates either as text strings with quotation marks (for example, “5/1/1999”) or as serial numbers representing the dates using Excel’s convention (that is, serial date values). You can also simply enter the dates in cells and use the cell references as arguments. If you are not working with a specific bond but want to calculate the duration of, say, a 10-year bond with a certain coupon, and so on, use two made-up dates 10 years apart (for example, 1/1/1990 and 1/1/2000). 11.7.3 MDURATION Returns the modified duration of a bond. The syntax is: 258 CU IDOL SELF LEARNING MATERIAL (SLM)
MDURATION(settlement,maturity,coupon,yield,frequency,basis) Arguments for this function are the same as those for DURATION 11.7.4 ACCRINT Returns the accrued interest on a security such as a bond that pays periodic interest. The accrued interest is calculated from the last coupon payment date (or issue date for new bonds) to the settlement date. The syntax of the ACCRINT function is: ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis) Issue is the issue date of the security, first_interest is its first interest payment date, settlement is the date when your trade settles (that is, the date on which you pay the money and the security is transferred to your name if you are buying), rate is the security’s annual coupon rate, par is its par value, frequency is the number of coupon payments per year, and basis is the type of day-counting convention to use. Par and basis are optional. If omitted, par is assumed to be $1,000 and basis is assumed to be 30/360 11.7.5 ACCRINTM Returns the accrued interest on a security that pays interest at maturity. The syntax is: ACCRINTM(issue,maturity,rate,par,basis) Issue is the issue date of the security, maturity is the maturity date, rate is the security’s annual coupon rate, par is its par value, and basis is the type of day counting convention to use. Par and basis are optional. If omitted, par is assumed to be $1,000 and basis is assumed to be 30/360. Functions for Treasury Bills Excel provides 3 functions to calculate the bond-equivalent yield, the price, and the yield on a Treasury bill. They all use the same arguments: the settlement and maturity dates, the discount rate, and the price. 11.7.6 TBILLEQ: Returns the bond-equivalent yield for a Treasury bill. 11.7.7 TBILLPRICE: Returns the price per $100 face value for a Treasury bill. 9D: Returns the yield for a Treasury bill. 11.8 STATISTICAL FUNCTIONS Excel provides a large number of statistical functions. Before you use any of them, refer to my comments on them at the beginning of this chapter. 259 CU IDOL SELF LEARNING MATERIAL (SLM)
11.8.1 Normal Probability Distributions You will use the bell-shaped normal probability distribution in many models and calculations. Normal distributions are characterized by just two parameters, the mean and the standard deviation. You can do most calculations you need to do for normally distributed variables using the NORMDIST and NORMINV functions. A normal distribution with a mean of 0 and standard deviation of 1 is called a standard normal distribution. You can, of course, use the functions for normal distributions for standard normal distributions as well. However, because people use standard normal distributions frequently, Excel provides the functions NORMSDIST and NORMSINV specifically for standard normal distributions. They are similar to the two functions for the general normal distributions. Excel also provides a function called STANDARDIZE, which you can use to calculate how many standard deviations away from the mean a particular observation is. You will rarely use this function 11.8.2 NORMDIST The NORMDIST function returns the normal cumulative distribution for the specified mean and standard deviation. In other words, if you know the mean and standard deviation of a normal distribution, you can use NORMDIST to find the probability that a random draw from it will have a value less than x. (This probability is equal to the area under the familiar bell-shaped graph of the normal density function to the left of x.) The syntax of the NORMDIST function is: NORMDIST(x,mean,standard_dev,cumulative) Here, x is the value for which you want the cumulative probability and cumulative is a logical value that determines the output of the function. If cumulative is TRUE, NORMDIST returns the cumulative probability; if FALSE, it returns the probability mass function. Because the total area under the graph of a normal density function is 1, the probability that a random draw will have a value greater than x is equal to 1 minus the probability that it will have a value less than x. You can also use this function to calculate the probability that a random draw will have a value between x and y. Example: What is the probability that a random draw from a normal distribution with mean 3 and standard deviation 4 will have a value less than 2? Enter the formula =NORMDIST(2,3,4,TRUE) to get the probability 0.4013. This is the area under the normal density function to the left of the value 2. Example: What is the probability that a random draw from a normal distribution with mean 3 and standard deviation 4 will have a value between 2 and 6? Enter the formula =NORMDIST(6,3,4,TRUE)- NORMDIST(2,3,4,TRUE) to get the probability 0.3721. It is easiest to think of this as the area under the normal density function between 2 and 6. 260 CU IDOL SELF LEARNING MATERIAL (SLM)
11.8.3 NORMINV The NORMINV function returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. Think of NORMINV as the inverse of NORMDIST. If you know x and want to find the probability that a random draw will have a value less than x, you will use NORMDIST. If you know the probability and want to find the corresponding x, you will use the NORMINV function. The syntax of the NORMINV function is: NORMINV(probability,mean,standard_dev) Here, probability is the probability you want to specify. If you enter the formula =NORMINV(0.3,3,4) in a cell, you will get a value of 0.9024, which says that for a normal distribution with mean 3 and standard deviation 4, there is a 30% (0.3) probability that a random draw will have a value of 0.9024 or less. As I explained under NORMDIST, you can also say that there is a 70% probability that a random draw will have a value greater than 0.9024. 11.8.4 NORMSDIST This function, which is designed specifically for standard normal distributions— that is, normal distributions with mean of 0 and standard deviation of 1—works the same way as the NORMDIST function except that it requires only one argument (the x value). The syntax is NORMDIST(x) 11.8.5 NORMSINV This function, which is designed specifically for standard normal distribution— that is, normal distributions with mean of 0 and standard deviation of 1—works the same way as the NORMINV function except that it requires only one argument (the probability). The syntax is NORMINV(probability). 11.8.6 Standardize The STANDARDIZE function returns the distance of an observation, measured in terms of standard deviation, from the mean. This measure, called the Z-value, was used in pre- computer days to look up probabilities from standard normal distribution tables. Now that you can find the probabilities easily using functions like NORMDIST, this function is of limited use. The syntax of the STANDARDIZE function is: STANDARDIZE(x,mean,standard_dev) 11.8.7 Random Numbers Excel provides two functions called RAND and RANDBETWEEN as well as a Random Number Generation tool in the Analysis ToolPak to generate random numbers (the same 261 CU IDOL SELF LEARNING MATERIAL (SLM)
thing as drawing random samples) from a number of common statistical distributions. (Excel includes RAND and RANDBETWEEN in the Mathematical and Trigonometric functions category.) The RAND and RANDBETWEEN functions can generate random numbers only for uniform distributions whereas the Random Number Generation tool offers a number of other choices of distribution and some additional flexibilities. Another difference is that the two functions may be called dynamic in the sense that every time the worksheet recalculates, they generate new draws. If you use them in a model such as one that simulates stock prices, then every time the worksheet recalculates—you can force it by pressing F9—you will have a new simulation. The Random Number Generation tool generates a set of static draws that are not going to change as your worksheet recalculates or other changes take place. If you want a new set of draws, you will have to generate them by repeating the necessary steps. 11.8.8 RAND RAND function returns an evenly or uniformly distributed random number greater than or equal to 0 and less than or equal to 1. Evenly or uniformly distributed means all numbers between 0 and 1 will have equal probability of being drawn. The RAND function does not take any argument, but you still have to include a pair of empty parentheses. To use it you enter =RAND() in a cell, and every time the worksheet is recalculated RAND will generate a new random number in that cell. (You can force a recalculation and generate a new random numbers by pressing F9.) If you need 20 random draws, you have to enter the RAND function in 20 cells, and every time the worksheet recalculates you will have 20 new similar random draws that are independent of one another. Although the RAND function does not seem very versatile, with a little ingenuity you can make additional uses of it. 11.8.9 RANDBETWEEN The RANDBETWEEN function returns a uniformly distributed random number between the numbers you specify. A new random number is returned every time the worksheet is calculated. The syntax of the RANDBETWEEN function is: RANDBETWEEN (bottom, top) Bottom and top are the lower and upper limits of the range. You use it the same way as the RAND function. Random Number Generation (This is an Analysis ToolPak tool.) This tool generates one or more columns of random numbers with one of the following distributions: Uniform, Normal, Bernoulli, Binomial, Poisson, and Discrete. (There is a seventh option called Patterned, but it does not generate random numbers.) Unlike the RAND() function, this tool creates a table of random 262 CU IDOL SELF LEARNING MATERIAL (SLM)
numbers that will not change when the worksheet recalculates. If you want another set of random numbers, you will have to repeat the steps. The inputs to the dialog box are: • Number of variables: Enter the number of columns of values you want to generate. If you do not specify a number, Excel will fill all columns in the output range you specify. If you want 100 random numbers, this gives you an option to specify whether you want them in one column with 100 rows or 5 columns with 20 rows, or something else. If you opt for the latter, you can also think of the random numbers produced as 20 values for each of 5 different variables with the same distribution. This is why the number of columns is referred to as the number of variables. • Number of random numbers: Enter the number of data points you want to see in each column. If you do not specify a number, Excel will fill all rows in the output range you specify • Distribution: Choose the distribution you want. You will mostly use the Uniform or the Normal distributions, as shown in the examples below. (If you need descriptions of the other distributions or how to use them, click the Help button in the tool’s dialog box.) • Parameters: Enter a value or values to characterize the distribution you selected. For example, the minimum and maximum values for uniform distribution or the mean and standard deviation for normal distribution. • Random seed: You usually leave this blank. However, if you think that in the future you may want to generate the same sequence of random numbers again, enter an integer between 1 and 32,767 and make a note of it. Every time you generate a set of random numbers with the same distribution and parameters and the same seed, Excel will generate the same sequence of random numbers. • Output options: Choose Output Range and specify a range if you want your output in a specific range in a specific worksheet. Otherwise choose one of the other options. If you are not specifying a range, then make sure that you have specified the number of random numbers you want. Example: To generate 30 random numbers uniformly distributed between 1 and 3, enter 1 for Number of Variables or leave it blank, enter 30 for Number of Random Numbers, choose Uniform for distribution, specify between 1 and 3 in Parameters, and enter a range with 1 column and 30 rows in the Output Range box. Click OK to generate the random numbers. (Uniform distribution means every number in the specified range will have equal probability of being drawn.) 263 CU IDOL SELF LEARNING MATERIAL (SLM)
Example: To generate 100 random draws for each of two normally distributed variables with a mean of 10 and a standard deviation of 15, enter 2 for Number of Variables, 100 for Number of Random Numbers, choose Normal distribution, and specify a mean of 10 and standard deviation of 15. If you want the output in a new worksheet named Test, select New Worksheet Ply in Output Options and type “Test” in the box next to it. Click OK and Excel will insert a properly named new worksheet and generate two columns of 100 random numbers, each starting in A1 and B1. 11.9 CALCULATING VARIOUS STATISTICS • Average Returns the arithmetic average of its arguments that are numbers. Includes zeros in the calculations but ignores text and other values. • AVERAGEA Calculates the arithmetic average of the values in the list of arguments. In addition to numbers, includes text and logical values such as TRUE and FALSE in the calculation. TRUE is included as 1, texts, blanks, and FALSE are included as 0. • Median Returns the median of the given numbers. Ignores texts, logical values, and empty cells but includes zeros. • Mode Returns the most frequently occurring, or repetitive, value in an array or range of data. Ignores texts, logical values, and empty cells but includes zeros. • Stdev Estimates the population standard deviation assuming the data provided is a random sample. Logical values (such as TRUE and FALSE) and text are ignored. If logical values and text must not be ignored, use the STDEVA worksheet function. • STDEVA Estimates the population standard deviation assuming the data provided is a random sample. Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the STDEV worksheet function instead. • STDEVP 264 CU IDOL SELF LEARNING MATERIAL (SLM)
Calculates standard deviation assuming that the data provided is the entire population— that is, it calculates sample standard deviation. Logical values (such as TRUE and FALSE) and text are ignored. If logical values and text must not be ignored, use the STDEVPA worksheet function. • STDEVPA Calculates standard deviation, assuming that the data provided is the entire population— that is, it calculates sample standard deviation. Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the STDEVP worksheet function instead. • VAR Estimates (population) variance based on a sample. Logical values (such as TRUE and FALSE) and text are ignored. If logical values and text must not be ignored, use the VARA worksheet function. • VARA Estimates (population) variance based on a sample. Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the VAR worksheet function. • VARP Calculates variance assuming that the data provided is the entire population— that is, it calculates sample variance. Logical values (such as TRUE and FALSE) and text are ignored. If logical values and text must not be ignored, use the VARPA worksheet function. • VARPA Calculates variance assuming that the data provided is the entire population— that is, it calculates sample variance. Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the VARP worksheet function instead. • KURT Returns the kurtosis of a data set or distribution. • SKEW Returns the skewness of a data set or distribution. • CORREL 265 CU IDOL SELF LEARNING MATERIAL (SLM)
Returns the correlation coefficient between two sets of data. If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. • COVAR Returns the covariance, that is, the average of the products of deviations for each pair of data points for two sets of data. If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with zero values are included. 11.10 SUMMARY • Excel provides numerous useful built-in functions as well as several useful tools in the Analysis Tool Pak which helps in building financial models on Excel. • It is important to understand how a function calculate the answer rather than blindly applying it in your modelling. This will help in eliminating errors while and also track back where a possible error might have happened. • Analysis Tool Pak is an add-on feature of Excel. It includes a number of analysis tools as well as a number of functions. You can add it in as follows: Select Tools ➩ Add-Ins, in the Add-Ins dialog box check the box to the left of Analysis ToolPak, and click OK. • Annuity Functions Constant annuities are equal cash flows (either all inflows or all outflows) that take place at equal time intervals. Constant annuities are governed by a set of 4 variables; if you specify 3, then the fourth can be calculated. The Excel functions for calculating these variables are: PV, FV, PMT, RATE, and NPER. • Rate is the interest rate per period (month, year, etc.). • Nper is the total number of periods (same as total number of payments) for the annuity. • Pmt is the annuity amount per period. • Pv is an additional cash flow now (time 0) • Fv is an additional cash flow nper periods from now. 266 CU IDOL SELF LEARNING MATERIAL (SLM)
• Type is 0 for a regular annuity or annuity in arrears where the first payment occurs one period from now and 1 for an annuity due or annuity in advance where the first payment occurs now. • PV(rate,nper,pmt,fv,type) The PV function calculates the present value of a constant annuity. • FV(rate,nper,pmt,pv,type) The FV function calculates the future value of a constant annuity. • PMT(rate,nper,pv,fv,type) The PMT function calculates the constant annuity payments you will have to make to pay off a loan or to accumulate a certain amount of money. • NPER(rate,pmt,pv,fv,type) The NPER function calculates how many constant annuity payments you will have to make to pay off a loan or to accumulate a certain sum of money. • RATE(nper,pmt,pv,fv,type,guess) The RATE function calculates the periodic interest rate on a loan that can be paid off with a certain number of equal periodic payments. • IPMT(rate,per,nper,pv,fv,type) The IPMT function calculates how much of the equal periodic payment you make on a loan in a particular period will be applied towards interest. • PPMT(rate,per,nper,pv,fv,type) The PPMT function calculates how much of the equal periodic payment you make on a loan in a particular period will be applied towards the loan principal. • CUMIPMT(rate,nper,pv,start_period,end_period,type) The CUMIPMT function calculates the cumulative interest payment you will make on a loan over a number of consecutive periods if you are repaying the loan with equal periodic payments. 267 CU IDOL SELF LEARNING MATERIAL (SLM)
• CUMPRINC(rate,nper,pv,start_period,end_period,type) The CUMPRINC function calculates the cumulative principal payment you will make on a loan over a number of consecutive periods if you are repaying the loan with equal periodic payments. • Other time value of money functions: • NPV (rate, value1, value2,) The NPV function calculates the net present value of a series of cash flows equally spaced in time with the cash flows taking place at the end of the periods. • IRR(values,guess) The IRR function calculates the internal rate of return for a series of cash flows. • MIRR(values,finance_rate,investment_rate) The MIRR function calculates the modified internal rate of return for a series of cash flows. • XNPV(rate,values,dates) The XNPV function calculates the net present value of a series of cash flows that are not equally spaced in time. • XIRR(values,dates,guess) The XIRR function calculates the internal rate of return for a series of cash flows that are not necessarily periodic. • FVSCHEDULE(principal,schedule) FVSCHEDULE calculates the future value of a cash flow today by applying a series of different interest rates for the intervening periods. • Bond functions • PRICE(settlement,maturity,rate,yield,redemption,frequency,basis) Returns the price per $100 face value of a bond that pays periodic interest. 268 CU IDOL SELF LEARNING MATERIAL (SLM)
• YIELD(settlement,maturity,rate,pr,redemption,frequency,basis) Returns the yield to maturity for a bond that pays periodic interest. The syntax is: • DURATION(settlement,maturity,coupon,yield,frequency,basis) Returns the Macauley duration of a bond. The syntax is: • MDURATION(settlement,maturity,coupon,yield,frequency,basis) Returns the modified duration of a bond. The syntax is: • ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis) Returns the accrued interest on a security such as a bond that pays periodic interest. • ACCRINTM(issue,maturity,rate,par,basis) Returns the accrued interest on a security that pays interest at maturity. • TBILLEQ: Returns the bond-equivalent yield for a Treasury bill. • TBILLPRICE: Returns the price per $100 face value for a Treasury bill. • TBILLYIELD: Returns the yield for a Treasury bill. • Statistical Functions • Normal Probability Distributions • NORMDIST(x,mean,standard_dev,cumulative) Returns the normal cumulative distribution for the specified mean and standard deviation. • NORMINV(probability,mean,standard_dev) Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. • NORMDIST(x) 269 CU IDOL SELF LEARNING MATERIAL (SLM)
• esigned specifically for standard normal distributions, i.e., normal distributions with mean of 0 and standard deviation of 1—works the same way as the NORMDIST function except that it requires only one argument (the x value). • NORMINV(probability). Designed specifically for standard normal distributions, i.e., normal distributions with mean of 0 and standard deviation of 1—works the same way as the NORMINV function except that it requires only one argument (the probability). • STANDARDIZE(x,mean,standard_dev) Returns the distance of an observation, measured in terms of standard deviation, from the mean. • RANDOM NUMBERS • RAND() Returns an evenly or uniformly distributed random number greater than or equal to 0 and less than or equal to 1. • RANDBETWEEN(bottom,top) Returns a uniformly distributed random number between the numbers you specify. • Random Number Generation using Analysis ToolPak Generates one or more columns of random numbers with one of the following distributions: Uniform, Normal, Bernoulli, Binomial, Poisson, and Discrete. Unlike the RAND() function, this tool creates a table of random numbers that will not change when the worksheet recalculates. The inputs to the dialog box are: o Number of variables o Number of random numbers o Distribution o Parameters o Random seed 270 CU IDOL SELF LEARNING MATERIAL (SLM)
o Output options • Calculating various statistics •AVERAGE Returns the arithmetic average of its arguments that are numbers. •AVERAGEA Calculates the arithmetic average of the values in the list of arguments. •MEDIAN Returns the median of the given numbers. •MODE Returns the most frequently occurring, or repetitive, value in an array or range of data. •STDEV Estimates the population standard deviation assuming the data provided is a random sample. •STDEVA Estimates the population standard deviation assuming the data provided is a random sample. •STDEVP Calculates standard deviation assuming that the data provided is the entire population— that is, it calculates sample standard deviation. •STDEVPA Calculates standard deviation, assuming that the data provided is the entire population— that is, it calculates sample standard deviation. •VAR 271 CU IDOL SELF LEARNING MATERIAL (SLM)
Estimates (population) variance based on a sample. Logical values (such as TRUE and FALSE) and text are ignored. •VARA Estimates (population) variance based on a sample. Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). •VARP Calculates variance assuming that the data provided is the entire population— that is, it calculates sample variance. •VARPA Calculates variance assuming that the data provided is the entire population— that is, it calculates sample variance. •KURT Returns the kurtosis of a data set or distribution. •SKEW Returns the skewness of a data set or distribution. •CORREL Returns the correlation coefficient between two sets of data. •COVAR Returns the covariance, that is, the average of the products of deviations for each pair of data points for two sets of data. 11.11 KEYWORDS • Treasury Bills • IRR-Internal Rate of Return • NPV – Net present value 272 CU IDOL SELF LEARNING MATERIAL (SLM)
11.12 LEARNING ACTIVITY 1. Forecast working capital for a company from the FMCG sector. Use last 3 years data. ________________________________________________________________________ ________________________________________________________________________ 11.13 UNIT END QUESTIONS A.Descriptive Questions Short Questions 1. What is NPV and IRR? How do you calculate them? 2. What do you understand from Working Capital? 3. What is the difference between NPV and XNPV functions in Excel? Long Questions 1. Which tools would you use in Excel to audit your model? 2. What are LOOKUP and VLOOKUP? What to use when? 3. What is the difference between NPV and XNPV? B. Multiple choice Questions 1. Syntax for ACCRINTM: a. ACCRINTM(issue,maturity,rate,par,basis) b. ACCRINTM(rate,par,basis,issue,maturity,) c. ACCRINTM(par,basis,issue,maturity,) d. ACCRINTM(rate,par,basis,maturity,issue,) 2. Syntax for NORMINV a. NORMINV(probability,median,standard_dev) b. NORMINV(mean,probability,standard_dev) c. NORMINV(probability,mean,standard_dev) d. NORMINV(probability,par,standard_dev) 3. Functionality of YIELD a. Returns the modified duration of a bond b. Returns the yield to maturity for a bond that pays periodic interest c. Returns the accrued interest on a security such as a bond that pays periodic interest d. Returns the Macauley duration of a bond 273 CU IDOL SELF LEARNING MATERIAL (SLM)
4. The NPER function calculates a. The periodic interest rate on a loan that can be paid off with a certain number of equal periodic payments. b. How much of the equal periodic payment you make on a loan in a particular period will be applied towards interest. c. The number of constant annuity payments you will have to make to pay off a loan or to accumulate a certain sum of money. d. How much of the equal periodic payment you make on a loan in a particular period will be applied towards the loan principal. 5.Which function calculates the net present value of a series of cash flows equally spaced in time with the cash flows taking place at the end of the periods. a. NPV b. NPER c. IRR d. MIRR 6.Which function calculates the net present value of a series of cash flows that are not equally spaced in time. a. NPER b. XNPV c. XIRR d. NPV 7.Returns the price per $100 face value of a bond that pays periodic interest. This is the primary function for pricing regular bonds. a. RATE b. PRICE c. YIELD d. DURATION 8.Which function returns the inverse of the normal cumulative distribution for the specified mean and standard deviation? a. NORMINV b. NORMSDIST c. NORMINV d. STANDARDIZE 274 CU IDOL SELF LEARNING MATERIAL (SLM)
9.RANDBETWEEN returns a. A uniformly distributed random number between the numbers you specify. b. The arithmetic average of the values in the list of arguments. c. Returns an evenly or uniformly distributed random number greater than or equal to 0 and less than or equal to 1. d. The periodic interest rate on a loan that can be paid off with a certain number of equal periodic payments. 10. STDEVA a. Estimates the population standard deviation assuming the data provided is a random sample. Logical values (such as TRUE and FALSE) and text are ignored. b. Estimates the population standard deviation assuming the data provided is a random sample. c. Calculates standard deviation, assuming that the data provided is the entire population—that is, it calculates sample standard deviation. d. Estimates (population) variance based on a sample. Logical values (such as TRUE and FALSE) and text are ignored. Answers 1-a, 2-c, 3-b, 4-c, 5-a, 6-b, 7-b, 8-a, 9-a, 10-b. 11.14 REFERENCES Textbooks: • Edward Yescombe, Principles of Project Finance, Yecombe Consulting Ltd., Academic Press • Michael Rees, Principles of Financial Modelling: Model Design and Best Practices Using Excel and VBA , The Wiley Finance Series) Reference Books: • Edward Bodmer, Corporate and project finance modelling, Wiley Finance Series 275 CU IDOL SELF LEARNING MATERIAL (SLM)
UNIT 12- ADVANCE MODELING TECHNIQUE Structure 12.0Learning Objectives 12.1Lookup and Reference Functions 12.1.1 Looking Up Values 12.1.2 Lookup (Vector Form) 12.1.3 Lookup (Lookup Value, Lookup Vector, Result Vector) 12.1.4 HLOOKUP, VLOOKUP 12.1.5 HLOOKUP (Lookup Table, Table Array, Row Index Num, Range Lookup) 12.1.6 Match 12.1.7 Choose 12.1.8 Index 12.1.9 Offset 12.2What is a Pivot Table in Excel 12.3How to Use a Pivot Table in Excel 12.4Why Macros are Important in Financial Modelling Syllabus 12.5Understanding Basic Macros 12.6Summary 12.7Keywords 12.8Learning Activity 12.9Unit End Questions 12.10 References 12.0 LEARNING OBJECTIVES After studying this unit, you will be able to: • Use Excel for Advance Modelling • Learn lookup and reference functions • Explain the PIVOT Table • Learn importance of Macros 12.1 LOOKUP AND REFERENCE FUNCTIONS 12.1.1 Looking Up Values Excel offers several functions to look up values from a table or from the arguments of the function itself. For example, you may want to look up from a tax table the marginal tax 276 CU IDOL SELF LEARNING MATERIAL (SLM)
rate for a certain taxable income. You may want to look up from a table of detailed information on inventory the cost and availability of particular products. These Excel functions will help you do such things easily. 12.1.2 Lookup (Vector Form) Looks in a one-row or one-column range for a value and returns a value from the same position in a second one-row or one-column range. (This is called the vector form of LOOKUP. There is a second form of the LOOKUP function known as the array form. It is better to use the HLOOKUP or VLOOKUP to do what the array form does.) The syntax of the vector form of the LOOKUP function is: 12.1.3 Lookup (Lookup_Value, Lookup_Vector, Result_Vector) Lookup value is the value that LOOKUP searches for in the first vector. Lookup value can be a number, text, a logical value, or a name or reference that refers to a value. Lookup vector is a range that contains only one row or one column. The values in it can be text, numbers, or logical values. The values in lookup vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase text and lowercase text are considered equivalent. Result vector is a range that contains only one row or column. It must be the same size as lookup vector. If LOOKUP cannot find lookup value, it matches the largest value in lookup vector that is less than or equal to lookup value. This makes it possible to look up values where lookup value falls in a range instead of matching a specific value. If lookup value is smaller than the smallest value in lookup vector, LOOKUP gives the #N/A! error value. Example: The tax table in the below Figure provides information for calculating taxes given the taxable income. In the table, the marginal tax rate is 15% and the base tax amount is $0 for taxable income less than $26,000, they are 28% and $3,900, respectively, for income greater than or equal to $26,000 but less than $63,000, and so forth. Here is how you will use the LOOKUP function to look up the marginal tax rate and the results you will get for various taxable incomes. =LOOKUP(29000,A4:A8,B4:B8) will return 28% =LOOKUP(26000,A4:A8,B4:B8) will return 28% =LOOKUP(300000,A4:A8,B4:B8) will return 40% 277 CU IDOL SELF LEARNING MATERIAL (SLM)
12.1.4 HLOOKUP, VLOOKUP HLOOKUP and VLOOKUP are parallel functions that work the same way. HLOOKUP searches for a value in the top row of a table or an array of values and then returns the value from a specified row in the same column of the table or array. VLOOKUP searches for a value in the leftmost column of a table and then returns a value from a specified column in the same row of the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The syntax of the HLOOKUP function is: 12.1.5 HLOOKUP (Lookup_Value, Table_Array,Row_Index_Num,Range_Lookup) Lookup value is the value to be found in the first row of the table. Lookup_ value can be a value, a reference, or a text string. Table array is a table of information in which data is looked up. Use a reference to a range or a range name. The values in the first row of table array can be text, numbers, or logical values. If range lookup is TRUE, then the values in the first row of table array must be placed in ascending order. (See LOOKUP for definition of ascending order.) Row_index_num is the row number in table array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table array, a row_index_num of 2 returns the second row value in table array, and so on. Range lookup is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the largest value that is less than lookup value is returned. If FALSE, HLOOKUP will look for an exact match. If one is not found, the error value #N/A! is returned. This argument is optional, and if omitted is assumed to be TRUE. Here is how you will use the VLOOKUP function to look up the base tax amounts from the above table. =VLOOKUP(140000,A4:C8,3) will return $35,650 =VLOOKUP(63000,A4:C8,3) will return $14,260 =VLOOKUP(140000,A4:C8,3,FALSE) will return #N/A! (no exact match). You could also use VLOOKUP to look up the marginal tax rates as follows: 278 CU IDOL SELF LEARNING MATERIAL (SLM)
=VLOOKUP(140000,A4:C8,2,TRUE) will return 36% 12.1.6 Match Returns the relative position of an item in a range of contiguous cells in a row or a column that matches a specified value. Even though the function is called MATCH, as with the lookup functions, it does not need to find an exact match (unless you specifically require it). Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. The syntax of the MATCH function is: MATCH(lookup_value,lookup_array,match_type) Lookup value is the value you use to find the value you want in a table. Lookup array is a range of contiguous cells containing possible lookup values. Lookup array can be an array or an array reference. Match type is the number -1, 0, or 1. Match type specifies how Microsoft Excel matches lookup value with values in lookup array. ■ If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. ■ If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order. ■ If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A...2, 1, 0, -1, -2..., and so on. ■ This argument is optional. If match_type is omitted, it is assumed to be 1. We can use the MATCH function to find the row number for a certain taxable income to get the following results: =MATCH(64000,A4:A8,1) will return 3. =MATCH(63000,A4:A8,0) will return 3. =MATCH(64000,A4:A8,0) will return #N/A! (no exact match) . =MATCH(64000,A4:A8,-1) will return #N/A! (values not in descending order). Note that the 3 in the above examples indicates the relative position within the array, that is, it means the third row of the array and not the row number 3 in the Excel spreadsheet. Of course, if the array started in Excel’s row number 1, then the number returned would also refer to Excel’s row number. 279 CU IDOL SELF LEARNING MATERIAL (SLM)
12.1.7 Choose Based on a specified index number (1st, 2nd, etc.), this function chooses a value from the list of its value arguments. The arguments can be numbers, cell references, defined names, formulas, functions, or text. 12.1.8 Index Returns the value of an element in a table or an array, selected by the row and column number indexes. The syntax of the INDEX function is: INDEX(array,row_num,col_num) Array is a range of cells. Row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required. Column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required. We can use the INDEX function to look up any value in the tax table by specifying its row and column numbers within the table as follows: =INDEX(A4:C8,2,3) will return 3900. If the array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column of the array. If you need to use this option, you will have to enter the formula as an array formula in an appropriate size range. For example, if you want the entire second column of the tax table, you can select A13:A17, type in the formula =INDEX (A4:C8,2) and then press Shift+Ctrl+Enter. 12.1.9 Offset Returns the reference to a single cell or a range of cells that is a specified number of rows and columns from a cell or range of cells. If you enter the function by itself in a cell and the reference created is a single cell, then its value will be displayed. If you want similar results when the reference created is a range of cells, then you will have to enter it as an array formula in an array of appropriate dimensions. Alternately, you can do other things with the created reference when it is a range of cells, for example, sum the values in the reference range by using the OFFSET function as an argument of the SUM function. 12.2 WHAT IS A PIVOT TABLE IN EXCEL? A pivot table allows you to organize, sort, manage and analyze large data sets in a dynamic way. Pivot tables are one of Excel’s most powerful data analysis tools, used extensively by financial analysts around the world. In a pivot table, Excel essentially runs a database behind the scenes, allowing you to easily manipulate large amounts of information. 280 CU IDOL SELF LEARNING MATERIAL (SLM)
12.3 HOW TO USE A PIVOT TABLE IN EXCEL Below is a step by step guide of how to insert a pivot table in Excel: 1 .Organize the data The first step is to ensure you have well-organized data that can easily be turned into a dynamic table. This means ensuring that all data is in the proper rows and columns. If data is not properly organized, then the table will not work properly. Ensure that the categories (category names) are located in the top row of the dataset, as shown in the screenshot below. 2. Insert the pivot table In step two, you select the data you want to include in the table and then, on the Insert Tab on the Excel ribbon, locate the tables Group and select Pivot Table, as shown in the screenshot below. 281 CU IDOL SELF LEARNING MATERIAL (SLM)
When the dialog box comes up, ensure the right data are selected and then decide if you want the table to be inserted as a new worksheet, or located somewhere on the current worksheet. This is entirely up to you and your personal preference. 282 CU IDOL SELF LEARNING MATERIAL (SLM)
3. Setup the pivot table fields Once you’ve completed step two, the “PivotTable Fields” box will appear. This is where you set the fields by dragging and dropping the options that are listed as available fields. You can also use the tick boxes next to the fields to select the items you want to see in the table. 4. Sort the table Now that the basic pivot table is in place, you can sort the information by multiple criteria, such as name, value, count, or other things. To sort the date, click on the auto sort button (highlighted in the image below) and then click “more sort options” to pick from the various criteria you can sort by. Another option is to right-click anywhere in the table and then select Sort, and then “more sort options”. 283 CU IDOL SELF LEARNING MATERIAL (SLM)
5 Filter the data Adding a filter is a great way of sorting the data very easily. In the above example, we showed how to sort, but now with the filter function, we can see the data for specific sub- sections with the click of a button. In the image below you can see how, by dragging the “channel” category from the list of options down to the Filters section, all of a sudden an extra box appears at the top of the pivot table that says “channel”, indicating the filter has been added. 284 CU IDOL SELF LEARNING MATERIAL (SLM)
Next, we can click on the filter button and select the filters we want to apply (as shown below). After this step is completed, we can see the revenue, shipping, and marketing spending for all products that were sold via the Instagram channel, for example. More filters can be added to the pivot table as required. 285 CU IDOL SELF LEARNING MATERIAL (SLM)
6. Edit the data values (calculations) The default in Excel pivot tables is that all data is shown as the sum of whatever is being displayed in the table. For example, in this table, we see the sum of all revenues by category, the sum of all shipping expenses by category, and the sum of all marketing expenses by category. To change from showing the sum of all revenues to the “count” of all revenue we can determine how many items were sold. This may be useful for reporting purposes. To do this, right-click on the data you wish the change the value of and select “Value field settings” which will open the box you see in the screenshot below In accounting and financial analysis, this is a very important feature, as it’s often necessary to move back and forth between units/volume (the count function) and total cost or revenue (the sum function). 7. Adding an extra dimension to the pivot table At this point, we only have one category in the rows and one in the columns (the values). It may be necessary, however, to add an extra dimension. A brief warning, however, that this could significantly increase the size of your table. 286 CU IDOL SELF LEARNING MATERIAL (SLM)
In order to do this, click on the table so that the “fields” box pops up and drag an extra category, such as “dates”, into the columns box. This will subdivide each column heading into additional columns for each date contained in the data set. In the example below, you can see how the extra dates dimension has been added to the columns to provide much more data in the pivot table. 12.4 WHY MACROS ARE IMPORTANT IN FINANCIAL MODELING SYLLABUS Financial Modelling syllabus teaches you to work with historical information on companies and analyze the company/industry performance on relevant financial parameters. This analysis is then used as an input to build financial models. These models then project the future financial performance of the companies analyzed and hence give us an estimate of the valuation of the companies. Financial Modelling is used in every sphere of the financial world. In fact, it is one of the most important aspects of any corporate set up. In Financial Modelling syllabus, Macros and VBA for Excel are commonly used to develop and maintain complex financial models. They allow the finance professional to increase efficiency and accuracy as well as providing more flexibility in building models. A macro is a collection of commands which are executed in a set order. A macro allows you to repeat operations that you would usually do manually by hand. Macros are much faster, and when written accurately and much more dependable. Macro can perform any task in seconds which can take lots of time manually. It also does all the tasks which can’t be done manually. Macro can help you to record all the actions, if you perform the same commands or actions over and over again, in exactly the same 287 CU IDOL SELF LEARNING MATERIAL (SLM)
sequence. A macro could be assigned to a button and then it could be operated using a single click and macro could even be assigned to a keyboard command. Macros are a great way of automating mundane Financial Modelling syllabus that would have taken a lot of time. For example, while preparing a financial model, you need to format certain cells in particular type across all the sheets. Macros will help you to record this repetitive task and help you to copy the formatting across all the sheets. Macros have made things easier for many of the analytical situations, as it helps to save time and increase accuracy. It is not necessary to build macros within financial models, they could be used in the group of the data that goes into the model. Visual Basic for Applications allows a lot of customization beyond what is typically available in Excel. A user type commands to create a macro. Macros allow the user to automatically generate customized charts, reports and perform other data processing functions. Although it is not necessary to become an amazing VBA programmer in a process to become a good financial modeller, it would be an add-on to have a working knowledge of macros and the language they are built in, Visual Basic for Applications (VBA). 12.5 UNDERSTANDING BASIC MACROS. Using Excel Macros can speed up work and save you a lot of time. One way of getting the VBA code is to record the macro and take the code it generates. However, that code by macro recorder is often full of code that is not really needed. Also macro recorder has some limitations. So it pays to have a collection of useful VBA macro codes that you can have in your back pocket and use it when needed. While writing an Excel VBA macro code may take some time initially, once it’s done, you can keep it available as a reference and use it whenever you need it next. In this massive article, I am going to list some useful Excel macro examples that I need often and keep stashed away in my private vault. I will keep updating this tutorial with more macro examples. If you think something should be on the list, just leave a comment. Now before I get into the Macro Example and give you the VBA code, let me first show you how to use these example codes. Using the Code from Excel Macro Examples Here are the steps you need to follow to use the code from any of the examples: • Open the Workbook in which you want to use the macro. 288 CU IDOL SELF LEARNING MATERIAL (SLM)
• Hold the ALT key and press F11. This opens the VB Editor. • Right-click on any of the objects in the project explorer. • Go to Insert –> Module. • Copy and Paste the code in the Module Code Window. In case the example says that you need to paste the code in the worksheet code window, double click on the worksheet object and copy paste the code in the code window. Once you have inserted the code in a workbook, you need to save it with a .XLSM or .XLS extension. How to Run the Macro Once you have copied the code in the VB Editor, here are the steps to run the macro: • Go to the Developer tab. • Click on Macros. • In the Macro dialog box, select the macro you want to run. • Click on Run button. 289 CU IDOL SELF LEARNING MATERIAL (SLM)
In case the code is pasted in the worksheet code window, you don’t need to worry about running the code. It will automatically run when the specified action occurs. Now, let’s get into the useful macro examples that can help you automate work and save time. • Unhide All Worksheets at One Go If you are working in a workbook that has multiple hidden sheets, you need to unhide these sheets one by one. This could take some time in case there are many hidden sheets. Here is the code that will unhide all the worksheets in the workbook. 'This code will unhide all sheets in the workbook Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub The above code uses a VBA loop (For Each) to go through each worksheet in the workbook. It then changes the visible property of the worksheet to visible. Here is a detailed tutorial on how to use various methods to unhide sheets in Excel. • Hide All Worksheets Except the Active Sheet If you’re working on a report or dashboard and you want to hide all the worksheet except the one that has the report/dashboard, you can use this macro code. 'This macro will hide all the worksheet except the active sheet Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub • Sort Worksheets Alphabetically Using VBA 290 CU IDOL SELF LEARNING MATERIAL (SLM)
If you have a workbook with many worksheets and you want to sort these alphabetically, this macro code can come in really handy. This could be the case if you have sheet names as years or employee names or product names. 'This code will sort the worksheets alphabetically Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j). Name < Sheets(i). Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub • Protect All Worksheets At One Go If you have a lot of worksheets in a workbook and you want to protect all the sheets, you can use this macro code. It allows you to specify the password within the code. You will need this password to unprotect the worksheet. 'This code will protect all the sheets at one go Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = \"Test123\" 'replace Test123 with the password you want For Each ws In Worksheets ws.Protect password:=password Next ws 291 CU IDOL SELF LEARNING MATERIAL (SLM)
End Sub • Unprotect All Worksheets At One Go If you have some or all of the worksheets protected, you can just use a slight modification of the code used to protect sheets to unprotect it. 'This code will protect all the sheets at one go Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = \"Test123\" 'replace Test123 with the password you want For Each ws In Worksheets ws.Unprotect password:=password Next ws End Sub Note that the password needs to the same that has been used to lock the worksheets. If it’s not, you will see an error. • Unhide All Rows and Columns This macro code will unhide all the hidden rows and columns. This could be really helpful if you get a file from someone else and want to be sure there are no hidden rows/columns. 'This code will unhide all the rows and columns in the Worksheet Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub • Unmerge All Merged Cells It’s a common practice to merge cells to make it one. While it does the work, when cells are merged you will not be able to sort the data. In case you are working with a worksheet with merged cells, use the code below to unmerge all the merged cells at one go. 'This code will unmerge all the merged cells Sub UnmergeAllCells() 292 CU IDOL SELF LEARNING MATERIAL (SLM)
ActiveSheet.Cells.UnMerge End Sub Note that instead of Merge and Centre, I recommend using the Centre Across Selection option. • Save Workbook with TimeStamp in Its Name A lot of time, you may need to create versions of your work. These are quite helpful in long projects where you work with a file over time. A good practice is to save the file with timestamps. Using timestamps will allow you to go back to a certain file to see what changes were made or what data was used. Here is the code that will automatically save the workbook in the specified folder and add a timestamp whenever it’s saved. 'This code will Save the File with a Timestamp in its name Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, \"dd-mm-yyyy\") & \"_\" & Format(Time, \"hh-ss\") ThisWorkbook.SaveAs \"C:UsersUsernameDesktopWorkbookName\" & timestamp End Sub You need to specify the folder location and the file name. In the above code, “C:UsersUsernameDesktop is the folder location I have used. You need to specify the folder location where you want to save the file. Also, I have used a generic name “WorkbookName” as the filename prefix. You can specify something related to your project or company. • Save Each Worksheet as a Separate PDF If you work with data for different years or divisions or products, you may have the need to save different worksheets as PDF files. While it could be a time-consuming process if done manually, VBA can really speed it up. Here is a VBA code that will save each worksheet as a separate PDF. 'This code will save each worksheet as a separate PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet 293 CU IDOL SELF LEARNING MATERIAL (SLM)
For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, \"C:UsersSumitDesktopTest\" & ws.Name & \".pdf\" Next ws End Sub In the above code, I have specified the address of the folder location in which I want to save the PDFs. Also, each PDF will get the same name as that of the worksheet. You will have to modify this folder location (unless your name is also Sumit and you’re saving it in a test folder on the desktop). Note that this code works for worksheets only (and not chart sheets). • Save Each Worksheet as a Separate PDF Here is the code that will save your entire workbook as a PDF in the specified folder. 'This code will save the entire workbook as PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, \"C:UsersSumitDesktopTest\" & ThisWorkbook.Name & \".pdf\" End Sub You will have to change the folder location to use this code. • Convert All Formulas into Values Use this code when you have a worksheet that contains a lot of formulas and you want to convert these formulas to values. 'This code will convert all formulas into values Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub This code automatically identifies cells are used and convert it into values. • Protect/Lock Cells with Formulas You may want to lock cells with formulas when you have a lot of calculations and you don’t want to accidentally delete it or change it. 294 CU IDOL SELF LEARNING MATERIAL (SLM)
Here is the code that will lock all the cells that have formulas, while all the other cells are not locked. 'This macro code will lock all the cells with formulas Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub • Protect All Worksheets in the Workbook Use the below code to protect all the worksheets in a workbook at one go. 'This code will protect all sheets in the workbook Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Next ws End Sub This code will go through all the worksheets one by one and protect it. In case you want to unprotect all the worksheets, use ws.Unprotect instead of ws.Protect in the code. • Insert A Row After Every Other Row in the Selection Use this code when you want to insert a blank row after every row in the selected range. 'This code will insert a row after every row in the selection Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer 295 CU IDOL SELF LEARNING MATERIAL (SLM)
Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Next i End Sub Similarly, you can modify this code to insert a blank column after every column in the selected range. • Automatically Insert Date & Timestamp in the Adjacent Cell A timestamp is something you use when you want to track activities. For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc. Use this code to insert a date and time stamp in the adjacent cell when an entry is made or the existing contents are edited. 'This code will insert a timestamp in the adjacent cell Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> \"\" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), \"dd-mm-yyyy hh:mm:ss\") Application.EnableEvents = True End If Handler: End Sub Note that you need to insert this code in the worksheet code window (and not the in module code window as we have done in other Excel macro examples so far). To do this, in the VB Editor, double click on the sheet name on which you want this functionality. Then copy and paste this code in that sheet’s code window. 296 CU IDOL SELF LEARNING MATERIAL (SLM)
Also, this code is made to work when the data entry is done in Column A (note that the code has the line Target.Column = 1). You can change this accordingly. • Highlight Alternate Rows in the Selection Highlighting alternate rows can increase the readability of your data tremendously. This can be useful when you need to take a print out and go through the data. Here is a code that will instantly highlight alternate rows in the selection. 'This code would highlight alternate rows in the selection Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub Note that I have specified the color as vbCyan in the code. You can specify other colors as well (such as vbRed, vbGreen, vbBlue). • Highlight Cells with Misspelled Words Excel doesn’t have a spell check as it has in Word or PowerPoint. While you can run the spell check by hitting the F7 key, there is no visual cue when there is a spelling mistake. Use this code to instantly highlight all the cells that have a spelling mistake in it. 'This code will highlight the cells that have misspelled words Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If 297 CU IDOL SELF LEARNING MATERIAL (SLM)
Next cl End Sub Note that the cells that are highlighted are those that have text that Excel considers as a spelling error. In many cases, it would also highlight names or brand terms that it doesn’t understand. • Refresh All Pivot Tables in the Workbook If you have more than one Pivot Table in the workbook, you can use this code to refresh all these Pivot tables at once. 'This code will refresh all the Pivot Table in the Workbook Sub RefreshAllPivotTables() Dim PT as PivotTable For Each PT in ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub You can read more about refreshing Pivot Tables here. • Change the Letter Case of Selected Cells to Upper Case While Excel has the formulas to change the letter case of the text, it makes you do that in another set of cells. Use this code to instantly change the letter case of the text in the selected text. 'This code will change the Selection to Upper Case Sub ChangeCase() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub Note that in this case, I have used UCase to make the text case Upper. You can use LCase for lower case. 298 CU IDOL SELF LEARNING MATERIAL (SLM)
• Highlight All Cells With Comments Use the below code to highlight all the cells that have comments in it. 'This code will highlight cells that have comments` Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub In this case, I have used vbBlue to give a blue color to the cells. You can change this to other colors if you want. • Highlight Blank Cells With VBA While you can highlight blank cell with conditional formatting or using the Go to Special dialog box, if you have to do it quite often, it’s better to use a macro. Once created, you can have this macro in the Quick Access Toolbar or save it in your personal macro workbook. Here is the VBA macro code: 'This code will highlight all the blank cells in the dataset Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub In this code, I have specified the blank cells to be highlighted in the red color. You can choose other colors such as blue, yellow, cyan, etc. • How to Sort Data by Single Column You can use the below code to sort data by the specified column. Sub SortDataHeader() Range(\"DataRange\").Sort Key1:=Range(\"A1\"), Order1:=xlAscending, Header:=xlYes End Sub Note that the I have created a named range with the name ‘DataRange’ and have used it instead of the cell references. Also there are three key parameters that are used here: 299 CU IDOL SELF LEARNING MATERIAL (SLM)
• Key1 – This is the on which you want to sort the data set. In the above example code, the data will be sorted based on the values in column A. • Order- Here you need to specify whether you want to sort the data in ascending or descending order. • Header – Here you need to specify whether your data has headers or not. Read more on how to sort data in Excel using VBA. • How to Sort Data by Multiple Columns Suppose you have a dataset as shown below: Below is the code that will sort the data based on multiple columns: Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range(\"A1\"), Order:=xlAscending .SortFields.Add Key:=Range(\"B1\"), Order:=xlAscending .SetRange Range(\"A1:C13\") .Header = xlYes .Apply End With End Sub Note that here I have specified to first sort based on column A and then based on column B. The output would be something as shown below: 300 CU IDOL SELF LEARNING MATERIAL (SLM)
Search
Read the Text Version
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
