Using Spreadsheets to Solve Problems

1.10 Financial Functions

People often use spreadsheets to track a variety of financial information, such as the value of investment portfolios, loan obligations, income, and expenditures. Money is earned on sums invested in savings accounts, certificates of deposit (CD’s), and money market funds. Borrowers pay for the use of money they have borrowed for school loans, mortgages, car payments, or credit card purchases. This charge for money is called interest.

Usually this fee is given as a rate of interest which is then is multiplied by the principal value to calculate the interest fee amount. The principal is the current value of the financial instrument, either a loan or investment. In a finance course, how these interest rates are set is of major import, as well as understanding the time value of money (what you expect to be paid for use of your money) and risk (the uncertainty of getting this money back from the borrower).

In this class we will study how to calculate the effects of applying an interest rate to monies both lent and invested using some Excel tools known as Financial Functions. To do so, let’s first look at how interest is calculated.

Calculating interest

Simple Interest

Again, interest is a fee that is paid for use of someone else’s money. A bank pays you interest on your savings account. You pay interest to your bank for the money they have lent you to buy a car. Interest that is paid solely on the original amount invested or lent is called simple interest. The computation of simple interest is based on the following formula:

Simple interest = Principal * Interest rate per time period * Number of time periods

Here is an example using simple interest: You have invested $1000 in a savings account that pays 5% of the principal annually. At the end of each year you will take out the interest paid. How much interest will you have collected at the end of four years?

Year 1 – Principal $1000 * Interest rate .05 = $ 50

Year 2 – Principal $1000 * Interest rate .05 = $ 50

Year 3 – Principal $1000 * Interest rate .05 = $ 50

Year 4 – Principal $1000 * Interest rate .05 = $ 50

Total 4 year Interest: = $200

Another example would be a loan for $1000 with 5% annual interest due at the end of each year and the original principal amount ($1000) payable at the end of the four years. Here each year the borrower would owe $50, and then at the end of four years owe the original principal amount.

First year: / Interest / $1,000 * 0.05 / = $50
Second year: / Interest / $1,000 * 0.05 / = $50
Third year: / Interest / $1,000 * 0.05 / = $50
Fourth year: / Interest / $1,000 * 0.05 / = $50
Total Debt / =$1000

Notice that in both of these examples the principal, or the amount of the original investment or loan, never changes. Coupon bonds work in this way, where the interest is always removed after each period. However, most financial instruments such as savings accounts, zero-coupon bonds, certificates of deposit, mortgages, and car loans usually assume that the interest from previous periods is either added or subtracted to the principal amount each period.

Compound INTEREST - Savings

Now consider the original example of investing $1000 at 5% annual interest over a period of four years, but this time the interest will be reinvested at the end of each period. In other words, the amount of earned interest will be added to the principal at the end of each period. How will this affect the total interest earned?

When interest earned each period is added to the principal for purposes of computing interest for the next period, this is known as compound interest. As shown in the examples below, the total value of interest payments using compounding is greater than that of the interest payments using a simple interest of the same percentage. Most financial instruments use compounding; these include bank accounts, certificates of deposits (CD’s), loans, etc.

To determine how much interest is earned over a 4-year period, break down the payments by the compounding period, in this case yearly. The principal in year 1 is $1000 which then earns $50 of interest. At the beginning of year 2 the principal is now $1000 plus $50 ($1050) and interest is now computed on this new amount, resulting in $52.50 in interest during year 2. This pattern continues in subsequent years. The total interest earned on this investment is $215.51. This is $15.51 more than if only simple interest is used.

Year 1 – Principal $1000.00 * Interest rate .05 = $ 50.00

Year 2 – Principal $1050.00 * Interest rate .05 = $ 52.50

Year 3 – Principal $1102.50 * Interest rate .05 = $ 55.13

Year 4 – Principal $1157.63 * Interest rate .05 = $ 57.88

Total 4 year Interest: = $215.51

So when calculating compound interest it is critical that the calculations are broken up into the periods of compounding using the corresponding interest rate per period. Otherwise, the correct values for interest paid will not be obtained.

Another example is as follows: Assume that Ying has deposited $1,000 in a credit union, which pays interest at 8% per year compounded quarterly. Determine the amount of money Ying will have on deposit at the end of 1.5 years assuming all of the interest is left in the savings account.

Quarter 1 – Principal $1000.00 * Interest rate .08/year ÷ 4 quarters/year = $ 20.00

Quarter 2 – Principal $1020.00 * Interest rate .08/year ÷ 4 quarters/year = $ 20.40

Quarter 3 – Principal $1040.40 * Interest rate .08/year ÷ 4 quarters/year = $ 20.81

Quarter 4 – Principal $1061.21 * Interest rate .08/year ÷ 4 quarters/year = $ 21.22

Quarter 5 - Principal $1082.43 * Interest rate .08/year ÷ 4 quarters/year = $ 21.65

Quarter 6 - Principal $1104.08 * Interest rate .08/year ÷ 4 quarters/year = $ 22.08

Total Interest: = $126.16

Total savings: = $1126.16

Note that if the annual interest is 8%, the quarterly interest is 8% divided by 4 quarters per year or 2%. Also notice that the compounding has been performed six times, corresponding to the number of quarters in 1.5 years (1.5 years * 4 quarter/year = six quarters). The total amount at end of 1.5 years is $1,000 + $126.16 = $1126.16.

Compound INTEREST - Loans

