# Building Financial Functions into Excel 2010

July 2011

## Excel 2010 Financials Cookbook

 Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel

(For more resources on this subject, see here.)

Till now, in the previous articles, we have focused on manipulating data within and outside of Excel in order to prepare to make financial decisions. Now that the data has been prepared, re-arranged, or otherwise adjusted, we are able to leverage the functions within Excel to make actual decisions. Utilizing these functions and the individual scenarios, we will be able to effectively eliminate the uncertainty due to poor analysis. Since this article utilizes financial scenarios for demonstrating the use of the various functions, it is important to note that these scenarios take certain "unknowns" for granted, and makes a number of assumptions in order to minimize the complexity of the calculation. Real-world scenarios will require a greater focus on calculating and accounting for all variables.

# Determining standard deviation for assessing risk

In the recipes mentioned so far, we have shown the importance of monitoring and analyzing frequency to determine the likelihood that an event will occur. Standard deviation will now allow for an analysis of the frequency in a different manner, or more specifically, through variance. With standard deviation, we will be able to determine the basic top and bottom thresholds of data, and plot general movement within that threshold to determine the variance within the data range. This variance will allow the calculation of risk within investments.

As a financial manager, you must determine the risk associated with investing capital in order to gain a return. In this particular instance, you will invest in stock. In order to minimize loss of investment capital, you must determine the risk associated between investing between two different stocks, Stock A, and Stock B.

In this recipe, we will utilize standard deviation to determine which stock, either A or B, presents a higher risk, and hence a greater risk of loss.

## How to do it...

We will begin by entering the selling prices of Stock A and Stock B in columns A and B, respectively:

Within this list of selling prices, at first glance we can see that Stock B has a higher selling price. The stock opening price and selling price over the course of 52 weeks almost always remains above that of Stock A. As an investor looking to gain a higher return, we may wish to choose Stock B based on this cursory review; however, high selling price does not negate the need for consistency.

1. In cell C2, enter the formula =STDEV(A2:A53) and press Enter:

2. In cell C3, enter the formula =STDEV(B2:B53) and press Enter:

We can see from the calculation of standard deviation, that Stock B has a deviation range or variance of over \$20, whereas Stock A's variance is just over \$9:

Given this information, we can determine that Stock A presents a lower risk than Stock B. If we invest in Stock A, at any given time, utilizing past performance, our average risk of loss is \$9, whereas in Stock B we an average risk of \$20.

## How it works...

The function of STDEV or standard deviation in Excel utilizes the given numbers as a complete population. This means that it does not account for any other changes or unknowns. Excel will use this data set as a complete set and determine the greatest change from high to low within the numbers. This range of change is your standard deviation. Excel also includes the function STDEVP that treats the data as a selection of a larger population. This function should be sed if you are calculating standard deviation on a subset of data (for example, six months out of an entire year).

If we translate these numbers into a line graph with standard deviation bars, as shown in the following screenshot for Stock A, you can see the selling prices of the stock, and how they travel within the deviation range:

If we translate these numbers into a line graph with standard deviation bars, as shown in the following screenshot for Stock B, you can see the selling prices of the stock, and understand how they travel within the deviation range:

The bars shown on the graphs represent the standard deviation as calculated by Excel. We can see visually that not only does Stock B represent a greater risk with the larger deviation, but also many of the stock prices fall below our deviation, representing further risk to the investor.

With funds to invest as a finance manager, Stock A represents a lower risk investment.

## There's more...

Standard deviation can be calculated for almost any data set. For this recipe, we calculated deviation over the course of one year; however, if we expand the data to include multiple years we can further determine long-term risk. While Stock B represents high short-term risk, in the long-term analysis, Stock B may present as a less risky investment. Combining standard deviation with a five-number summary analysis, we can further gain risk and performance information.

(For more resources on this subject, see here.)

# Analyzing benefits between interest and payment investments

Determining the interest and principal of a loan over the course of the life of the loan is an important function to assist in making decisions of whether the use of a loan is a good use of capital.

As a financial manager, you have \$100,000 to spend on the purchase of land to be developed. Although you have the money to purchase the land, we must forecast the potential return on investment in the purchase and whether purchasing via a loan presents a better return. In five years, the land will potentially appreciate approximately \$15,000.

In this recipe, you will learn to use Excel interest and payment functions to determine the amount of interest and payments you will pay within a loan for a specific amount of time.

## How to do it...

First, we will need to gather the parameters of the loan:

1. Enter principal and interest as shown in the following screenshot (we will assume a 4.5% fixed interest rate):

Next, we will assume a 20-year term and calculate the payment.

2. In cell B4, enter the term as 20.
3. In cell B5, enter the formula =PMT(B3/12,B4*12,B2) and press the Enter key:

We now have the payments for the loan. We know, for the sake of the example that the land will be bought by a builder in five years, so we will only need to carry the note for 60 payments.

4. In B6, enter the formula =B5*60 and press Enter:

