Financial Mathematics



Financial Topics: Interest Rates and Present Value

One of the most common usages of spreadsheet software is financial mathematics. Indeed, spreadsheets were invented as a tool for quick and easy presentation and calculation of financial data. Excel has an abundance of financial functions that perform standard finance calculations. A large number of books explain these functions so here we only show how to enter some of these financial formulas into spreadsheets,

Interest rates and Effective Rates

The most basic question that is asked in financial mathematics is: If I put S0 dollars in the bank today, how much will I have in n years? In EMEA you learn that that depends on the annual interest rate and the length of the interest period. As explained in EMEA, section 1.2 and in chapter 1 of this booklet, this amount is given by:

[pic]

Here r is the annual interest rate, n is the number of interest periods per year and t is the number of years the amount is left in the bank. In order to calculate how much you have after t years, you need to calculate how much you have after nt interest periods.

Example 1

A deposit of £5000 is put into an account earning interest at the annual rate of 9%, with interest paid quarterly. How much will be in the account after 8 years and how many years will it take for the deposit to increase to $15000?

Solution.

Enter the number of years in cell B1, enter the number of interest periods in B2 and enter the annual interest rate, divided by 100, in B3. Then, enter the formula =5000*(1+B3/B2)^(B2*B1) into cell B5. The spreadsheet should look like Figure 1.

[pic]

Figure 1, Compound interest rate in Example 1

Note the formula in the formula bar. The answer, 10190.52, is reported in cell B5. In order to calculate how long it will take before the deposit is £15000, use the Solver. Set the Solver up so that it will set cell B5 to 15000 by changing cell B1. The Solver should look like Figure 2. Click Solve and the solver should report the answer 12.34 in cell B1.

[pic]

Figure 2

Effective Interest Rate.

From the preceding example it is clear that the annual interest rate is not the same as the rate actually received if the interest rate is added more than once a year. When interest is added n times during the year at the rate r/n per period, the effective yearly rate R is:

[pic]

Example 2

With reference to example 1 above, what is the effective interest rate?

Solution: If the spreadsheet is set up as in Figure 1 above, enter the formula =(1+B3/B2)^B2-1 into any cell to get the answer R ( 0.0931.

Exercises

1. Solve Exercise 1 in Section 10.1 in EMEA with Excel.

2. Solve Exercise 2 in Section 10.1 in EMEA with Excel.

3. Exercise 1 in Section 10.1 in EMEA with the Solver. (Hint: Use the solver to solve the equation (1 + r)100 = 100 with respect to r.)

4. You are offered a loan of £1000 at an annual rate of 7.5% interest rate where interest is added monthly. Another bank offers you the same loan with the same annual interest rate, but the interest is added quarterly. After reading EMEA you decide that you should accept the offer where interest is added quarterly. But when you go to the bank to sign the forms, the bank manager tells you that there has been a slight increase in the interest rate. Use the Solver to calculate the highest annual rate of interest, when interest is added monthly, that gives you the same present value as if you paid an annual 9% interest, but where interest is added quarterly.

Continuous Compounding of Interest.

If interest is added continuously, the principal S0 will increase to S0ert after t years. To do calculations with continuous compounding in Excel, use the exponteial function =exp(arg). If a principal, say £5000, is deposited in a bank at 5% interest continuously compounded, then you can calculate the amount in the account after 10 years by entering 5000 in cell A1 and entering the formula =A1*exp(0.05*10) in any cell.

Exercises

1. Solve Exercise 1 in Section 10.2 in EMEA with Excel. (Use the Solver to do (b)

2. Solve Exercise 2 in Section 10.2 in EMEA with Excel.

3. Solve Exercise 4 in Section 10.2 in EMEA with Excel.

Present Value

Above we posed and answered the question: If I save x pounds today, what will I receive in t years if the interest rate is r? Here, we turn the question around and ask which amount x must I deposit today in order to receive K pounds tomorrow if the interest rate is r? The answer, the present value of K, is K(1 + r)-t if interest is added once per year and Ke–rt if interest is added continuously. These computations are straightforward to do in Excel.

Example 3

Let r = 0.05, K = 100000 and t = 10. Find the present value of receiving K at time t if the interest is r.

Solution:

You find K(1 + r)-t by entering the formula =100000*(1+0.05)^(-10) and Ke–rt by entering the formula =100000*exp(-0.05*10). The answers are 100000(1 + 0.05)-10 ( 61391.325 and 100000e-0.05(10 ( 60653.066.

Exercises

1. Solve Exercise 1 in Section 10.3 in EMEA with Excel.

2. Solve Exercise 3 in Section 10.3 in EMEA with Excel. (Use the Solver to solve (a)).

Geometric Series

You can compute the sum of any finite geometric series with Excel as long as the numbers stay within the ranges that are allowed by Excel (see Chapter 1). Infinite geometric series cannot be calculated unless you find some analytical expression that can be used or you come up with a good way to approximate the series.

Example 4.

Let S6 = 2 + 2([pic] + 2([pic] + 2([pic] + 2([pic] + 2([pic], Compute Let S6 directly.

Solution:

Enter 0 into the cell A1 and enter =A1+1 in cell A2. Then, copy the contents of A2 into the range A2:A6. Then enter =2*(1/2)^A1 into cell B1. Then, copy the content of cell B1 into the range B2:B6. The cells in the range B1:B6 now contain each individual element in S6. Finally, enter =sum(B1:B6) into the cell B7. The value that appears in B7 is S6 ( 3.94.

Of course, by using the summation formula for a finite geometric series, [pic] we find the answer in Example 4 somewhat easier: [pic].

Investment projects

When there are successive payments of different amounts or the interest rate varies over time, it is rarely the case that a general formula for the sum of discounted payments can be found. Say we will receive €1000 today, €500 after one year, €1000 after two years, €1500 after three years, but after four years we must make a payment of €3000. What is the present value of this payment schedule if the interest rate is 7%? To answer this question we must compute the sum:

[pic]

One way of calculating this sum is shown in Figure 3. Column A gives the year the payment is received or made, Column B gives the discount factor 1/(1 + r)year. Column C gives the payment made or received at the end each individual year. Receiving a payment “today“ is equivalent to receiving a payment at the end of year 0. The sum in B9 is the present value and indicates that the payment schedule is worth €1276.49.

[pic]

Figure 3, Calculating the present value of an investment project

Exercises

All the review problems for Chapter 10 are suitable. The following exercise may also provide some insight.

Exercise 1.

Consider the case where a kind aunt leaves a nephew a yearly stipend of £1000 as long as he is at university, but limited to a maximum of 10 years, and £10000 the year he graduates. The annual interest rate is 5%. The aunt hopes that this scheme will give the bright, but lazy student an incentive to work hard at university and graduate as soon as possible so that he can receive his graduation bonus of £10000. The student is bright enough to complete his education whenever he wants to.

a)

Calculate the total present value of the aunt’s stipend for all possible graduation dates. Does the stipend provide the student with an incentive to complete his education as soon as possible?

b)

If the interest rate increased to 15%, would this provide more incentive to graduate as soon as possible.

c)

Use the Solver to find the interest rate that makes the present value of graduating the first year equal to the present value of graduating after 10 years.

-----------------------

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download