The world of finance and investments has as its basis the time value of money, the assumption that one pound/euro/dollar is worth more today than it will be sometime in the future. For example, one unit of whatever currency can be invested to give an annual rate of return r and grow to 1 + r after one year. This means that £1 today is worth 1 + r pounds in one year. After two years £1 grows to (1 + r)(1 + r) = (1 + r)2. After n years it becomes (1 + r)n. The rate of return r is a number. If r = 0.1 it is the equivalent of 10 per cent.
The present value PV of an investment with a rate of return equal to r per year grows to a future value FV in the course of n years:
The value of receiving FV in n years’ time is PV:
Example 6.1
If the rate of return is 8 per cent, the present value of receiving €500 in three years is equal to €396.92:
The easiest way to do this in Excel is to use the formula shown in figure 6.1.
Figure 6.1 Present value calculation.
6.1 Present value of a stream of cash flows
If one will receive several payments in the future, e.g. C1 at the end of year 1, C2 at the end of year 2, etc., the total present value is the sum of several individual present values:
Example 6.2
Assume that you will receive $300 in one year’s time, $500 in two years’ time, and $200 in three years’ time. The rate of return is 8 per cent.
The present value of this is:
In Excel this can be calculated using the NPV function, as shown in figure 6.2.
Figure 6.2 Present value calculation.
Example 6.3
Assume that today’s date is 16 July 2011, and that you are going to receive £250 on 15 December 2011, £320 on 21 May 2012, and £500 on 02 April 2014. The rate of return is 8 per cent.
To calculate the present value on paper will be a relatively complicated process. A more appropriate approach is to use Excel’s XNPV function. This is shown in figure 6.3.
The result is a present value of £947.58. Today’s date can be found using the formula TODAY.
Figure 6.3 Present value calculation.
6.2 Present value of perpetuities and annuities
The present value of a payment C, that is paid out each year for ever, and that grows with a growth factor g each year, can be calculated as:
This formula for a growing perpetuity is only valid if r > g. Normally, the growth factor will be based on a price or inflation index. The interest rate r is therefore higher than g.
If g = 0 the growing perpetuity becomes an ordinary perpetuity, and the formula we have for the present value of receiving a fixed payment C every year for eternity:
In Excel, the easiest way to calculate the present value of a growing or ordinary perpetuity is to use their respective formulas C/(r – g) and C/r directly.
An annuity is similar to a perpetuity, but with the payment being made for a specified number of years. The present value of a payment, C, paid out each year for n years and that grows with a growth factor g each year, can be calculated as:
If g = 0 we have the present value of receiving a fixed payment C each year for n years. This is referred to as the present value of an annuity, C:
Here the annuity factor An,r indicates the present value of receiving 1 currency unit each year for n years when the interest rate is r. An,r can be found in the interest tables. The equivalent future value is:
Example 6.4
You have won a lottery and will be receiving E1,000 at the end of each year for the next 20 years. The first payment will be made in one year. The interest rate is 8 per cent.
Figure 6.4 Present value calculation.
The present value of this prize is:
The Excel function PV can be used to calculate this value. See figure 6.4. Note that this function regards PV as a loan or an investment, which results in PV having the opposite sign to the payment.
The future value of the prize is:
The Excel function FV can be used to calculate this value.
6.3 Annuity loans
Equation 6.2 can also describe an annuity loan where PV is the amount borrowed, r is the annual interest rate and n is the number of payments. The constant periodic payment C can be calculated as follows:
Example 6.5
Your firm is granted a four-year annuity loan of $500,000 at 7 per cent annual interest. Payments representing interest and repayment of the principal (amount borrowed) are to be made at the end of each year.
The periodic payment is calculated to be:
It is relatively easy to make a repayment plan in Excel, as shown in figure 6.5. The periodic payment can be calculated using Excel’s PMT function. For each year, interest and principal can be calculated using Excel functions IPMT and PPMT respectively. Note that these functions also give an amount with the opposite sign with respect to the sum borrowed.
Figure 6.5 Repayment plan.
Example 6.6
You have a £900,000 annuity loan at 8 per cent annual interest rate. Interest and principal have to be paid at the end of each quarter (four times a year). You can afford to pay £20,000 each quarter (in interest and principal). How many years will it take before the loan is repaid?
In this case the periodic interest rate is (8/4) per cent = 2 per cent. As shown in figure 6.6, the number of periodic payments can be calculated using the Excel function NPER. The solution is 32.4 periodic payments, which would be rounded up to 33 periods. This is equal to 8 years and 3 months.
Figure 6.6 Calculation of number of periods.
It is quite common to discuss interest rates for periods shorter than a year. Quarterly and monthly rates are the two most common ones.
Assume that a bank lends you money at a nominal annual interest rate r. The bank calculates interest quarterly, i.e. four times each year. Interest, i.e. the price of the loan, is r/4 for each quarter. Under these conditions, in the course of one year with compound interest £1 grows to:
To find out the equivalent annual interest rate, the effective annual interest rate is:
Generally, with a nominal interest rate r and interest compounded m times per year, the effective annual interest rate can be calculated as:
Example 6.7
Consider a loan with a nominal annual interest rate of 8.00 per cent. Interest is to be paid quarterly.
Figure 6.7 Calculation of effective interest.
The effective annual interest rate will be:
This can also be calculated using the Excel function EFFECT as shown in figure 6.7.
Assume that the effective annual interest for a loan is 8.00 per cent and that interest is to be paid quarterly.
The nominal interest rate can be calculated as:
This can also be calculated using Excel’s NOMINAL function.
Problems
6-1. What is the present value of £800,000 received after five years if the interest rate is 7 per cent?
6-2. Calculate the present value of the following cash flow when the discount rate is 12 per cent.
6-3. You will receive £500 in two years, £1,200 in five years, £1,800 in six years, and £1,800 in eight years.
a) Calculate the present value of this cash flow when the discount rate is 15 per cent.
b) You pay £2,000 to obtain the cash flow. Calculate the return on this investment.
6-4. You will receive £8,000 each year for the next eight years. The first amount will be paid in one year.
a) Calculate the present value of this cash flow when the discount rate is 12 per cent.
b) You pay £30,000 to obtain the cash flow. Calculate the return on this investment.
6-5. You will receive £25,000 each year for the next 12 years. The first amount will be paid in one year. This cash flow is increased by a factor g starting in year 2.
a) Calculate the present value of the cash flow when the factor g = 5 per cent and the discount rate r = 15 per cent.
b) You pay £200,000 to obtain the cash flow. Calculate the return on this investment. (The factor g is still 5 per cent.)
c) What must the factor g be set to in b) to obtain a return of 15 per cent on the investment?
6-6. You have invested $120,000 in a security. What return will you obtain if the security pays off $20,000:
a) each year in 10 years?
b) each year in 10 years with an annual increase of 4 per cent each year starting in year 2?
6-7. You borrow $700,000 as an annuity loan over 10 years at 7 per cent interest p.a. Interest and principal are paid at the end of each year.
a) Make a detailed payment schedule for the loan.
b) Assume that you can pay no more than $60,000 each year in interest and principal. How many years will it take for you to pay back the loan?
6-8. For a loan with no fees you pay a nominal annual interest rate of 7 per cent.
a) Calculate the effective annual interest rate assuming that interest is to be paid in arrears four times a year.
b) Calculate the effective annual interest rate assuming that interest is to be paid in arrears 12 times a year.
c) Draw a curve showing the effective annual interest rate as a function of periods per year.
6-9. A loan with no fees has an effective annual interest rate of 7 per cent. Calculate the nominal annual interest rate assuming that interest is to be paid in arrears 12 times a year.
6-10. An insurance company offers a policy for children where one pays $500 on the child’s first seven birthdays. The child will receive $50,000 on its 60th birthday. How low does the discount rate have to be for the policy to be profitable?
6-11. An insurance company offers a policy where you first pay in a certain amount of money each year for 10 years, and then receive the same amount each year for life. You are now 30 years old and estimate that you will die sometime after you are 60. Draw a graph that shows the return of this investment as a function of your life expectancy.