Carrying this loan of \$100,000 for 5 years will require the expenditure of \$37,958.96.

Now, we must determine how much profit can be made if we use the cash for another purpose other than for purchasing the land. We can invest the \$100,000 earning 5% interest and continue to reinvest the interest over the five years.

5. In cell D2, enter the formula =IPMT(0.05,1,1,100000) and press Enter:

6. In cell D3, enter the formula =IPMT(0.05,1,1,100000-D2) and press Enter:

7. Continue to enter formulas for each of the remaining years, continuing to include the previous year's interest to compound the interest within the initial balance:

Finally, we show that by compounding the interest for the initial \$100,000 we make \$27,628.16 over the course of five years.

We know, for the sake of this example, that in five years, the land will be worth \$15,000 more than what it is now. Therefore, if we pay for the land in cash, in five years, we will make \$15,000.

However, if we purchase the land via a loan and invest the \$100,000 elsewhere we have:

While the interest gained from the investment of the \$100,000 was initially more than the profit from purchasing the land in cash, overall, due to the payments, purchasing the land in cash presents the better use of money.

## How it works...

This recipe utilizes several functions. Beginning with the payment function, we provide the function several pieces of information collectively known as PMT(Interest rate, Term, Principal).

In the function, we divide the interest by 12 and multiply the term by 12 in order to calculate the payments monthly.

In the interest function, we calculate the interest earned for the \$100,000 investment.

The formula =IPMT(0.05,1,1,100000) entered assumes the 5% annual interest, a number of payments of 1, to account for the initial deposit, the term of 1 to calculate annually, and finally the principal amount. The remaining year formulas utilize this same method; however, the principal includes the addition of the subsequent year's interest amount to account for reinvesting the interest back into the principal.

Finally, we simply arithmetically calculate the net profit from the known information.

## There's more...

While the information presented within this recipe provides real-world percentages, other loan terms such as interest only will reduce the amount of payment expenditure showing that the loan may be the better method for maximizing investment potential.

(For more resources on this subject, see here.)

# Calculating the number of payments in a loan

When purchasing equipment or other items utilizing a loan, it is important to know how many payments it will take to pay off the item completely. Granted, loan information will often provide a minimum payment to pay off a loan over a set term; however, to maximize the amount of capital, it is important to know how much quicker a loan will be paid off with a higher payment.

In this recipe, you will learn to use payment functions to calculate the number of payments to pay off a loan amount, including interest.

## How to do it...

We will begin by entering all applicable loan information into the appropriate cells of the worksheet:

1. In cell A6, enter the title, # of payments, and in cell B6, enter the formula =NPER(B4/12,B5,B3) and press the Enter key:

Excel now shows that it would take just over 47 months to pay off the loan with the current payment schedule.

A financial manager may now adjust the payment information in order to adjust the payment schedule.

2. Change the payment from \$100 to \$300:

With these changes, the number of payments has now dropped to just over 16 months. This change will drastically reduce the amount of interest that is accrued on the principal balance and allows much more capital to be allocated to other uses.

## How it works...

The NPER function in Excel utilizes the following criteria:

=NPER(Rate, Payment, Principal)

For rate, we utilize the interest listed divided by 12 in order to reduce the interest to monthly values from the annual interest rate.

NPER will calculate the payment for an investment as long as the interest is fixed, and the frequency of payments is fixed.

## There's more...

In this recipe, we utilize changes to the payment amount to affect changes in the number of payments. Depending on the type of investment, changes to rate and principal will also allow modifications to the number of payments.

# Determining the difference between effective and nominal interest rates

Interest rates are used in many places within finance. An investment or financial account may yield interest or a loan may charge interest as the cost of loan. Stated interest rates provide you the annual interest; however, they do not account for the interest compounding on a monthly basis, as is the case in most situations.

As a financial manager, you must determine the true cost of an investment or rather the effective cost of compounding annual interest on a monthly basis. An investment requires an annual interest rate of 15%; we must determine the true interest.

In this recipe, you will learn to use Excel functions to determine the true cost of an investment.

## How to do it...

We must enter the investment information on the Excel worksheet in order to determine the effective interest rate:

1. In cell B2, enter the annual interest rate.

The stated annual interest rate is also known as the nominal interest rate.

2. In cell B3, enter the number of times per year the interest is compounded on the principal of the investment. (In most situations, this will be 12 for a monthly compounding.)

3. In cell B4, enter the formula =EFFECT(B2,B3) and press Enter:

We can now see that although the stated interest rate for the investment is 15%, the effect of monthly compounding has created an effective interest rate of just over 16%.

## How it works...

When annual interest is compounded on a monthly basis, or any period other than yearly, the extra sum of the calculated interest builds to an amount higher than the original interest rate.

The Excel function EFFECT is entered with the following parameters:

=EFFECT(Nominal interest, Number of times compounding occurs each year)

## There's more...

Now that the effective rate has been calculated, it is the effective rate not the nominal rate that should be used to calculate the true cost of profit from the investment interest.