Syntax. PRICE(Settlement,Maturity,Rate,Yield,Repayment,Frequency,Basis)
Definition. This function calculates the price of a fixed-interest security (loan); that is, the purchase price without accrued interest.
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The date when the repayment of the loan certified by the security takes place.
Rate (required) The agreed-upon annual interest rate as the price for the borrowed money.
Yield (required) The market interest rate on the settlement day, at which during calculation of the duration all future payments are discounted.
Repayment (required) The percentage of the par value of a security (as if it was 100 monetary units) at which repayment takes place on the day of maturity.
Frequency (required) Because it is quite common to have fixed-interest securities with several interest dates per year, the required argument Frequency gives you their number. Allowed values are: 1 = annual; 2 = biannual; 4 = quarterly.
Basis (optional) The method you want to use for determining the days in the year according to Table 15-2, shown earlier. If this argument is omitted, Excel calculates with Basis = 0.
The arguments of PRICE() have the following requirements:
Date specifications must not have a time; in other words, decimal places are truncated. The arguments Frequency and Basis are also truncated to integers.
If date arguments cannot be resolved to a valid date, the PRICE() function returns the
#NUMBER!error.Yield and Rate require nonnegative numbers. Repayment requires positive numbers. Otherwise, PRICE() returns the
#VALUE!error.If Frequency cannot be resolved to 1, 2, or 4, and Basis cannot be resolved to a number from 0 through 4, PRICE() returns the
#NUMBER!error. The same happens when the settlement date is later than the maturity.
Background. To implement the finance mathematical benefit principle
Payment of the creditor = Payment of the debtor
for the start of the transaction, the price of a fixed-interest security (loan) plus potential accrued interest equals the cash value of the debtor’s future payments certified in this security. The price is the percentage of the security’s par value; as if the par value was 100 monetary units.
The cash value calculation is no problem if the purchase date (change of ownership) of a security with an annual interest payment is the same as the day of the interest payment. In this case, only the whole year needs to be taken into consideration. A situation in which the change of ownership takes place between interest due dates or in which interest payments take place several times a year is not so easy. In finance mathematics there are several ways to deal with a year that is divided into parts. The best known methods are Moosmüller and Braess/Fangmeyer as well as ISMA. ISMA stands for International Securities Market Association, an organization that sprang from the Association of International Bond Dealers (AIBD).
The ISMA method returns the same as PRICE() for a one-time interest payment per year and can easily be recreated for biannual and quarterly interest payments by using PRICE().
In Excel terminology and for the functions derived from it, the benefit principle of creating cash values (discounting future payments) for price calculation by Excel works as follows:
COUPNUM is the number of interest payments after the purchase, COUPDAYSNC is the number of days until the next interest payment date, and COUPDAYBS is the number of days since the last interest payment date.
If Frequency = 1, this formula also represents the ISMA method. If Frequency equals 2 or 4, Excel calculates with an even distribution of the yield over the periods within the year. The ISMA method, however, uses a period apportionment following the correlation between nominal and effective interest, which you can find in the function descriptions for EFFECT() or NOMINAL():
In case of several interest payments per year, you can therefore calculate the price by using the ISMA method with PRICE(). Before you do this, however, you need to convert the “effective” yield into a nominal yield (by using the function NOMINAL()).
Caution
The explanations shown here apply only when more than a single interest payment date is left before maturity. Otherwise, Excel does not calculate according to the formula for the price shown here, but uses a fractional yield instead of the fractional duration. PRICE() then returns the same result as PRICEMAT().
Example. The following examples describe how to use the PRICE() function.
Annual Interest Payment. A 4.500 percent federal loan initiated in 2003 had a yield of 0.61 percent on August 31, 2010. It matures on January 4, 2013. How much do you need to pay if you want to purchase securities at a par value of $1,000.00?
Note
This exercise is not a typical situation, because prices are created by supply and demand, and the yield is then calculated from the price. However, the intention is to demonstrate the principle with a security whose behavior you can read daily in the newspapers or on the Internet.
Such price calculations might happen if an issuer issues a security, gives it a “round” nominal interest (1/8 intervals), and wants to launch it with certain yields with price bonuses or discounts.
If you want to determine the ISMA price with the day counter method day exact/day exact without using the function PRICE() (in other words, if you want to learn what exactly PRICE() is converting), you can prepare a worksheet as shown in Figure 15-7.
Type in the known data into the first seven cells from C5 through C11. In preparation, enter the day counter mode with 4 (this corresponds to Basis = 4 in all of the integrated functions that are used).
Then use =COUPNCD(C5,C7,4,C11) to determine the next interest payment date:
April 1, 2011. The formula =COUPDAYSNC(C5,C7,4,C11) helps you count the days up
to this interest date: 124. To calculate the accrued interest, you need the days
since the last interest payment date (=COUPDAYBS(C5,C7,4,C11) counts 236 here) so
that you can use =C8*C15/C16*C9 to determine an amount of $29.50. The number of
remaining interest dates, which you need so that you can discount the future payments, is determined
with =COUPNUM(C5,C7,4,C11) and returns 3.
Now let’s look at discounting. The function PV() helps you here. You can use =PV(C10,C18-1,- C8*C9,-C8*C9,1) as a yield calculation to determine the cash value of future interest payments (the result is $134,18) and as a compound interest calculation for discounting the repayment amount with =PV(C10,C18-1,0,-C6*C9) to $987.91.
You now need to determine the sum of the two cash values with the ISMA method over the
fractional duration of 124 of 365 days with a fractional exponent in the interest formula:
=(C20+C21)/(1+C10)^(C14/C16) returns $1,119.74.
The price is the percentage (that is, a number based on a par value of 100) that must subtract the value of the accrued interest from the cash value you just calculated: =(C23-C17)/C9*100.
The result is the same as with =PRICE(C5,C7,C8,C10,C6*100,1,C11): 109.027.
Note that because of the low yield, there can be deviations from the calculated price on the respective day (rounding errors).
Biannual Interest Payment. There are only a few loans that have more than one interest payment date per year. In the sample files accompanying this book, the loan of the previous example was therefore given a fictitious biannual interest payment so that the calculations could be performed. The Excel price has a different number of decimal places than the ISMA price. If you want to determine the ISMA price, you need to divide the market interest yield evenly over the periods with NOMINAL(yield,frequency) and use the function PRICE().