Financial functions: price, pricedisc, pricemat, pv, rate
Excel Function PRICE
The PRICE() function is used to calculate the price per face value of $100 of a security paying interest.
This requires that the interest paid by the security is periodic.
The Syntax of the function is as follows:
PRICE(settlement,maturity,rate, yield,redemption,frequency, basis)
| Settlement Date | Maturity Date | Rate | Yield | Redemption | Payment mode | PRICE() | Formula |
| 01/02/2017 | 31/12/2018 | 0.08 | 0.1 | 100 | 2 | 96.5790546044462 | =PRICE(L4,M4,N4,O4,P4,Q4) |
| 01/03/2017 | 31/07/2018 | 0.02 | 0.15 | 200 | 1 | 166.49507537164 | =PRICE(L5,M5,N5,O5,P5,Q5) |
| 20/07/2017 | 01/04/2018 | 0.06 | 0.1 | 150 | 4 | 144.007668918963 | =PRICE(L6,M6,N6,O6,P6,Q6) |
| 01/03/2018 | 09/09/2018 | 0.09 | 0.12 | 300 | 1 | 286.47791718946 | =PRICE(L7,M7,N7,O7,P7,Q7) |
Excel Function PRICEDISC
The PRICEDISC() function calculates the price of a discounted security per $100 face value.
This function is similar to the PRICE() except that, this calculates the discounted price.
The Syntax of the function is as follows:
PRICE(settlement,maturity,discount rate,redemption,basis)
| Settlement Date | Maturity Date | Discount | Redemption | Basis | PRICEDISC() | Formula |
| 01/02/2017 | 31/12/2018 | 0.08 | 100 | 1 | 84.7013698630137 | =PRICEDISC(L16,M16,N16,O16,P16) |
| 01/03/2017 | 31/07/2018 | 0.02 | 200 | 2 | 194.255555555556 | =PRICEDISC(L17,M17,N17,O17,P17) |
| 20/07/2017 | 01/04/2018 | 0.06 | 150 | 3 | 143.712328767123 | =PRICEDISC(L18,M18,N18,O18,P18) |
| 01/03/2018 | 09/09/2018 | 0.09 | 300 | 4 | 285.9 | =PRICEDISC(L19,M19,N19,O19,P19) |
Excel Function PRICEMAT
The PRICEMAT() function calculates the price of a security that pays interest at maturity.
This function also takes into account, a face value of $100.
The Syntax of the function is as follows:
PRICE(settlement,maturity,issue,rate,yield,basis)
| Settlement Date | Maturity Date | Issue Date | Rate | Yield | Basis | PRICEMAT() | Formula |
| 01/02/2017 | 31/12/2018 | 01/01/2017 | 0.1 | 100 | 2 | -0.244092867097428 | =PRICEMAT(L28,M28,N28,O28,P28,Q28) |
| 01/03/2017 | 31/07/2018 | 01/03/2016 | 0.15 | 200 | 1 | -14.5207439888209 | =PRICEMAT(L29,M29,N29,O29,P29,Q29) |
| 20/07/2017 | 01/04/2018 | 20/07/2016 | 0.1 | 150 | 4 | -8.89213364903973 | =PRICEMAT(L30,M30,N30,O30,P30,Q30) |
| 01/03/2018 | 09/09/2018 | 01/03/2016 | 0.12 | 300 | 1 | -23.1569677186028 | =PRICEMAT(L31,M31,N31,O31,P31,Q31) |
Excel Function PV
The PV() function is used to determine the present value of an investment.
The calculation is based on a series of future payments.
The Syntax is as follows:
PV(rate,no. of periods, payment per period, future value, type [0 or 1])
| Rate | No. of periods | Payment per period | future value | type | PV() | Formula |
| 0.08 | 60 | 1000 | 0 | 0 | -49318.4333356258 | =PV(L40,M40,N40,O40,P40) |
| 0.1 | 60 | 1500 | 1 | 1 | -71186.9784370162 | =PV(L41,M41,N41,O41,P41) |
| 0.14 | 60 | 2000 | 2 | 0 | -85955.0301444769 | =PV(L42,M42,N42,O42,P42) |
| 0.095 | 60 | 2200 | 3 | 1 | -105583.780863498 | =PV(L43,M43,N43,O43,P43) |
Excel Function RATE
The RATE() function is used to calculate the interest rate payable for a specified loan amount.
This function can also be used to calculate the rate for achieving a target investment amount.
The calculation is performed over a certain defined period.
The Syntax is:
Rate(no.of periods, payment per period, present value, [future value],[type],[guess])
The last three parameters are optional.
| No. of periods | Payment per period | Present value | future value | type | RATE() | Formula |
| 60 | 1000 | 50000 | 0 | 0 | 6.18341316125377E-03 | =RATE(L52,-M52,N52,O52,P52) |
| 60 | 1500 | 60000 | 1 | 1 | 1.49703649906816E-02 | =RATE(L53,-M53,N53,O53,P53) |
| 60 | 2000 | 45000 | 2 | 0 | 4.02892879025226E-02 | =RATE(L54,-M54,N54,O54,P54) |
| 60 | 2200 | 100000 | 3 | 1 | 9.9575220332307E-03 | =RATE(L55,-M55,N55,O55,P55) |