Loans also work differently than the example given in the simple interest section. Normally a loan is made for an original face amount, the initial principal, at a given interest rate. If the loan is paid off in equal monthly installments, then each month the borrower will pay interest on the remaining principal plus a portion of that principal.

For example, consider a car loan of $10,000 at 12% annual interest compounded monthly with a monthly payment of $888.49 payable over one year. This transaction is illustrated in Figure 1.

In the first month of the loan the accrued interest expense would be $10,000 times the monthly rate of interest. The monthly rate of interest is calculated as 12% divided by 12 months per year or 1% per month. This amount is $100. So of the $888.49 payment, $100 is used to pay the interest expense and $788.49 is applied toward lowering the remaining principal. The new principal at the beginning of period 2 is becomes $10,000-788.49 = $9211.51.

In period 2 the interest expense is calculated by multiplying the new principal $9,211.51 by the 1% monthly rate of interest for an interest expense of $92.12. The amount $888.49 - 92.12 = $796.37 is applied toward reducing the principal. This repeated reduction of principal is illustrated in Figure 1 and is sometimes referred to as an amortization schedule. The loan would be paid off at the point where the remaining principal value is $0.

Using Financial Functions to Calculate Compound Interest

As you can see, the calculation of compounding even for a few periods can become tedious. Imagine the calculation for a 30-year mortgage that is compounded monthly: there would be 12*30=360 calculations. Excel provides a set of built-in functions to perform these calculations. The user need not understand the detailed mathematics or repeat the principal/interest calculations for each period of an investment or loan. You need only to know which function to use and how to use it. The spreadsheet program takes over from there, performing the often complex calculation and returning the result.

The variables


As already seen in our compound interest examples, a financial transaction requires several component pieces of information to calculate interest. These include the original amount of the financial transaction (loan or deposit), an interest rate, the duration of the transaction in terms of the number of times it is compounded, and the ending value of the transaction. Each of these pieces of information is a term in a complex formula which can simulate the step-by-step compounding approach that was just presented:

The good news is you never need write this formula or solve for the variable you need to determine. A set of five functions are available within Excel to do this: PV, RATE, NPER, PMT, FV. Select the function for the value to be calculated and then supply the other four terms as the function arguments. Understanding this complex mathematical formula is not required. What is required is the knowledge of what these terms are and how to apply them correctly.

To explain the meaning of each of these terms, look at the diagram in Figure 2 representing a loan for the amount of $100 payable in equal quarterly installments over a period of two years.

Figure 2

The Present Value of this loan, represented by the term PV, is $100. This is the amount of money (cash flow) into or out of a financial transaction at the beginning of the transaction.

The Rate, represented by the term RATE, is interest rate per period. If the interest rate is 8% per year compounded quarterly, then the rate per period in this transaction will be the quarterly interest rate of 8%/4 or 2%.

The Number of Periods, represented by the term NPER, is the duration of the loan. This is a two-year loan compounded quarterly, so the number of periods is 2 years * 4 quarters per year for a total of 8 quarters.

The Payment, represented by the term PMT, is the amount that is paid in equal installments each period. This payment may include periodic interest and a portion of the principal. If there are 8 periods, then there will be 8 payments of this specified amount.

The Future Value, represent by the term FV, is the final amount (cash flow) into or out of this transaction. In a loan, if the transaction is completely paid off this amount will be zero. If money is put into savings and compound interest accrued, this will be the value at the end of the transaction’s duration including the original principal, any periodic payments, and accrued interest.

Each of these terms can be solved for using the corresponding Excel function where the other four terms being the arguments of that function.

USING the FV function to find future value

Let's take another look at the compound interest example where Ying has deposited $1,000 in a credit union which pays interest at 8 percent per year compounded quarterly. Our goal is to determine the amount of money on deposit at the end of 1.5 years if all interest is left in the savings account.

In this problem the value being sought is the final value of the transaction which is the future value (FV). The inputs are the remaining terms: the RATE is 8% per year compounded quarterly or 2% per quarter, the number of periods (NPER) is 1.5 years times 4 quarters per year or 6 quarters, and the original value (present value PV) of the transaction is $1000. Since there are no periodic payments (PMT), that value is $0/quarter.

The function to calculate Future Value is as follows:

= FV (rate, nper, pmt, pv, type)

Substituting the values from this example into the function gives the formula =FV(.08/4,1.5*4, 0, -1000) resulting value is $1126.16.

Notice two things which may not appear clear in this example:

·  Why is the PV argument (present value) a negative value, -1000?

·  What is the type argument and why is it missing?

Cash flow

To understand why -$1000 was substituted into this formula rather that +$1000, it is necessary to understand how cash into and out of a financial transaction is represented. The FV, PV and PMT arguments are all cash amounts that are either received or paid out. These inputs and outputs are referred to as cash flow. In order for these financial functions to work properly, the computer must understand which amounts are flowing to you or from you. The algorithm used in these Excel financial functions requires that when cash is received it is considered positive cash flow, and when cash is paid out it is considered negative cash flow. In this problem Ying gives the bank the $1000 at the beginning of the transaction. Though the bank account belongs to Ying, the cash has flowed from Ying to the bank and thus is a negative cash flow. At the end when Ying retrieves her principal and interest the monies will flow back to her, resulting in a positive future value.

The type argument

The last parameter in the FV function is type. The type argument designates when payments are made. There are two different values the type argument can be:

·  Type 0 = payments made at the end of the period (default)

·  Type 1 = payments made at the beginning of the period